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

Perfect statistics histogram in just few steps

Cross post with http://aka.ms/sqlserverteam A similar question about statistics came to the team twice this week: why does updating with fullscan result in fewer histogram steps than when doing a sampled scan? The answer is: the number of histogram steps can be fewer than the number of distinct values. For a histogram with perfect distribution…

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

Persisting statistics sampling rate

Cross post with http://aka.ms/sqlserverteam When SQL Server creates or updates statistics and a sampling rate is not manually specified, SQL Server calculates a default sampling rate. Depending on the real distribution of data in the underlying table, the default sampling rate may not accurately represent the data distribution and then cause degradation of query plan…


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


New in SSMS: Performance Dashboard built-in

Cross post with http://aka.ms/sqlserverteam Back in 2007, we released the Microsoft SQL Server 2005 Performance Dashboard Reports, which were designed to provide fast insight into performance issues from some newly created system views – DMFs (Dynamic Management Views). These were updated for SQL Server 2008 and later to SQL Server 2012, and while being very…


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…