Level 400 Performance tuning

Troubleshooting Happening Locking Facts

http://blog.csdn.net/envykok/archive/2010/11/04/5986732.aspx

1) Sp_WHO2
    DBCC INPUTBUFFER(BLK_SPID) - tell us what is the executing statement inside
2) Sp_lock
3) Sys.dm_tran_locks
4) sys.dm_exec_requests
    SELECT session_id, request_id, blocking_session_id,database_id,user_id,sql_handle,t.text
    FROM sys.dm_exec_requests
        outer apply sys.dm_exec_sql_text(sql_handle) t
    WHERE blocking_session_id > 1

Tuning a specific query

1) Memory utilization
    SELECT granted_memory_kb, used_memory_kb, max_used_memory_kb
    WHERE session_id=XX
    
2) Block (Wait Type)
e.g:
Session 55:
    WHILE 1=1
    BEGIN
        EXEC PRC_TEST
    END

Session 56:

    SELECT status, wait_type
    FROM sys.dm_exec_requests
    WHERE session_id=55
    
3) CPU (If wait type is 'CXPACKAGE' - Parallel Query)
    --Which core currently query running
    select scheduler_id from sys.dm_exec_requests
    where session_id=55
    
    --task information
    SELECT task_state, exec_context_id, parent_task_address,task_address
    FROM sys.dm_os_tasks
    WHERE session_id =XXX

    --waiting information
    SELECT wait_type, exec_context_id, blocking_exec_context_id,
    waiting_task_address, blocking_task_address
    FROM sys.dm_os_waiting_tasks
    
    --CPU running time and total schedulared_time, if it is big different meaning CPU beening block
    SELECT CPU_time, total_schedulared_time FROM sys.dm_exec_sessions
    WHERE session_id=55
    
    --Execution Plan
    ? - Each core has nearly amount of data rows to process??

4) "Sort" slow
    ? - split to tempdb??
    Way1: tempdb got read/write
        SELECT num_of_reads, num_of_bytes_read,num_of_writes,num_of_bytess_written
        FROM sys.dm_io_virtual_file_stats(db_id('tempdb'),1)
    Way2: SQL profiler
        --Error and Warning Event
            --Sort Event
    Way3: Execution Plan
        --Estimate row size ~? Actually row size
    Way4: Memory grant
        SELECT granted_memory_kb, used_memory_kb, max_used_memory_kb
        WHERE session_id=XX

Q&A

1) How to solve when paraller query each CPU core has different amount of row to process?
    Answer1 : rewrite the query
            e.g.: use "in" instead of sub query or inner join
    Answer2 : CPU partition - soft-numa
            use different TCP/IP ports to run different query

2) How to solve parameter sniffing issue happen in store procedure? - same as "sort" or "hash" join split to tempdb issue
    Answer1 :
          Add 'optimized for' since from sql server 2005 fixed parameter value
          e.g: (notes: be ware of achieve - remove the old data like @i=400000 will affect the performance)
            SELECT *
            FROM Test
            WHERE columnA>@i
            ORDER BY columnB
            Option (maxdop 1, optimized for (@i=400000))
    Answer2 :
            Add Option recompile
            e.g: (notes: it is more suitable to use in report service)
            DECLARE @ExecStr nvarchar(4000)
            SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)'
            EXEC sp_executesql @ExecStr, N'@lastname varchar(15)', 'Anderson'
    Answer3 :
            Extra logic : base on parameter range use different query - one query with different comment will create different execution plan
            e.g:
            IF @data>'2010-01-01'
            BEGIN
                --Execution plan 1
                EXEC sp_executesql N'--<2010-01-01
                SELECT *
                FROM Test_Table'
            END
            ELSE
            BEGIN
                --Execution plan 2
                EXEC sp_executesql N'-->2010-01-01
                SELECT *
                FROM Test_Table'            
            END
    Answer4:
            Plan guide
            To create a plan guide example (http://technet.microsoft.com/en-us/library/bb895390.aspx)

               1.In Object Explorer, connect to an instance of Database Engine and then expand that instance.
               2. Expand Databases, expand the AdventureWorks2008R2 database, and then expand Programmability.
               3.Right-click Plan Guides, and then click New Plan Guide.
               4.In Name, enter ForceseekPlan as the name of the plan guide.
               5.In Statement, enter the following Transact-SQL statement. This is the statement against which the plan guide is to be applied.

                  SELECT p.LastName, p.FirstName, HumanResources.Employee.JobTitle
                  FROM HumanResources.Employee
                  JOIN Person.Person AS p ON HumanResources.Employee.BusinessEntityID = p.BusinessEntityID
                  WHERE HumanResources.Employee.OrganizationLevel = 3 ORDER BY p.LastName, p.FirstName

               6.In Scope type, select SQL as the type of entity in which the Transact-SQL statement appears.
               7.In Hints, enter the following OPTION clause.

                  OPTION (TABLE HINT(HumanResources.Employee, FORCESEEK))
               8.To create the plan guide, click OK.
               
3) tempdb - table varaible vs. temp table

Statistics:
        temp table - YES
        table varaible - NO; SO estimate execution plan maybe wrong, so will always use nested loop join
                               Estimated row number in execution plan for table variable is always 1
Index:
        temp table - YES
        table varaible - NO (only PK)
Cause store procedure recompile?
        temp table - MAYBE - http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p3.aspx
        
        * If a temporary table is dropped and then recreated between calls to a stored procedure which references the temporary table, this will cause a recompile next time the stored procedure references the table. It is recommended that each session keeps its temporary tables created permanently throughout the life of the session, using TRUNCATE instead of dropping and re-creating.
        * If statements that contain the name of a temporary table refer to a table created by a calling or called stored procedure or in a string execute by using sp_executesql or the EXECUTE statement.
        * If any statement that contains the name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger.
        * If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table.
        * If any statements that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources).
        * If any statement that creates a temporary table appear in a control-of-flow statement.
    
        table varaible - NO
    The Profiler SP:Recompile event identifies recompiles.
    The way to avoid temp table cause recompile - using 'KEEP PLAN' Hint (http://support.microsoft.com/kb/243586)
    e.g:
        select count(*) from #t
        option (KEEP PLAN)
    
4) merge join, hash join and nested loop join

Hash JOIN is useful when the large percent of rows contributes to the resultset.
Nested Loop JOIN is useful small amount rows

Performance Monitor

Database server:

 

Web server: