Performance

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by Miroslav Dimitrov

Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.

The tempdb database, introduction and recommendations

January 5, 2016 by Daniel Calbimonte

Introduction

Many times the people focus on the user databases to improve the performance, but sometimes the problem is not the user database itself. Sometimes the problem is the tempdb.

In this article, we will give a brief introduction about the tempdb database, show how to create some temporary objects there and show how to improve and monitor it.

Creating an automatic View to an In-line table function conversion stored procedure

December 22, 2015 by Eli Leiba

Introducing the Problem

We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.

When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.

Filtered indexes: Performance analysis and hidden costs

December 11, 2015 by Ed Pollack

Introduction

Filtered indexes are well documented, as they have been around in SQL Server for almost six years now. Despite their longevity and usefulness, discussions of them tend to be very simple overviews using simple queries and not digging too deeply into more precise costs and benefits. This article is inspired by a production problem that cropped up recently involving a filtered index that illustrated that general knowledge of their function was not as complete as it should have been.

Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports

September 12, 2014 by Milena Petrovic

In the previous part of this article, we introduced SQL Server Management Studio custom reports, showed their advantages over other native performance reporting features, and gave steps to create them. In this part, we will give some security recommendations and show how to run and modify these reports. Read more »

Using custom reports to improve performance reporting in SQL Server 2014 – the basics

September 8, 2014 by Milena Petrovic

SQL Server Management Studio provides three built-in performance monitoring features. These are Activity Monitor, Data collection sets, and standards reports. Read more »

Performance Dashboard Reports in SQL Server 2014

July 29, 2014 by Milena Petrovic

SQL Server Management Studio provides a set of standard reports that show basic performance information. These reports are available out of the box, no previous installation and configuration is needed. There are more than 20 reports that show usually required monitoring and troubleshooting information at the SQL Server instance level. Read more »

SQL Server Management Studio performance reports

July 24, 2014 by Milena Petrovic

Basic information about performance on a SQL Server instance is useful to at least scratch the surface of a performance issue. To be able to obtain this information, it’s not necessary to have a specialized monitoring tool that provides tiniest details. SQL Server Management Studio provides features that can quickly help. Read more »

Poor SQL query design – a SQL query performance killer – the basics

May 23, 2014 by Milena Petrovic

Poor query design is one of the top SQL Server performance killers. Even with good database design, no frequent recompilations, and no other SQL performance killers, poor query design can severely degrade performance.
Read more »

Inaccurate SQL Server statistics – a SQL query performance killer – updating SQL Server statistics

April 28, 2014 by Milena Petrovic

As shown in the previous part of this series, inaccurate statistics can degrade SQL Server performance. We described how to work with SQL Server statistics using SQL Server Management Studio options and T-SQL. In this article, we will show how to update SQL Server statistics, what are the updating costs, and when updating is recommended. Read more »

Inaccurate SQL Server statistics – a SQL query performance killer – the basics

April 22, 2014 by Milena Petrovic

SQL Server statistics are used for cost-based SQL query optimization. Cost-based optimizations estimate costs of various SQL query execution plans applicable to the query and select the one that uses the least hardware resources, i.e. has the smallest cost. Read more »

SQL Server alerts in troubleshooting performance issues: How to create and use SQL Server alerts

March 15, 2014 by Milena Petrovic

In the first part of this series, we presented the alerting feature available via SQL Server Agent. In this sequel, we will show how to provide all prerequisites, create alerts, and adequate notifications

Before creating a SQL Server alert, make sure that Database Mail and SQL Server Agent properties are properly configured Read more »

SQL Server alerts in troubleshooting performance issues: SQL Server alerting basics

March 14, 2014 by Milena Petrovic

Why is SQL Server alerting important?

To be aware of what’s going on with your system and how SQL Server performs, monitoring is necessary. However, just monitoring might not be enough. Being notified about a performance issue whenever a specific SQL Server counter meets a threshold value, or a specific event occurs, boosts monitoring usability. These notifications enable DBAs to be aware of every important system performance change as soon as it happens, and therefore they can act immediately to fix the issue and prevent further problems Read more »

Troubleshooting SQL Server issues with sys.dm_os_performance_counters

March 13, 2014 by Milena Petrovic

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 »

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 »