DaveWentzel.com            All Things Data

Profiler Tips

RPC Event vs Language Event...what's the difference
Plan Guide Successful and Plan Guide Unsuccessful Events are new in Denali.  These are helpful to determine if your plan guides are really being utilized the way you hope they should be.  
Sort Warning Event
A sort warning occurs whenever sql server cannot perform a sort in RAM and the data has to be written out to tempdb first.  The solution may be a rewrite of the query, more RAM, faster tempdb disk, etc. 
Attention Events
Attention events indicate a query was "cancelled" by the client.  This could be due to the driver timeout being exceeded or the user actually cancelling the query. 
This becomes problematic when done inside a transaction.  If the client doesn't issue a rollback the connection may be returned to the pool and if it isn't reset using sp_reset_connection you can see some obviously bizarre results. 
Load Test Scripts
...before a load test:

--run before the load test to zero out cumulative counters

use master


DBCC SQLPERF ('sys.dm_os_wait_stats',CLEAR)


DBCC TRACEON (1204,-1)


DBCC TRACEON (3605,-1)



In 2005 there is the Blocked Process Report which can be captured in a trace and can trigger code to handle notification or remediation.

But it only detects blocking on resources that support deadlock detection, so again, not all blocking can be caught, but probably everything that you can control will be.

Wait types that handle this:


CXPACKET, EXCHANGE, other parallel query issues


CLR stuff

You need to set a threshold using something like:


sp_configure 'show advanced options',1




sp_configure 'blocked process threshold',2




I see a lot of SET FMTONLY ON in my trace...
If you call SQLDescribeCol or SQLDescribeParam before calling SQLExecute, you generate an extra round-trip to the server.  When SQLDescribeCol is called, the driver removes the WHERE clause from the query and sends it to the server with SET FMTONLY ON to get the description of the columns in the first result set returned by the query. 

EXEC master.dbo.sp_configure 'allow updates', 0;


Some additional differences
Default Trace
written to DATA directory
written to LOG directory
fill rollover at 5MB
sp_trace_create (8)
works in 2000/2005
2005 only
  look at the settings via SELECT * FROM fn_trace_getinfo(default);





Add new comment