To understand why we get this performance degradation with SQL Server 2016 RC1 three key parts of a transactions life cycle need to be understood along with the serialisation mechanisms that protect them
Chris digs into call stacks as part of his post. We’ll see if there are some performance improvements between now and RTM on this front.
The SQL Server query optimizer can find interesting ways to tackle seemingly simple operations that can be hard to optimize. Consider the following query on a table with two indexes, one on (a), the other on (b):SELECT a, b FROM #data WHERE a<=10 OR b<=10000;
The basic problem is that we would really want to use both indexes in a single query.
We get to see a few different versions of the query as well as the execution plans which result.
Performance Monitor uses incorrect calculation for certain types of counters in Windows 8, Windows Server 2012, Windows 7 SP1, or Windows Server 2008 R2 SP1
This only cost us a week of reviewing results.
Follow up on the link because there’s a fix available through Windows Update.
Getting plan details isn’t free. The amount of impact depends on what the query is doing, but there’s a stiff overhead to collecting actual execution plans and to watching live query statistics.
These tools are great for reproing problems and testing outside of production, but don’t time query performance while you’re using them– you’ll get too much skew.
Live Query Statistics is one additional tool, but won’t replace actual execution plans. At its best, it will make you think more about what’s going on with the system, whether row counts are what you’re expecting, and take account of which operators stream data through without blocking (such as nested loop joins) versus those which require all the data before continuing (sorts).
While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF.
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.
As Kevin notes, this extended event runs the risk of degrading performance, so don’t do this in a busy production environment.
The topic of baselines in SQL Server is one that I’ve had an interest in for a long time. In fact, the very first session I ever gave back in 2011 was on baselines. I still believe they are incredibly important, and most of the data I capture is still the same, but I have tweaked a couple things over the years. I’m in the process of creating a set of baseline scripts that folks can use to automate the capture of this information, in the event that they do not have/cannot afford a third-party monitoring tool (note, a monitoring tool such as SQL Sentry’s Performance Advisor can make life WAY easier, but I know that not every can justify the need to management). For now, I’m starting with links to all relevant posts and then I’ll update this post once I have everything finalized.
If you don’t know what “normal” looks like, you’ll have a hard time discerning whether something is wrong. The better you understand a normal workload, the easier it is to spot issues before end users call you up.