Monitoring

How to install the SQL Server Utility Control Point and configure multiple instances for monitoring

January 16, 2017 by

As we progress in our career as a SQL Server Database Administrators we encounter situations where managing and monitoring a large number of SQL Servers is the daily job requirement. Monitoring SQL Server is a very vast and detailed area of study. Out of all the monitoring the very basis and critical monitoring in the Resource Utilization Monitoring for SQL Server.

Read more »
Ahmad Yaseen

Monitor the Query timeout expired message from a SQL Server Agent job

January 11, 2016 by

SQL Server provides you with a good solution to automate a lot of your administrative tasks using the SQL Server Agent jobs. These jobs are handled from the operating system side by a Windows service that is responsible for executing these jobs and feeding the SQL Server systems tables with the metadata about these jobs. The system database that is used by the SQL Server Agent for the job management called the msdb database. All information related to the job steps, schedules and the history can be found in the msdb database tables. The msdb system database is also responsible for the SQL Server Mail, Service Broker, SQL Server Maintenance Plans and the databases backup history.

Read more »
Eli Leiba

Construct a special multi-statement table function for checking SQL Server’s health

December 24, 2015 by

The Problem

Checking the many SQL Servers health conditions is one of the DBA basic tasks. Monitoring many servers and databases, day after day can be a very tedious job. There are many aspects to look for when checking the server performance figures. Such aspects include among others: CPU, Read and Write Disk counters, Various memory counters and locks performance counters. The DBA needs a tool that checks the most important counters and output a report that states whether the server is considered to be healthy or not. For every aspect, a counter is chosen to represent it to be included in the overall report.

Read more »
Milena Petrovic

Troubleshooting SQL Server issues with sys.dm_os_performance_counters

March 13, 2014 by

sys.dm_os_performance_counters

sys.dm_os_performance_counters is a system Dynamic Management View (DMV) that returns one row for each SQL Server performance counter. It’s useful for obtaining information about current performance counter values. These counter values are also shown in Windows Performance Monitor, besides other operating system counters Read more »
Milena Petrovic

Windows Performance Monitor basics

March 12, 2014 by
Windows Performance Monitor is a tool shipped with Windows that can be used to monitor and examine how applications affect computer performance. It can measure real-time server performance and save the measured values into a log file that can be analyzed later. It enables real time system and SQL Server performance monitoring, tracking the performance impact of applications and services, setting threshold for each monitored metric, and generating an alert or performing a specific action when the defined threshold is exceeded
Read more »
Milena Petrovic

SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

March 12, 2014 by
So far, we have presented the most important memory and processor metrics. These metrics indicate system and SQL Server performance, and are useful for troubleshooting performance issues and bottlenecks. Besides memory and processor metrics, equally important are SQL Server disk metrics. Sometimes a metric from one category can be masked by other events and be misleading – e.g. a disk issue can cause processor bottlenecks. That’s why it’s necessary to understand the cause and effect of each metric Read more »
Milena Petrovic

SQL Server processor performance metrics – Part 4 – processor metrics useful for deeper investigation and troubleshooting

March 12, 2014 by

In the previous part of this series, we presented the processor metrics that indicate hardware component problems that can affect SQL Server performance

In this part, we will present processor metrics that are not necessary in every performance monitoring scenario, but are useful for deeper investigation and troubleshooting Read more »
Milena Petrovic

SQL Server processor performance metrics – Part 3 – measures that indicate hardware component problems

March 11, 2014 by
In the Part 1 and Part 2 of the SQL Server processor performance metrics series, we described the most important processor performance metrics – Processor: % Processor Time, Process: % Processor Time, Processor Queue Length, Processor: % User Time, Processor: %Privilege Time, as well as various total times and thread metrics Read more »
Milena Petrovic

SQL Server processor performance metrics – Part 2 – Processor: % User Time, Processor: % Privilege Time, total times and thread metrics

March 11, 2014 by

In SQL server processor performance metrics – Part 1 – The most important cpu metrics, we described the Processor Queue Length and two most important processor time counters – Processor % Processor Time and Process % Processor time

In this part, we will continue describing important time processor counters Read more »
Milena Petrovic

SQL Server memory performance metrics – Part 6 – other memory metrics

March 10, 2014 by
So far, we have described the most popular and most important SQL Server memory metrics. Monitoring these metrics is recommended in every scenario when SQL Server performance monitoring and troubleshooting is needed. Besides the SQL Server Memory Manager metrics, the memory pressure can be indicated via the Buffer Manager metrics, as the buffer is the component that uses most of the SQL Server memory Read more »
Milena Petrovic

SQL Server memory performance metrics – Part 5 – understanding Lazy Writes, Free List Stalls/sec, and Memory Grants Pending

March 7, 2014 by
We’ve started the SQL Server performance metrics series with the SQL Server memory metrics that should be monitored to indicate and help troubleshoot SQL Server performance issues

In this part, we will continue with SQL Server memory performance metrics and present Lazy Writes, Free List Stalls/sec, and Memory Grants Pending Read more »

Milena Petrovic

SQL Server memory performance metrics – Part 4 – Buffer Cache Hit Ratio and Page Life Expectancy

March 5, 2014 by

In SQL Server performance metrics – part 3, we presented some of the SQL Server Buffer Manager metrics. As the SQL Server buffer pool manages data cache, it’s important to monitor various buffer pool metrics as they help identify and solve memory pressure issues

In this article, we will present 2 more Buffer Manager counters – Buffer Cache Hit Ratio and Page Life Expectancy.

Read more »
Milena Petrovic

SQL Server memory performance metrics – Part 3 – SQL Server Buffer Manager metrics and memory counters

March 3, 2014 by
In the previous parts of the SQL Server performance metrics series, we presented most important SQL Server memory metrics that indicate overall SQL Server memory pressure. The described metrics show how SQL Server uses memory and physical I/O to store, read and write data pages. In this part, we continue with SQL Server Buffer Manager metrics and memory counters Read more »
Milena Petrovic

SQL Server memory performance metrics – Part 2 – available bytes, total server, and target server memory

February 21, 2014 by
In the first part of this series, we started with most important SQL Server performance memory metrics. We described Pages/sec and Page faults/sec, what they indicated, and what the normal values were In this part, we will present three more memory metrics that affect SQL Server performance – Available memory bytes, Total server memory, and Target server memory Read more »
Milena Petrovic

SQL Server performance monitoring with Data Collector – Part 2 – set-up and usage

February 21, 2014 by
In the previous part of this article, we described SQL Server Data Collector, its features, and pre-defined collection sets. We showed what data is collected and described the mechanisms used to collect, store, and purge the saved records. In this part, we will give detailed steps to provide all necessary pre-requests and set up Data Collector Read more »