SQL Server 2017 Showplan enhancements

Cross post with http://aka.ms/sqlserverteam In my previous post on Easy way to get statistics histogram programmatically, I referred to statistics as the building blocks on which the Query Optimizer reasons to compile a good enough plan to resolve queries. Knowing the status of distribution statistics over a given table and set of columns allows the…


New in SSMS: Query Performance Troubleshooting made easier!

Cross post with http://aka.ms/sqlserverteam The community already uses tools that can make it easier to read and analyze query plans (including SSMS), but these require significant expertise in understanding query processing and plans in order to be able to actually find and fix root causes. In the latest version of SSMS that released last week,…


Easy way to get statistics histogram programmatically

Cross post with http://aka.ms/sqlserverteam Statistics being the building blocks on which the Query Optimizer reasons to compile a good enough plan to resolve queries, it’s very common that anyone doing query performance troubleshooting needs to use DBCC SHOW_STATISTICS to understand how statistics are being used, and how accurately they represent data distribution. Let’s see how…


Getting more statistics information programmatically

Cross post with http://aka.ms/sqlserverteam As the building blocks which the Query Optimizer uses to know data distribution on tables, statistics are one of the key aspects in calculating a good enough plan to read data. So, when engaging in query performance troubleshooting, information about statistics is something you need to look at. Let’s say we need…

0

Unicode strings and implicit conversions

Cross post with http://aka.ms/sqlserverteam Recently we had an interesting customer question about a seemingly strange behavior (and perhaps not widely known) on implicit conversions to Unicode. Imagine you declare a non-unicode string variable, and when concatenating strings that seem to fit the variable declaration, you get a result that is trimmed, although the sum of…


Extended per-operator level performance stats for Query Processing

Cross post with http://aka.ms/sqlserverteam Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016, such as CPU and I/O. Observe the example, where by executing the below query in AdventureWorks2014, on SQL Server 2016 SP1, we get…


Query progress: anytime, anywhere

Cross post with http://aka.ms/sqlserverteam One of the most important actions when a performance issue hits, is to get precise understanding on the workload that’s executing and how resource usage is being driven. The actual execution plan is an invaluable tool for this purpose. Query completion is a prerequisite for the availability of an actual query…


Developers Choice: USE HINT query hints

Cross post with http://aka.ms/sqlserverteam The use of Trace Flags has always been understood as a sort of ‘hack’, and a solution that is hard to manage and sometimes understand (namely because it depends on a number). Over the years, users have seen several knobs added to SQL Server to influence the Query Optimizer. These trace…

0

Developers Choice: CREATE OR ALTER

Cross post with http://aka.ms/sqlserverteam Today we are starting a new blog post series entitled “Developers Choice”. In the first few posts we will be focusing on SQL Server 2016 SP1 enhancements that focus on the developer community, including all developer DBA’s. Following in the same reasoning of simplifying DDL statements that was started by the…

0

New Showplan XML properties in SSMS October Release

Cross post with http://aka.ms/sqlserverteam Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the…

2