03Jun 2019 by Grant Fritchey 2 Comments

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
16May 2016 by Grant Fritchey 8 Comments

Query Store, Forced Plans, and New Plans

Azure, SQL Server 2016
I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let's say you have selected a plan that you want to force. You set it up. Now, let's say the plan ages out of cache or even goes through a recompile. During the recompile, due to out of date statistics or skew in the statistics, you would, under normal circumstances, get a new plan. However, with Query Store and plan forcing, the plan that's going to be used is the plan that is being forced. But, does that other plan, the one not used, get stored in Query Store? I have no idea. Let's find out. The Setup To start with, a small stored procedure…
Read More
03May 2016 by Grant Fritchey 5 Comments

Implicit Conversion and Performance

SQL Server, SQL Server 2016, T-SQL
Letting SQL Server change data types automatically can seriously impact performance in a negative way. Because a calculation has to be run on each column, you can't get an index seek. Instead, you're forced to use a scan. I can demonstrate this pretty simply. Here's a script that sets up a test table with three columns and three indexes and tosses a couple of rows in: CREATE TABLE dbo.ConvertTest ( BigIntColumn BIGINT NOT NULL, IntColumn INT NOT NULL, DateColumn VARCHAR(30) ); CREATE INDEX BigIntIndex ON dbo.ConvertTest (BigIntColumn); CREATE INDEX IntIndex ON dbo.ConvertTest (IntColumn); CREATE INDEX DateIndex ON dbo.ConvertTest (DateColumn); WITH Nums AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1 )) AS n FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ) INSERT INTO dbo.ConvertTest (BigIntColumn, IntColumn, DateColumn ) SELECT Nums.n, Nums.n,…
Read More
14Aug 2014 by Grant Fritchey No Comments

A Full Day of Query Tuning

SQL Server, T-SQL
I'm excited to able to say that I've been given the opportunity to put on a full day workshop at SQL Connections on Friday, September 19th, 2014. The title is "Query Performance Tuning in SQL Server 2014", but I assure you we're going to cover things that are applicable if you're still working on SQL Server 2005. We'll start the day covering the different mechanisms you have to capture query metrics. We'll go over dynamic management objects and extended events that are incredibly important to you in understanding which queries you need to tune. We'll get an introduction into how the optimizer works and the importance that statistics, indexes and constraints play in helping the optimizer make the choices it makes. I promise, execution plans will be covered throughout the…
Read More
27Jun 2012 by Grant Fritchey 5 Comments

The New Phone Book Is Here!

PASS
I can't help it. I get really terribly excited when I publish a book. Maybe it should be old hat. Maybe I should be jaded. But I'm just a 12 year old (it's been argued 10) in reality so I get really, really jumping up & down excited when I get that wad of paper and my name is on the cover. It just doesn't seem to get old. What am I talking about? Oh, sorry. Let me explain. After about nine months of work, my new, revised edition, of the Query Performance Tuning book is available. I want to publicly, and loudly, thank Joe Sack(blog|twitter) for the incredible job he did as tech editor. His hard work, and ruthless criticism, made this book what it is. Despite the scar…
Read More