Strange performance difference between SQL Compatibility levels (100 vs. 90)

Question

Post reply

Strange performance difference between SQL Compatibility levels (100 vs. 90)

roblxndr

Old Hand

Points: 381
More actions
April 22, 2010 at 12:57 pm

#220788

Anybody have any idea as to why the compatibility level would affect the performance of this?
The following statement performs very, very poorly when run against a database with Compatibility Level = 100. The same statement, when run on the same server against a database with Compatibility Level = 90, returns almost instantly. NOTE: The bad performance is not apparent unless the number of items in the xml string is greater than 3000, but for obvious reasons, I have not included a fully populated xml string in this post.
Server Configuration:
-SQL Version: 10.0.2757
-Data/Log/TempDB are all on separate drives.
DECLARE @p3 xml
SET @p3=convert(xml,N'<IdList><Id>13187666</Id><Id>13187667</Id><Id>13187668</Id><Id>13187669</Id></IdList>')
DECLARE @IdTable TABLE (Id int primary key)
INSERT INTO @IdTable
SELECT xmln.nv.value('.','int') as Id
FROM @p3.nodes( 'IdList/Id' ) as xmln(nv)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

There are a lot of moving parts to consider but I have seen such behavior before.

First you have to prove your point - to yourself, not to me 😀

Take a test box then...

1- Set test environment.

2- Take a full backup

3- Set database compatibility level to 90

4- Run query and capture exec plan and timing

5- Restore database

6- Set database to compatibility level 100

7- run query and capture exec plan and timing.

Query is too simple to show different explain plan - I think.

If confirmed I would think something has changed on xml management kernel.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

roblxndr Old Hand Points: 381 More actions · Answer 2

Pablo,

I have several servers at my disposal. All are configured the same. I tested this on all of them, and the results are the same. Running under compatibility 100 is very slow for this particular query. Interestingly enough, the query plan IS different for each compatibility setting. The 100-level plan includes an "eager spool" in one of the branches. The 90-level plan does not include the spool.

Rob

Paul White SSC Guru Points: 150468 More actions · Answer 3

CheapTrick (4/22/2010)
Interestingly enough, the query plan IS different for each compatibility setting. The 100-level plan includes an "eager spool" in one of the branches. The 90-level plan does not include the spool.

There are very many detailed changes in XML processing between 90 and 100. See ALTER DATABASE Compatibility Level (Transact-SQL) for a list of changes.

That said, I tried to reproduce your problem with a 15,000-element XML structure, and failed:

DECLARE @p3 XML;

WITH Numbers (n)

AS (

SELECT TOP (15000)

ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3

)

SELECT @p3 =

(

SELECT [Id] = N.n

FROM Numbers N

FOR XML PATH (''), ROOT ('IdList')

);

DECLARE @IdTable TABLE (Id INTEGER PRIMARY KEY);

INSERT @IdTable

SELECT xmln.nv.value(N'.', N'INTEGER') AS Id

FROM @p3.nodes(N'IdList/Id') AS xmln(nv);

The optimiser will introduce an eager spool if the information available to it suggests that it would be worthwhile...but it is hard to say for sure why that might be unless you post the actual execution plans (*.sqlplan files). Perhaps your XML 'string' contains many duplicates?

Anyway, you could try adding an OPTION (FAST 1) hint to the query. That should remove the spool.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

roblxndr Old Hand Points: 381 More actions · Answer 4

Thanks for looking at this. I also posted over at MSDN and received a very good answer to this problem. The solution is to replace my original insert statement with the following:

INSERT INTO @IdTable

SELECT xmln.nv.value('(./text())[1]','int') as Id

FROM @p3.nodes( 'IdList/Id' ) as xmln(nv)

Credit for this answer goes to Brad Shulz and Allejandro Mesa. More information about this solution can be found here:

http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html