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
15Jul 2014 by Grant Fritchey 2 Comments

Execution Plan Details

PASS, SQL Server, T-SQL
I wouldn't say it's common knowledge that you should look at execution plans when tuning queries, but it's not exactly uncommon knowledge either. But, people tend to get focused on just looking at the graphical part of the plan and there's just not enough information there. Let's take a look at a query: SELECT pc.Name, ps.Name, v.Name, pr.ReviewerName, p.Name, v.ModifiedDate, p.Color FROM Production.Product AS p LEFT JOIN Production.ProductReview AS pr ON pr.ProductID = p.ProductID JOIN Production.ProductSubcategory AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID JOIN Production.ProductCategory AS pc ON pc.ProductCategoryID = ps.ProductCategoryID JOIN Purchasing.ProductVendor AS pv JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = pv.BusinessEntityID ON pv.ProductID = p.ProductID WHERE v.ModifiedDate = '2006-02-17 00:00:00.000' AND p.Color LIKE 'Y%'; This generates an execution plan that looks like this: Neither v.ModifiedDate nor p.Color have indexes.…
Read More
13May 2014 by Grant Fritchey 8 Comments

Add an Instance to SQL Server Azure Virtual Machine

Azure, SQL Server
How do you add an instance to your local SQL Server installation? You run the executable that you probably downloaded from MSDN or maybe from a CD. Works the same on an Azure VM right? Sure... but wait. Do I have to go and download the software to my VM instance? Let's assume that you're running one of the VMs from the Gallery, then, the answer is "No." Just navigate to C:\SQLServer_12.0_Full. There you'll find the full installation setup for SQL Server. And you're off and running... Until you realize that you don't have the Product Key for this thing. What happens when you get to this screen: You can look around all you want and you won't see a product key anywhere. At least no where that I could…
Read More
08Apr 2014 by Grant Fritchey 6 Comments

Curation and Performance

Misc, SQL Server
I'm trying out a new web site from Microsoft called Curah! that is all about curation. Curation is basically what blogging started out as. Blogs, short for Web Log, was really just a collection of links you'd visited recently and what you thought about them. But it's grown into all manner of things, the least of which is a collection of links and what I thought about them. However, the concept of a useful set of links, why they might be useful, what you'll find there, these concepts still have value. Hence the rise of curation. As a concept, I get it. I don't think it deviates radically from what we do with our blogs, our resources pages (see the links above), and other similar functions. But, it is rather…
Read More
19Mar 2014 by Grant Fritchey No Comments

Query Tuning Near You

SQL Server, T-SQL
It really is so much easier to just throw hardware at badly performing databases. Just buy a bigger, faster server with more and faster disks and you can put off doing tuning work for another 6-9 months, easily. But, for most of us, sooner or later, our performance problems get so big or, we just don't have any more money to spend, and we're stuck. We have to tune the queries. And frankly, query tuning is a pain in the nether regions. But, after you've tuned queries 20 or 30 times, you start to recognize the patterns and it gets easier (never easy, just not as hard). But, if you haven't done it 20 or 30 times, what do you do? My suggestion, talk to someone who has done it…
Read More
18Mar 2014 by Grant Fritchey 2 Comments

Finding Mistakes

SQL Server, T-SQL
Ever had that moment where you start getting errors from code that you've tested a million times? I had that one recently. I had this little bit of code for pulling information directly from query plans in cache: WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@PhysicalOp', N'varchar(50)') AS OperatorName, RelOp.pln.value(N'@NodeId',N'integer') AS NodeId, RelOp.pln.value(N'@EstimateCPU', N'decimal(10,9)') AS CPUCost, RelOp.pln.value(N'@EstimateIO', N'decimal(10,9)') AS IOCost, dest.text FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp CROSS APPLY deqp.query_plan.nodes(N'//RelOp') RelOp (pln) ) SELECT qp.OperatorName, qp.NodeId, qp.CPUCost, qp.IOCost, qp.CPUCost + qp.IOCost AS EstimatedCost FROM QueryPlans AS qp WHERE qp.text = 'some query or other in cache' ORDER BY EstimatedCost DESC; I've probably run this... I don't know how many times. But... I'm suddenly getting an error: Msg 8114, Level 16, State 5,…
Read More
11Mar 2014 by Grant Fritchey 4 Comments

sp_updatestats Is Not Smart

SQL Server, T-SQL
No, I don't mean the use of sp_updatestats is not smart. It's a fine, quick mechanism for getting statistics updated in your system. But the procedure itself is not smart. I keep seeing stuff like "sp_updatestats knows which statistics need to be updated" and similar statements. Nope. Not true. Wanna know how I know? It's tricky. Ready? I looked at the query. It's there, in full, at the bottom of the article (2014 CTP2 version, just in case yours is slightly different, like, for example, no Hekaton logic). Let's focus on just this bit: if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0))) The most interesting part is right at the front, @ind_rowmodctr <> 0. That value is loaded with the cursor and comes from sys.sysindexes and the rowmodctr column…
Read More
27Feb 2014 by Grant Fritchey 2 Comments

Let’s Talk Query Tuning

Professional Development, SQL Server, T-SQL
I spend quite a bit of time writing about query tuning on this blog. I've written (re-written and am actively re-writing) books on query tuning. But what I like most is talking about query tuning. I love giving sessions at various events on different aspects of query tuning, but, what I like the most is spending a whole day, trying to do a complete brain dump to get as much information out there as possible. Sound attractive? Then I've got a great deal for you. Come to Louisville on June 20th, 2014. We will talk query tuning at length. You have a specific question? Let's get it answered. Then, the next day, we can all go to SQL Saturday 286 there in Louisville to get more learning and some serious…
Read More
18Feb 2014 by Grant Fritchey 14 Comments

The CASE Statement and Performance

SQL Server, T-SQL
In case you don't know, this query: UPDATE dbo.Test1 SET C2 = 2 WHERE C1 LIKE '%33%'; Will run quite a bit slower than this query: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 LIKE '333%'; Or this one: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 = '333'; That's because the second two queries have arguments in the filter criteria that allow SQL Server to use the statistics in an index to look for specific matching values and then use the balanced tree, B-Tree, of the index to retrieve specific rows. The argument in the first query requires a full scan against the index because there is no way to know what values might match or any path through the index to simply retrieve them. But, what if we…
Read More
20Jan 2014 by Grant Fritchey 35 Comments

Time for a Quick Rant

Professional Development, SQL Server
This is an actual quote from what we can only assume is a functional human being: The database is very big so we stopped taking backup's. Eight lords a leaping are you kidding me? Seriously! Seriously? By the Great Gu and all the Valkyries in Valhalla, you stopped taking backups of your PRODUCTION database because it was "very big." And I'll put down Brobdingnagian stacks of cash that "very big" in this case is probably 200-500gb or at worst 1-2tb. People, assuming you have enough brain stem intact to regulate breathing, you must know, you must by all the sparkly vampires in Twighlight KNOW that you need to have backups. Right? I mean, nothing ever goes wrong on this shiny marble we call Dirt, does it? No one would EVER…
Read More