06Jan 2015 by Grant Fritchey 5 Comments

Execution Plans, Performance Tuning and Rum

Misc
[caption id="attachment_2827" align="alignleft" width="150"] This is me on the last cruise[/caption] In just a few more weeks I'll be setting sail on the first of two SQL Cruise events this year. I'm honored beyond my ability to appropriately express it to be included as one of the Technical Leads for both the cruise in February (7-14, 2015, I think there's a seat or two left) to the Caribbean and the one in June (14-21, 2015, definitely a couple of seats left) to the Mediterranean. Lest you think that this is just an excuse to hang out and drink, you ought to know a little about how sessions are presented on the cruise and the sessions I'm presenting. Don't mistake sessions on the boat for your typical one hour session at…
Read More
19Dec 2014 by Grant Fritchey 20 Comments

“Pretty Plans vs. Performance” or “Grant Gets Pwned”

SQL Server
If you get an execution plan that looks like this: I wouldn't blame you for immediately thinking about query tuning. Especially if the code that generated it looks like this: SELECT soh.OrderDate, sod.OrderQty, sod.LineTotal FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80,…
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
16Sep 2014 by Grant Fritchey No Comments

PASS Summit 2014 Pre-Conference Seminar

PASS, T-SQL
I'm putting on a pre-conference seminar (also known as a pre-con) at the PASS Summit this year. I'm really honored to be able to present this and I'm pretty excited about it. So, if you want to talk query tuning, let's get together at the Summit. For a few fun facts about the event, check out this Q&A over at PASS. To register for the event and my pre-con, go here now.
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
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
21Mar 2014 by Grant Fritchey No Comments

PASS DBA Virtual Chapter Talk

Azure, PASS
I almost forgot to tell you about the Database Administration Virtual Chapter meeting next week, March 26th, 2014. I'll be doing a talk about query tuning in Windows Azure SQL Database. It's a talk I've given before (it was in the top 10 at the PASS Summit last year). Come find out why you'll need to tune queries in WASD, the tools you get, and the glorious fact that you'll actually be actively saving your business money by tuning queries! Click here now to register.
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
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