07May 2012 by Grant Fritchey 1 Comment

Which SELECT * Is Better?

SQL Server, T-SQL
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT    * FROM    Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Read More
07Feb 2012 by Grant Fritchey 6 Comments

Avoiding Bad Query Performance

T-SQL
There’s a very old saying, “When you find yourself in a hole, stop digging.” And my evidence today is: That’s certainly not the hairiest execution plan I’ve seen. In some ways, it’s not all that horrible. But it sure is evidence that someone was down in a hole and they were working that shovel hard. If you’re interested, most of the operators are scans against a table variable that’s 11 million rows deep. There are also table spools chugging away in there. And the select statement only returns 1500 rows. Please, stop digging.
Read More
30Jan 2012 by Grant Fritchey 12 Comments

Execution Plans, What Do I Look At?

SQL Server, T-SQL
The question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6: Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s…
Read More
05Oct 2011 by Grant Fritchey 1 Comment

Extended Events and Performance Tuning Knowledge

Uncategorized
I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge. Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are.…
Read More
22Sep 2011 by Grant Fritchey 5 Comments

SQL Server Execution Plans

PASS, Professional Development, SQL Server
I write quite frequently about SQL Server Execution Plans. I started in that area just because that’s how you figure out what a query is doing and sooner or later, we all have to tune a query. I found I was doing it sooner and more frequently. When the opportunity came up to write a book , I jumped on it. Now I find myself presenting, rather frequently, on execution plans. One of the people I’ve learned from over the last several years is Gail Shaw (blog|twitter). I first saw Gail on stage at the PASS Summit, I think it was 2007. A co-worker of mine was picked, along with Gail, to go on stage for the Quiz Bowl. Gail was answering all the questions. If you go over to…
Read More
20Sep 2011 by Grant Fritchey 17 Comments

Statistics in Execution Plans

SQL Server, T-SQL
I was presenting on execution plans when another question came up that I didn’t know the answer to immediately. Yes, I know you’ve seen that phrase before on this blog. I love presenting because you get exactly the kinds of questions that make you think and make you learn. I’m presenting, in part, to learn, just as much as I am to teach. It was the same with kenpo. The more I taught, the better I learned the art. Wait, this isn’t supposed to be a blog post about learning. This one is about statistics. The question was, does the execution plan have the statistics that were used by the optimizer to decide on the execution plan. And no, what was meant, was not does it show the estimated rows,…
Read More
15Sep 2011 by Grant Fritchey 6 Comments

Probe Residual on Hash Match

SQL Server, T-SQL
I have to say, I only recently noticed this on a tool tip: and this in the property sheet: The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn't remember having seen one before. Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter: SELECT  soh.PurchaseOrderNumber, soh.AccountNumber, p.Name, sod.OrderQty, sod.LineTotal, cc.CardNumber FROM    Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID JOIN Production.Product AS p ON sod.ProductID = p.ProductID…
Read More
30May 2011 by Grant Fritchey 7 Comments

Optimizer Timeouts with XQuery

SQL Server
I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out? This sounds like a job for XQuery! There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code. WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),  QueryPlans AS  (  SELECT  RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,         RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,         --dest.text,         SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,                   (deqs.statement_end_offset - deqs.statement_start_offset)                   / 2 + 1) AS StatementText,         deqp.query_plan,         deqp.dbid,         deqs.execution_count,         deqs.total_elapsed_time,         deqs.total_logical_reads,         deqs.total_logical_writes FROM    sys.dm_exec_query_stats AS deqs         CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)…
Read More
04May 2011 by Grant Fritchey No Comments

SQL Rally

PASS
The Rally is next week. It’s not too late to register. This is going to be a very solid event with excellent opportunities for learning and networking. If you’re on the fence about going, don’t be. You should attend. I even have a few (somewhere near 10) slots open in my pre-conference seminar, Query Performance Tuning: Start to Finish. I’ve been working on hard on this presentation and showing pieces of it to various user groups around the country. The reception so far has been very good. If you want a seven hour brain dump on the ins and outs of performance tuning your queries, I strongly recommend it. Since there are still openings, you can get in, but I’d act quickly. Not because they’re all likely to fill, but…
Read More
02May 2011 by Grant Fritchey 3 Comments

I’ve got 99 Problems, but a disk ain’t one

nHibernate, Object Relational Mapping, SQL Server, T-SQL, Tools
Tom LaRock has a new meme for Meme Monday. It’s all about the problems caused in your system other than disks. Thankfully, despite the title, I don’t have to list 99 separate things, only 9, but you know what, 99 is possible. I’m going to present the problems. You find the solutions on your own today. Let’s go. Recompiles I’ve seen queries so big that they take more than three minutes to compile. That’s the edge case, but as an edge case it is educational. The most important thing to remember about recompiles is that they are driven by data changes. Once a threshold is reached on any given set of statistics, all queries referencing that set of statistics gets marked for recompile. The key words and tricky phrase here…
Read More