Sql server performance issues

  • 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?

  • 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
  • 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.

  • 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
  • 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?

  • 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
  • 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

  • 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
  • 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