SQL Server

Some tips to use System Monitor with SQL Server 2014

Some tips to use System Monitor with SQL Server 2014

System Monitor works on Windows operating systems and can be used to monitor an instance
of SQL Server 2014. 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.

Ensure that SQL Server Agent is currently running.
If SQL Server Agent were stopped, it cannot be notified of the alert’s action.

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.

Try to avoid running the System Monitor on your production SQL Server 2014.
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.

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.

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.

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.

Use System Monitor’s Log Mode to collect counter data over a period of time.
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.

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. The value of the Page Splits/sec counter
should be as low as possible. 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:
EXECUTE 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’

Consider using the “SQL Server Databases Object: Percent Log Used” counter to indicate
the percent of space in the log that is in use.

When you use this counter, you should also specify the database name to check log. 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%:
EXECUTE 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.
This System Monitor counter indicates the number of SQL batch requests received by server.
Because the excess batch requests can degrade SQL Server 2014 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 2014 alert that is raised when the
value of the Batch Requests/Sec is more than 500:
EXECUTE 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 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 2014 alert that is raised when the value of the
Page Life Expectancy is less than 300 seconds:
EXECUTE 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.
This is one of the main SQL Server 2014 performance counter the DBA usually used. This counter
indicates how often SQL Server 2014 goes to the buffer instead of the hard disk to get data.
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 2014 alert that is raised when the value of the
Buffer Cache Hit Ratio is less than 90%:
EXECUTE 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 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.
EXECUTE 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’