Press "Enter" to skip to content

Category: Performance Tuning

TVF Actual Execution Plans

Published 2016-01-18 by Kevin Feasel

Kevin Eckart shows us how to get table-valued function execution plan details:

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.

Comments closed

Building A Baseline

Published 2016-01-15 by Kevin Feasel

Erin Stellato has put together a set of scripts to collect baseline stats for an instance:

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.

Comments closed