SQL Server Performance

HOW TO CHECK SQL Server Performance Tuning for Stored Procedures

Discussion in 'Getting Started' started by rajeshp, Jul 2, 2008.

  1. rajeshp New Member

    Hi, I'm creating a stored procedures of an application that. that will generate some bussiness logic then how can i check performance of stored procedures like speed,accuracy etc. Any ideas or direction will be much appreciated
  2. madhuottapalam New Member

    Performance is always relative. There should be some benchmark to compare the performance. Ie. today the sp is performence is slower/better than ..... .
    Generally in performance tuning you can check the following steps
    (a) Data fragmentation. if fragmented defag it (use DBCC SHOWCONTIG and DBCC DBREINDEX or DBCC INDEXDEFRAG)
    (b) Update the statistics
    (d) Create proper index according to the query
    (d) check for recompilation
    these are the very highlevel check list. There are many articles on performance tuning in this site. Please have a look on those
    SQL Server Blog
    SQL Server 2008 Blog
  3. Luis Martin Moderator

    You can use Management Studio and :
    set statistics io on
    your sp
    set statistics io off
    to find out reads ahead, etc.
    Also DTA to check indexes.
  4. preethi Member

    this isa very high level overview of and action plan:
    Check the Stored procedure code for obvious issues: Some fonthe common issues I have seen are, Cursors, Mixing of DDL and DML statements (With SQL server 2005, this is al esser problem) usage of UNION instead of UNION ALL, unnecessary DISTINCT, ORDER BY
    2. Execute the stored procedure with Sample parameters. (supplying the best parameters is a little tricky at times) Monitor the time taken and IO cost involved. There are various methods to do this: Please refer this article http://sqlserveruniverse.com/content/PERF0600104282008MeasuringPerformanceOfStoredProcedures.aspx
    3. Get the execution plan: Instead of Graphycal plan, use STAT STATISTICS PROFILE ON before the Execution of the procedure so that you can get the execution plan in table format. I will look for these columns: Rows, Executes, StmtText, & TotalSubTreeCost. Additionally, I look for high cost operations in the inner loops. (Higher level in the tree structure)
    4. On the StmtText, I also check for certain operations: Table Scan, Clustered Index Scan, Too Many RID Lookup /Clustered Index Lookup, Join methods. If I feel that it could be done in a different way with less cost, I would try to rewrite code to do that,
    This is only an overview of few things. When you start doing it, you will find issues you can resolve.
    Hope this helps
  5. JKAU New Member

    You can profile it to see duration, Reads, writes and CPU. Also when you run query plans, check each components rowcounts and cost. You can decide based on all these whether your SP is performing well.
  6. satya Moderator

    Class room question?

Share This Page