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 |
|
techies
Starting Member
2 Posts |
 Posted - 2008-12-18 : 17:48:32
|
| Hello! I had a question regarding optimizing a SQL database with the use of nolocks.The database in question has many concurrent users constantly accessing and modifying data. Due to this the server chugs when there are multiple access attempts to a locked table/view and the users get queued. Is there any way to determine which views/stored procedures/whatever are taking up the most system time and resources? I've used cleartrace to get the queries that are affecting the system, but have no idea where to find these queries within their respective views/sp's. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-12-18 : 18:35:01
|
| Nolock shouldn't be used unless you don't care about dirty reads. We can't use the nolock hint as we need accurate data at all times. Once you have a trace file/table, you can then query it to see the worst performers:SELECT Duration/1000000.0 AS DurationInSecs, TextDataFROM ...WHERE TextData IS NOT NULLORDER BY Duration DESCIf you need to correlate them back to a parent object, you'll probably need to search syscomments. Once you know what queries are causing the issue, check the execution plan for them to see if you can make any improvements. You might be missing a crucial index, statistics might be out of date, or perhaps the index is heavily fragmented.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
General Optimization/nolock question