SQL Server Blog

Performance Tuning - Tips

Whats Running (Detailed)


SELECT
   r.session_id,
   r.request_id as session_request_id,
   --r.connection_id,
   r.status,
   s.host_name,
   c.client_net_address,
   CASE WHEN s.login_name = s.original_login_name THEN s.login_name ELSE s.login_name + ' (' + s.original_login_name + ')' END as login_name,
   s.program_name,
   db_name(r.database_id) as database_name,
   r.command,
   --r.sql_handle,
   --r.statement_start_offset,
   --r.statement_end_offset,
   --r.plan_handle,
   substring(st.text,r.statement_start_offset/2 ,(CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement,
   st.text as query_text, 
   qp.query_plan as xml_query_plan,
   r.start_time,
   r.total_elapsed_time as total_elapsed_time_ms,
   r.cpu_time as cpu_time_ms,
   r.wait_type as current_wait_type,
   r.wait_resource as current_wait_resource,
   r.wait_time as current_wait_time_ms,
   r.last_wait_type,
   r.blocking_session_id,
   r.reads,
   r.writes,
   r.logical_reads,
   r.row_count,
   r.prev_error,
   r.nest_level,
   r.granted_query_memory,
   r.executing_managed_code,
   r.transaction_id,
   r.open_transaction_count,
   r.open_resultset_count,
   r.scheduler_id
   --r.user_id,
   --r.percent_complete,
   --r.estimated_completion_time, -- This is an internal, MS only.
   --r.task_address,
   --r.text_size,
   --r.language,
   --r.date_format,
   --r.date_first,
   --r.quoted_identifier,
   --r.arithabort,
   --r.ansi_null_dflt_on,
   --r.ansi_defaults,
   --r.ansi_warnings,
   --r.ansi_padding,
   --r.ansi_nulls,
   --r.concat_null_yields_null,
   --CASE r.transaction_isolation_level
   --   WHEN 0 THEN 'Unspecified'
   --   WHEN 1 THEN 'ReadUncomitted'
   --   WHEN 2 THEN 'ReadCommitted'
   --   WHEN 3 THEN 'Repeatable'
   --   WHEN 4 THEN 'Serializable'
   --   WHEN 5 THEN 'Snapshot'
   --   ELSE CAST(r.transaction_isolation_level AS VARCHAR(32))
   --END as transaction_isolation_level_name,
   --r.lock_timeout,
   --r.deadlock_priority,
   --r.context_info,
FROM
   sys.dm_exec_requests(NOLOCK) r
      LEFT OUTER JOIN sys.dm_exec_sessions(NOLOCK) s on s.session_id = r.session_id
      LEFT OUTER JOIN sys.dm_exec_connections(NOLOCK) c on c.connection_id = r.connection_id       
      OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st 
      CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE
   --r.status NOT IN ('background','sleeping') AND 
   c.session_id <> @SPID
/*
--KEY WAIT RESOURCE
SELECT 
 o.name AS TableName, 
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID 
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID  AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594040811520
--PAGE WAIT RESOURCE
SELECT DB_NAME(68)
DBCC traceon (3604)
GO
DBCC page (68, 1, 492478) --Database_id,file_id,page_id 
--OBJECT WAIT RESOURCE
SELECT DB_NAME(5)
SELECT OBJECT_NAME(981083477) 
--OBJECT: 5:981083477:20 
--OBJECT: 5:981083477:0 
--OBJECT: 5:981083477:26 
SELECT * FROM sys.dm_exec_query_memory_grants
*/

3 Ways I like to SQL Tune

1) Query Tuning

Look at Actual Execution Plan (if possible otherwise esitmated)--Missing index?
Look at where clause, joins.. (Is there an index on those columns)
Look at selected column.. (can these be included on index to create covering)
Look at tables involved.. sp_help.. understand table/index structure(PK/Clustered/HEAP?)
Look at sys.indexes (anything similar to tweak.. any hypothetical indexes? --BAD)

Define potential indexes
Test by both running forcing with hint (old vs new) and check Overall Batch %
Compare both using statistics io

2) WorkLoad Tuning

DTA - Plan Cache Analysis

DTA - Query from SSMS

3.1) DMV Tuning - System DMVs

Wait Stats - dm_os_wait_stats (Clear Waitstats - DBCC SQLPERF(Waitstats,Clear)
Perfmon Stats - dm_os_performance_counters (Tuning Insert Statements - How many page splits?)
Index Stats - dm_db_index_usage_stats

3.2) Query DMVs

sys.dm_exec_requests

Missing Indexes
                
                    --Missing indexes in Current Database
                    Select 
                        user_seeks * avg_total_user_cost * (avg_user_impact*0.01) [IndexAdvantage],
                        migs.last_user_seek,
                        mid.statement,
                        mid.equality_columns,
                        mid.inequality_columns,
                        mid.included_columns,
                        migs.unique_compiles,
                        migs.user_seeks,
                        migs.avg_total_user_cost,
                        migs.avg_user_impact
                    FROM sys.dm_db_missing_index_group_stats as migs
                    INNER JOIN sys.dm_db_missing_index_groups as mig ON migs.group_handle = mig.index_group_handle
                    INNER JOIN sys.dm_db_missing_index_details as mid ON mig.index_handle = mid.index_handle
                    WHERE mid.database_id = DB_ID()
                    ORDER BY 1 DESC
                            
        

3.2) Index DMVs

sys.dm_db_index_usages_stats

sys.dm_io_virtual_file_stats

Useful Tips

CHECKPOINT - Clears the dirty pages out of the buffer cache (move them out to the IO subsystem)

Clear out 2 main areas of sql server memory reservation

a) DBCC FreeProcCache - Clear Procedure Cache and remove all plans (whole instance)

b) DBCC FlushProcinDB(dbid) - Drop specifc plan

c) DBCC DropCleanBuffers - Clear out the data buffers **Dont do this in Production**

SET STATISTICS TIME ON -Elapsed Time and IO Time

SET STATISTICS IO ON -Scans, Logical Reads (Memory), Physical Reads (IO Disk)

SET SHOWPLAN / EXECUTION PLANS -

Execution Plans - Red Flags

-Table Scans (Bad)
-Clustered Index Scan (Bad)
-Lookups
-Spools (Something getting buffered out to TempDB)
-Parallelism -CXPACKET wait sympton of underlying issue
-Range Scan can be good
-Estimated estimated plan vs Actual estimated plan (Differences mean very bad statistical info / Parameter Sniffing)
-Physcial Reads (why not in buffer) - Lack of Memory
-Missing Indexes / Dont have Statistics / Auto Generated Statistics
-Hash Joins / Merge Joins - do bunch of sorts in TempDB
-Implicit Data Conversions
-Cursors: http://bit.ly/AB-cursors --> Set Based / Windows functions fastest - Ok for Running Totals (better than self joins)
Can improve by using LOCAL FAST_FORWARD
-Page Life Expectancy (relative)
-NOT IN / LEFT OUTER JOIN is null --> NOT EXISTS is better -> EXCEPT new functionality (adds distinct sort operator can add performance issue)
-IN / EXISTS / APPLY / JOIN /CTE --> EXISTS uses alot less IO as can sort circuit.
-UNION ALL faster as UNION does distinct sort

7) Optimising for SELECT instead of DML -Make sure you have a full / representatble workload when adding an index -Cache data needed for report. -Cost to achieve Benefit -Keys and Constraints added when doing DML which do not appear at all when doing SELECTs (Triggers included) 8) Unwanted Recompiles IN memory -> No -> Load MetaData -> Compile - > Optimize - > Execute IN memory -> Yes -> Execute Causes -OPTION RECOMPILE -SP_RECOMPILE -Dont use at procedure level (only use at statement level) -Plan aged out of memory -Interleaved DDL and DML -Big Changes since last execution --Shcema changes to objects in underlying code --New/Updated index statistics --sp_configure will flush plan cache (true for max server memory) - myths 9) Kitchen Sink Procedure -Many optional parameters to satify a variety of search conditions -Dynamic SQL is often the best route here -Especially if optimize for ad hoc workloads is enabled -(Stored a stub/fingerprint in plan cached) -sp_executesql ATsql, N'ATCustomerID INT, ATOrderDate DATE', ATCustomerID, ATOrderDate -sp_executesql vs EXEC (sp_ promotes bteeer plan re-use, encourages strongly typed parameters instead of concat string) -can use recompile in dynamic sql 10) Comma-Delimited parameters -String splitting expensive (even using CLR) -Table-valued parameters are typically a better approach 11) Implicit Conversions -SQL Server has to do alot of extra work /scans when conversion operations are assumed by SQL programmer -Happens al lthe time with data types you'd think wouldnt need it.. (between date types and character types) -http://bit.ly/15bDRRA -http://bit.ly/13io1f (Type precedence call also have impact) Ian Stirks Column Mismatch Utility - http://www.sqlservercentral.com/articles/Administration/65138/ Jonathan Kehayias plan cache analyzer - http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx Jonathan Kehayias index scan study - http://www.sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs 12) Tempoarary Structures Temp Table Temp Variable Stored in? Tempdb Tempdb Statistics Yes No (1 row) Indexes/Keys Yes 1UK/PK only Truncate Yes No Recompiles Yes No Parallelism Yes No Metadata Overhead Low Lowest Lock Overhead Normal Lowest 13) UDFs -Most scalar and multi-statement table-valued UDFs can be evaluated for each row -Move the function to be inline. -Be very cautious using (make sure not being invoked for every row in the result set)