Execution plans

The SQL Server Query Store in Action

November 26, 2015 by Ed Pollack

Introduction

The Query Store is a new feature in SQL Server 2016 that will greatly improve the process of performance tuning! The biggest limit of the query plan cache and the dynamic management views that relates to it is that it is very temporary. Queries are only accessible for as long as they are in cache, and as a result there is no way to travel back in time to determine what happened at 9am yesterday, or why performance worsened over a week-long period.

Query Store – the next generation tool for every DBA

November 13, 2015 by Brian Bønk Rueløkke

Along with the release of SQL server 2016 CTP 3 now comes the preview of a brand new feature for on premise databases – the Query Store. This feature enables performance monitoring and troubleshooting through the log of executed queries.

This blogpost will cover the following aspects of the Query Store feature:

Introduction
How to activate it
Configuration options
What information is found in the Query Store
How to use the feature
What’s in it for me

Behold the new live query stats in SQL Server 2016

September 11, 2015 by Brian Bønk Rueløkke

With the release of SQL Server 2016 also comes a great new feature to get a live view of the current execution plan for an active query.

This blogpost will cover the aspects of this new feature including:

Introduction
How to activate
How to use and read the output
Downsides – if any

Poor database indexing – a SQL query performance killer – recommendations

April 14, 2014 by Milena Petrovic

In the previous article, we explained what clustered and nonclustered indexes were, and showed how to create them. In this article, we will explain what poor indexing is and give general recommendations for table indexing and selecting the right columns to create indexes on. Read more »

Frequent query recompilations – a SQL query performance killer – detection

April 11, 2014 by Milena Petrovic

In the previous part of this article, we presented query recompilation basics. We explained when a query is compiled, when recompiled, and what preferred behavior is. We also showed the how to trigger a recompilation using T-SQL and provided examples. In this part, we will focus on detecting frequent query recompilations and frequently recompiled queries. Read more »

Frequent query recompilations – a SQL query performance killer – introduction

April 9, 2014 by Milena Petrovic

When it comes to SQL Server performance tuning, there are many factors that should be checked. One of the top performance killers is a frequent query recompilation. It adds pressure to the processor and degrades performance. Read more »

SQL query performance killers – understanding poor database indexing

March 31, 2014 by Milena Petrovic

SQL Server performance is affected by many factors. The most common SQL Server performance killers are poor database design, poor indexing, poor query design, not reusable execution plans, frequent query recompilation, excessive fragmentation, and more. With these factors present on your machine, even adding additional hardware resources might not help, as these SQL Server performance killers can use all available resources. Read more »

SQL Server query execution plans – Examples with the WHERE clause

March 21, 2014 by Milena Petrovic

In the previous part of this article, we explained how indexes affect SQL Server query execution performance and plans, and provided some basic examples. In this part, we will continue with examples that show how indexes affect query execution plans and costs. Read more »

SQL Server query execution plans – Examples with the SELECT statement

March 19, 2014 by Milena Petrovic

SQL Server query execution plans are very useful in query performance analysis and troubleshooting. There are two types of query execution plans in SQL Server: actual and estimated. They show how a query was executed and how it will be executed Read more »

SQL Server query execution plans – Understanding and reading the plans

March 17, 2014 by Milena Petrovic

In the previous parts of this series, we explained what SQL Server query execution plans were, why they should be used, and presented various methods to show them. In this part, we will explain the symbols used, how to read the plans, and how to use these plans in performance analysis and troubleshooting

SQL Server query execution plans – Viewing the plans

March 15, 2014 by Milena Petrovic

In the SQL Server query execution plans – Basics, we described the query execution plans in SQL Server and why they are important for performance analysis. In this article, we will focus on the methods for opening the plans, both actual and estimated ones

SQL Server query execution plans – Basics

March 15, 2014 by Milena Petrovic

Why is query execution important for SQL Server performance?

SQL Server performance monitoring depends on the performance goals. The first step in monitoring is to determine optimal performance for your server and create performance trends based on the captured metric information. If performance is not satisfactory, it’s necessary to diagnose performance problems and find their origin. This involves testing how different queries and applications affect performance Read more »