A DBA guide to SQL Server performance troubleshooting – Part 2 – Monitoring utilities March 13, 2014 by Milena Petrovic In this article, we will present native Windows and SQL Server tools commonly used for troubleshooting SQL Server performance issues. Selecting the right performance monitoring utility depends on your monitoring goals and coding knowledge, as the tools provide different information and have different analysis capability Read more »
SQL Server network performance metrics – The most important metrics March 13, 2014 by Milena Petrovic Memory, processor, and disk performance parameters are most commonly monitored metrics for identifying and troubleshooting SQL Server performance issues. However, sometimes these are not enough. Even with a fast and smooth running server, a client who accesses the server over a slow or saturated network will encounter delays Read more »
SQL Server performance tuning using Windows Performance Monitor March 12, 2014 by Milena Petrovic In the Windows Performance Monitor basics article, we described the most important Windows Performance Monitor features, gave general recommendations on how to use it for monitoring and troubleshooting SQL Server performance issues, and showed some of the counters it can monitor Read more »
Windows Performance Monitor basics March 12, 2014 by Milena Petrovic 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 »
SQL Server disk performance metrics – Part 2 – other important disk performance measures March 12, 2014 by Milena Petrovic In the previous part of the SQL Server performance metrics series, we presented the most important and useful disk performance metrics. Now, we’ll show other important disk performance measures Read more »
SQL Server disk performance metrics – Part 1 – the most important disk performance metrics March 12, 2014 by Milena Petrovic 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 »
SQL Server processor performance metrics – Part 4 – processor metrics useful for deeper investigation and troubleshooting March 12, 2014 by Milena Petrovic 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 »
SQL Server processor performance metrics – Part 3 – measures that indicate hardware component problems March 11, 2014 by Milena Petrovic 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 »
SQL Server processor performance metrics – Part 2 – Processor: % User Time, Processor: % Privilege Time, total times and thread metrics March 11, 2014 by Milena Petrovic 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 »
SQL Server processor performance metrics – Part 1 – the most important CPU metrics March 11, 2014 by Milena Petrovic In the previous parts of the SQL Server performance metrics series, we described and recommended various memory metrics that should be tracked when monitoring SQL Server performance. Besides the memory metrics, monitoring processor usage and disk performance is recommended Read more »
SQL Server memory performance metrics – Part 6 – other memory metrics March 10, 2014 by Milena Petrovic 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 »
SQL Server memory performance metrics – Part 5 – understanding Lazy Writes, Free List Stalls/sec, and Memory Grants Pending March 7, 2014 by Milena Petrovic 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 »
SQL Server memory performance metrics – Part 4 – Buffer Cache Hit Ratio and Page Life Expectancy March 5, 2014 by Milena Petrovic 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 »
SQL Server memory performance metrics – Part 3 – SQL Server Buffer Manager metrics and memory counters March 3, 2014 by Milena Petrovic 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 »
SQL Server memory performance metrics – Part 2 – available bytes, total server, and target server memory February 21, 2014 by Milena Petrovic 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 »
SQL Server memory performance metrics – Part 1 – Memory pages/sec and Memory page faults/sec February 21, 2014 by Milena Petrovic In the SQL Server performance basics article, we explained why performance monitoring was important and what you should consider before you start monitoring SQL Server performance Read more »
SQL Server performance monitoring with Data Collector – Part 3 – reading the reports February 21, 2014 by Milena Petrovic In the first part of this article, we described Data Collector, its features and pre-defined collection sets. In the second part, we showed how to set up data collecting. In this final part, we will show the reports available in Data Collector and how to use them for performance troubleshooting Read more »
SQL Server performance monitoring with Data Collector – Part 2 – set-up and usage February 21, 2014 by Milena Petrovic 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 »
SQL Server performance monitoring with Data Collector February 19, 2014 by Milena Petrovic What is SQL Server Data Collector SQL Server Data Collector is a feature for performance monitoring and tuning available in SQL Server Management Studio. It was introduced in SQL Server 2008 and is available in the Enterprise, Standard, Business Intelligence, and Web editions Read more »
SQL Server performance basics February 19, 2014 by Milena Petrovic Monitoring is recommended in dynamic environments. When it comes to SQL Server – users, DBAs, and the system itself are performing events constantly – changing data and database structure, user privileges, etc. SQL Server can manage automatically all these requests, but its self-tuning doesn’t provide best results, and additional monitoring and manual tuning can provide better performance Read more »
SQL Server Activity Monitor February 14, 2014 by Milena Petrovic Monitoring system and SQL Server databases shows the metrics that indicate server performance. Efficient monitoring provides enough information to diagnose problems, blocks, bottlenecks, and to fix them Read more »
