Home » sql performance monitor

sql performance monitor

sql server DBA 999

Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016

Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL Servers.

These counters are helpful for both Virtual or Physical SQL Servers. They are available in all versions like sql server 2008 data collector, sql server 2012 data collector, sql server 2014 data collector

    The Counters related to SQL Instance which are really important are:


SQLServer:Access MethodsFreeSpace Scans/sec
SQLServer:Access MethodsFull Scans/sec
SQLServer:Access MethodsIndex Searches/sec
SQLServer:Access MethodsPages Allocated/sec
SQLServer:Access MethodsWorkfiles Created/sec
SQLServer:Access MethodsWorktables Created/sec
SQLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerCheckpoint pages/sec
SQLServer:Buffer ManagerDatabase pages
SQLServer:Buffer ManagerFree list stalls/sec
SQLServer:Buffer ManagerFree pages
SQLServer:Buffer ManagerLazy writes/sec
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer ManagerReadahead pages/sec
SQLServer:Buffer ManagerReserved pages
SQLServer:Buffer … Read the rest

sql server DBA 999

Active Connections to SQL Server Database 2008, 2005 by Application or User

To Check Active Connections to SQL Server Database grouped by User

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections grouped by Login name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Login name


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.original_login_name AS login
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.original_login_name

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by

… Read the rest
sql server DBA 999

How to check Log shipping status in SQL Server 2008

How to check Log shipping status in SQL Server 2008:

SQL server recovery model has to be Full recovery mode for log shipping which allows log backups.

sql server database log backup is shiped and restore from one server or Primary to another server Secondary/destination server. Reports can be run from Secondary server for business needs. Using sql server nolock is recommended if dirty reads are allowed for reporting purposes.

This can be done by two methods:

1.By using the standard report on Instance level for Transaction log shipping status both on Primary and each secondary/destination servers individually.
On the Primary/source server, it shows when backup jobs succeeded.
On Secondary/Destination servers, it shows both when last copy job succeeded plus … Read the rest

Monitoring  SQL Server Performance is one of important task for a DBA using Performance Microsoft Management Console (MMC) snap-in. 

Performance monitoring can by done by many ways including monitoring SQL Performance Counters below which are for %Processor utilization and Disk Queue Monitoring. Perfmon analysis is important step in troubleshooting issues when a application goes slow.

Performance Monitor in SQL Server 2012 , 2014, 2016 etc are also very capable

 Important Perfmon Counters %Processor and Disk monitoring counters

S.NO Performance Counter Name Comments
9 Processor – %Processor Time The percentage of time the processor spent executing a non-idle thread. This value is subtracted from the time the processor was idle 100 percent. This is an indicator to the overall CPU utilization
… Read the rest