Geek City: Ordered Seeks and Scans

Kalen Delaney — Fri, 21 Jan 2011 23:28:00 GMT

I got a couple of really great questions during my SSWUG Workshop this morning , as I was discussing seeks and scans, and since the answers to the two questions are very related, I decided to address both of them in more detail in this blog post. Most...(read more)

TSQL Tuesday #8: Those who can, do, and those who want to learn more, teach!

Kalen Delaney — Tue, 13 Jul 2010 14:02:00 GMT

It's time for the eighth T-SQL Tuesday , managed this time by Robert Davis at SQL Server Central . This time, the topic was announced very late, and I was just starting to write a blog post of my own when I saw it. Fortunately, my topic easily fits into...(read more)

Did You Know: My next web seminar is this Wednesday!

Kalen Delaney — Mon, 28 Jun 2010 03:40:00 GMT

I know, I haven't made a geeky post in a while... but I promise that I'll do one after this seminar. I'll take some of the best questions that I get during the chat and write them up here. Query Plans Workshop (June 30, 2010) Learn about basic elements,...(read more)

Geek City: String Statistics

Kalen Delaney — Wed, 13 Feb 2008 22:18:00 GMT

If you’ve ever run DBCC SHOW_STATISTICS, you know you get 3 sections of information back. The first section is basic information about the last time the statistics were updated, the number of rows, the number of steps, etc. The second section is density information for each left-based subset of columns. The third section is the histogram for the first column in the statistics. I won't be going into detail on what any of these things mean (i.e. steps, density, histogram), but you can get lots more information from this whitepaper:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

What you might not know is that you can get each of the three sections independently by adding an option to the DBCC command:

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH STAT_HEADER;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH DENSITY_VECTOR;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH HISTOGRAM;

These options are documented as part of the DBCC SHOW_STATISTICS command for SQL Server 2005. These options were actually available in SQL Server 2000, but they just aren’t documented.

In SQL Server 2005, the first section contains a column of output called "String Index", which I like to call "String Statistics". I just wrote an article for www.SQLCommunity.com about what these string statistics do for you.

http://www.sqlcommunity.com/default.aspx?tabid=77&id=178

Have fun!

~Kalen

Did You Know? Estimated vs Actual Plans

Kalen Delaney — Mon, 30 Jul 2007 18:41:00 GMT

In my previous post, I mentioned that it is important to understand the difference between estimated and actual query plans, so I decided to go into a few more details regarding the differences.

Optimization takes place before execution, so in one sense, any query plan is an estimated plan. But when you request an actual plan, SQL Server will actually execute each statement as it displays the plan, so the plan for subsequent statements can change after the earlier statements are executed. In addition, SQL Server adds additional information to the actual plan, after the execution takes place. As mentioned last time, the actual plan includes the number of processors used to execute the query. It will also include the number of rows returned from each step of the plan as it is executed, and the number of times each step is executed. If you are executing a plan that has already been cached and is now being reused, the actual plan will include both the parameter values the plan was originally compiled with and the parameter values used during execution.

You can request that SQL Server display ESTIMATED plans without executing the query with any of the following options:

SET SHOWPLAN_TEXT ON

SET SHOWPLAN_ALL ON

SET SHOWPLAN_XML ON

For graphical estimated plans, you can use the Query | Display Estimated Execution Plan menu option. This can be invoked with a toolbar button, or with Cntl-L.

You can request that SQL Server display actual plans with any of the following options:

SET STATISTICS PROFILE ON

SET STATISTICS XML ON

For graphical actual plans, you can use the Query | Include Actual Execution Plan menu option. This can be invoked with a toolbar button, or with Cntl-K.

I'll show you 3 examples of ways in which the actual plan can be useful. They require that you have the AdventureWorks database installed.

Example 1:

First, create a stored procedure called 'testtemp' that will build a temporary table.

USE AdventureWorks
GO
IF EXISTS (SELECT 1 FROM sys.procedures
WHERE name = 'testtemp')
DROP PROC testtemp
GO
CREATE PROC testtemp
(@p int)
AS
SELECT * INTO #t
FROM Sales.SalesOrderDetail
CREATE INDEX t_index ON #t (ProductID)
SELECT * FROM #t
WHERE ProductID < @p
RETURN
GO

Try to look at the estimated plan for the procedure, and you will get an error because the plan for the SELECT cannot be generated when the temp table #t does not exist:

-- Estimated plan
SET SHOWPLAN_TEXT ON
GO
EXEC testtemp 896
-- error is generated
GO
SET SHOWPLAN_TEXT OFF
GO

Now look at the actual plan and you will see plans for the temp table creation and for the SELECT from the temp table.

SET STATISTICS PROFILE ON
GO
EXEC testtemp 896
GO
SET STATISTICS PROFILE OFF
GO

Example 2:

One of the main reasons that the actual plan may different from the estimated plan is because of data changes to your data. In general (and assuming you have the option 'auto update statistics' enabled) if more than 20% of the data in a table changes, the optimizer will detect stale statistics and update them automatically. The updated statistics will then trigger a recompile.

This example makes a copy of Sales.SalesOrderDetail in the AdventureWorks database and builds an index on the table.

SELECT * INTO NewOrders
FROM Sales.SalesOrderDetail
GO
CREATE INDEX IX_NewOrders_ProductID on NewOrders(ProductID)
GO
SET SHOWPLAN_ALL ON -- Estimated Plan
GO
-- The estimated plan shows us that a seek is done on the SELECT from NewOrders because at optimization time
-- there are only a few rows that have a ProductID value of 897.
BEGIN TRAN
UPDATE NewOrders
SET ProductID = 897
WHERE ProductID between 800 and 900
SELECT OrderQty, CarrierTrackingNumber
FROM NewOrders
WHERE ProductID = 897
ROLLBACK TRAN
GO
SET SHOWPLAN_ALL OFF
GO

-- The actual plan shows us that a table scan is done on the SELECT from NewOrders because after the update is executed,
-- statistics are updated and the query is recompiled. Now there are lots of rows that have a ProductID value of 897.
SET STATISTICS PROFILE ON -- Actual Plan
GO
BEGIN TRAN
UPDATE NewOrders
SET ProductID = 897
WHERE ProductID between 800 and 900

SELECT OrderQty, CarrierTrackingNumber
FROM NewOrders
WHERE ProductID = 897
ROLLBACK TRAN
GO

SET STATISTICS PROFILE OFF
GO

Example 3:

For this example, you can create a stored procedure that uses a parameter to determine which rows from the Sales.SalesOrderDetail table to select.

USE AdventureWorks
GO
IF EXISTS (SELECT 1 FROM sys.procedures
WHERE name = 'GetProducts')
DROP PROC GetProducts
GO
CREATE PROC GetProducts
(@p int)
AS
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = @p
RETURN
GO

The first time you run the procedure, a plan will be built based on the first parameter. The second time you run the procedure, the original plan will be used, and the statistics should show that SQL Server is performing more reads than there are pages in the table. (There are 1238 pages in the table.)

SET STATISTICS IO ON
GO
EXEC GetProducts 710 -- using nc index takes 145 logical reads
GO
EXEC GetProducts 707 -- using nc index takes 9458 logical reads.
GO

You can look at the actual plan in XML to see the parameters used for compile and execute.

SET STATISTICS XML ON
GO
EXEC GetProducts 707
GO

Near the bottom of the XML document, you should see the following:

<ParameterList>

<ColumnReference Column="@p" ParameterCompiledValue="(710)" ParameterRuntimeValue="(707)" />

</ParameterList>

Seeing the different values used for compilation and execution can lead you to suspect an issue with parameter sniffing. There are several ways around this problem, but that's the topic for another day.