Below is a portion of trace from a SQL Server 2005. The client is a Java based application making plain T-SQL calls (No stored procedures involved. I do not have access to the code, but it is taking more than 2 minutes to do an update task involving few tables. SQL parameterizes the dynamic sql and assigning an ad hoc number for the proc and calling the proc several times. I failed to understand why the call is taking different durations each time it is called. The prepared SQL call is as below declare @p1 int set @p1=929 exec sp_prepexec @p1 declare @p1 int set @p1=929 exec sp_prepexec @p1 output,N'@p1 bigint,@p2 bigint,@p3 bigint',N' select colE,colF,colG, ColF from MyTable where colA = @p1 and colB = @p2 and colC = @p3 ',10002490,1251,7540 select @p1 MyTable has a composite primary key on colA , colB and colC &colD and it is clusered on this key and it has less than 100 records. ------------------ Now the proc 929 is called several times Event Name: RPC Completed Text data: exec sp_execute 929,10002490,1251,10017 Duration: 204 --- After some time in the same trace , call to the same proc takes more time Event Name: RPC Completed Text data: exec sp_execute 929,10002490,1251,10017 Duration: 970567 --- I need to know what is causing the proc to take more time 970567 ms when it is called second time.
Does the table have any indexes? Especially on the columns in the WHERE clause of the UPDATE statement? When was the last time the table was reindexed? Also, how big is the table?
Yes. The where clause is part of the indexed primary key in that order. I already mentioned it in my post.
http://msdn2.microsoft.com/en-us/library/ms175848.aspx http://vyaskn.tripod.com/analyzing_profiler_output.htm
If Duration was 970567, what were the values for CPU, Reads, Writes?If they were low, then it was a long running lock (use Deadlock Detector http://www.sqlsolutions.com/products/sql-deadlock-detector/index.html or something like that to record locks/deadlocks). Otherwise, it is probably value with irregular selectivity causing a query to behave this way. Like, there is a SSN, which is unique, but there is a value 'N/A' for temporary visitors, so when you get such value as a parameter, the statically prepared execution plan becomes not efficient as it affects much more records then expected.
You could use native SQL methods to do so, http://msdn2.microsoft.com/en-us/library/aa213030(sql.80).aspx and as usual you need to have 3605 and 1204 trace flags in the startup of SQL server. This will write the deadlock information to the SQL Server errorlog. This deadlock information will gve the complete info on the resources that were a part of the deadlock event.