Cool Tips‎ > ‎

Performance Measurement, Analysis and Optimization

 

Title

Description

Applicability

 

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


 

 


Comments