Find Indexes Used In Query Store

SQL Server
One of the most frequent questions you'll hear online is how to determine if a particular index is in use. There is no perfect answer to this question. You can look at the sys.dm_db_index_usage_stats to get a pretty good picture of whether or not an index is in use. However, this DMV has a few holes through which you could be mislead. I thought of another way to get an idea of how and where an index is being used. This is also a flawed solution, but, still, an interesting one. What if we queried the information in Query Store? Indexes Used in Query Store Now Query Store itself doesn't store index usage statistics. It stores queries, wait statistics and runtime metrics on individual queries. All useful stuff. Oh, and,…
Read More

Microsoft Tools That Help Query Tuning

SQL Server, T-SQL, Tools
Query tuning is not easy. In fact, for a lot of people, you shouldn't even try. It's much easier to buy more, bigger, better hardware. Yeah, the query is still slow on newer, faster hardware, but not as a slow as it was. However, sooner or later, you're going to have to start to spend time fixing queries. In fact, you can find that fixing queries actually is more cost effective than buying more hardware. The problem is, query tuning is not easy. So, what do you do? Microsoft Can Help There are a number of tools available to you, right now, provided by Microsoft that can help you better and more easily tune your queries. This ranges from extended events to query store, and absolutely includes execution plans and…
Read More

Which Query Used the Most CPU? Implementing Extended Events

SQL Server, T-SQL, Tools
A question that comes up on the forums all the time: Which query used the most CPU. You may see variations on, memory, I/O, or just resources in general. However, people want to know this information, and it's not readily apparent how to get it. While you can look at what's in cache through the DMVs to see the queries there, you don't get any real history and you don't get any detail of when the executions occurred. You can certainly take advantage of the Query Store for this kind of information. However, even that data is aggregated by hour. If you really want a detailed analysis of which query used the most CPU, you need to first set up an Extended Events session and then consume that data. A…
Read More

Missing Indexes in the Query Store

SQL Server 2016, SQL Server 2017, T-SQL, Tools
I've shown before how to use the DMVs that read the plan cache as a way to connect the missing indexes suggestions with specific queries, but the other place to find missing index suggestions is the Query Store. Pulling from the Query Store The plans stored in the Query Store are exactly the same as the plans stored within the plan cache. This means that the XML is available and you can try to retrieve information from it directly, just as we did with the missing index queries against the DMVs. Here's the query modified for the Query Store: WITH XMLNAMESPACES (DEFAULT '') SELECT qsqt.query_sql_text, rts.plan_id, rts.NumExecutions, rts.MinDuration, rts.MaxDuration, rts.AvgDuration, rts.AvgReads, rts.AvgWrites, qsp.QueryPlan, qsp.QueryPlan.value(N'(//MissingIndex/@Table)[1]', 'NVARCHAR(256)') AS TableName, qsp.QueryPlan.value(N'(//MissingIndex/@Schema)[1]', 'NVARCHAR(256)') AS SchemaName, qsp.QueryPlan.value(N'(//MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)') AS ProjectedImpact, ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage, ColumnGroupColumn.value('./@Name',…
Read More

Query Store and a READ_ONLY Database

T-SQL, Tools
What happens in Query Store when the database itself is READ_ONLY? Yeah, I don't know. Let's find out. READ_ONLY The only way to find out how this works is to test it. So, let's write some code: CREATE DATABASE testquerystore; GO ALTER DATABASE testquerystore SET READ_ONLY; GO ALTER DATABASE testquerystore SET QUERY_STORE = ON; Executing that resulted in a small glitch in the Matrix: 8:00:54 AMStarted executing query at Line 1Commands completed successfully.8:00:54 AMStarted executing query at Line 2Commands completed successfully.8:00:54 AMStarted executing query at Line 5Msg 5004, Level 16, State 6, Line 5To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.Msg 5069, Level 16, State 1, Line 5ALTER DATABASE statement failed.Total execution time: 00:00:01.448 Well that's not going to…
Read More

All Day, Training Day at SQLBits

Azure, SQL Server 2016, SQL Server 2017, T-SQL
It's a somewhat late addition, but I have an all-day Training Day at SQLBits. It takes place on Thursday, February 28th. You can read all about it on the SQLBits web site. I want to take a moment here to expand on the information that we're going to cover. I think the abstract does a good job of conveying what we'll be doing all day, but I figured a little more detail won't hurt. Query Tuning is Hard This is the very first thing I talk about. Query tuning is hard. I've got a nearly 1,000 page book on the topic, which should give you an idea of just how much material there is to cover. With the training day I've decided to focus on the tools that Microsoft gives…
Read More

Measuring Query Execution Time: What Is Most Accurate

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL, Tools
Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. However, all measures are not created equally. In fact, they frequently disagree with one another. Let's take a look at this odd phenomenon. Measuring Query Execution Time Before we get into all the choices and compare them, let's baseline on methodology and a query to use. Not sure why, but many people give me blow back when I say "on average, this query runs in X amount of time." The feedback goes "You can't say that. What if it was just blocking or resources or..." I get it.…
Read More

A View Will Not Make Your Query Faster

SQL Server 2017
Twice recently, one on a blog post, and one in a forum post, I've seen people state, unequivocally, without reservation or hint of a caveat, that, "Oh, just put that query into a view. It will run faster." To quote the kids these days... Time for a rant. But First... Frequently when I post something that says, "Query Y runs faster than Query Red", I get responses from people saying, "Yeah, but if you run Query Red more than once..." or "Query Red was experiencing blocking..." or "You can't say Query Y is ALWAYS faster..." So, before we go down that road, a quick note on methodology. First, I'll be using Adventureworks because, reasons. Second, I won't run any of the following queries once. When doing something like this, I'll…
Read More

Automating Automatic Indexing in Azure SQL Database

I've been in love with the concept of a database as a service ever since I first laid eyes on Azure SQL Database. It just makes sense to me. Take away the mechanics of server management and database management. Focus on the guts of your database. Backups, consistency checks, these easily automated aspects can just be taken care of. Same thing goes with some, not all, but some, index management. Azure SQL Database can manage your indexes for you. I call it weaponizing Query Store. Anyway, I needed a way to automate this for the book I'm writing. I couldn't find any good examples online, so I built my own. Queries in Need of Automatic Indexing Because I want this to be as simple and repeatable as possible, I'm using…
Read More

Every Single Execution Plan is an Estimated Plan

SQL Server, SQL Server 2016, SQL Server 2017
All the execution plans are estimated plans. All of them. There fundamentally isn't any such thing as an "Actual" plan. Where Do You Get Execution Plans? There are a lot of sources for execution plans. You can capture them using extended events (or, if you must, trace). You can capture them through the Management Studio gui. You can also capture them from the SQL Operations Studio gui. You can query the cache through the DMVs and pull them in that way. You can look at plans in query store. All these resources, yet, for any given query, all the plans will be identical (assuming no recompile at work). Why? Because they're all the same plan. Each and every one of them is an estimated plan. Only an estimated plan. This…
Read More