How to fine tune a Select / Update or Delete statements in SQL Server
This is a Flowchart of how will you fix or fine tune Select / Update or Delete statements in SQL Server
If customer comes and says that a specific SQL statements is running from long however it should actually complete in few seconds then give a try to this approach of troubleshooting first before looking in depth
1. Figure out which is the problematic active query
sp_whoisactive link to download http://whoisactive.com/downloads/
or
execute active_query.sql (Query below)*
2. Once you are aware of active query, check for its status. if status is running, and query is not blocked or not waiting for any resource then most probably the query plan is not good.
3.capture the estimated plan of the running query using following script.
SELECT getdate() as "RunTime", st.text as batch,
SUBSTRING(st.text,statement_start_offset / 2+1 ,
( (CASE WHEN a.statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE a.statement_end_offset END) - a.statement_start_offset) / 2+1) as current_statement
,qp.query_plan, a.* FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) as qp
where a.database_id = '8'
order by CPU_time desc
4. Then go through the plan and rather than spending much time on plan, first do a update statistics
for all the tables which are part of the concerned query.
4. we can figure out the concerned tables by checking the XML plan.
5. once update statistics is done, ask customer to rerun the query if the query is DML type otherwise if it is a select query then you can also run. or execute the concerned job
6.update statistics for the tables should be done with full scan
use <dbname>
go
update statistics <tablename> with fullscan
or
more granular like (if the table is huge).
if update statistics for big table is taking huge time then you can individually update stats using following query.
SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications",
'UPDATE Statistics ' + OBJECT_NAME([sp].[object_id]) + ' ( ' + [s].[name] + ')' + ' with fullscan'
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'<tablename>');
7. If above steps do not resolve the problem then there is a need for extensive performance troubleshooting which may involves looking into
Missing indexes
Parameter sniffing
Optimizer timeout
Execution warning (hash warning,exchange spell and sort warning)
Implicit conversion because of mismatch in the data type
<Script for active_query.sql>*
SELECT s.session_id
,r.STATUS
,r.blocking_session_id 'blocked by'
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'Wait Time (in Sec)'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
Customer will be happy. :)
Thanks for reading..
Thanks for reading..