Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changed behavior or am I missing something?


Changed behavior or am I missing something?

Author
Message
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6742 Visits: 17717
Hi all,
when porting and testing code from 2008/2012 to 2014 I noticed that some queries where significantly slower. Digging into it, I found that there is what seems to me a different behavior between the versions.

Well it's been a long day and I might be missing something, (no need to be polite ;-) ) but then again, I have written and used this kind of code hundreds of times. To demonstrate, the following code exhausts the NUMBERS CTE, far beyond the length of the character column specified in the filter;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#MYTEMP_%' AND TABLE_SCHEMA = N'dbo')
DROP TABLE #MYTEMP

create table #MYTEMP
(
MYTEMP_ID INT IDENTITY(1,1) NOT NULL
,MYTEMP_STR varchar(50)
);

insert into #MYTEMP(MYTEMP_STR)
values ('12345ABCD')
,('23456BCDE')
,('34567CDEF')
,('45678DEFG');

;WITH NX(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
FROM NX N1,NX N2,NX N3 ,NX N4,NX N5,NX N6,NX N7,NX N8
)
SELECT
*
,SUBSTRING(MT.MYTEMP_STR,NM.N,1) AS PART
FROM #MYTEMP MT
CROSS APPLY NUMBERS NM
WHERE LEN(MT.MYTEMP_STR) <= NM.N



and slightly different flavour
[code="sql"]IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#MYTEMP_%' AND TABLE_SCHEMA = N'dbo')
DROP TABLE #MYTEMP

create table #MYTEMP
(
MYTEMP_ID INT IDENTITY(1,1) NOT NULL
,MYTEMP_STR varchar(50)
);

insert into #MYTEMP(MYTEMP_STR)
values ('12345ABCD')
,('23456BCDE')
,('34567CDEF')
,('45678DEFG');

;WITH NX(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
FROM NX N1,NX N2,NX N3 ,NX N4,NX N5,NX N6,NX N7,NX N8
)
SELECT
*
,SUBSTRING(MT.MYTEMP_STR,NM.N,1) AS PART
FROM #MYTEMP MT
INNER JOIN NUMBERS NM
ON LEN(MT.MYTEMP_STR) <= NM.N

[/code]

and then again, even worse
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#MYTEMP_%' AND TABLE_SCHEMA = N'dbo')
DROP TABLE #MYTEMP

create table #MYTEMP
(
MYTEMP_ID INT IDENTITY(1,1) NOT NULL
,MYTEMP_STR varchar(50)
);

insert into #MYTEMP(MYTEMP_STR)
values ('12345ABCD')
,('23456BCDE')
,('34567CDEF')
,('45678DEFG');

;WITH NX(N) AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMBERS(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
FROM NX N1,NX N2,NX N3 ,NX N4,NX N5,NX N6,NX N7,NX N8
)
SELECT
*
,SUBSTRING(MT.MYTEMP_STR,NM.N,1) AS PART
FROM #MYTEMP MT
INNER JOIN NUMBERS NM
ON NM.N <= LEN(MT.MYTEMP_STR)



Before jumping to any conclusions, am I doing something wrong or has something changed?

Thanks,
Eirikur
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17597 Visits: 32267
Tons of stuff has changed. First, did you change the compatibility level on your database to 120? If not, you're using the old statistics with the new optimizer in compatibility mode, which might explain a difference in behavior, possibly. If you've changed the compatibility level to 120, then you've got an all new optimizer and a whole new cardinality estimator, which will seriously change the behavior of some plans. That, frankly, is the most likely explanation for many differences. Get the execution plan with the compatibility set to 110 and 120 and compare them.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
Eirikur Eiriksson (4/30/2014)
INNER JOIN NUMBERS NM
ON LEN(MT.MYTEMP_STR) <= NM.N


I'm thinking that bit of code cannot possibly be correct even for the old versions.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6742 Visits: 17717
Jeff Moden (4/30/2014)
Eirikur Eiriksson (4/30/2014)
INNER JOIN NUMBERS NM
ON LEN(MT.MYTEMP_STR) <= NM.N


I'm thinking that bit of code cannot possibly be correct even for the old versions.

Thanks Jeff, this I missed!

:-D that is a typo if I ever seen one :-P
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6742 Visits: 17717
Grant Fritchey (4/30/2014)
Tons of stuff has changed. First, did you change the compatibility level on your database to 120? If not, you're using the old statistics with the new optimizer in compatibility mode, which might explain a difference in behavior, possibly. If you've changed the compatibility level to 120, then you've got an all new optimizer and a whole new cardinality estimator, which will seriously change the behavior of some plans. That, frankly, is the most likely explanation for many differences. Get the execution plan with the compatibility set to 110 and 120 and compare them.

Thanks Grant, looking further into this and getting even more puzzled.
The testing has so far been done on an upgraded system, will test on a clean 2014 system later.
Cool
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 7827
I get the same crappy performance on 2012 as 2014 for these...I haven't got a 2008 to test on right now.

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Eirikur Eiriksson
    Eirikur Eiriksson
    SSCertifiable
    SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

    Group: General Forum Members
    Points: 6742 Visits: 17717
    mister.magoo (4/30/2014)
    I get the same crappy performance on 2012 as 2014 for these...I haven't got a 2008 to test on right now.


    That means I might be missing something:-P
    Thanks!
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2270 Visits: 7827
    Or I have a crappy 2012 install?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Eirikur Eiriksson
    Eirikur Eiriksson
    SSCertifiable
    SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

    Group: General Forum Members
    Points: 6742 Visits: 17717
    mister.magoo (4/30/2014)
    Or I have a crappy 2012 install?

    Few weeks back, before upgrading the 2012 system I´m working on to 2014, I ran into a similar behavior when forgetting the filter/limit on the numbers cte. Adding the filter corrected the problem then.
    Was the changes in the cardinality estimator or guesstimator? Seeing estimated 10 rows, actual rowcount 10000000 rows in the exec plan.;-)
    Changing to 110 compatibility halves the execution time.
    Markus
    Markus
    SSCommitted
    SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

    Group: General Forum Members
    Points: 1583 Visits: 3676
    Make sure you rebuild indexes and update stats too. Every time I move a db from older version of SQL Server to a new version I rebuild the indexes.



    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search