SQL Server Performance Monitoring Tools

1.            SQL Server 2000

1)    Query Analyzer

2)    SQL Server Profiler

3)    SQL Trace

4)    Index Tuning Wizard

5)    Performance Monitor

6)    Activity Monitor

 

2.            SQL Server 2005 (basically the same as SQL Server 2000)

1)    Query Analyzer – in SSMS now

2)    SQL Server Profiler

3)    SQL Trace

4)    Index Tuning Wizard – in DTA now

5)    Performance Monitor

6)    Activity Monitor – now location

 

3.             SQL Server 2008 – Data Collector/MDW, and Extended Event Added

1)    Data Collector and Management Data Warehouse

a.   What’s for?

To collect performance-related data from multiple sources from SQL Servers, store it I a central data warehouse, and present the data in SSMS, automate the collection of the critical performance data.

b.   How to do it?

·       Define the data collection sets

o   Disk Usage

o   SQL Server Activity

o   Query Statistics

·       The runtime component dcexec.exe is used to load and execute the SSIS packages that are part of a collection set. It can in two collection an upload modes:

o   Noncached – collection and update are executed on the same schedule. That is, collecting the data as scheduled and uploading it immediately

o   Cached – collection and upload are performed on different schedules

·       SSIS Packages are used to collect and upload the data. Two key components

o   Data collection

o   Data upload

·       Management Data Warehouse database – the central repository for the data collection

o   Data Collected

o   Views

o   SPs

·       MSW reports- built in to SSMS

 

2)    SQL Server Extended Events (No GUI in SQL Server 2008/R2)

a.   What is it?

A general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application

b.   What’s for in SQL Server?

You can use Extended Events for a wide range of monitoring and troubleshooting scenarios. The following scenarios illustrate a few of the situations where Extended Events can provide valuable data for resolving issues in such areas as:

·       Troubleshooting the cause of working set trimming.

·       Troubleshooting excessive CPU usage.

·       Troubleshooting deadlocks.

·       Correlating request activity with Windows ETW logs.

c.   Key components - packages

Type of Packages for Extended Events in SQL Server 2008

·       package0 - Extended Events system objects. This is the default package.

·       sqlserver - SQL Server related objects.

·       sqlos - SQL Server Operating System (SQLOS) related objects.

·       SecAudit – Security Audit events.

Objects defined in a package for Extended Events in SQL Server 2008

·       Events

·       Targets

·       Actions

·       Types

·       Predicates

·       Maps

d.   How to do it - using T-SQL

-- Create Event Session

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test_session')

    DROP EVENT session test_session ON SERVER;

GO

 

CREATE EVENT SESSION test_session

ON SERVER

    ADD EVENT sqlos.async_io_requested,

    ADD EVENT sqlserver.lock_acquired

    ADD TARGET package0.etw_classic_sync_target

        (SET default_etw_session_logfile_path = N'C:\demo\traces\sqletw.etl' )

    WITH (MAX_MEMORY=4MB, MAX_EVENT_SIZE=4MB);

GO

 

4.             SQL Server 2008 R2 - SQL Utility Added

a.   What’s for?

To monitor specific performance metric for one or more instances in a single view from a single instance

b.   How to do? – Configure Utility Explorer

·       Create a utility control point (UCP)

·       Connect to an existing UCP

·       Enroll instances of the SQL Server into the UCP

 

5.            SQL Server 2012 - SQL Server Extended Events – GUI Added

 

6.             SQL Server 2014 – No Changes?

 

7.             Some Third-Party Tools

1)    SQL Monitor from Red Gate - Performance monitoring, alerting, and diagnosis for SQL Server

http://www.red-gate.com/products/dba/sql-monitor/ 

 

2)    SQL Diagnostic Manager from Idera - performance monitoring, alerting and diagnostics

http://www.idera.com/productssolutions/sqlserver/sqldiagnosticmanager 

 

3)    Spotlight on SQL Server Enterprise from Quest - for Operational Monitoring, Diagnostics, Administration and Automated Tuning for SQL Server

http://www.quest.com/spotlight-on-sql-server-enterprise/ 

 

4)    DiaB from DBAInABox LLC – for SQL server performance tuning, SQL server performance dashboard, Query tuning, SQL server performance history, SQL replication monitor, SQL database monitoring, SQL analyzer, SOX compliance, with tools designed to: scan, alert, and investigate, SQL server failures.

http://www.dbainabox.com/download.html 

 

5)    ProActive DBA SQL Capture from White Sands Technology provides continuous network-based monitoring of all SQL executed against your database server.

 

Major benefits include:

·        Captures all SQL sent to the database server

·        Network-based capturing does not use polling or tracing, incurs no overhead in the database server

·        Powerful reporting summarizes and analyzes captured SQL

·        Monitors exact end-user response times down to the millisecond

·        Alerts on long-running SQL and end-user response times

·        Analyze SQL to identify potential table scans

·        Login-based reporting

 

http://www.whitesands.com/sql-capture/ 

 

6)    SQL Health Monitor from ManageEngine (free)

http://www.manageengine.com/sql-performance-monitor/sql-server-monitoring-index.html