Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql server performance issues Expand / Collapse
Author
Message
Posted Wednesday, October 08, 2008 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:10 PM
Points: 10, Visits: 124
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?
Post #582645
Posted Wednesday, October 08, 2008 9:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #582699
Posted Wednesday, October 08, 2008 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:10 PM
Points: 10, Visits: 124
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.
Post #582802
Posted Wednesday, October 08, 2008 11:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #582814
Posted Wednesday, October 08, 2008 1:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:10 PM
Points: 10, Visits: 124
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?




Post #582888
Posted Wednesday, October 08, 2008 2:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #582908
Posted Wednesday, October 08, 2008 2:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:10 PM
Points: 10, Visits: 124
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
Post #582945
Posted Thursday, October 09, 2008 3:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #583145
Posted Thursday, October 09, 2008 3:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 27, 2014 11:17 PM
Points: 309, Visits: 814
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
Post #583157
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse