SQL Server Performance

Help analyzing the trace

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by araskas100, Sep 10, 2007.

  1. araskas100 New Member

    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.
  2. Luis Martin Moderator

    Did you take a look of execution plan?.
  3. ndinakar Member

    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?
  4. araskas100 New Member

    I checked teh execution plan I captured in the trace 'show plan text event' and it used index seek.
  5. araskas100 New Member

    Yes. The where clause is part of the indexed primary key in that order. I already mentioned it in my post.
  6. Luis Martin Moderator

    Check for locks (using profiler or QA) when you run the sp.
  7. araskas100 New Member

    Did that, there were no blocks or deadlocks.
  8. Luis Martin Moderator

    Are you running the sp in server, ws. If ws what OS?.
  9. satya Moderator

  10. evilDBA New Member

    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.
  11. satya Moderator

    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.

Share This Page