Developer’s Choice: Hinting Query Execution model

This is a cross post with http://aka.ms/sqlserverteam Over the years you have read a number of blogs advocating for or against trace flags that influence SQL Server’s query execution model. You can see a number of query execution related trace flags are documented at http://aka.ms/traceflags. However, trace flags are deemed a hacky way of influencing…

0

Predicate Pushdown and why should I care?

Cross post with http://aka.ms/sqlserverteam If you follow this blog, you know that in the last couple years, we have been adding more information to showplan, to make it a one-stop-shop for all your query performance troubleshooting insights. You can see more about those showplan enhancements here. Hold on, there’s a reason I’m writing about showplan:…


More Showplan enhancements – UDFs

Cross post with http://aka.ms/sqlserverteam As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here. In this article I’ll talk about the second showplan improvement we worked on, to assist in the discoverability of UDF…

0

More Showplan enhancements – Row Goal

Cross post with http://aka.ms/sqlserverteam As I shared before, we have been working for over a year to make showplan the one-stop-shop for query performance analysis and troubleshooting (as much as possible). With the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements…

0

Making parallelism waits actionable

Cross post with http://aka.ms/sqlserverteam During PASS we announced changes to how users will be able to make parallelism waits more actionable – specifically the “infamous” CXPACKET. This change effectively splits CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER). This change will be effective starting with upcoming SQL Server 2017 CU3 and…

0

New Showplan enhancements

Cross post with http://aka.ms/sqlserverteam If you follow this blog, you have seen that in the past few releases we have continuously included a number of diagnostic improvements to Showplan. You can read about some of them here. Looking at the actual execution plan is one of the most used performance troubleshooting techniques. Having information on…

0

New in SSMS: Searching in Showplan

Cross post with http://aka.ms/sqlserverteam Have you ever analyzed a somewhat big plan? And while doing so did you ever wish you could search for something like table name, index name or column name? In SSMS 17.2, we are including the ability to search all these and more in graphical showplan. Let’s look at an example….


Correlating xEvents with DMVs

Cross post with http://aka.ms/sqlserverteam Since the inception of xEvents there has been the need to occasionally correlate data from xEvent collections and DMVs. Specifically, using the query_hash and query_plan_hash actions and correlate with DMVs such as sys.dm_exec_requests and sys.dm_exec_query_stats for example. The blog post on Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to…


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,…