Stored Procedure execution

Question

Stored Procedure execution

Viewing 10 posts - 31 through 39 (of 39 total)

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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

So, my suggestion is, leave the compatibility at 120 and just add the traceflag to force it to use the older cardinality estimator.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

One more question:

Is substring() not compatible with SQL Server 2014 ?

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Raghavender (7/7/2016)
One more question:
Is substring() not compatible with SQL Server 2014 ?

It is compatible. Can you tell us more about the issue you have with it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Grant Fritchey (7/7/2016)
So, my suggestion is, leave the compatibility at 120 and just add the traceflag to force it to use the older cardinality estimator.

This one you can solve for yourself very easily. Just look in SQL Server Books Online for that version. Google/Bing are amazing. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.

AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100

AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.
AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100
AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.

Explain "not working"

You get an error message?

There's no error message but the query returns unexpected results?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.
AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100
AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.

https://msdn.microsoft.com/en-us/library/ms187748.aspx

Depending on your definition of "not working" it is either a bug (VERY unlikely), something funky with your data and the query plan choice causing an invalid length (negative value) that is being filtered out with the 100-generated plan.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 8

ChrisM@Work (7/7/2016)
Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.
AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100
AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.
Explain "not working"
You get an error message?
There's no error message but the query returns unexpected results?

It is running for 4 hours without any result. Where as in 2008 it completed in 3 to 4 secs

Thank You.

Regards,
Raghavender Chavva

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Raghavender (7/8/2016)
ChrisM@Work (7/7/2016)
Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.
AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100
AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.
Explain "not working"
You get an error message?
There's no error message but the query returns unexpected results?
It is running for 4 hours without any result. Where as in 2008 it completed in 3 to 4 secs

That doesn't mean not working. It means running very slowly. 🙂

I don't have time to read the thread tonight - sorry. Could be lots of things, with cost optimizer differences in 2014 leading to different plan being a good option. Other things leading to different plan. Different server perf, especially IO. locking/blocking.

sp_whoisactive for 60 seconds shows what?

File IO stall analysis for 180 seconds shows what?

Wait stats analysis for 180 seconds shows what?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Raghavender (7/8/2016)
ChrisM@Work (7/7/2016)
Raghavender (7/7/2016)
in the stored procedure if we change the substring in condition to like its working if we set the compatibility level to 120, where as with substring in condition its not working.
AND NL.NetworkLogonId like 'UD1%' --- This condition is working with compatibility level 120 and 100
AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1' --- is not working with compatibility level 120 but working with 100.
Explain "not working"
You get an error message?
There's no error message but the query returns unexpected results?
It is running for 4 hours without any result. Where as in 2008 it completed in 3 to 4 secs

Referring back to the execution plans you posted much earlier in this thread; one of the plans shows implicit conversions on four columns, the other does not. IIRC there are index differences between the two environments also. You CANNOT expect performance to be the same unless the environments are the same. Your environments are not the same, they are similar.

SQL Server can estimate rowcounts and make use of an index with

LIKE 'UD1%'

but not with

SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1) = 'UD1'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden