Performance Counter in SQL Server

Performance Counter in SQL Server

Performance Counter always helps DBA as well as system administrator to keep watch on performance of Operating System as well as SQL Server. There are thousands of performance counter offered by Windows but I mostly interested in some of the important performance counter related to SQL Server and hence I have created one view which I can use directly from SSMS or from one of web page for monitoring I have.

Keeping a watch on performance, especially in business hours , is one of the mandatory task for any DBA or system administrator. Herewith, I am sharing the TSQL script which can be helpful to any DBA or system administrator who wants to monitor performance of SQL Server & Database along with some of the crucial information about buffer & memory usage, lock & dead lock details and compilation-recompilation & caching.

You can run performance counter from GUI in Windows as well as from DMVs of SQL Server (Given in this article). Performance counter from GUI in windows give lot more counters then we are provided in DMV of SQL Server. Windows’ performance counter intend to keep an eye on every aspect of Windows not only SQL Server but DMV provided in SQL Server will have only those performance counter which are important and necessary to keep a watch on performance of SQL Server instance and its databases. I will cover Performance Counter from Windows GUI sometime later.

CREATE VIEW WhatIsPerformance
AS
-- Given counter in this query are most important as per my opinion
-- so gave status "Important" and sort order 1.....
SELECT
'Important' AS CounterType
,1 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
(
([counter_name] IN(
'active transactions',
'Average Wait Time (ms)',
'errors/sec',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Repl. Pending Xacts',
'Transactions/sec') and instance_name = '_total'
)
or
[counter_name] IN (
'User Connections',
'Number of Deadlocks/sec',
'Repl. Trans. Rate',
'Bytes Sent/sec',
'Bytes Received/sec',
'SQL SENDs/sec',
'SQL RECEIVEs/sec',
'Enqueued Messages/sec',
'Send I/Os/sec',
'Receive I/Os/sec',
'lock waits',
'Network IO waits',
'Active Temp Tables',
'Temp Tables Creation Rate',
'Logins/sec',
'Logouts/sec'
)
)
AND cntr_value > 0

UNION ALL

-- important memory statistics
SELECT
'Memory Stat' AS CounterType
,2 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Connection Memory (KB)')
OR ([counter_name] = 'Optimizer Memory (KB)')
OR ([counter_name] = 'SQL Cache Memory (KB)')
OR ([counter_name] = 'Granted Workspace Memory (KB)')
OR ([counter_name] = 'Maximum Workspace Memory (KB)')
OR ([counter_name] = 'Memory Grants Outstanding')
OR ([counter_name] = 'Memory Grants Pending')
OR ([counter_name] = 'Lock Memory (KB)')
OR ([counter_name] = 'Lock Blocks Allocated')
OR ([counter_name] = 'Lock Owner Blocks Allocated')
OR ([counter_name] = 'Lock Blocks')
OR ([counter_name] = 'Lock Owner Blocks')

UNION ALL

-- buffer statistics
SELECT
'Buffer Stat' AS CounterType
,3 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Buffer cache hit ratio')
OR ([counter_name] = 'Buffer cache hit ratio base')
OR ([counter_name] = 'Page lookups/sec')
OR ([counter_name] = 'Readahead pages/sec')
OR ([counter_name] = 'Page reads/sec')
OR ([counter_name] = 'Page writes/sec')
OR ([counter_name] = 'Page life expectancy')

UNION ALL

-- total lock statistics
SELECT
'Lock Stat' AS CounterType
,4 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Lock Requests/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Timeouts/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Number of Deadlocks/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Waits/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Wait Time (ms)' and [instance_name] ='_Total')
OR ([counter_name] = 'Average Wait Time (ms)' and [instance_name] ='_Total')
OR ([counter_name] = 'Average Wait Time Base' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Timeouts (timeout > 0)/sec' and [instance_name] ='_Total')

UNION ALL

-- compilation and cache stat
SELECT
'compilation and cache stat' AS CounterType
,5 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Cache Hit Ratio' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Hit Ratio Base' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Pages' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Object Counts' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Objects in use' and [instance_name] ='_Total')
OR ([counter_name] = 'SQL Compilations/sec')
OR ([counter_name] = 'SQL Re-Compilations/sec')
GO

Once you have the view created, you can query the view with the kind of counter you wanted to see based on the “CounterType” field or any other column provided by above view.

SELECT
CounterType
,CounterObject
,CounterName
,CounterValue
,CounterInstanceName
FROM
WhatIsPerformance
ORDER BY
SortOrder ASC
GO

I have some more views and script which DBA can keep handy, you never know, when you have to use it. Have a look at the articles given:

http://blog.extreme-advice.com/2012/09/14/find-currently-running-sessionprocess-in-sql-server-databaseinstance/
http://blog.extreme-advice.com/2012/09/15/use-sys-dm_exec_requests-and-sys-dm_exec_sessions-to-find-who-are-logged-in/
http://blog.extreme-advice.com/2012/09/17/find-locked-table-name-with-duration-and-count-in-sql-server/
http://blog.extreme-advice.com/2012/09/25/find-blocking-in-sql-server-and-use-it-to-send-an-alert/
http://blog.extreme-advice.com/2012/10/03/calculate-table-schema-size-in-sql-server/
http://blog.extreme-advice.com/2012/10/05/find-database-restore-date-in-sql-server/
http://blog.extreme-advice.com/2012/10/06/find-database-backup-history-in-sql-server/
http://blog.extreme-advice.com/2009/07/08/keep-watch-on-your-disk-space-in-sql-server-20082005/
http://blog.extreme-advice.com/2009/07/10/keep-watch-on-your-database-file-size-in-sql-server-20082005/
http://blog.extreme-advice.com/2012/10/10/repl-pending-xacts-performance-counter-and-high-value-in-sql-server/

http://blog.extreme-advice.com/2012/10/11/performance-counter-in-microsoft-windows-server-2008/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .