October 15, 2008 at 1:33 pm
Would you be allowed to make changes to the SQL code?
Probably not.
Would you be allowed to make changes to the indexing?
Probably not, however I do defrag/rebuild them from time to time
If not, would you be able to recommend/suggest to the nameless consultants that they fix their code?
We have requested this.
If you see certain queries involved in blocking frequently, then post them, the table structure and the indexes here and we can make suggestions.
What's the best way to go about this?
October 15, 2008 at 2:03 pm
Start a new thread for each query. Give the query and the indexes on those tables. We'll ask for more data.
Go with your gut on the ones you think are the worst problem. Or run Trace/Profiler and see which ones take a long time and are run often.
You can move tempdb, and that could help. However I'd also be sure you have the backups on either c: or the log disk
October 15, 2008 at 2:03 pm
Andy Leffler (10/15/2008)
What's the best way to go about this?
The code I gave you will show which processes are involved in the blocking. Use the DBCC command that I gave you to get the exact SQL that they are running. Monitor that at regular intervals while you're having blocking problems and store the results somewhere. Excel even, if it works for you.
If you see the same SQL query or stored procedure again and again in the output of the DBCC command, then it's a good candidate for a little optimisation.
Post any queries that you see again and again here, we can have a look at them and give you some comments that you can send on to the nameless consultants.
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
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply