16Oct 2014 by Grant Fritchey No Comments

Query Performance Tuning in SQL Server

SQL Server
I have a pre-con coming up at the PASS Summit. You can read about it here. I named it "Query Performance Tuning in SQL Server 2014" because it seemed like a good idea to bring out the aspects of 2014, and we will. But, I need to tell you, this is primarily a session about query performance tuning in SQL Server, full stop. I'm going to cover information that's applicable all the way back to SQL Server 2000 and 2005. The majority of the information will be applicable to 2008 and up. I'm going to go over the things you can do with dynamic management views to pull information about queries to tune right out of the cache. That's applicable to more than 2014. We're also going to go over…
Read More
23Sep 2014 by Grant Fritchey 8 Comments

SQL Server Query Performance Tuning

SQL Server, T-SQL
The latest update to my book, SQL Server Query Performance Tuning was released last week. This is the fourth edition of the book, and the third edition that I've been responsible for. At the urging of my editor, Jonathan Gennick, I have completely restructured the book for this release. The chapters have been broken up and rearranged so that they're smaller, more easily consumed. Yes, I've worked with my technical editor, Joe Sack, to add lots of new information and to ensure that the existing information is more accurate and more useful. But, we've also added new chapters on topics that weren't given enough attention in the previous versions of the book, such as parameter sniffing. It can certainly seem like these book releases are just some incremental changes on top of…
Read More
10Sep 2014 by Grant Fritchey No Comments

Statistics in SQL Server

SQL Server
I've put together a new Curah! of links to some of the better articles on SQL Server Statistics, specifically those for SQL Server 2014. The changes in the cardinality estimator can't be ignored. If you want to talk about statistics, I'll be presenting at Live360 in November on that topic and several others. Go here to register for this event.
Read More
03Sep 2014 by Grant Fritchey 12 Comments

Left or Right?

SQL Server, T-SQL
No, this is not about politics. It's about your WHERE clause... and your JOIN criteria... and your HAVING clause. It's about a canard that still makes the rounds occasionally. Please, help me put this statement to sleep for once and all: A function on the left side of the equals sign can lead to performance problems Well, you know, it's sort of true. But then, a function on the right side of the equals sign can also lead to performance problems. In short, it's not the placement of the function that causes issues, it's the function that causes issues. Let's take a look at a really simple example: SELECT a.AddressID, a.AddressLine1, AddressLine2 FROM Person.Address AS a WHERE a.AddressLine1 = 'Downshire Way'; This simple query results in an equally simple execution plan: Now,…
Read More
20Aug 2014 by Grant Fritchey 2 Comments

The Red Gate Way…

DevOps, PASS, Professional Development, Redgate Software, SQL Server, T-SQL
As companies go, Red Gate is a little different. That is readily apparent in our tools and the philosophy behind them, ingeniously simple. But, we do a lot of other things too. There's the Simple-Talk web site where we publish serious articles on all aspects of development and database administration across platforms and programming languages. There's SQL Server Central, the single largest SQL Server community on the planet. There's Ask SQL Server where you can get direct answers to your direct questions about SQL Server. If all that's not enough, there are all the books, which we give away for free, on, again, all aspects of programming and database administration. But, we like to do more, so we also bring you training, the Red Gate way, at the SQL in…
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
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