Cool Tips‎ > ‎

Performance Measurement, Analysis and Optimization






Search SQL Server Error Logs    

Programmatically search through one or all  of the SQL Server error log files .

2000, 2005, 2008, 2008R2, 2012 


Get CPU Usage for last 4 hours   

Retrieves SQL Server, Idle, and Other Process processor percentages from the 'System Health' Extended Events Ring Buffer.
2000, 2005, 2008, 2008R2, 2012  


Kill all Processes in a DB

Kill all user processes in a specified database. Will fail if the current connection is in the specified database or if no database is supplied.

2000, 2005, 2008, 2008R2, 2012 


Kill all Orphan Processes

Kills all user processes on a server with last_batch time older than a supplied number of hours.  Defaults to 24 hours

2000, 2005, 2008, 2008R2, 2012


Get Server Uptime

Retrieve the last time the SQL Server service was restarted, calculate uptime, and indicate whether SQLAgent service is running

2000, 2005, 2008, 2008R2, 2012


Get Database Buffer Cache Size    

Retrieves the Number of pages in the buffer pool with database content. 

2000, 2005, 2008, 2008R2, 2012


Retrieve Key Default Trace Information

Several code snippets to  manage and retrieve SQL Server default perfmon trace information

2000, 2005, 2008, 2008R2, 2012


Server-Side Profiler Traces

A set of tables and SPs to schedule, run and summarize multiple SQL Server Profiler server-side traces. 

2000, 2005, 2008, 2008R2, 2012


SQL Profiler Traces - revised

 A standalone SP to allow the invocation of a SQL Server server-side trace.  The previous version above was a little cumbersome.  This version is more portable and easier to run across multiple servers.

2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017


SQL Profiler Traces - Login Events only

 A cut-down server-side trace SP that monitors only login events

2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017


Get Slow Running Queries

 Lists top 100 slow running queries.  Uses IO and duration history to identify those queries that have degraded the most

2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017