November 21, 2017 at 9:04 am
If seeing a client make a regular query that uses the same plan but completes in radically different times on alternate occasions. Is there are a way to determine if the cause is from the server or client?
We see nothing on the SQL Server signifying the difference i.e. no significant waits,no locking, server resources are not too different each time the query is made, as I said, same plan. Similar amount of data.
It makes me think the issue is with the client or network but then again no waits indicating network io.
Data movement is around 5m rows.
Query can be done in 5 mins or 60+ mins.
Very perplexing.
Could it be the client? Can this be proven?
November 21, 2017 at 9:55 am
leehbi - Tuesday, November 21, 2017 9:04 AMIf seeing a client make a regular query that uses the same plan but completes in radically different times on alternate occasions. Is there are a way to determine if the cause is from the server or client?
We see nothing on the SQL Server signifying the difference i.e. no significant waits,no locking, server resources are not too different each time the query is made, as I said, same plan. Similar amount of data.
It makes me think the issue is with the client or network but then again no waits indicating network io.
Data movement is around 5m rows.
Query can be done in 5 mins or 60+ mins.
Very perplexing.
Could it be the client? Can this be proven?
Can you post the plans please? (as .sqlplan file attachments) There may be subtle differences which you have missed.
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
November 21, 2017 at 11:07 am
leehbi - Tuesday, November 21, 2017 9:04 AMIf seeing a client make a regular query that uses the same plan but completes in radically different times on alternate occasions. Is there are a way to determine if the cause is from the server or client?
We see nothing on the SQL Server signifying the difference i.e. no significant waits,no locking, server resources are not too different each time the query is made, as I said, same plan. Similar amount of data.
It makes me think the issue is with the client or network but then again no waits indicating network io.
Data movement is around 5m rows.
Query can be done in 5 mins or 60+ mins.
Very perplexing.
Could it be the client? Can this be proven?
It could be a lot of things. Have you checked the query stats for that query? You could check to see if the same radical differences are seen in the stats - there are min, max, last (and you can calculate averages) statistics available. Differences could be based on other things but it's still worth checking. If it sometimes runs for a long time, you can check the stats after that run. Waits on network could be on the client and not SQL Server so I wouldn't necessarily rule that out at this point. I'd still post the plan though - there could be something in the plan which could point to whatever the issues are if it's on the SQL Server side.
Sue
November 21, 2017 at 4:59 pm
It sounds like a classic case of the bad kind of parameter sniffing. My first step on something that handles that many rows would be to add OPTION (RECOMPILE) to the query and see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2017 at 5:00 am
I can't easily share the plans as there is sensitive data in column names. We've pushed the plans through a diff tool and the only differences are changes in plan cost related to the differences in rows - the operators are the same. We do have parallel operations all over the plan - something funny could be going on there - one of the plans has come from the cache and one has not. The one from the cache runs fast so I doubt an option recompile will make a difference.
Am I wrong to expect to see SQL Waits for client/network issues? Wouldn't SQL Server show waits if there were client/network issues eg ASYNC_NETWORK_IO.
Other thing is that we're re-building the table prior to it being queried so we don't see much IO in stats - assume the data is still in memory.
I've requested monitoring for the client to see if there is anything going on there.
November 22, 2017 at 6:33 am
Capture the query runtime metrics. Do those vary wildly on the server? Also, measure the runtime from the client. Do they vary between that shown on the server? What are all the waits when the query runs (extended events can help there)? That's where I would focus. Don't get bogged in execution plans just yet (and look who is saying that).
"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
November 22, 2017 at 7:19 am
leehbi - Wednesday, November 22, 2017 5:00 AMI can't easily share the plans as there is sensitive data in column names. We've pushed the plans through a diff tool and the only differences are changes in plan cost related to the differences in rows - the operators are the same. We do have parallel operations all over the plan - something funny could be going on there - one of the plans has come from the cache and one has not. The one from the cache runs fast so I doubt an option recompile will make a difference.
You should still try it because it still sounds like parameter sniffing, especially when it comes to "cost related to the differences in rows". It will cost you almost nothing to see if it works or not which means that is also costs you nearly nothing to eliminate it as a possibility if it doesn't work.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2017 at 9:12 am
Jeff Moden - Wednesday, November 22, 2017 7:19 AMleehbi - Wednesday, November 22, 2017 5:00 AMI can't easily share the plans as there is sensitive data in column names. We've pushed the plans through a diff tool and the only differences are changes in plan cost related to the differences in rows - the operators are the same. We do have parallel operations all over the plan - something funny could be going on there - one of the plans has come from the cache and one has not. The one from the cache runs fast so I doubt an option recompile will make a difference.You should still try it because it still sounds like parameter sniffing, especially when it comes to "cost related to the differences in rows". It will cost you almost nothing to see if it works or not which means that is also costs you nearly nothing to eliminate it as a possibility if it doesn't work.
Jeff is right. You can get a copy of the free tool from SentryOne called Plan Explorer. It's free. It has the facility to anonymize the plan. It'll remove any sensitive information. You can then post the plans, good & bad so we can help out. Please though, post the whole plans, not pictures of them. All the good data is in the properties (which get anonymized too).
"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
November 26, 2017 at 3:55 pm
Just curious- if we run same query (let say we have this option) on sql server directly (via SSMS) and won't have any issues- does it prove this (issue) is application/network related or we cannot be 100% sure? Thanks
November 27, 2017 at 6:14 am
Yuri55 - Sunday, November 26, 2017 3:55 PMJust curious- if we run same query (let say we have this option) on sql server directly (via SSMS) and won't have any issues- does it prove this (issue) is application/network related or we cannot be 100% sure? Thanks
Not unless you can show that there are two different execution plans. This can happen due to ANSI settings and some other stuff, but you need to prove that.
By the way, since you're running this on 2016, a really easy way to capture the information and have the ability to compare plans is to enable Query Store on the database in question. It will capture all the plans created for the given query and it will have an aggregated history of the query runtime information. Then you can use SQL Server Management Studio 17.4 and it's ability to compare two execution plans to spot specific differences. That's what I would do if you just posted to the two execution plans.
"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
November 27, 2017 at 6:26 am
Grant Fritchey - Wednesday, November 22, 2017 9:12 AMJeff Moden - Wednesday, November 22, 2017 7:19 AMleehbi - Wednesday, November 22, 2017 5:00 AMI can't easily share the plans as there is sensitive data in column names. We've pushed the plans through a diff tool and the only differences are changes in plan cost related to the differences in rows - the operators are the same. We do have parallel operations all over the plan - something funny could be going on there - one of the plans has come from the cache and one has not. The one from the cache runs fast so I doubt an option recompile will make a difference.You should still try it because it still sounds like parameter sniffing, especially when it comes to "cost related to the differences in rows". It will cost you almost nothing to see if it works or not which means that is also costs you nearly nothing to eliminate it as a possibility if it doesn't work.
Jeff is right. You can get a copy of the free tool from SentryOne called Plan Explorer. It's free. It has the facility to anonymize the plan. It'll remove any sensitive information. You can then post the plans, good & bad so we can help out. Please though, post the whole plans, not pictures of them. All the good data is in the properties (which get anonymized too).
I don't know because I don't use the tool but, if you need the tool to see the plans, it would be better to post the plans from SSMS on forums so that more people can take a gander.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2017 at 8:12 am
Jeff Moden - Monday, November 27, 2017 6:26 AMI don't know because I don't use the tool but, if you need the tool to see the plans, it would be better to post the plans from SSMS on forums so that more people can take a gander.
I hate to praise a competitor of mine, but no, it's a fantastic tool. you can save the file as a sqlplan file and share it after making it anonymous. And it's free. Hard to find fault.
"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
November 27, 2017 at 11:47 am
Grant Fritchey - Monday, November 27, 2017 8:12 AMJeff Moden - Monday, November 27, 2017 6:26 AMI don't know because I don't use the tool but, if you need the tool to see the plans, it would be better to post the plans from SSMS on forums so that more people can take a gander.
I hate to praise a competitor of mine, but no, it's a fantastic tool. you can save the file as a sqlplan file and share it after making it anonymous. And it's free. Hard to find fault.
I'll 2nd that... Plan Explorer really is an awesome tool that provides a ton more information than SSMS and it presents that information far more usable format (IMO).
November 28, 2017 at 4:30 am
Jason A. Long - Monday, November 27, 2017 11:47 AMI'll 2nd that... Plan Explorer really is an awesome tool that provides a ton more information than SSMS and it presents that information far more usable format (IMO).
I am a little torn on the "far more usable format" bit. It can be. However, since it doesn't, by default, display all properties, we're dependent on them picking the right ones to display. I think they largely nail it, but I'll bet there are misses in there. It's my one critique since we are so dependent on those properties when you really start working with plans.
"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
November 28, 2017 at 6:00 am
Plans attached. I will explore extended events. Good tip about SQL Sentry Plan Explorer.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply