Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changed behavior or am I missing something? Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 11:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 AM
Points: 1,907, Visits: 5,243
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
Post #1566518
Posted Wednesday, April 30, 2014 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 13,755, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566550
Posted Wednesday, April 30, 2014 12:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1566557
Posted Wednesday, April 30, 2014 12:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 AM
Points: 1,907, Visits: 5,243
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!

that is a typo if I ever seen one
Post #1566564
Posted Wednesday, April 30, 2014 1:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:29 AM
Points: 1,907, Visits: 5,243
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.
Post #1566573
Posted Wednesday, April 30, 2014 1:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 1,778, Visits: 5,730
I get the same crappy performance on 2012 as 2014 for these...I haven't got a 2008 to test on right now.



MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1566576
    Posted Wednesday, April 30, 2014 1:19 PM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 2:29 AM
    Points: 1,907, Visits: 5,243
    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
    Thanks!
    Post #1566579
    Posted Wednesday, April 30, 2014 1:36 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 3:47 PM
    Points: 1,778, Visits: 5,730
    Or I have a crappy 2012 install?

    MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1566585
    Posted Wednesday, April 30, 2014 1:46 PM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 2:29 AM
    Points: 1,907, Visits: 5,243
    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.
    Post #1566594
    Posted Thursday, May 1, 2014 8:48 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Friday, September 19, 2014 1:34 PM
    Points: 1,276, Visits: 2,803
    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.


    Post #1566735
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse