Mike's Database Place

MSSQL Performance Tuning

Databases

SQL Server

MSSQL Diagnostics & Tuning

MSSQL Wait Events

First Responder Kit

PAL

Performance Analysis of Logs

MSSQL Optimizer

StatisticsParser

http://statisticsparser.com/

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

statisticsparser

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 > 300

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)

Page updated

Google Sites

Report abuse