August 30, 2017 at 5:09 am
Hi,
We have an integration query running at 5am - simple view on 3 tables (2 tables have 4-6m rows) - on some days the client extracts data in 4 minutes and in some days it can be 30-40 minutes.
The base tables being queried are re-built everyday.
We've focused on comparing a good day vs bad day i.e. checking waits, query plans using redgate sql monitor. I think monitor software only allows 10 minute windows. The query taking 30-40 minutes doesn't show up in the top 10 waits, queries (I find this very odd) during the time of its execution. Looking at waits across all queries we did see an increase in page splits towards the end of the query window - I'm not sure if this is causing the performance increase.
The query plans are the same - we see parallel operations. We see no evidence of network issues i.e. no network related increase in waits. We do see instances of CXPACKET waits but nothing significantly different over the good / bad days.
I'm reluctant to tune the query before understanding what is causing the difference day to day.
Any ideas if there is another approach that sheds light on the issue? Is it possible use Redgate SQL Monitor to capture and report on the individual query?
Is it possible to have network bottlenecks with no network waits in SQL Server?
August 30, 2017 at 6:04 am
Using SQL Monitor, you can sort the queries by duration, waits, several different ways. If you use the top of the screen to highlight a particular time range, say the 40 minutes when the query ran, you should be able to see it right there in the query list, sorted by duration or one of the other options. I just validated it by testing it. It works. That should give you some insight as to what's happening with that query, the top waits, different plans, if they are different, etc.
If the plans are the same (and just being visually the same doesn't mean that they are), then the differences between days is more likely to be resource contention of some kind. That doesn't mean that tuning the query won't be necessary. Making queries run faster makes them less likely to suffer from resource contention.
"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
August 30, 2017 at 8:22 am
I had to search for a bit before the expensive query appeared in the top 10. Can we show more than top 10? Anyways I have drilled into the query on the two days that are fast and slow. It's very tricky for me to compare the days as on the slow day the query is taking so long that it overlaps with other heavy queries. I've zoned into the first 10 minutes of the queries being run to compare stats as the query should finish within these 10 minutes. My top wait is CXPacket but I'm expecting this as the dbase is a DW.
The stats for the SQL Server are very similar. CPU no higher than 8% either day, memory is similar as is disk io. I've verified the same plan is being pulled from the cache.
There is no obvious reason reason why the queries are so different 1 day to the next. The client query has an ORDER BY which is expensive - I've asked if this can be removed. If not I'll see if we can put the ORDER BY into the ETL.
Are there any other clues I can look for to determine the difference in performance?
August 30, 2017 at 8:44 am
It's only 10 at a time in the GUI. You can query the database directly to get more specific info if you want. If you highlight a query, you will, in some cases, see multiple waits. So rather than trying to go from top waits, you can try that. I'd suggest setting up the Blocked Proceses report to capture blocks. It'll show you specifically the process that is being blocked and how.
From what you're describing, I think you may be seeing different queries. It's hard to know for sure. You mention that some use ORDER BY and some don't. That's going to result in radically different execution plans and performance. This will be especially true depending on how they're filtering the data (if they're filtering the data). Download the query plan that works well and the one that works poorly. Open them in the SSMS 2017 GUI (doesn't matter that you're on 2012). You can compare the two plans to see if there are detailed differences. That's how I'd pursue this.
First, capture blocking through the alert. Second, get the exec plans. Both are likely the core of the issue.
"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
August 30, 2017 at 9:11 am
If there was blocking wouldn't I see that in the top 10 waits? My second highest wait is LCK_M_SCH_S but when I click on it there is No significant data to display for the 10 minutes I have selected.
The queries on good and bad days are identical - the execution plan id is the same on each execution - I think you're right it must be blocking issue if it's not the execution plan. The data day to day doesn't grow significantly.
We already have a blocking alert so I'll dig into that.
I appreciate your help.
August 30, 2017 at 10:02 am
Blocking will lead to waits, but it's not necessarily going to appear in the top waits, depending on which resource is blocking on what. It can all get pretty much like spaghetti, all mixed together. That's why I liked the blocked process alert. It shows exactly what was blocked and what was blocking, clarity.
"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
August 30, 2017 at 5:26 pm
I have to ask if there's a pattern for which days are good and which ones are bad.
Also, does it correlate to any statistics maintenance or periodic data loads you have scheduled?
If you have larger, periodic data loads, the statistics aren't up to date. If you rebuild them a couple of days later and the problem goes away, then you have your answer.
August 30, 2017 at 5:34 pm
If you want to query the dates statistics were last updated on your tables, this should do it for you. Just adjust the WHERE clause to limit the query to the tables you want to check.
SELECT so.name, s.name, STATS_DATE(so.object_id, s.stats_id)
FROM sys.objects so
INNER JOIN sys.stats s ON s.object_id = so.object_id
WHERE so.name = 'YourTableName';
Hope this helps.
August 31, 2017 at 3:54 am
I don't believe it's an issue with stats - they were updated last week.
I've noticed there is some blocking immediately before the query starts. This blocking seems to correlate strongly with performance of the query even though the tables being blocked aren't involved in the actual query being run.
I've tested good / bad days and the only thing that's different (from SQL Monitor stats) is the absence of blocking on a good day.
I'll do more testing but I think this is the explanation.
What a great community this is ++
SQL Monitor has also been useful here - very handy comparing the different time windows and as Grant said the blocking alert is very handy showing the dependencies.
Best
Lee
August 31, 2017 at 6:23 am
Happy to hear SQL Monitor is helping.
I agree on the community. SQL Server Central has helped me tons over the years.
By the way, if you do get stuck on SQL Monitor specifically you can always ask a question on the Redgate Hub.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply