Execution plans

$C:\Users\Neeraj\Desktop\TOP\IMAGES\FinalSerialNL.JPG$

SQL Server TOP clause performance problem with parallelism

April 13, 2017 by Neeraj Prasad Sharma

Basics of TOP Operator

The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because it can produce results in any order depending on the current schema and execution plan. You can also specify a percent with TOP expression which returns only the expression percent of rows from the result set. Float expressions used in TOP percent are rounded up to the next integer value.

Saving the Plan Cache storage using the Optimize for Ad hoc Workloads option

February 23, 2017 by Ahmad Yaseen

When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.

Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.

Different Ways to set the Max Degree of Parallelism in SQL Server

December 29, 2016 by Ahmad Yaseen

In this article, we will go through the different ways to set the Max Degree of Parallelism option. Before doing that, it is helpful to understand the concept of parallelism, how it works and what the MSXDOP is.

SQL Server Estimated Vs Actual Execution Plans

December 29, 2016 by Ahmad Yaseen

A SQL Server execution plan is the most efficient and least cost road map that is generated by the Query Optimizer’s algorithms calculations to execute the submitted T-SQL query. Execution plans are used by the database administrators to troubleshoot the performance of poorly performing queries to isolate the part of the query that is at the root of the performance issue.

What’s new in SQL Server 2014 Cardinality Estimator?

November 14, 2016 by Ahmad Yaseen

The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return. This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute.

SQL Server 2016 Parameter Sniffing

October 19, 2016 by Ahmad Yaseen

SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.

Force query execution plan using SQL Server 2016 Query store

July 29, 2016 by Ahmad Yaseen

SQL Server Query Store is a new feature introduced in SQL Server 2016 that is used to automatically and asynchronously capture query execution history, statistics and plans, with minimal impact to overall SQL Server Performance. The Query Store feature makes performance problem troubleshooting simple; you can view the query execution plans changes and compare its performance to decide which execution plan the SQL Server Query Optimizer should use for that query.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

Searching the SQL Server query plan cache

February 8, 2016 by Ed Pollack

Whenever a query is executed in SQL Server, its execution plan, as well as some useful execution data are placed into the plan cache for future use. This information is a treasure trove of metrics that can allow some very useful insight into your server’s performance and resource consumption. Much of this information would be difficult or impossible to acquire otherwise.

Understanding how to access and use the metadata about query execution will provide us the tools we need to answer questions about our server and gain fascinating performance data. I’ve found myself spending more and more time writing, tweaking, and using queries against the plan cache lately and look forward to sharing these adventures with you!

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 »