Combining R and SQL Server to diagnose performance bottlenecks

Using clustering algorithms to analyse index usage data from SQL Server’s DMVs & simplify complex performance investigations.

CXPACKET

Analyzing I/O Performance for SQL Server

Inadequate storage is one of the most common bottlenecks seen in subsystem performance. There are a number of reasons for poor storage performance, but measuring it and understanding what needs to be measured and monitored is always a useful exercise.

Query Performance Report

The procedure created by this script rolls up the collected query execution statistics and groups them daily by the plan hash value.

SQL Server 2014 Checklist for Performance

A checklist to help optimize the performance of SQL Server

Execution Plan reuse

Using DBCC DROPCLEANBUFFERS When Testing Performance

Over the years I've had many discussions with other SQL Server Database Administrators as to whether or not you should have the database buffer pool loaded with data pages or empty when testing queries for performance. This tip will take a look at the effects on query performance for both cases by using the DBCC DROPCLEANBUFFERS statement to empty the buffer pool.

SQL Server CPU Usage History Histogram

Returns a summary of CPU usage by SQL Server over the last 4 hours (default).

Using the sample values collected in the dm_os_ring_buffers dmv, this query will show CPU usage by the SQL Server process for the last for hours even if you have no other benchmarking activities in place on your server yet.

Hash Match

SQL Server Health and History Tool