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
