June 10, 2015 at 7:36 am
Help me out, I tried everything possible.
Copied a db from the current server (SQL 2008 v.10.0.05512.0, 8 cores, 40955 mem) to new server (SQL 2014 v.12.0.2000.8, 8 cores, 49152 mem). Same basic file setup on both servers: RAID10, data on E:\, logs on F:\. MAXDOP=0. There are no other jobs or functions running on the either server.
Each early morning, about 1am, both servers perform the same functions, so I can test the new one's performance against the current one.
Step 1, take a text file, and convert to a bulk file ready for loading into SQL.
Step 2, disable indexes.
Step 3, bulk load file.
Step 4, rebuild indexes.
The performance on the new server is improved on steps 1, 2, & 3. But step 4, rebuilding the indexes is significantly slower. I have already made sure on the new server, the compatibility level is set to 2014, stats were updated. I even dropped the indexes and recreated them from scratch. All the indexes are non-unique, non-clustered.
So what am I missing? Why is it slower on the new server?
Thanks,
Michal.
June 10, 2015 at 7:44 am
Is the storage setup different for each server?
June 10, 2015 at 8:10 am
What is the recovery model of the two servers? Also, what are the growth rates for the database on the new machine? Last but not least, are the disk drives on the new machine the same ones for the old?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2015 at 8:17 am
Drive setup is the same for both machines. The file layout is the same as well. The machines each have their own set of drives. So there is no resource conflict.
Recovery model is set to Simple on both copies of the db (the data is rebuilt each night).
Growth rate is only marginal, and both copies of the db have plenty of available free space at the moment.
June 10, 2015 at 8:43 am
Try setting compatibility level to 110 and see if that helps.
June 10, 2015 at 6:53 pm
Capture the wait statistics on the job to see where it's slowing down.
"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
June 12, 2015 at 9:20 am
Update to my indexing problem...
There are two instances of SQL2014 running on this hardware. The instance I was working with to solve the indexing problem, had a Maximum server memory setting established, however, the other instance did not. So the second instance was using up everything it could, leaving the server maxed out on ram. This morning, we set the max limits on both instances added to equal roughly 70% of total physical ram; which we may bump up later if needed. So far things are running a lot faster, like I expected them to.
Indexing a 140mm row table on the old server takes about 15 minutes. On the new server it was taking about 75 minutes. After the max memory setting update, that index rebuild now takes 8 minutes. So much better.
Lesson learned, check ALL settings, not just the current ones. (And don't assume the server admin knows how to properly install and setup SQLServer)
June 12, 2015 at 9:43 am
Outstanding feedback, Michael. Thanks for taking the time to post it!
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2015 at 10:05 am
+1 to what Jeff said. It's always great to get the information of what really happened and what the issue was. Thank you!
"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
June 13, 2015 at 11:02 am
Michal-469362 (6/12/2015)
Update to my indexing problem...There are two instances of SQL2014 running on this hardware. The instance I was working with to solve the indexing problem, had a Maximum server memory setting established, however, the other instance did not. So the second instance was using up everything it could, leaving the server maxed out on ram. This morning, we set the max limits on both instances added to equal roughly 70% of total physical ram; which we may bump up later if needed. So far things are running a lot faster, like I expected them to.
Indexing a 140mm row table on the old server takes about 15 minutes. On the new server it was taking about 75 minutes. After the max memory setting update, that index rebuild now takes 8 minutes. So much better.
Lesson learned, check ALL settings, not just the current ones. (And don't assume the server admin knows how to properly install and setup SQLServer)
Quick questions, is anything else than the two SQL Server instances running on the server? Have you check the "available memory", OS memory paging and the related wait stats to confirm the settings?
😎
48 Gb is not a lot when you have two instances of SQL Server, the OS and then some other activity such as SSIS, RDP etc. Given the price of memory (RAM) these days, I must question this meagre allocation.
June 16, 2015 at 12:43 am
http://www.sql-server-performance.com/2014/trouble-shooting-sql-server-ra-memory-consumption/
Go through this .. its quite a useful for reindexing and update stats .. by my colleague and friend satnam singh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply