Querying the Execution Plan for Subtree Cost - Take 2

A few weeks ago I shared a query for probing the execution plan for the Estimated Subtree Cost value.

That query was rather simple but it got the job done. In that post I also explained how, although there are several ways of finding missing index suggestions, they were not always very helpful. After posting I was thinking that it would be helpful to combine the two, or at least display the results so that it is easy to determine which execution plans include missing index warnings.

Querying the Execution Plan XML for Subtree Cost

There are quite a lot of great tools and queries available to the DBA that aid in troubleshooting and performance tuning. The list is incredibly long, but I mainly use RedGate's SQL Monitor (which has come on in the last 5 years in leaps and bounds), Glenn Berry's DMV queries, Adam Machanic's sp_whoisactive and Brent Ozar Unlimited's sp_Blitz suite (predominantly sp_Blitz and sp_BlitzIndex). In writing this, I see that Brent Ozar has expanded his offering, so it looks like I have a weekend project ahead of me!

SQL 2016 Database Scoped Configuration: MAXDOP

SQL Server 2016 Microsoft expanded the range and flexibility of database level configuration settings. There are some new features, as well as features which that can be set at more than just the server or database scope. Now, among other things, we are able to:

• set certain options both at the instance and the database level (MAXDOP)
• set instance level trace flag settings at the database level (LEGACY_CARDINALITY_ESTIMATION)
• the Trace Flag 1117 is now set at the FILEGROUP LEVEL
• the brand new database level CLEAR PROCEDURE_CACHE

A summary of the new options can be found on the Technet blog.

In this post I am going to focus on the MAXDOP feature. Having read a few articles and posts about this feature it wasn't clear to me exactly how this feature worked. Especially in regard to the relationship between the instance level setting and the database level setting.

Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.

Compress your database tables

SQL Server has offered data compression since SQL 2008. It is a very good feature but, as with almost everything SQL Server, it needs to be deployed judiciously. Data compression has been written about and reviewed by many SQL experts who are much more qualified and knowledgeable than me, so I won't go into it too much.

First, a quick summary of what the feature does as well as its benefits and effects:
As the feature's name suggests, when it is enabled on a table and/or its index(es), SQL server compresses (or shrinks) the data before it is written to the database. There are two types: ROW and PAGE.