THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: UNION ALL views, ANSI_PADDING, and bad query plans

Whether or not you specify it explicitly, ANSI_PADDING setting is there when you create a table, and can have an impact on the performance of some queries. If you are not careful, it can even hurt performance big time!

 

Let’s demonstrate that with an extremely simple example.

 

Assume that you have this simple view with a UNION ALL between two tables:

 

CREATE VIEW myView

as

select * from tb1

UNION ALL

select * from tb2

go

 

The two tables are created as follows:

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING OFF

GO

 

-- Create the main table   

CREATE TABLE tb1 (

     [TicketID]    [char](13),

     [Edition]     [smallint],

     [FlowType]    [char](3),

     [Amount]      [float] NOT NULL,

     [Code]        [char](3) NOT NULL,

     [LogDate]     [datetime] NOT NULL,

     [id]          [int] NOT NULL

)

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

 

CREATE TABLE [tb2](

     [TicketID]    [char](13),

     [Edition]     [smallint] NOT NULL,

     [FlowType]    [char](3),

     [Amount]      [float] NOT NULL,

     [Code]        [char](3) ,

     [LogDate]     [datetime] NOT NULL,

     [id]          [int] NOT NULL

)

GO

 

 

And you run the following query on the view to return a single row from tb1:

 

SELECT TicketID, Amount

  FROM myView

 WHERE FlowType ='2'

   and TicketID = '200002000'

   and Edition = 0

 

Since both tb1 and tb2 have a covering index on this query (see the attached repro script for the indexes), you’d expect an efficient index seek. But you may discover that SQL Server chooses to process the query with an index scan, which could easily sink the performance if the table is rather large.

 

What’s going on?

 

Well, notice that the only difference between tb1 and tb2 is that tb1 is created with ANSI_PADDING set to OFF whereas tb2 is created with ANSI_PADDING set to ON. If you create these two tables with the same ANSI_PADDING setting, SQL Server does generate a correct plan for the query.

 

So for some reason, the SQL Server 2005/2008 optimizer can’t deal with this type of mismatched ANSI_PADDING settings in a view intelligently. Although one may question whether the two tables should have different ANSI_PADDING settings, it’s clear that the generated plan is not optimal. And I’d argue that this is a bug in the SQL Server optimizer because there does exist an optimal plan that uses an index seek, and the optimizer fails to find it.

 

To see the details of the behavior for yourself, you can run the attached repro script in any test database. The repro demonstrates the bad plans on both SQL Server 2005 and SQL Server 2008. On SQL Server 2000, you should see a good plan with an index seek. But this does not mean that SQL Server 2000 is immune to the problem. In fact, with a different but slightly more complex repro script, I see the same bad plans on SQL Server 2000.

 

Note that to save time, the repro script populates the table with 100,000 rows, and can finish in several seconds on any decent machine. With 100,000 rows, you won’t feel any pain even with a bad plan that does an index scan. If the table had millions of rows, the pain would be quite acute.

 

Also, I have filed a bug report at Microsoft Connect. Please vote there to get some traction from Microsoft on this issue.

 

Published Friday, December 04, 2009 3:43 PM by Linchi Shea

Attachment(s): Bad_plan_with_diff_ansi_padding_repro.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jack Corbett said:

Interesting find.  I wouldn't think that this would/should happen in your example because ANSI_PADDING should not affect the char() columns  anyway.  If you had used varchar() columns I wouldn't have been surprised by this behavior.

December 4, 2009 3:44 PM
 

said:

Linchi - Very interesting post! Comments picked up my trackback. Not sure if this affects your post in any way - likely not. May do some more research.

Stephen Horne aka Bluedog67

December 4, 2009 5:35 PM
 

Adam Machanic said:

Hi Linchi,

Can you paste your repro script into the Connect item in a comment so that people can validate?

December 5, 2009 2:10 PM
 

Linchi Shea said:

Adam;

I attached the repro script as a file when I filed the connect item. They should be able to download that file (I think). But I'll paste the script to a comment as you suggested just in case.

December 6, 2009 12:47 AM
 

AaronBertrand said:

No, end users cannot see or download your attachments; only Microsoft folks can.  This is true even for screenshots, which is kind of too bad.  I understand why they don't make them public by default (they could contain sensitive information), but they *could* allow us to specify that we are posting attachments so that other users can see them as well.  There is an open feedback item about this:

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=416225

December 6, 2009 9:46 AM
 

Linchi Shea said:

Okay, posted the repro script in a comment.

December 6, 2009 9:57 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement