SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql server performance issues


Sql server performance issues

Author
Message
sqlservertica
sqlservertica
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234542 Visits: 46373
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

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


sqlservertica
sqlservertica
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234542 Visits: 46373
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

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


sqlservertica
sqlservertica
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234542 Visits: 46373
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

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


sqlservertica
sqlservertica
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234542 Visits: 46373
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

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


Sakthivel Chidambaram
Sakthivel Chidambaram
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1441 Visits: 838
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search