SQL Server – Baseline and Performance Optimization

optimizationperformancesql server

I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance.

Could anyone help with this or point me to where I can possibly get a helpful information around this topic?

Best Answer

Troubleshooting Performance

It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads.

SELECT TOP 50 qs.creation_time
, qs.execution_count
, qs.total_worker_time as cpu
, qs.total_elapsed_time as duration
, qs.total_logical_reads as reads
, t.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
ORDER BY qs.total_worker_time DESC

CPU should roughly equal Duration, or C = D

CPU * 100 should roughly equal Reads, or *C*100 = R*

If C < D, then we have a Waiter

If C = D, but C*100 > R, then we have Computation

If C = D and C*100 = R, then we have a Runner

Waiter means we are waiting on something: I/O, blocking, latches, CPU.

Computation means we are doing something other than Reads: CPU bottleneck, spinlock, query compilation, UDF/function - computation, calculation, SQLCLR/XP, SQL Server code

Runner means nothing without a baseline. You would have to know that the query normally takes less time to run: Outdated stats, missing indexes, poorly designed query, suboptimal plan, parameter sniffing, optimizer timeout

Data to collect

Waiter: wait stats, query_plan, perfmon, profiler, blocker script/per_stats script, DMVs, Xevent

Computation: spinlock stats, query plan, profiler, set statistics time, statistics IO, query plan XML, Trace flags, Xperf, Kernrate, F1 Visual Studio, Query text, perfmon

Runner: query plan, schema, query text, statistics info, missing indexes info, index fragmentation

There are many DMVs, 3rd party software, and scripts that will help you gather this data.

Related Question