Prioritizing Stored Procedures

Question

Prioritizing Stored Procedures

Shaun Dayaram

SSC Rookie

Points: 30
More actions
February 2, 2012 at 5:33 am

#267490

HI
I there a way in SQL 2008 to prioritizing stored procedures, like DB2.
Example, we have a sp that update details and another that updates transactions. During peak period we want to sp that updates transactions to have priority over the update details.
If not available in 2008, will something like this be available in 2012.
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 1

I'm not aware of anything like this in 2012. To my knowledge the best you could do is use the resource governor to try to limit the one you don't want to get resources.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Enterprise Edition has a feature called Resource Governor which allows you to restrict resources that I'm sure could group then into two different workloads, however it depends what you mean by prioritise.

Do they both touch a different set of tables and it's literally CPU/memory/IO contention that you're trying to manage, or is this more about locking?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

It sounds almost like something you'd use resource govenor for, except that is for connections not procedures. Maybe worth investigating

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shaun Dayaram SSC Rookie Points: 30 More actions · Answer 4

Thanks guys,

What we trying to do is give certain sp's priority over others.

The sp's could be accessing the same tables.

But some have a higher importance with regards to CPU/memory/IO contention with a little little blocking.

Will Resource Govenor assist ?

Thanks again

HowardW One Orange Chip Points: 29109 More actions · Answer 5

It won't help with blocking at all if they're accessing the same tables, in fact it would potentially make it worse.

It could help with physical resource contention if you're able to seperate out the workloads at the connection level (e.g. the login used, host that the connection comes from etc.). But if they all come from the same host under the same login, then I don't think you can. You also need Enterprise Edition.

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 6

I agree with everyone else. Resource Governor is going to get you close-ish, to what you're looking for, but it's not going to do specifically what you need. And, it's rather permanent. If you put it in place, it governs the resources while it's enabled, period. It doesn't selectively do what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning