Measuring SQL Performance

  • For starters, you could try adding the "WITH RECOMPILE" option to the sprocs just to test the water. A better way would be to actually assume that parameter sniffing is, in fact the problem, and fix it on the more troublesome code. Last, but certainly not least, it to check the execution plans under different conditions...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • hi but we are talking about many stored procedures, how would i know the ones that have the have the parameter sniffing problem? do i have to check each one? i have a query i found which gives me the number of recompilations, writes, reads, can this query help me find the stores procedures which have this issue?

    this is the query:

    SELECT

    substring(text,qs.statement_start_offset/2

    ,(CASE

    WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2)

    ,qs.plan_generation_num as recompiles

    ,qs.execution_count as execution_count

    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time

    ,qs.total_worker_time as cpu_time

    ,qs.total_logical_reads as reads

    ,qs.total_logical_writes as writes

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    LEFT JOIN sys.dm_exec_requests r

    ON qs.sql_handle = r.sql_handle

    ORDER BY 3 DESC

    can i run this query when i am having the performance problem to see which stored procedure/query could be the one with the problems? we are talking about 30-40 transactions per min.

  • I'd suggest running a trace while the events are occurring. Collect the data out to a file. You can then import it back into a table on a different server and run aggregate queries to identify which procedures are causing the most problems, say the top 10. Focus on them. Get them fixed and running well, then go and collect another trace. Repeat ad infinitum.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • thank you 🙂

  • hi just a quick question, i was able to identify some stored procedures with parameter sniffing and was able to fix them. After updating this stored procedures, should i do an update statistics with full scan to my DB?

  • That really depends on the system. Most of our systems work just fine with the sampled statistics update, but we have a few tables on a few systems that need an occasional full scan. I wouldn't recommend running full scan all the time, but an occasional use of it sure won't hurt. That assumes you're doing it off-hours if you're going to full scan the entire db.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • thank you, so its not really needed to do an update statistics after these stored procedures parameter sniffing issues has been fixed? would it make any difference? i don't have my update statistics scheduled until the weekend, or should i run it now?

  • When you deploy the stored procedures, they'll use the statistics to compile a new plan. If the statistics are maintained well enough for normal purposes, they'll be fine for this. No, I wouldn't say you need to do a special update just because you've tune the queries.

    That's assuming that you know that your statistic maintenance routines are good.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • THANK YOU!

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply