MSSQL Performance Tuning
StatisticsParser
http://statisticsparser.com/StatisticsParser
SET STATISTICS IO, TIME ON;- Click Query, Include Actual Execution Plan
- Look at the Messages tab
- Near the top SQL Server gives a row of messages for each table. Copy & Paste that into statistics Parser
- "Logical Reads" generally has the biggest impact on query performance
Long Running Queries
Long Running Queries
SELECT st.text, qp.query_plan, qs.* FROM (SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300Bibliography
Bibliography
https://www.microsoft.com/en-us/research/uploads/prod/2020/06/Anytime-Algorithm-of-Database-Tuning-Advisor-for-Microsoft-SQL-Server.pdfhttps://www.erikdarlingdata.com/starting-sql/starting-sql-measuring-a-query/
Optimizer Planshttp://statisticsparser.com/https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/https://www.brentozar.com/blitzcache/parameter-sniffing/https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql (
sys.dm_exec_query_stats)