17Jun 2014 by Grant Fritchey 2 Comments

Natively Compiled Procedures and Bad Execution Plans

Uncategorized
I've been exploring how natively compiled procedures are portrayed within execution plans. There have been two previous posts on the topic, the first discussing the differences in the first operator, the second discussing the differences everywhere else. Now, I'm really interested in generating bad execution plans. But, the interesting thing, I wasn't able to, or, rather, I couldn't see evidence of plans changing based on silly things I did to my queries and data. To start with, here's a query: CREATE PROC [dbo].[AddressDetails] @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID JOIN dbo.CountryRegion AS cr ON…
Read More
10Jun 2014 by Grant Fritchey 3 Comments

Differences In Native Compiled Procedures Execution Plans

Uncategorized
All the wonderful functionality that in-memory tables and natively compiled procedures provide in SQL Server 2014 is pretty cool. But, changes to core of the engine results in changes in things that we may have developed a level of comfort with. In my post last week I pointed out that you can't see an actual execution plan for natively compiled procedures. There are more changes than just the type of execution plan available. There are also changes to the information available within the plans themselves. For example, I have a couple of stored procedures, one running in AdventureWorks2012 and one in an in-memory enabled database with a few copies of AdventureWorks tables: --natively compiled CREATE PROC dbo.AddressDetails @City NVARCHAR(30) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL…
Read More
05Jun 2014 by Grant Fritchey 2 Comments

Natively Compiled Procedures and Execution Plans

T-SQL
The combination of in-memory tables and natively compiled procedures in SQL Server 2014 makes for some seriously screaming fast performance. Add in all the cool functionality around optimistic locking, hash indexes and all the rest, and we're talking about a fundamental shift in behavior. But... Ah, you knew that was coming. But, you can still write bad T-SQL or your statistics can get out of date or you can choose the wrong index, or any of the other standard problems that come up that can negatively impact all those lovely performance enhancements. Then what? Well, same as before, take a look at the execution plan to understand how the optimizer has resolved your queries. But... Yeah, another one. But, things are a little different with the natively compiled procedures and…
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
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
29Oct 2013 by Grant Fritchey 1 Comment

Query Tuning in Dallas

SQL Server, T-SQL
Let's have some fun. This Friday, November 1, 2013, I'm putting on an all day seminar on query tuning. It's set up as a pre-conference event for SQL Saturday 255 in Dallas. It's a 200 level course on understanding how the query optimizer works, the importance of statistics, constraints and indexes, how to read execution plans, and how to take all that knowledge and go to work on tuning your queries. Here's the fun. Sign up for the seminar, and bring a nasty query you've been trying to tune or a query you don't understand or an execution plan that's making you crazy. Depending on the time available near the end of the day, we'll walk through a few of them. I've slightly restructured the seminar so I have some…
Read More
18Sep 2013 by Grant Fritchey 17 Comments

Finding Ad Hoc Queries with Query Hash

SQL Server, T-SQL
I was presenting a session on how to read execution plans when I received a question: Do you have a specific example of how you can use the query hash to identify similar query plans. I do, but I couldn't show it right then, so the person asking requested this blog post. If you're dealing with lots of application generated, dynamic or ad hoc T-SQL queries, then attempting to determine tuning opportunities, missing indexes, incorrect structures, etc., becomes much more difficult because you don't have a single place to go to see what's happening. Each ad hoc query looks different... or do they. Introduced in SQL Server 2008 and available in the standard Dynamic Management Objects (DMO), we have a mechanism to identify ad hoc queries that are similar in…
Read More
16Sep 2013 by Grant Fritchey 1 Comment

Why the Lazy Spool Is Bad

SQL Server, T-SQL
First thing, there are no bad operators, just bad parents, uh, I mean query writers, or database designers, or ORM tools. Why do I say this? Because all the operators within a query execution plan serve a purpose. They are there to fulfill a task. Depending on where and when you see them, they're doing exactly what you ask of them. The issues come up because you're asking them to do a task that they may not be well suited for. This comes from inappropriate structures and inappropriate code. Lazy spools are not really bad (that was just link bait). In fact, depending on the query, what's being done, how you're retrieving data, what data is being retrieved, the lazy spool is actually awesome. But, it's good to know what…
Read More