January 22, 2019 at 4:45 pm
Ok very strange one this that is outside of my skill level unfortunately. We've got a fairly large database (35GB) with medium usage. This was on oldish hardware and SQL Server 2008. We got a new server, lots more ram / faster processors - great! HDD setup is the same (i.e. raid / configuration / file location). Backed up the database and restored on the new server (running in 2012 mode). Everything seemed fine - but all was not well. I'm getting very strange performance issues. Most queries are running slightly faster, which is great, but some queries on the first time are running a lot slower.
Example - we have a query that on initial run takes 7 seconds to complete. If I run it again it takes 250ms. If I change a parameter value it takes 7 seconds to run again. If I clear the query plan cache it takes 7 seconds to run again. If I run the same query on the old database, it takes 500ms on first run, 400ms on second run.
So something is defo up with how long it takes to compile the query. When I return the actual execution plan, its the same but the estimate rows / subtree costs are a lot higher on the new server. When I do properties and get the compile time its 7000 vs 350 on the old server (assuming thats ms).
If I amend the query and have options(recompile) it takes 3 seconds to run pretty much each and every time. So faster initial, but still too slow on recalls.
As part of the migration, rebuilt all indexes and updated statistics.
So long story short, new server is quicker but only after the query plan has been created. Ideas?
January 23, 2019 at 8:43 pm
goksly - Tuesday, January 22, 2019 4:45 PMOk very strange one this that is outside of my skill level unfortunately. We've got a fairly large database (35GB) with medium usage. This was on oldish hardware and SQL Server 2008. We got a new server, lots more ram / faster processors - great! HDD setup is the same (i.e. raid / configuration / file location). Backed up the database and restored on the new server (running in 2012 mode). Everything seemed fine - but all was not well. I'm getting very strange performance issues. Most queries are running slightly faster, which is great, but some queries on the first time are running a lot slower.Example - we have a query that on initial run takes 7 seconds to complete. If I run it again it takes 250ms. If I change a parameter value it takes 7 seconds to run again. If I clear the query plan cache it takes 7 seconds to run again. If I run the same query on the old database, it takes 500ms on first run, 400ms on second run.
So something is defo up with how long it takes to compile the query. When I return the actual execution plan, its the same but the estimate rows / subtree costs are a lot higher on the new server. When I do properties and get the compile time its 7000 vs 350 on the old server (assuming thats ms).
If I amend the query and have options(recompile) it takes 3 seconds to run pretty much each and every time. So faster initial, but still too slow on recalls.As part of the migration, rebuilt all indexes and updated statistics.
So long story short, new server is quicker but only after the query plan has been created. Ideas?
First thought is you may have more data cached on the old server vs new which means more physical reads now. As queries are being ran, data will load into memory and be quicker. If you want to test that theory, clear cache on the old server too and see if those same queries have quicker execution times still.
January 24, 2019 at 2:33 am
New server is very similar to old server just newer (i.e. better CPU, more ram, more storage - but same raid / SSD setup etc). Nothing seems to explain why there is such a massive disparity between initial run and second run, and the same thing isn't seen on old server?
January 24, 2019 at 6:18 am
Without access to the servers to really drill down on this, it sounds like differences in the optimizer between 2008 and 2012. Every time you upgrade some queries are going to experience regressions because of that. Query Store, introduced in 2016, is the major tool for addressing this issue through plan forcing.
"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
January 28, 2019 at 4:04 pm
Do be sure to update your statistics also, though, esp. when restoring SQL to a different version.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply