April 30, 2016 at 2:18 am
Geeks,
We have a query which is taking different execution times in 2 different servers. We have plan to migrate databases from Instance -A to Instance B and hence these are being tested.
A query on Instance A takes 35seconds whereas the same query takes 25min in Instance B
Instance A(non-clustered): SQL 2012 Standard Edition with SP2 (x64) on Windows 2008 R2 SP1.
Instance B(Clustered) : SQL 2012 Enterprise Edition with SP2 (x64) on Windows 2012 R2
Both have similar Intel CPU's with same number of cores (2 cpu, 6 cores, 12 logical )
I had matched the SQL configuration paramaters to be the same including Max memory.
Database size is 260GB and the problem table which is taking longer has a size of 40GB with 180+ million rows.
To be sure, i had refreshed a fresh copy of DB from Instance A to Instance B and updated stats.
I noticed that the execution plans are different and setting STATISTIC IO ON i see that there are only 3 physical reads on Instance A vs 400k physical reads in Instance B. This makes sense as to why it is slower but how can we have SQL choose a similar plan as that of Instance A.
Does the Edition and OS difference make any difference in the execution plan SQL optimizer chooses.
Thanks,
April 30, 2016 at 5:42 am
Can you post the execution plans for both queries? When updating the statistics did you use full scan, sample or resample? Are the maxdops the same on both?
😎
April 30, 2016 at 6:08 am
While updating the stats i used FULL SCAN and also the MAXDOP settings are same on both the servers.
I enclosed the plans.
April 30, 2016 at 6:37 am
In the slow plan, there is a RID lookup on the table [DB_FRMIS_det].[dbo].[MI_ORT_OrderRowTransaction_T]
Could there be a missing clustered index?
I suggest you compare the index structures for all of the tables, and make sure that they are in place on the slow server.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 30, 2016 at 7:22 am
There are a few clues around this. If you look at the properties of the SELECT operator, there are differences in the apparent setup of the servers. One has 12 processors available for parallelism and the other only 6. Both plans are timing out. This alone could account for the differences in the plan if one server was able to try a couple of extra plans more than the other before the timeout occurred. One big difference here is that you're moving from Standard to Enterprise. The Standard edition has limits on the processors. That's going to result in differences to the plans (and might explain why we're seeing differences in the SELECT operation).
However, I'm seeing differences in the cardinality between the tables in the two plans. Just taking one example, [DB_FRMIS_det].[dbo].[MI_DEP_Department_T].[IX_MI_DEP_Department_T_1] [MI_DEP_Department_T] shows as having a cardinality of 14013 in the slow plan and 14211 in the fast one. That difference isn't enough to make a difference in the plans, but it makes me wonder if there are other differences. Are you sure these are the same databases with the same structures, the same data, and updated statistics? They don't appear to be. Another example [DB_FRMIS_det].[dbo].[MI_ORT_OrderRowTransaction_T].[IX_MI_ORT_ACT_ID] [ORT] shows as 187,690,000 in the fast plan and 178,890,000 in the slow plan. That many rows different, that's going to lead to problems.
I also think the statistics are way off in the slow instance. Your estimate for the same table above is 169,083 while the actual is 76,852,437. That's a huge disparity.
From what I can see, your issues are, at least in part, caused by the move from Standard to Enterprise. However, beyond that, these are not the same data sets and the statistics don't seem to be updated at all. We're comparing apples to hammers because of this.
"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
April 30, 2016 at 8:55 am
Thanks you for a very detailed explanation @Grant.
There might be few differences in the number of rows from fast - slow instance. That is because Fast instance is currently LIVE production instance and it might just have 1 days transactions so the difference would be minimal and also when i tested it was like-to-like and the execution plan was the same for fast instance.
I updated the stats using the command :
EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'
However i will rebuild / reorganize indexes and test it again and will update here.
I would also guess this could be something due to Standard v Enterprise edition.
May 1, 2016 at 12:18 pm
harikumar.mindi (4/30/2016)
EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'However i will rebuild / reorganize indexes and test it again and will update here.
Just so you know, rebuilding/reorganizing indexes will not affect plan choice. When you rebuild an index, the statistics are updated. It's the statistics update that affects the plans. If you're going to rebuild indexes, don't bother updating statistics. If you just want the statistics updated, just update the statistics, don't rebuild the indexes.
Rebuilding the indexes helps if you're seeing lots of scans. If you're seeing lots of seek operations, rebuilding indexes can be a waste of time.
"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
May 2, 2016 at 4:18 am
I had made sure that all the stats for tables being referenced are updated. i ran the below query to verify the same.
use <Database Name>
go
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('<Table Name>')
GO
I raised a Microsoft support ticket for this issue and also simultaneously i have requested for a similar environment to test this query (one which replicates fast execution)
May 5, 2016 at 5:52 am
Update to the topic.
Microsoft really did not suggest a great deal of improvements except to use OPTION (Force Order), changing MAXDOP settings, running the query in DTA and implementing index suggestions and saying that little difference in CPU architecture can make a difference (latest 2.5Gz vs Old 2.8Gz)
Implementing an index suggestion from DTA tool on the biggest table did make a difference and the execution time has come down to 1min, however this does not explain why on the other server without this index it is still better.
We had taken a look at the histogram and did realize that sampling is very much different in both. Faster one has a sample of only .25% of the total rows whereas the slower one has sampled the total rows.
Hence we restored the backup again and did not update the stats and then ran the query, surprisingly it is exactly taking the same time as the old server which was faster !
May 16, 2016 at 1:53 am
Finally we have managed to find the answer !
The difference was caused due to the bad format of disks being used for the data file. We had few disks formatted with 4k block allocation unit size but there was one which was with 64k.
My first database was on 4k disk and the database which was giving better performance has its data file on 64k disk.
Recommended by Microsoft for SQL server is 64k, hence we made sure all the clustered disks used are with 64 block allocation unit sizes.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply