Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rphsql
Starting Member
1 Post |
 Posted - 2012-01-24 : 09:27:27
|
| Is there a way to have SQL transaction priorities set for different types of transcations? This would be for a read only database. For example, a query to pull commisions for sales people would be a high priority over a query to pull an employee absentee port. And if so, is there a queuing type mechanism to prevent queue starvation?Thank you. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-01-24 : 10:36:19
|
| The only reliable mechanism to support such a thing is Resource Governor: http://msdn.microsoft.com/en-us/library/bb933866.aspxKeep in mind it's only usable in Enterprise Edition. There's also quite a bit of setup involved, and it only applies to connections, not the queries being run. You'd have to use specific logins or other connection-related features in order for the queries to be governed. Also remember that resource governor applies to the entire server, not just one database.Another option is to use the MAXDOP query hint: http://msdn.microsoft.com/en-us/library/ms181714.aspxThis limits how many processors can be used in parallel to process a query. You have very fine control, but the queries have to be written to use the hint, and it won't necessarily make them run faster or slower. It also doesn't control how much of a particular CPU or how much memory is used; only resource governor can control that.By the way, a read-only database will not enlist transactions, since its data can't be modified. |
 |
|
|
|
|
|
Create transaction priorities - High, Med, Low