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…


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

When do conversions generate conversion warnings? And why are these bad?

Hello, A few days ago a colleague asked me why was he seeing implicit conversions in the execution plan, but no warnings of such sort were being issued. Warnings are seen in the plan if the conditions they affect occur, and then are surfaced in the <Warnings> element of SHOWPLAN, either as affecting Cardinality Estimates…

0

Table variables and row estimations – Part 2

Hello again, On my previous post, we covered how the Query Optimizer handles row estimation when using Table variables under specific conditions. Following up on that, I will demonstrate other scenarios where the Query Optimizer must try to optimize queries when no statistics and histograms are available. In the 2nd example of the series I…


Table variables and row estimations – Part 1

Hello all, The subject of estimated rows vs. actual rows in plan execution has a lot of impact in query performance, and the source of these skews can be quite diverse, from outdated statistics, to incorrect sampling, or the inability of the query processor to know the value of certain variables at compile time, just…

0

A case of seeks and scans

Hello all, A couple weeks ago while I was onsite, I was looking at some queries that had performance issues. To the customer, one in particular had become a conundrum that could not be easily answered with the usual approach of good indexing, simply because there was already a good covering index for that highly…

2

Auto Update Statistics Asynch - Are you really, really using it?

Hello all, It’s been awhile since I’ve updated the blog from my part. It’s been a rough month, and I’ve been on vacation prior to that, but I’m back. What I want to get your attention in this post is to the “Auto Update Statistics Asynchronously” database option. Not to discuss in which scenarios to…

0