October 8, 2008 at 8:48 am
Hi Guys,
Recently we migrated to a new server, and we decided to split our database in different file groups and these file groups in different disks.
We are experiencing a big performances difference but in a bad way, queries that in the old server use to take 30 minutes now take 7 hours.
This new servers is a 64bit machine and the old one was not. I noticed that our old server had configured in the advance options a 0 for Max Degree of Parallelism, do you guys think that will make that much difference?
October 8, 2008 at 9:29 am
Not enough information to say anything useful.
What were the specs of the old server. What OS, what version of SQL?
What are the specs of the new server. What OS, what version of SQL?
What's the max memory setting on the new server? What's the max degree of parallelism set to on the new server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2008 at 11:36 am
Sorry about that,
The old server was installed on Windows Server 2003, SQL Server 2005, 32bit machine, the max degree of parallelism was 4
The new server is installed on Windows Server 2003, SQL Server 2005, 64bit machine, the max degree of parallelism is 0.
The other difference is the in the old server we didn't split the file groups in different disk, in the new server we are doing that.
Any ideas if parallelism can be the problem, since I read having the file group in different disk can actually improve performance.
October 8, 2008 at 11:56 am
What are the specs of the two servers (memory, disk)?
What's the max memory setting on the new server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2008 at 1:35 pm
These are the specs of the old server
AMD Opeteron(tm) Processor 875, 2.20 GHZ, 7.83 GB of RAM.
Disks: C: 33.9GB,
D: 67.8 GB,
V: 1.36 TB,
W: 169 GB
These are the specs of the new server
Intel(r) Xeon(tm) CPU 3.20 GHZ, 3.19 GHZ, 7.99 GB of RAM.
Disks: C: 68.2 GB,
D: 68.2 GB,
V: 683 GB,
W:683 GB
I'm not sure which max memory setting are you talking about, RAM?
October 8, 2008 at 2:21 pm
How many processing cores did the old server have, how many does the new one have?
SQL Server's max memory setting. Configured under server properties (object explorer in management studio) or by using sp_configure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2008 at 2:57 pm
The old server had 8 processors and the new server has 16, but remember that our new server is a 64bit machine and the old one was 32bit.
The max server memory in the new server is 2147483647 MB and in the old one it was 7000 MB
October 9, 2008 at 3:12 am
sqlservertica (10/8/2008)
The old server had 8 processors and the new server has 16, but remember that our new server is a 64bit machine and the old one was 32bit.
The main difference between 32 and 64 is the amount of memory that can be addressed.
The max server memory in the new server is 2147483647 MB and in the old one it was 7000 MB
I would suggest that you configure the max memory on the new one to be the same as the old. With 64 bit, the memory needs to be limited. You can try setting the max degree of parallelism to 4 or 8. I doubt it will make that much of a difference. Not 6 hours of difference.
Are the drives direct attached? SAN? Are they RAID arrays or single drives?
Are all queries running slower or just one or two?
The one that's gone from 30 min to 7 hours, is it a single query or a complex procedure. If the latter can you run it piece by piece to find where the worst performing portion.
How did you migrate the DB? Backup/restore, detach/attach?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2008 at 3:41 am
Hi Gila,
Do you think it will be helpful if he can post the performance counter results so that it will helpful to find performance/IO bottleneck etc?
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply