SQL Server 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 X 100 should roughly equal Reads, or C X 100 = R

If C > R, then we have Computation

If C = D and C X 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s