SQL Server

Tips to use System Monitor with SQL Server 2016

Tips to use System Monitor with SQL Server 2016

System Monitor works on Windows operating systems and can be used to monitor

an instance of SQL Server 2016. The difference between SQL Server Profiler

and System Monitor is that SQL Server Profiler monitors Database Engine events,

whereas System Monitor monitors resource usage associated with server processes.

To start System Monitor in Windows you can point to Run on the Start menu, then

type perfmon in the Run dialog box, and then click OK.

Avoid monitoring counters you do not need.

The more counters you monitor, the more overhead that is required to perform

the monitoring. So, to boost the System Monitor performance, you should monitor

only those counters that you need.

Stop all screen-saver and anti-virus programs on a computer you run System Monitor.

Because using screen-saver and anti-virus programs can degrade the total SQL Server

performance, try to avoid using this software on your production server.

Do not collect data more often than you really need.

The more often you will collect data, the more overhead that is required to

perform the monitoring. So, to boost the System Monitor performance, you can

increase the counters collection interval. The default counter collection

interval is 1 second. Try to increase the counter collection interval to 5

seconds and continue monitoring.


Try to avoid running the System Monitor on your production SQL Server 2016.

Because the System Monitor puts a large load on SQL Server, you should run the

System Monitor on a workstation connected to the SQL Server via network. In this

case, the System Monitor will get data from the SQL Server, but will calculate

and display the counters data using the workstation’s CPU and memory resources.

Do not place System Monitor’s log files on a disk you are monitoring.

Using the same disk to store log files reduces the total disk performance and can

also provide inaccurate monitoring results.

Avoid running System Monitor in graph view.

Use System Monitor’s Log Mode instead because log mode puts less overhead than

graphical mode. When you use System Monitor’s Log Mode to collect counter data,

you can specify the time when the monitoring will be finished. Microsoft recommends

set the collection interval to 15 seconds, if you are monitoring for periods of

less than four hours. If you are monitoring for periods of more than eight hours,

set the collection interval to 300 seconds or more.

Consider using the “SQL Server Buffer Manager Object: Page Life Expectancy”

counter to indicate how long data pages are staying in the buffer.

When this value is less than 300 seconds, this is a potential indication

that SQL Server could use more memory in order to boost performance.

For example, you can define an SQL Server 2016 alert that is raised when

the value of the Page Life Expectancy is less than 300 seconds:

EXEC msdb.dbo.sp_add_alert

@name = ‘Page_Life_Expectancy’,

@notification_message = ‘The Page Life Expectancy < 300 seconds!',

@performance_condition = ‘SQLServer:Buffer Manager|Page Life Expectancy||<|300'

Use “SQL Server Buffer Manager Object: Buffer Cache Hit Ratio” counter to indicate

how often SQL Server 2016 goes to the buffer instead of the hard disk to get data.

This is one of the main SQL Server 2016 performance counter the DBA usually used.

The value of this counter should exceed 90% otherwise you should consider adding

more RAM to your server to increase performance.

For example, you can define an SQL Server 2016 alert that is raised when the value

of the Buffer Cache Hit Ratio is less than 90%:

EXEC msdb.dbo.sp_add_alert

@name = ‘Buffer_Cache_Hit_Ratio’,

@notification_message = ‘The buffer cache hit ratio < 90%!',

@performance_condition = ‘SQLServer:Buffer Manager|Buffer Cache Hit Ratio||<|90'

Consider using the “SQL Server Databases Object: Percent Log Used” counter

to indicate the percent of space in the log that is in use.

For example, you can define an SQL Server alert that is raised when the

used log space in the tempdb database is more than 90%:

EXEC msdb.dbo.sp_add_alert

@name = ‘Percent_Log_Used’,

@notification_message = ‘The TempDB log used > 90%!’,

@performance_condition = ‘SQLServer:Databases|Percent Log Used|tempdb|>|90’

Use the “SQL Server SQL Statistics Object: Batch Requests/Sec” counter to

indicate the number of SQL batch requests received by server.

Because the excess batch requests can degrade SQL Server 2016 performance,

you can monitor the value of the Batch Requests/Sec counter. Usually the

value for this counter should be 500 or less.

For example, you can define an SQL Server 2016 alert that is raised when

the value of the Batch Requests/Sec is more than 500:

EXEC msdb.dbo.sp_add_alert

@name = ‘Batch_Requests_Sec’,

@notification_message = ‘The batch requests/sec > 500!’,

@performance_condition = ‘SQLServer:SQL Statistics|Batch Requests/Sec||>|500’

Consider using the “SQL Server SQL Statistics Object: SQL Compilations/Sec”

counter to indicate the number of SQL Server compilations occur per second.

In general case, if SQL Server performs more than 100 compilations per second,

then you may be experiencing unnecessary compilation overhead.

For example, you can define an SQL Server alert that is raised when the number

of SQL Server compilations is more than 100 per second.

EXEC msdb.dbo.sp_add_alert

@name = ‘SQL_Compilations_Sec’,

@notification_message = ‘SQL Server performs more than 100 compilations per second!’,

@performance_condition = ‘SQLServer:SQL Statistics|SQL Compilations/Sec||>|100’

Use the “SQL Server Access Methods Object: Page Splits/sec” counter.

When you add a new row to a full data or index page, SQL Server moves approximately

half the rows to a new page to make room for the new row. This reorganization is

called a page split. Because page splitting can degrade performance, you should know

when a large number of page splits occurs. To find out the occurrence of the excess

page splits, you should monitor the SQL Server Access Methods Object: Page Splits/sec.

If you find out the excess page splits, you should rebuild the indexes on the tables

and increase the fillfactor of these indexes.

For example, you can define an SQL Server alert that is raised when the value of the

Page Splits/sec is more than 10:

EXEC msdb.dbo.sp_add_alert

@name = ‘Page_Splits_sec’,

@notification_message = ‘The page splits/sec > 10!’,

@performance_condition = ‘SQLServer:Access Methods|Page Splits/sec||>|10’

Facebooktwittergoogle_plusredditpinterestlinkedinmail