July 5, 2016 at 5:00 am
But I did not understand why substring made all the noise.
Then how it worked after changing it to like 'UD1\%'
Thank You.
Regards,
Raghavender Chavva
July 5, 2016 at 5:41 am
Raghavender (7/5/2016)
But I did not understand why substring made all the noise.Then how it worked after changing it to like 'UD1\%'
The expression LIKE 'UD1\%' is SARGable, meaning SQL Server can use an index for searching for matching values and can exploit statistics to estimate the number of rows which might be returned with this expression. SUBSTRING() is not SARGable.
There are differences between the two environments in terms of data types and indexes, both of which affect execution plans. I think in your test environment, despite the plethora of non-SARGable predicates, SQL Server was able to generate a surprisingly efficient plan. In the production environment the old plan was different (and inefficient), but with more accurate information to work with when you changed from SUBSTRING to LIKE, the new plan is probably similar to the plan from the test environment. You could probably work out exactly what was happening with actual plans rather than estimates.
Note that LIKE is only SARGable if there's only one wildcard, on the right-hand end of the string.
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
July 5, 2016 at 6:10 am
If there are differences in structure, are there also differences in the amount of data? Take away different data types and different indexes (which makes comparisons impossible really), just having 100 rows versus 1000 rows can lead to differences in the choices the query optimizer makes.
In order for you to test prior to production, you need to, as closely as possible, get the environments to be the same, for structure, but also, where possible for data. It's not always possible, but it frequently is.
"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
July 5, 2016 at 6:16 am
Grant Fritchey (7/5/2016)
If there are differences in structure, are there also differences in the amount of data? Take away different data types and different indexes (which makes comparisons impossible really), just having 100 rows versus 1000 rows can lead to differences in the choices the query optimizer makes.In order for you to test prior to production, you need to, as closely as possible, get the environments to be the same, for structure, but also, where possible for data. It's not always possible, but it frequently is.
They're very similar Grant, but that doesn't completely rule out a "tipping point". These are table cardinality figures (56937/59184):
FROM DirectoryV4.dbo.UserData UD -- (56937/59184) (index seek/CI seek)
INNER JOIN DirectoryV4.dbo.NetworkLogon NL -- (69090/74753) (CI Scan/index scan)
ON NL.UserId = UD.UserId
INNER JOIN DirectoryV4.dbo.DepartmentMaster DM -- (16207/16307) (CI Scan/CI Seek)
ON UD.DepartmentID = DM.DepartmentID
INNER JOIN DirectoryV4.dbo.CompanyMaster CM -- (12/12) (CI scan/CI seek)
ON CM.CompanyId = DM.CompanyID
INNER JOIN [RCDFeed].[dbo].UD1 -- (56168/56168) (table scan/table scan)
ON UD.EmployeeID = UD1.EmployeeID
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
July 5, 2016 at 6:24 am
ChrisM@Work (7/5/2016)
Grant Fritchey (7/5/2016)
If there are differences in structure, are there also differences in the amount of data? Take away different data types and different indexes (which makes comparisons impossible really), just having 100 rows versus 1000 rows can lead to differences in the choices the query optimizer makes.In order for you to test prior to production, you need to, as closely as possible, get the environments to be the same, for structure, but also, where possible for data. It's not always possible, but it frequently is.
They're very similar Grant, but that doesn't completely rule out a "tipping point". These are table cardinality figures (56937/59184):
FROM DirectoryV4.dbo.UserData UD -- (56937/59184) (index seek/CI seek)INNER JOIN DirectoryV4.dbo.NetworkLogon NL -- (69090/74753) (CI Scan/index scan)
ON NL.UserId = UD.UserId
INNER JOIN DirectoryV4.dbo.DepartmentMaster DM -- (16207/16307) (CI Scan/CI Seek)
ON UD.DepartmentID = DM.DepartmentID
INNER JOIN DirectoryV4.dbo.CompanyMaster CM -- (12/12) (CI scan/CI seek)
ON CM.CompanyId = DM.CompanyID
INNER JOIN [RCDFeed].[dbo].UD1 -- (56168/56168) (table scan/table scan)
ON UD.EmployeeID = UD1.EmployeeID
Yeah.. those feel awfully close. Different structures are more likely to be the culprit. Or... I haven't looked at the exec plans, different ANSI settings, cost thresholds, max dops? Stuff along those lines?
"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
July 5, 2016 at 8:06 am
This query was working fine previously. Since last 4 days only issue started.
Not sure what happend.
Can you please let me know the reasons.
Thank You.
Regards,
Raghavender Chavva
July 5, 2016 at 8:40 am
What has changed? Data? Structure? Code? Settings?
Nothing just randomly changes how it works out of the blue. If a query starts behaving differently, it could be any of the things I mentioned above, or it could be due to parameter sniffing. You have to determine what changed. Remotely, I can't tell you. I can just make suggestions.
"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
July 5, 2016 at 8:50 am
Do we have any mechanism to trace the changes in DB.
Thank You.
Regards,
Raghavender Chavva
July 5, 2016 at 9:13 am
Not really. You can look at create & update dates for the objects (sys.sysobjects), but it won't tell you what the changes are. You can compare between what's currently in the database and a backup from prior to the changes.
Better still, establish source control as a mechanism for managing changes and control what happens in the production server. But that won't help you find out what happened here, just what happens in the future.
"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
July 5, 2016 at 9:46 am
In addition to everything Grant has said, it's worth noting that the performance of any query depends upon the accuracy of the information which SQL Server can obtain from it. Your query tells SQL Server where to look and what to look for. There are predicates in your query, still, which obfuscate details needed by the optimiser - the non-SARGable predicate which you experimented with is only one example. Poor information will often result in a suboptimal plan. Any of a number of factors could cause a recompile resulting in a different suboptimal plan - which might be better or worse than the original. Recompiling a query which has only sargable predicates will most likely result in the same optimal plan, assuming there haven't been major environmental changes.
Knowing that you have different data types between production and test (which will often cause poor performance), and different indexes too, I'd begin with synchronising data structures and perhaps then checking queries for non-SARGable predicates. Why worry about collecting the metrics of a bad query when you have more useful work to do?
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
July 5, 2016 at 10:48 am
ChrisM@Work (7/5/2016)
In addition to everything Grant has said, it's worth noting that the performance of any query depends upon the accuracy of the information which SQL Server can obtain from it. Your query tells SQL Server where to look and what to look for. There are predicates in your query, still, which obfuscate details needed by the optimiser - the non-SARGable predicate which you experimented with is only one example. Poor information will often result in a suboptimal plan. Any of a number of factors could cause a recompile resulting in a different suboptimal plan - which might be better or worse than the original. Recompiling a query which has only sargable predicates will most likely result in the same optimal plan, assuming there haven't been major environmental changes.Knowing that you have different data types between production and test (which will often cause poor performance), and different indexes too, I'd begin with synchronising data structures and perhaps then checking queries for non-SARGable predicates. Why worry about collecting the metrics of a bad query when you have more useful work to do?
Serious agreement. If you have differences between test and production, how can you reliably test anything. Granted that when testing a change prior to putting into production, you will have differences. That doesn't mean that differences between the two systems should be permanent. You have to establish a mechanism of ensuring that there is a reset of the test system on a regular basis so that it accurately mirrors production.
"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
July 7, 2016 at 1:54 am
These both test and prod databases have been migrated from SQL Server 2008 to SQL Server 2014.
Both test and prod databases are having same data, tables, data types and indexes etc... I can say it as replica of Prod.
But not sure why the query is not working in prod since last 5 days and working in Test perfectly.
I am not getting any Idea about how I need check the Root Cause of this issue.
Could you please suggest how to check the root cause.
Thank You.
Regards,
Raghavender Chavva
July 7, 2016 at 4:05 am
There are differences, and this could account for everything. The first plan I looked at, 1027.sqlplan, is using the 70 Cardinality Estimation Engine. That means that database is using the old cardinality estimator. The compatibility level is still at the old version. The other plan, 1048.sqlplan, is using the 120 Cardinality Estimation Engine. You might just be seeing a regression here.
Does this index exist on both?
[IX_NetworkLogon_UserId]
It's being used in one plan, but not the other. It could be because of the cardinality estimation. To test this, if the index is the same, you could try using the trace flag 9481 on the database that's in the 120 compatibility mode. Or, you could try running the query using trace flag 2312 on the database that's in 70 cardinality mode.
Another difference between the machines is available memory. One of the boxes has considerably more available. That can affect plan choice as well (depending). There might be other differences, but I'm stopping there for now.
I know you said you've updated the statistics on both, but they still feel out of wack. It might just be the cardinality estimator, but one thinks it's getting back a single row, the other thinks it's getting back 7,000+. From what you're saying, the 7,000+ estimate must be more accurate since it's the faster performing query. I would suggest updating the statistics on both and use a full scan. But, it could be down to the 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
July 7, 2016 at 4:41 am
Grant Fritchey (7/7/2016)
There are differences, and this could account for everything. The first plan I looked at, 1027.sqlplan, is using the 70 Cardinality Estimation Engine. That means that database is using the old cardinality estimator. The compatibility level is still at the old version. The other plan, 1048.sqlplan, is using the 120 Cardinality Estimation Engine. You might just be seeing a regression here.Does this index exist on both?
[IX_NetworkLogon_UserId]
It's being used in one plan, but not the other. It could be because of the cardinality estimation. To test this, if the index is the same, you could try using the trace flag 9481 on the database that's in the 120 compatibility mode. Or, you could try running the query using trace flag 2312 on the database that's in 70 cardinality mode.
Another difference between the machines is available memory. One of the boxes has considerably more available. That can affect plan choice as well (depending). There might be other differences, but I'm stopping there for now.
I know you said you've updated the statistics on both, but they still feel out of wack. It might just be the cardinality estimator, but one thinks it's getting back a single row, the other thinks it's getting back 7,000+. From what you're saying, the 7,000+ estimate must be more accurate since it's the faster performing query. I would suggest updating the statistics on both and use a full scan. But, it could be down to the cardinality estimator.
Thank You Very Much Grant Fritchey for clear explannation.
After reading your point about compatibility level, I changed the compatibility of the test database to 120, then recompiled the stored procedure and executed it. Its executing for last 5mins without any results. Where as it used to complete in 3secs previously.
Thank You.
Regards,
Raghavender Chavva
July 7, 2016 at 4:47 am
And I found the root cause for this issue.
On 30th june somebody has changed the Compatibility to 120 on production. After that only SP is not working.
Thank You Very Much again for your valauable suggestions.
Thank You.
Regards,
Raghavender Chavva
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply