SQL Server Profiler for the Developer#

This is one of those “I always wanted to write this” sessions about a bit of software I think not enough people know about - the SQL Server Profiler.

Profiler is your friend - it shows you what arrives at SQL Server from your application. And, believe it or not, what arrives there is not always what you sent. Sometimes middleware messes with your SQL.

Profiler can also help you trace down tricky things, like where deadlocks are happening. A deadlock occurs when two connections each hold one resource that the other needs to finish a transaction. Neither one will finish unless the other gives up its resource. When SQL Server detects a deadlock, it picks a loser, failing that transaction with a 1205 error. The other connection can then complete its transaction.

Obviously, the best way to avoid deadlocks is to not ever do that - always lock resources in the same order. I make a list of the sequence in which I'll modify tables and then always write my stored procedures to do updates in that sequence. That way, stored procedures can block each other, but not deadlock. However, this still doesn't get rid of all deadlocks.

And the problem is, the error you get for deadlocks is a bit vague - the error message returns what the competing SPID was, which would typically indicate the other computer who's transaction succeeded, but not what table was involved in the deadlock. In the session (and included in the attachment for this blog entry) is the sample code to show a deadlock in Profiler and then you can use the Object ID information around the deadlock to query sysobjects and find what table/resource was involved in the deadlock.

Also in the sample are some queries for testing out the Index Tuning Wizard, part of the Profiler and a tool that will look at the queries in a Profiler Trace file and decide if any new indexes would benefit performance. It'll also recommend removing indexes that aren't ever used.

Finally, there's my little VB6 sample app that's good for playing with ADO 2.x to see how OLE DB can mess with your SQL - in the session I demonstrated how OLE DB actually converts your SQL statement into a cursor to fulfill your request for a modifyable server-side recordset.


Profiler.zip (375.31 KB)
Monday, September 20, 2004 3:43:25 AM (Pacific Standard Time, UTC-08:00) #    Comments [0]  | 


Comments are closed.
All content © 2020, Richard Campbell
On this page
This site
<August 2020>
Blogroll OPML

Powered by: newtelligence dasBlog 1.9.7067.0

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Send mail to the author(s) E-mail

Theme design by Jelle Druyts

Pick a theme: