Cardinality Estimation Place in the Optimization Process in SQL Server March 27, 2018 by Dmitry Piliugin In this blog post, I’m going to look at the place of the Cardinality Estimation Process in the whole Optimization Process. We’ll see some internals, that will show us, why the Query Optimizer is so sensitive to the cardinality estimation. To understand that we should observe the main steps that a server performs when the query is sent for execution. Plan Construction Process Below you may see the picture of the general plan-building process in SQL Server from the moment when a server receives a query till the storage engine is ready to retrieve the data. Take a quick look first, and next I’ll provide explanations. Read more »
Cardinality Estimation Role in SQL Server March 26, 2018 by Dmitry Piliugin This post opens a series of blog posts dedicated to my observations of the new cardinality estimator in SQL Server 2014. But, before we jump to the new features, I’d like to provide some background, to make the next posts clearer. We’ll start by discussing the role of Cardinality Estimation in SQL Server, trying to answer – what is it and why it is needed. Read more »
SQL Server Query Execution Plans for beginners – Clustered Index Operators March 5, 2018 by Ayman Elnory We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options. In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do. Read more »
OpenQueryStore for SQL Server – Installation and configuration January 24, 2018 by Marko Zivkovic The OpenQueryStore is an Open source implementation of the popular Query store functionality introduced in SQL Server 2016 CTP2. The OpenQueryStore was first introduced in June 2017. Its main contributors are William Durkin and Enrico van de Laar The OpenQueryStore collects query runtime execution statistics, wait statistics information against a specific database. This information helps identifying performance problems and troubleshooting by quickly finding performance differences. The main difference between the OpenQueryStore and the Query store feature is that the OpenQueryStore supports SQL Server 2005 to SQL Server 2014 while Query Store supports only SQL Server 2016 and higher. Also, the OpenQueryStore is open-source which means that the OpenQueryStore code can be changed whenever you like. Read more »
Understanding SQL Server query plan cache January 18, 2018 by Ben Richardson Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance. The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa. Read more »
SQL Server Query Execution Plans for beginners – Types and Options January 4, 2018 by Ayman Elnory When a DBA is working with the SQL Server, he/she might sometimes say that the execution plan is like a black hole. As a matter of fact, though, you have to look at execution plans as weather forecasts. In this article, I will be discussing various aspects of executions plans, types and common options. Read more »
SQL Server Query Store – Overview December 29, 2017 by Marko Zivkovic The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016. It is basically a SQL Server “flight recorder” or “black box”, capturing a history of executed queries, query runtime execution statistics, execution plans etc. against a specific database. This information helps in identifying performance problems caused by query plan changes and troubleshooting by quickly finding performance differences, even after SQL Server restart or upgrade. All data that SQL Server Query Store capture are stored on disk. Read more »
How to use parallel insert in SQL Server 2016 to improve query performance December 8, 2017 by Esat Erkec Introduction In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time. SQL Server can execute queries in parallel SQL Server creates a path for every query. This path is execution plan The SQL Server query optimizer creates execution plans SQL Server query optimizer decides the most efficient way for create execution plan Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed. Read more »
How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs December 1, 2017 by Ayman Elnory Quite often developers put the responsibility of the server performance on the shoulders of DBAs. But to be honest, as a developer, it is your responsibility to write code which is efficient and optimal. However, when writing code, the developer often lacks the knowledge and/or experience about SQL Server to meet this requirement. In this series, I will be discussing various aspects of query designing, keeping performance and scalability in mind. We will be addressing the most common concepts which we often see that negatively impact SQL code written by developers. Read more »
SQL query performance tuning tips for non-production environments September 12, 2017 by Derik Hammer It is a common misconception that you need real production data, or production like data, to effectively tune queries in SQL Server. I am going to explain how you can compile the same execution plans as what your production environment would compile, so that you can tune them in a non-production environment, gaining these benefits. Read more »
How to identify slow running queries in SQL Server May 31, 2017 by Musab Umair Overview Slow running queries are one of the most common problems in every organization dealing with huge amounts of data. And the most challenging problem, in almost all the clients, I work with, is how to find the queries running slow and figuring out what is the actual cause behind the performance problem. Thankfully, the solution, in most cases, is simple. Read more »
Understanding Skewed Data in SQL Server May 22, 2017 by Susantha Bathige Introduction I recently did some research to analyze skewed data distribution in SQL Server. This article is the outcome of my analysis to share with SQL Server community. SQL Server understands its data distribution using statistics. The SQL Server query optimizer then uses the statistics to calculate estimated row counts when executing the queries submitted by users. The accuracy of estimated row count is crucial to get optimal execution plans. The SQL Server query optimizer is a complex mathematical component and it does a decent job to create better execution plans during a very short period of time. Read more »
How to Spoof Production Environment Resources for Performance Tuning May 16, 2017 by Ahmad Yaseen Performance tuning is one of the most important and critical tasks that the SQL Server Database Administrator performs on a daily basis to keep a SQL Server running in a healthy state, by identifying the performance bottlenecks and fixing the main cause of these problems. Read more »
Adaptive Query Processing in SQL Server 2017 April 28, 2017 by Musab Umair The SQL Server Query Processing Engine is the most complex and sophisticated part of SQL Server. This engine has many responsibilities, but one of the most important parts is to design the query execution plan and execute it in a best and most efficient possible manner. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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! Read more »
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. Read more »
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 Read more »
