Collect SQL Server/Process Information - Quickly

Rudy Panigas, 2011-04-21 (first published: 2009-09-03)

If you need to get information out of your SQL server (2005 or 2008) quickly, execute the stored procedure and you will get a detailed report for the following areas

- Buffer statistics - Memory statistics - General statistics - Locks statistics/Totals for Locks statistics

- TempDB statistics - Totals of Database statistics - Plan Cache statistics - Transactions statistics

- SQL error statistics - SQL statistics (SQL compilations/recompilations) - Wait statistics - SQL Execution statistics

This script displays data from the dynamic management view called: sys.dm_os_performance and presents it in a report. I recommend that you look closer to this view for all your statistical needs.

If you find other information that you want to include, just modify this script to reflect the additional information.

Hope you find this script useful.

-- __ ____ -- _________ _/ / / _ \____ ____ -- / ___/ __ `/ / / / \ / _ \/ __/ -- (__ ) /_/ / / / /_/ / /_/ / /__ -- /___/\__, /_/ /_____/\____/\___/ -- / / -- /_/ -- -- SQL Doctor -- -- Welcome to the World of Databases -- -- By Rudy Panigas -- -- Code Name: usp_dba_SQL_Server_Stats -- Date of Creation: Aug 27, 2009 - version 1.0 -- Updated: Sept 3, 2009 - version 2.1 -- Comments: Code will produce a report with important information about your SQL server (2005/2008) and its processes -- USE [master] -- Doesn't have to be in master database. GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dba_SQL_Server_Stats]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_dba_SQL_Server_Stats] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_dba_SQL_Server_Stats] AS PRINT ' SQL 2005/2008 Server Statistics' PRINT ' -------------------------------' PRINT '' PRINT ' Displaying statistics of SQL server ' PRINT ' ' PRINT '...Buffer Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Buffer cache hit ratio') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Buffer cache hit ratio base') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page lookups/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Free pages') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Total pages') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Readahead pages/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page reads/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page writes/sec') OR ([object_name] LIKE '%Buffer Manager%'and [counter_name] = 'Page life expectancy') PRINT '...Memory Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%memory manager%'and [counter_name] = 'Connection Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Granted Workspace Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Blocks Allocated') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Owner Blocks Allocated') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Blocks') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Lock Owner Blocks') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Maximum Workspace Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Memory Grants Outstanding') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Memory Grants Pending') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Optimizer Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'SQL Cache Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Target Server Memory (KB)') OR ([object_name] LIKE '%memory manager%'and [counter_name] = 'Total Server Memory (KB)') PRINT '...General Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Active Temp Tables') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Temp Tables Creation Rate') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logins/sec') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logouts/sec') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'User Connections') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Logical Connections') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Transactions') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Mars Deadlocks') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'HTTP Authenticated Requests') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Processes blocked') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Temp Tables For Destruction') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Event Notifications Delayed Drop') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'Trace Event Notification Queue') OR ([object_name] LIKE '%General Statistics%'and [counter_name] = 'SQL Trace IO Provider Lock Waits') PRINT '...Locks Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Number of Deadlocks/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Waits/sec') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Wait Time (ms)') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time (ms)') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time Base') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts (timeout > 0)/sec') PRINT '...Total for Locks Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Requests/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Number of Deadlocks/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Waits/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Wait Time (ms)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time (ms)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Average Wait Time Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Locks%'and [counter_name] = 'Lock Timeouts (timeout > 0)/sec' and [instance_name] ='_Total') PRINT '...Temp DB Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Databases%'and [counter_name] = 'Data File(s) Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Used Size (KB)' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Percent Log Used' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Active Transactions' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Transactions/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Pending Xacts' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Trans. Rate' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Reads/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio Base' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Rows/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Throughput/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Backup/Restore Throughput/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'DBCC Logical Scan Bytes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Shrink Data Movement Bytes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flushes/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Bytes Flushed/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Waits/sec' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Wait Time' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Truncations' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Growths' and [instance_name] ='tempdb') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Shrinks' and [instance_name] ='tempdb') PRINT '...Totals of Database Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Databases%'and [counter_name] = 'Data File(s) Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log File(s) Used Size (KB)' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Percent Log Used' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Active Transactions' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Transactions/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Pending Xacts' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Repl. Trans. Rate' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Reads/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Cache Hit Ratio Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Rows/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Bulk Copy Throughput/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Backup/Restore Throughput/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'DBCC Logical Scan Bytes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Shrink Data Movement Bytes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flushes/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Bytes Flushed/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Waits/sec' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Flush Wait Time' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Truncations' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Growths' and [instance_name] ='_Total') OR ([object_name] LIKE '%Databases%'and [counter_name] = 'Log Shrinks' and [instance_name] ='_Total') PRINT '...SQL Error Statistics' PRINT ' ' SELECT [instance_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') OR ([object_name] LIKE '%SQL Errors%'and [counter_name] = 'Errors/sec') PRINT '...SQL Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%SQL Statistics%'and [counter_name] = 'SQL Compilations/sec') OR ([object_name] LIKE '%SQL Statistics%'and [counter_name] = 'SQL Re-Compilations/sec') PRINT '...Plan Cache Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Hit Ratio' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Hit Ratio Base' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Pages' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Object Counts' and [instance_name] ='_Total') OR ([object_name] LIKE '%Plan Cache%'and [counter_name] = 'Cache Objects in use' and [instance_name] ='_Total') PRINT '...Transactions Statistics' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Transactions%') PRINT '...Wait Statistics. Average execution time (ms)' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Wait Statistics%'and [instance_name] = 'Average execution time (ms)') -- PRINT '...Wait Statistics. Waits in progress' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Wait Statistics%'and [instance_name] = 'Waits in progress') -- PRINT '...SQL Execution Statistics. Average execution time (ms)' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'Extended Procedures') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'DTC calls') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'OLEDB calls') OR ([object_name] LIKE '%Exec Statistics%'and [counter_name] = 'Distributed Query') -- PRINT '...SQL Execution Statistics. Execution in progress' PRINT ' ' SELECT [counter_name] AS 'Description' , [cntr_value] AS 'Current Value' from sys.dm_os_performance_counters WHERE ([object_name] LIKE '%Exec Statistics' and [instance_name] ='Execs in progress') GO

SQL Monitor Metric: Processes in a runnable state (scheduler query)

by Press Release

SQL Monitor Metrics

Monitoring

Use this script in SQL Monitor to determine the general load on the operating system you can get a count of the processes that are in a runnable state. This value will go up and down as various systems run on the operating system. It’s only a measure of load on the system and won’t indicate the cause of problems, but will show you pressure on the system.

2013-12-03

3,671 reads

SQL Overview VI - Monitoring Process Locks

by David Bird

SQLServerCentral.com

Monitoring

In the sixth part of his series on monitoring your SQL Server, David Bird looks at process locks.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.17 (12)

You rated this post out of 5. Change rating

2011-07-22 (first published: 2010-03-31)

15,842 reads

Discuss

Monitoring Drive and Database Free Space

by Mark Nash

SQLServerCentral.com

Monitoring

SQL Server will autogrow your databases as they run out of space. But the process doesn't manage space, nor does it check the free space on the drive. Allowing autogrow to grow unchecked and unmanaged will eventually use up all the free disk space and potentially crash your server. New author Mark Nash brings you his system for monitoring space usage and generating a report that eases this process.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.4 (5)

You rated this post out of 5. Change rating

2017-02-02 (first published: 2004-07-01)

44,861 reads

Discuss

Automatically Gathering Server Information Part 1

by Steve Jones

SQLServerCentral.com

Monitoring

The first part of Steve Jones's series on having SQL Server automatically report information to a DBA.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.67 (3)

You rated this post out of 5. Change rating

2003-12-24

15,970 reads

Log Navigator Database Auditor Released

by Additional Articles

ApexSQL

Monitoring

Log Navigator 1.0, a passive SQL auditing system that uses the SQL Server transaction log, instead of triggers, to audit data changes has been released.

2003-09-29

1,076 reads

Discuss

Collect SQL Server/Process Information - Quickly

Rate

Share

Share

Rate

Collect SQL Server/Process Information - Quickly

Rate

Share

Share

Rate

Related content

SQL Monitor Metric: Processes in a runnable state (scheduler query)

SQL Overview VI - Monitoring Process Locks

Monitoring Drive and Database Free Space

Automatically Gathering Server Information Part 1

Log Navigator Database Auditor Released