SQL Server Performance

SP with different run times - help, please !

Discussion in 'Performance Tuning for DBAs' started by calarcon, Jan 10, 2003.

  1. calarcon New Member

    In the corporative ERP, there is a SP that sometimes (more and more) takes on minute and means in executing itself.

    With the analyzer I capture ' text' of the SPCompleted event of those SP that surpass the minute of execution. If that same ' text' I send it in the Query, single it takes 3 seconds to me (and it is the logical thing, since quite simple 2 the single SP does selects in 2 tables with index on the fields of select).

    I do not capture at the moments that take minute and means in executing nor re-compilations, nor Inter-lockeds.

    To what this difference in run time can be due?. During that minute and means, the use of reading of the RAID where they are located the data is of the 100%, which stops the rest of processes.

    Thanks for your aid, and you pardon my lamentable English.
  2. Chappy New Member

    If the server is capable of executing the query in 3 seconds, there are a few reasons why its not always this fast in production.

    Personally, I think I would suspect issues with locks, try running a perfmon on the lock wait time so you can see if this is a possible cause next time the query appears in profiler. Investigate possible use of NOLOCK hint on your query if this turns out to be the case.

    Also worth checking the disk queue length and cpu, perhaps there are periods of intense IO or processing which you are not fully aware of, and could adversely affect your query. Im not sure if you stated the RAID drive is spending excessive time reading, if so then obviously this is not good. Check buffer cache hit ratio and paging usage to see if the problem could be minimised by adding more memory.
  3. josephobrien New Member

    I had a similar situation.<br />I had two similarly named stored procs in this application <br />“sp_X_XX_XXXXXXXXXXX_XXXXXX_XX” and <br />“sp_X_XX_XXXXXXXXXXX_XXXXXX_XX _FIRSTIN”. <br /><br />I had been tracing the just the RPC:Completed and SQL:BatchCompleted Events in an attempt to get the duration of the different calls. So what I believe happened was that the TextData for the RPC:Completed event truncated the name of the Stored procedure call. <br /><br />So that “sp_ X_XX_XXXXXXXXXXX_XXXXXX_XX _FIRSTIN” appears in the <br />Trace as “sp_X_XX_XXXXXXXXXXX_XXXXXX_XX”. <br /><br />“sp_X_XX_XXXXXXXXXXX_XXXXXX_XX_FIRST” is a bad stored proc that is doing a table scan, while “sp_X_XX_XXXXXXXXXXX_XXXXXX_XX” is very well behaved. <br /><br /><br />How I found this was that I added the SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tatement Completed Event and found the SQL statement (there was only one) that appeared in the trace right after the RPC<img src='/community/emoticons/emotion-7.gif' alt=':S' />tarted and before RPC:Completed for the bad RPC call – was from the <br />sp_ X_XX_XXXXXXXXXXX_XXXXXX_XX _FIRSTIN stored proc. <br /><br />I ran this through Query Analyzer and found the same exact resource consumption of the RPC:Completed event. <br /><br />

Share This Page