SQLServerPedia

I keep thinking I’ve got a handle on the way execution plans are dealt with in SQL Server. I had a pretty simplistic view of things, there’s the estimated plan which comes out of the optimizer and there’s the actual plan which comes out of the data engine. The one shows the best guess for what will happen based on the query and the underlying statistics. The other shows what actually happened based on the query and the real data, load on the server, available memory, you get the idea. This model is easy to explain and understand. Too bad it’s not quite the way things work.

If you query the dynamic management function sys.dm_exec_query_plan, you can see a query plan. Once you drill down on the XML, or browse through the GUI, you’ll see that this is an estimated plan, with no execution statistics at all. The safe assumption here is that SQL Server takes the execution plan created by the optimizer and stores it. Ah, but is that exactly how it works? Can you generate an estimated execution plan for this batch statement:

CREATE TABLE dbo.TempTable (id INT IDENTITY(1, 1)
,val VARCHAR(50) ) ;
INSERT INTO dbo.TempTable
(val)
VALUES
(’dude’) ;
SELECT tt.*
FROM dbo.TempTable AS tt ;
DROP TABLE dbo.TempTable ;

No. You can’t. If you try to generate an estimated plan you’ll get the error “Invalid object name ‘dbo.TempTable’.” This is because the algebrizer, a part of the process of the optimizer, which is what generates the estimated plan, can’t resolve the table dbo.TempTable because it doesn’t exist yet. However, if you execute the batch above and then run this query:

SELECT deqp.query_plan
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE dest.text LIKE ‘create table%’

You can see the estimated plan for executing this batch. So… if the optimizer creates estimated plans, but it can’t create an estimated plan for this query since the algebrizer can’t resolve the object name… where did this estimated plan come from? Good question.

Here’s some more interesting info. You know that you can capture both estimated & actual execution plans through trace events, right? Watch what happens if you run the batch statement above and capture both the “Showplan XML” and “Showplan XML Statistics Profile” events, estimated and actual, respectively. You’ll get the estimated plan that is being stored in sys.dm_exec_query_plan and you’ll get an actual execution plan including actual rows vs. estimated, etc. Where did that execution plan come from? Another good question.

A better man than I, Tibor Karaszi, first noticed that there was, other than the execution statistics, no difference between “XML Showplan” and “XML Showplan Statistics Profile.” He asked whether or not we should ever expect to see a difference in a forum post. I jumped in with both feet, of course you’ll see a difference. After all, one is the estimated plan and one is the actual plan. Mr. Karaszi corrected a couple of my assumptions and suddenly, there I was, not understanding execution plans, again. I didn’t know where those estimated plans were coming from, not for sure. I assumed the optimizer, but then how were they getting past the algebrizer?

I had a theory and a mechanism for testing it. If I set up a trace and capture the recompile events using SQL:StmtRecompile and return the EventSubClass column, I can see what caused a recompile. When I run this query and check the trace information, I find that the EventSubClass resolves to 3-Deferred Compile. I’m not 100% certain that I’m correct here, but the following is what I think occurrs.

Because of the CREATE statement, the batch is going to fail in the optimizer. But, because it’s a DDL statement, it goes on to execute anyway. Then, a recompile puts the batch back through the optimizer. There, a table is now available where none was previously. An estimated execution plan is created. This plan, previously non-existent gets stored and can be accessed by us.

Within the facts available, this seems to explain the behavior. In fact, it’s basically supported by Connor Cunningham, one of the MS SQL Server developers, in some blog posts over at Conner vs. SQL.

Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer.

This morning a developer walked up and asked me what would happen if he ran a query that looked like this:

SELECT 1.SomeString

I said that he’d get an error. No, he says, try it. So I try it and I get this:

Somestring
1

Try it yourself. It works just fine. I’d never seen that before and didn’t have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this:

SELECT 'dude'.dudette

Which resulted in the error:

 

Msg 258, Level 15, State 1, Line 1
Cannot call methods on varchar.

 

Which is what I would have expected. I tried a couple of functions and some other bits & pieces of data, but only integers seemed to work… Or was it? What would be the purpose of a function that defines aliases for an integer?

I finally posted a question up on SQL Server Central. One of the local brain trust over there, Lynn Pettis, chewed on it for a bit and identified the issue. SQL Server is “helping” you by assuming that you meant to put a space between the period (dot or . ) and the string, Somestring, as an alias. I then tried this:

SELECT 'dude'dudette

And was rewarded with the same behavior.

Personally, in this case, I don’t see this as TSQL “helping” me nearly so much as it confused the heck out of me. Thanks again for figuring it out Lynn.

With all the cool kids posting about beginners and interview questions, I thought I’d toss my favorites out there, from the brief-case gang point of view. These are the technical phone-screening questions I use after I look at a resume. There are only 10. They’re simple. Stupid simple. Silly even. Yet, I can count on eliminating 4 out 5 people who have a resume that looks like a qualified DBA. I’ve seen people with 10 years experience fail on these questions.

I’m only going to provide the questions. If you can’t find the answers on your own, you’re already disqualified:

1. What is the difference between a clustered and non-clustered index?
   No, don’t tell me that one is clustered and the other is not. I don’t need specific low-level information on this, just a demonstration of knowledge that the difference is understood.
2.  What is the difference between a block (b – l – o – c – k) and a deadlock (d – e – a – d – l – o – c – k)?
   And yes, I spell the words. I don’t want any chance of misunderstanding. And yet, most people carefully explain what a block is and then carefully explain what a block is again.
3. Can you tell me two of the three recovery models in SQL Server and what the difference between them is?
   Again, I don’t need to know what happens differently inside of the checkpoint operation, just tell me what’s different. Yeah, I only ask for two since almost everyone only uses one of the two.
4. Can you tell me a few things that might cause a query stored in cache to recompile?
   Let me tell you that, yes, they do. I’ve had several people argue with me on that question.
5. What do you think the query hint NO_LOCK might do?
   This should be a give away. I’m not asking for specifics. I’m assuming you don’t know. Why would you say “I have no idea” to a question like this?
6. Can you tell me some of the various types of backups that are available in SQL Server?
   If you give me three, I’ll be overjoyed. I need at least two.
7. How did error handling change in SQL Server 2005?
   Note, not how do you write error handling based on the change in 2005, just, what was the change. I need to know you’re aware there was one.
8. Do you have any experience working with [latest hot topic] inside SQL Server?
   Our latest is Microsoft Dynamics CRM. We’ve also asked the question about XML and other stuff. It’s just an attempt to understand you. Talk about what you know or don’t know.
9. Do you have experience with Version X of SQL Server?
   Now I ask about 2008, but before I asked about 2005. “No” is a perfectly acceptable answer. “I’ve never heard of it” or “That’s not out yet” or “No, but I have lots of experience with 2009″ are pretty much disqualifiers. Broke my rule about no answers there, but I hate seeing people get this one wrong.
10. You’re the DBA. The phone rings. One of the users is on the line. They say “The database is slow.” Then they hang up. What do you do?
    My favorite was the guy who wanted to track down the user in order to get his name and his managers name and to fill out a series of forms before he’d even consider the technical aspects of the question. This is the only open-ended question I ask for screening.

Preparing this I went back through my notes. I keep notes on every interview. It’s creepy. Page after page of people who can’t answer even four of these questions. We only want you to correctly answer six before we bring you in for an interview.

So, if you’ve got five or ten years experience as a DBA and you think this was a tough quiz… time to evaluate what you’ve been doing. If you’re just starting out, here are some of the basics that it might be nice to know.

A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn’t know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that the view uses. Let’s take an example (using AdventureWorks2008):

CREATE VIEW dbo.vTest
AS
 SELECT soh.AccountNumber
,sod.LineTotal
,(SELECT COUNT(pcc.BusinessEntityId)
FROM Sales.PersonCreditCard AS pcc
WHERE pcc.CreditCardID = soh.CreditCardID
) AS PersonCreditCard
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID ;
GO

Whether or not this query is a good one (I’d say it isn’t) and whatever you might think about it, the view definition here is pretty straight forward. If you run a SELECT against this view and take a look at the execution plan, it looks like this:

As you can see, the behavior is completely as defined above. The view has been resolved out to it’s component parts in order to build the query and arrive at the data requested. What happens if, instead of selecting all the columns, only a couple are selected? Well, the query that defines the view is not changing, so the optimizer will arrive at the same execution plan as that shown in Figure 1, right? Run this query and get the execution plan:

SELECT
 vt.AccountNumber
,vt.LineTotal
FROM
 dbo.vTest AS vt;

What the heck just happened?

I told you, the optimizer is smart. Compare that execution plan with the one generated from this query:

 SELECT soh.AccountNumber
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID ;

You’ll find that they’re identical. As stated before, views are just a query stored on the side. The optimizer is smart enough to figure out that it only needs to generate a plan as if the query requested was this. But what happens when you start nesting views? Yes, yes, Yeah… I… Ok… But… YES! That’s not a good idea. So? When has that slowed people down for a New York second? I’ve seen views nested three and four layers deep. I’ve seen table valued user defined functions nested six and seven layers deep. The question is, how well does the optimizer handle something like this? This script sets up a new situation:

CREATE VIEW dbo.vSalesInfo
AS
SELECT soh.AccountNumber
,sod.LineTotal
,soh.CreditCardID
,sod.ProductID
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS  sod
ON soh.SalesOrderID = sod.SalesOrderID ;

CREATE VIEW dbo.vTest3
AS
SELECT vsi.AccountNumber
,vsi.LineTotal,
(SELECT COUNT(pcc.BusinessEntityId)
FROM Sales.PersonCreditCard AS pcc
WHERE pcc.CreditCardID = vsi.CreditCardID) AS PersonCreditCard
,(SELECT COUNT(v.BusinessEntityId)
FROM Purchasing.Vendor v) AS VendorCount
,(SELECT COUNT(a.AddressId)
FROM Person.Address AS a) AS AddressCount
,(SELECT COUNT(be.BusinessEntityID)
FROM Person.BusinessEntity be) AS BusinessEntityCount
FROM dbo.vSalesInfo AS vsi

From the script you can see that I’ve nested one view inside another and complicated the outer a view a bit. What happens when you run the query that would eliminate tables from the view this time?

 SELECT vt.AccountNumber
,vt.LineTotal
FROM dbo.vTest3 AS vt

 

See. The optimizer is very smart. Ah, but before you get all comfy and crack a bottle of some frothy beverage, we need to look at the compile times. Compare the STATISTICS TIME output from a query that pulls from the full view, the limited pull from the view and the pull from the tables:

–View 1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

–Table
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

–View 2
SQL Server parse and compile time:
CPU time = 8 ms, elapsed time = 8 ms.

These are small views and easy queries, so the compile times are not large. But with one consistently 30% larger than the others in terms of execution time and actually requiring CPU cycles when the others require less, you begin to see that, despite the extreme intelligence of the optimizer, there is a cost. TANSTAAFL still applies. Keep all this in mind if you think that nested views are a nifty way to write your code or you’re trying to convince someone that nested views are not a nifty way to write their code.

Unfortunately PASS decided to put the SQL Server Standard to sleep right after I got an article published in it (and no, it wasn’t my fault). However, PASS, being the service oriented organization that they are, has decided to make back issues of the magazine available online. So, to read the article I wrote comparing various methods of retrieving versioned data using different TSQL constructs, click this link. Go to page 14. Oh yeah, and you can see other peoples articles here too.

There are other things coming out of the editorial committee soon (although I need to get one of them done myself… yikes).

NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not “not equal” anything either. It can’t be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used “WHERE nt.NullableString NOT LIKE ‘null%’” was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or “not equal” anything and therefor when checking for the equality of something you won’t get NULL values.

Yeah, you’re thinking, so what’s the problem with the code then? This. It can lead to problems with your results. Here’s some sample code that illustrates the problem:

 CREATE TABLE #NullTest
(ID INT IDENTITY(1,1),NullableString NVARCHAR(50) NULL)

–I put the – next to the string NULL so you can see which one it is.
INSERT INTO #NullTest (
NullableString)
SELECT ‘Some Value’
UNION
SELECT ‘NULL-’
UNION

–returns the five rows available
SELECT * FROM #NullTest AS nt

–returns only two rows, although two other rows should be returned
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString NOT LIKE ‘null%’

–instead of returning three values, it only returns two, as it should
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString LIKE ‘NULL%’

– the real values that are not NULL, four of them
SELECT *
FROM #NullTest AS nt
WHERE NullableString IS NOT NULL

As you can see, the wrong number of rows are returned depending on how the query is used. There are four rows in the table that are NOT NULL (notice the syntax) but running the query the other way only returns two values. There are ongoing debates about the utility, meaning and purpose of NULLs. I’m not getting into it. If you’re using them, you need to understand what they are and what they are not.

A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn’t know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that the view uses. Let’s take an example (using AdventureWorks2008):

CREATE VIEW dbo.vTest
AS
 SELECT soh.AccountNumber
,sod.LineTotal
,(SELECT COUNT(pcc.BusinessEntityId)
FROM Sales.PersonCreditCard AS pcc
WHERE pcc.CreditCardID = soh.CreditCardID
) AS PersonCreditCard
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID ;
GO

Whether or not this query is a good one (I’d say it isn’t) and whatever you might think about it, the view definition here is pretty straight forward. If you run a SELECT against this view and take a look at the execution plan, it looks like this: As you can see, the behavior is completely as defined above. The view has been resolved out to it’s component parts in order to build the query and arrive at the data requested. What happens if, instead of selecting all the columns, only a couple are selected? Well, the query that defines the view is not changing, so the optimizer will arrive at the same execution plan as that shown in Figure 1, right? Run this query and get the execution plan:

SELECT
 vt.AccountNumber
,vt.LineTotal
FROM
 dbo.vTest AS vt;

What the heck just happened? I told you, the optimizer is smart. Compare that execution plan with the one generated from this query: SELECT soh.AccountNumber ,sod.LineTotal FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID ; You’ll find that they’re identical. As stated before, views are just a query stored on the side. The optimizer is smart enough to figure out that it only needs to generate a plan as if the query requested was this. But what happens when you start nesting views? Yes, yes, Yeah… I… Ok… But… YES! That’s not a good idea. So? When has that slowed people down for a New York second? I’ve seen views nested three and four layers deep. I’ve seen table valued user defined functions nested six and seven layers deep. The question is, how well does the optimizer handle something like this? This script sets up a new situation: CREATE VIEW dbo.vSalesInfo AS SELECT soh.AccountNumber ,sod.LineTotal ,soh.CreditCardID ,sod.ProductID FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID ; CREATE VIEW dbo.vTest3 AS SELECT vsi.AccountNumber ,vsi.LineTotal, (SELECT COUNT(pcc.BusinessEntityId) FROM Sales.PersonCreditCard AS pcc WHERE pcc.CreditCardID = vsi.CreditCardID) AS PersonCreditCard ,(SELECT COUNT(v.BusinessEntityId) FROM Purchasing.Vendor v) AS VendorCount ,(SELECT COUNT(a.AddressId) FROM Person.Address AS a) AS AddressCount ,(SELECT COUNT(be.BusinessEntityID) FROM Person.BusinessEntity be) AS BusinessEntityCount FROM dbo.vSalesInfo AS vsi From the script you can see that I’ve nested one view inside another and complicated the outer a view a bit. What happens when you run the query that would eliminate tables from the view this time? SELECT vt.AccountNumber ,vt.LineTotal FROM dbo.vTest3 AS vt See. The optimizer is very smart. Ah, but before you get all comfy and crack a bottle of some frothy beverage, we need to look at the compile times. Compare the STATISTICS TIME output from a query that pulls from the full view, the limited pull from the view and the pull from the tables: –View 1 SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 6 ms. –Table SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 6 ms. –View 2 SQL Server parse and compile time: CPU time = 8 ms, elapsed time = 8 ms. These are small views and easy queries, so the compile times are not large. But with one consistently 30% larger than the others in terms of execution time and actually requiring CPU cycles when the others require less, you begin to see that, despite the extreme intelligence of the optimizer, there is a cost. TANSTAAFL still applies. Keep all this in mind if you think that nested views are a nifty way to write your code or you’re trying to convince someone that nested views are not a nifty way to write their code.