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

Learning Jupyter Notebooks

Azure, Professional Development, Tools
I'm starting the process of learning how to use Jupyter Notebooks. Notebooks are documents that contain live code, commentary, results, pictures and more. Jupyter Notebooks are used for presentations, documentation, run books, troubleshooting guides and lots more. Their support within Azure Data Studio opens up lots of opportunities. Azure Data Studio If you're interested in learning about notebooks yourself, or, as I publish the notebooks that I put together and you want to consume them, you need to have a mechanism. There are any number of third party or open source solutions to read notebooks. However, since I'm focused primarily on the Microsoft data platform, I'm using Azure Data Studio to do this work. I've written in the past about using Azure Data Studio (ADS). I also have a bunch…
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 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 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

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

SQL Clone

Today is the launch of SQL Clone, a great new tool that helps you quickly and easily provision SQL Server databases for development and testing. Oh god, that sounds like marketing speak. To heck with that. Let me tell you why I'm so excited about SQL Clone and why I think you're going to be excited too. Once Upon a Time... Almost two years ago one of the developers here at Redgate called me over. He wanted to show off this neat trick he'd figured out. What I saw was a good-sized database, about 200gb, created on his local instance of SQL Server in about 10 seconds. Now, that's fast. Further, he showed me the files and disk space on his machine, and it was only taking up a few…
Read More

Toshiba Portege Z301-A Review

Last year I purchased a Lenovo W530. Nightmares then ensued. Nice laptop if you just want to use it as a laptop, and only a laptop. Horror show if you need to present from it. So, I replaced it with an HP... which also gave me some troubles (try presenting with your laptop bricked in front of your boss AND 100 people), but that's OK because I dropped it the next day (totally by accident, it had just worked great all day, I was happy with it, mostly). So there I was, laptop free and in need of something that would let me work, present, and be an all round road warrior. PLUS, I had just broke a laptop and was feeling bad that company would be replacing it. So…
Read More

Database in Source Control

SQL Server, Tools
Many years ago, I was working with a great DBA. Seriously, a very smart and capable guy. He told me, "We need to put the database into source control, just like app code." And I just laughed. Not because I disagreed with him. I knew he was right, but I had tried, several times, to do just that. See, I'm not really a DBA. I'm a developer. I knew that code (and all the T-SQL that describes databases is code) needed to be versioned, sourced, tracked and audited. But great googly moogly, it was not an easy thing to do. I first tried just exporting the entire database into a script and then occasionally checking that script into source control. Yay! Mission Accomplished... Well, I had a database in source…
Read More

24 Hours of PASS, Fall 2012

PASS, SQL Server, T-SQL, Tools
It's time to get your learn on again. The schedule for the Fall 24 Hours of PASS is up and ready for registration. This is the Summit preview session, so many (most, all) of the speakers are showing off some of what you can learn at their sessions at the PASS Summit 2012 itself. It looks like a pretty exciting bunch of topics given by some of the best professionals in the industry. I'll be presenting Three Ways to Identify Slow Running Queries on September 20th, 1400 GMT. This is just a sub-set of the information that I'll be presenting during my all day pre-conference seminar, Query Performance Tuning: Start to Finish. The full seminar I talk about how to measure the performance of your systems, identify which queries are…
Read More

SQL In The City: London 2012, Recap

Professional Development, Redgate Software, T-SQL, Tools
Wow! How's that for a recap? The concept for the SQL in the City events is pretty simple. Put on a free event that instructs people on SQL Server, Azure, and related technologies along with a healthy smattering of Red Gate tools. All teaching is done by some of the best people in the business (and me). This was the second event in London. The concept was launched there last year and succeeded quite well. This year the event filled it's registrations so quickly that Red Gate felt obligated to have a second day, which almost completely filled up too. There were more than 350 people in attendance on Friday, and then, on Saturday, a day off, another 250+ people showed up. That's well over 600 attendees over the two…
Read More