Author Archive: Gail

SQL in the Wild > Articles by: Gail

Structure of an execution plan

Author: 27 August 2007 1 Comment

So, this is the first part of the series on how to read an execution plan. In this post, I’m going to give a high-level overview of how the execution plan looks and how, in general, to read it.

For the purposes of this and other posts in this series, all screenshots will be from SQL Management Studio (the 2005 GUI) and all comments on features will refer to that tool. Query Analyser, from SQL 2000, is very similar. For other querying tools, your mileage may vary.

Here’s a very simple execution plan. The query that produced this plan is just a join of three tables in the AdventureWorks database on SQL 2005.

Execution plan

(more…)

Execution Plans, SQL Server

PASS 2007

Author: 22 August 2007 0 Comments

Well, looks like I’m going to Denver for the Pass 2007 summit after all. I wasn’t sure I’d be going this year.

As usual there are more fantastic looking sessions that I could possibly manage to go to. In one slot on the Wednesday, I’d need to be in four places at the same time to attend everything I want to see.

(more…)

Pass 2007

Reading Execution plans

Author: 20 August 2007 1 Comment

One of the more common questions I get from colleagues is on how to read the execution plan that SQL produces. At first I found it quite a hard question to answer as no one really taught me how to read them. When I started with performance tuning I got a bit thrown in the deep end.

This is going to be the first in a series, maybe short, maybe long, on reading SQL’s execution plans. I’m going to go into the overall structure of a plan; the differences between the actual and the estimated plans, and how to obtain both; and some of the more common query operators and how they affect query performance.

If you have any specific questions that you’d like answering regarding execution plans, please post a comment here. Otherwise I’m going to ramble on a bit touching on things that I feel are important and relevant and maybe missing things that you would like to know.

Current articles in this series, in order of writing, are

  1. Structure of an execution plan
  2. Execution plans, estimated vs actual
  3. Execution plans from Profiler
  4. A basic execution plan
  5. Execution plan operations – scans and seeks
  6. Execution plan operations – joins
  7. Execution plan operations – aggregate
  8. Execution plan operations – misc
  9. Execution plans – important properties
  10. Execution plan – more properties
  11. An example execution plan

There is more to come.

Execution Plans, SQL Server

DateTime Conversions

Author: 16 August 2007 0 Comments

Or ‘That’s not the date I wanted?‘

Date time conversions are among the trickiest of conversions in SQL, and the most likely to cause unexpected errors. Mostly, this is because of the variety of formats that dates, when expressed as strings, can be written with. Let’s start with a simple example.

SELECT CAST('20/05/2007' as DATETIME)

Should work?

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(more…)

SQL Server

Implicit conversions

Author: 11 August 2007 0 Comments

Or ‘How to slow down a query without apparent reason’

I’ve discussed functions in where clauses before and shown how they can prevent index usage and slow down queries. I’ve also discussed data type conversions and shown that SQL Server sometimes does the conversions in places not expected. There’s a nice little gotcha that results from the combination of these two. Let’s take a look at a simple example.

Which of the following queries will run slower? (sample code at the end)

Select ID from TestingConversion where Code = 'AAA'
Select ID from TestingConversion where Code = N'AAA'

(more…)

Performance, SQL Server

Order of execution

Author: 29 July 2007 0 Comments

Or “Which where runs when?”

There seems to be a lot of misunderstanding about what order conditions within a where clause are executed. In two days I heard from three different places comments about what order conditions must be put in the where clause.

The simple truth is that is doesn’t matter. Let’s look at a very simple example. (Sample code at the end as always)

SELECT * from tblTestWhereOrder WHERE A=1 and B=2 and C is not null

(more…)

SQL Server

Functions in a where clause

Author: 19 July 2007 0 Comments

Or ‘How to really slow a query down without trying’

Here’s a query that looks innocent enough. Table structure and sample data are at the end of the post.

-- returns 5 out of 5000 rows
SELECT InsertDate FROM TestingFunction WHERE LEFT(Code,1)='AA'

Knowing that there’s an index on the column code, the optimiser might be expected to use an index seek to satisfy the query. However, the execution plan shows an index scan. Why?

(more…)

Performance, SQL Server

Another day, another exam

Author: 13 July 2007 0 Comments

Just wrote the second of the Database developer exams: 70-442. Designing data tier and optimising data access.

I found it easier than the other database developer exam, but still, it wasn’t easy. If you don’t know your stuff, you are going to struggle.

Personally, I’m not very fond of the case study based exam format. When I tried to read through the case study before answering questions I almost ran out of time. Now I go straight to the questions and skim through for the appropriate info for each question.

Next up, the upgrade exam from MCDBA to the new DB Admin.

Career

Data Conversion Woes, pt 2

Author: 12 July 2007 0 Comments

In Part 1 I discussed why some queries that look like they should work fail with a conversion error. In this part, I’m going to go over some solutions to the problem.

  1. Probably best solution, at least in the long term is, as I mentioned previously, to correct the data type of the column and to remove the data that doesn’t fit. This is probably, however, the hardest of the solutions to implement, due to the potential impact.

(more…)

SQL Server, T-SQL

Data Conversion Woes

Author: 10 July 2007 0 Comments

Or “I’m filtering on ISDATE(dt) = 1. Why am I still getting conversion errors?”

Well, to put it simply, because SQL’s evaluating the where clause in a different order to what you may have expected.

Let’s try a simple example.

SELECT * FROM tbl_TestingConversion
WHERE ISDate(TheDate)=1
 AND CAST(TheDate AS DATETIME) > '2007/07/01'

(more…)

SQL Server, T-SQL

Copyright 2018 Gail Shaw