Implicit conversion in SQL Server January 22, 2019 by Esat Erkec This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of implicit conversion Read more »
Saving your SQL Execution Plan November 28, 2018 by Ahmad Yaseen In the previous articles of this series (see the index at bottom), we discussed the characteristics of the SQL Execution Plan from multiple aspects, that include the way the SQL Execution Plan is generated by the SQL Server Query Optimizer internally, what are the different types of plans, how to identify and analyze the different components and operators of the Execution Plans, how to work with the plans using different tools and finally, tuning the performance of simple and complex T-SQL queries using the Execution Plans. In this, the last article of this series, but not the least, we will discuss where the Execution plan is stored and how to save it for future use.
SQL index overview and strategy November 27, 2018 by Bojan Petrovic A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables. Read more »
Using the SQL Execution Plan for Query Performance Tuning November 23, 2018 by Ahmad Yaseen In the previous articles of this series (see the index at bottom), we went through many aspects of the SQL Execution Plan, where we discussed how the Execution Plan is generated internally, the different types of plans, the main components and operators and how to read and analyze the plans that are generated using different tools. In this article, we will show how we can use an Execution Plan in tuning the performance of T-SQL queries. Read more »
Lightweight performance profiling in SQL Server 2019 November 21, 2018 by Rajendra Gupta Database administrators are used to dealing with query performance issues. As part of this duty, it is an important aspect to identify the query and troubleshoot the reason for its performance degradation. Normally, we used to enable SET STATISTICS IO and SET STATISTICS TIME before executing any query.
A new SQL Execution Plan viewer November 20, 2018 by Ahmad Yaseen In the previous articles of this series (see the index at bottom), we discussed many aspects of the SQL Execution Plans, starting with the main concept of SQL Execution Plan generation, diving in the different types of the plans and showing how to analyze the components and operators of the SQL Execution Plans. Read more »
SQL Execution Plan enhancements in SSMS 18.0 November 13, 2018 by Ahmad Yaseen This article will provide an overview of the SSMS 18.0 with particular focus on improvements to the SQL execution plan feature. To work with Microsoft SQL Server Database Engine, you need to have an environment to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and JSON. In addition, you need a GUI tool that helps you to configure, query, monitor and administrate your SQL Server instances wherever they are hosted; locally at your machine, on a remote Windows or Linux server or in the cloud. All this can be achieved using SQL Server Management Studio aka SSMS. Read more »
SQL Server Execution Plan Operators – Part 4 October 29, 2018 by Ahmad Yaseen In the previous articles of this series, we went through three sets of SQL Server Execution Plan operators that you will meet with while working with the different Execution Plan queries. We described the Table Scan, Clustered Index Scan, Clustered Index Seek, the Non-Clustered Index Seek, RID Lookup, Key Lookup, Sort, Aggregate – Stream Aggregate, Compute Scalar, Concatenation, Assert, Hash Match Join, Hash Match Aggregate , Merge Join and Nested Loops Join Execution Plan operators. In this article, we will dive in the fourth set of these SQL Server Execution Plan operators. Read more »
SQL Server Execution Plan Operators – Part 3 October 29, 2018 by Ahmad Yaseen In the previous articles of this series, we discussed a group of SQL Server Execution Plan operators that you will face when studying the SQL Execution Plan of different queries. We showed the Table Scan, Clustered Index Scan, Clustered Index Seek, the Non-Clustered Index Seek, RID Lookup, Key Lookup and Sort Execution Plan operators. In this article, we will discuss the third set of these SQL Execution Plan operators. Read more »
SQL Server Execution Plan Operators – Part 2 October 26, 2018 by Ahmad Yaseen In the previous article, we talked about the first set of operators you may encounter when working with SQL Server Execution Plans. We described the Non Clustered Index, Seek Execution Plan operators, Table Scan, Clustered Index Scan, and the Clustered Index Seek. In this article, we will discuss the second set of these SQL Server execution plan operators. Read more »
SQL Server Execution Plan Operators – Part 1 September 12, 2018 by Ahmad Yaseen In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components, we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation. Read more »
How to Analyze SQL Execution Plan Graphical Components September 7, 2018 by Ahmad Yaseen In the previous articles of this series, SQL Server Execution Plans overview and SQL Server Execution Plans types we went through the different stages that the submitted SQL Server query followed and how it processed by the SQL Server Relational Engine that generates the Execution Plan and the SQL Server Storage Engine that performs the requested data retrieval or modification operation. In addition, we described deeply the different types and formats of the SQL Server Execution Plans that can be used for queries performance troubleshooting purposes. In this article, we will discuss the graphical query plan components and how to analyze it. Read more »
SQL Query Optimization Techniques in SQL Server: Parameter Sniffing September 4, 2018 by Ed Pollack Description Of the many ways in which query performance can go awry, few are as misunderstood as parameter sniffing. Search the internet for solutions to a plan reuse problem, and many suggestions will be misleading, incomplete, or just plain wrong. Read more »
Performance troubleshooting when the query plan from the application is different than SSMS August 24, 2018 by James Rhoat Troubleshooting performance issues in a database is one of the main jobs of DBAs and by now most can trace the problem back to a query which is either running to slow or is causing a blocking issue on a key table. However, what is often not known is why this doesn’t cause problems in SSMS or why you don’t get the same query plan as what is inside the app. For example, in your extended event trace you see the query running longer from the application when compared to SSMS. Read more »
Understanding automatic tuning in SQL Server 2017 August 22, 2018 by Prashanth Jayaram Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle. Read more »
SQL Server Execution Plans types July 23, 2018 by Ahmad Yaseen In the previous article, we described, in detail, the different stages that a submitted SQL Server query goes through and how it processed by the SQL Server Relational Engine. The SQL Server Relational Engine generates the Execution Plan and the SQL Server Storage Engine performs the requested data retrieval or modification process. In this article, we will discuss the different types and formats for SQL Server Execution Plans. Read more »
Use cases for Query Store in SQL Server July 18, 2018 by Aamir Syed Query store was introduced in SQL Server 2016. It is often referred to as a “flight data recorder” for SQL Server. Its main function is that it captures the history of executed queries as well as certain statistics and execution plans. Furthermore, the data is persistent, unlike the plan cache in which the information is cleared upon a server restart or reboot. You can customize, within Query Store, how much and how long the query store can hold the data. Read more »
Main Concepts of SELECT operators in SQL Server execution plans July 9, 2018 by Esat Erkec One of the main responsibilities of a database administrator is query tuning and troubleshooting query performance. In this context, SQL Server offers several tools to assist. But among them, query execution plans are essential for query optimization because they include all of the vital information about the query execution process. At the same time as it provides this valuable information “under the hood”, SQL Server creates a graphical description of the execution plan. Read more »
SQL Server Execution Plans overview July 4, 2018 by Ahmad Yaseen In this series of articles, we will navigate the SQL Server Execution Plan ocean, starting from defining the concept of the Execution Plans, walking through the types, components and operators of Execution Plans analyze execution plans and we’ll finish with how to save and administrate the Execution Plans. When you submit a T-SQL query, you tell the SQL Server Engine what you want, but without specifying how to do it for you. Between submitting the T-SQL query to the SQL Server Database Engine and returning the query result to the end user, the SQL Server Engine will perform four internal query processing operations, to convert the query into a format that can be used by the SQL Server Storage Engine easily use to retrieve the requested data, using the processes assigned to the SQL Engine from the Operating System to work on the submitted query. Read more »
Query optimization techniques in SQL Server: tips and tricks June 19, 2018 by Ed Pollack Description Fixing bad queries and resolving performance problems can involve hours (or days) of research and testing. Sometimes we can quickly cut that time by identifying common design patterns that are indicative of poorly performing TSQL. Read more »
Query optimization techniques in SQL Server: the basics May 30, 2018 by Ed Pollack Description Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem! Read more »
Tracing and tuning queries using SQL Server indexes May 18, 2018 by Ahmad Yaseen In the previous articles of this series (see the full article TOC at bottom), we discussed the internal structure of the SQL Server tables and indexes, the best practices to follow when designing a proper index, the group of operations that you can perform on the SQL Server indexes, how to design effective Clustered and Non-clustered indexes and finally the different types of SQL Server indexes, above and beyond Clustered and Non-clustered indexes classification. In this article, we will discuss how to tune the performance of the bad queries using SQL Server Indexes. Read more »
The Halloween Problem in SQL Server and suggested solutions May 4, 2018 by Bhavesh Patel Description As per Wikipedia, the Halloween problem was first discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan, on Halloween day, 1976. Read more »
SQL Server 2017: Adaptive Join Internals April 30, 2018 by Dmitry Piliugin SQL Server 2017 brings a new query processing methods that are designed to mitigate cardinality estimation errors in query plans and adapt plan execution based on the execution results. This innovation is called Adaptive Query Processing and consist of the three features: Adaptive Memory Grant Feedback; Interleaved Execution; Adaptive Joins. Read more »
SQL Server 2017: How to Get a Parallel Plan April 28, 2018 by Dmitry Piliugin SQL Server chooses parallel plans based on the costing (there are also some other factors that should be met for the plan that it can go parallel). Sometimes serial plan is slightly cheaper than a parallel, so it is assumed to be faster and picked by the optimizer, however, because the costing model is just a model it is not always true (for a number of reasons, enlisted in Paul’s article below) and parallel plan runs much faster. Read more »
