SQL Server Job or User Priority

[jrwinterburn]

Morning all.

I know this is a long shot, and probably not possible (as I have hunted high and low), but does anyone know if it is somehow possible in either MS SQL Server 2000 or even 2005 to set a priority for SQL jobs, or for SQL users?

The problem I have is that I need to run reports against my database which at times, causes the database to slow right down and even time out. I have tuned and optimised it as much as possible, so the only thing I can do now is try to prioritise the jobs (or the user who runs the jobs) so that the amount of CPU/RAM and SQL locks it uses are always in a priority lower than that of the sa user, who runs the main workings of the database.

Is this possible? Or is there another way around this?

Thanks in advance,

Jon

 

[Catadmin]

I know of no way to set a priority level for a job or a user. The only think of is an "outside of SQLServer" solution.

I know in AD you can set up times in which your users can and cannot log in to the domain. You might be able to set up new user accounts, add those into SQL and give those accounts the "run job" permissions, taking away the same permissions from the current logins. Then force the end users to log into the new accounts at certain times of the day to run the jobs.

It's a bulky solution, but it's better than nothing.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

There is no way to prioritize. What isolation level are the reports running under? Are you using the nolock table hint on every table?

As reports are read only they should be run using an isolation level that won't lock other users. Reports should not be taking locks as they aren't changing data (this isn't the default, and your reports need to be setup to do this).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jrwinterburn]

Hi and thanks to both of you for your helpful answers. Having spent a couple of days reading into isolation levels, I have found out the cause of the problems, and have now resolved it.

The issue (if anyone else ever has this problem) is that my reports are GT Crystal and I was using a COM+ component that was called by a SQL job to query the tables.

Although MS SQL isolation level is default to READ COMMITTED, the default (and in fact only) level of isolation in COM+ 1.0 is SERIALIZABLE (equivalent to HOLDLOCK). Therefore, the component was locking the tables being updated, causing deadlocks.

However, a way around it, was for me to specify:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

...at the top of each stored procedure called by the component - this then overrides the SERIALIZABLE isolation level.

Crazy of MS to only allow one isolation level! Apparently this is fixed in COM+ 1.5.

Thanks again!

Jon