Enterprise Edition SQL Server 2005 Performance issue on Multi Processor Server

  • We are facing SQL Server 2005 Enterprise Edition and SP 2 installed in Windows Server 2003 Enterprise Edition with SP 4. We have upgraded hardware for CPUs and RAM, 1 processor to 2 processors and 4GB to 8GB respectively.

    By adding new processor the performance affected badly. We did some configuration in SQL Server for the following areas

    Max degree of Parallelism = No of processors

    Max worker thread = 288 (according to formula,

    ref http://sqlblogcasts.com/blogs/thepremiers/archive/2007/05/17/max-worker-threads-configuration-in-sql-server-2005.aspx)

    We checked AWE to allocate memory from 1 GB to 7GB

    This is all we have done so far, but i think there must be more. please provide us help in this regards

    Mohid

  • kindly state more details about what is ur current avg response time and what do u mean by badly affected ? is it performing poorly or it has stopped responding ?

    and according to BOL its recommended to leave the MAX WORKER THREADS although there are other recommendations but for most systems its better to keep sql server handle the internal workings.

    make it 0 and restart the service and then check the response time.

    if problem is not resolved then state the Avg Disk Queue Lenght, Avg Batches and TempDB configurations also.

    What are the number of users connected to your system ?

    Musab
    http://www.sqlhelpline.com

  • thanks Musab,

    there are 20 to 25 users connected to the server. The performance decreased after adding new processor.

    Also please note that we are using 32Bit OS and SQL Server, is there any limitation of 32Bit.

  • did u change the max worker to 0 ?

    kindly let me know the details about the behaviour of the db server when its performing slow ? i mean the counters i have mentioned

    but first you need to try changing max worker threads to 0

    Musab
    http://www.sqlhelpline.com

  • It would also help if you would post the output from:

    SELECT * FROM sys.configurations;

    ...and...

    The current SQL Server error log

    ...and...

    DBCC MEMORYUSAGE;

    The output from the second and last ones will be large, so please post them in a zipped-up attachment.

    Those pieces of information will answer many questions all at once.

    Thank you

    Paul

  • You say you have asked for 7GB AWE memory on a 8GB box. Do you have the message at SQL startup that says you have allocated this memory? If not, then SQL is using probably only 1.6GB memory which could be less than it had before the upgrade.

    You are very unlikely to get much more than 6GB memory allocated to SQL Server on a 8GB box. Depending on what else is running, even 6GB may be hard to achieve.

    If you have time to experiment, try reducing your memory setting by 0.1GB steps until you get the startup message that AWE is being used, then reduce memory by another 0.1GB to give a safety margin. You should periodically review the startup messages, especially if SQL is a lot slower after a restart, as all sorts of Windows, anti-virus, SQL, etc fixes will tend to use more memory than was needed without the fixes.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • I think you should find out what the slowness truly is. Search web for SQL Server 2005 Waits and Queues, a marvelous best practices white paper from Microsoft. in it you will find track_waitstats_2005 and get_waitstats_2005. Learn how to use an interpret the output from that. Also check out file IO stalls using sys.dm_io_virtual_file_stats.

    Best, if you really want to get things running faster quickly, is to hire a performance analysis/tuning professional for a quick engagement. You can flop-and-twitch for days on this forum and not get things running right when a good consultant would likely have you fixed up in a matter of minutes to hours. He/she could also teach you how to monitor effectively in the future.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I agree with SQLGuru. You should time-box your tuning efforts.

    If you have not fixed the problem by then, getting a SQL expert could have the problem fixed in a day and give valuable guidence on how to troubleshoot future problems.

    If you are worried about costs, you could raise a support call with MS. This costs about GBP £300 if it is not included with your license agreement. My experience of PSS is they are very good at identifying the cause of the problem.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • TheSQLGuru (8/11/2009)


    Search web for SQL Server 2005 Waits and Queues, a marvelous best practices white paper from Microsoft.

    http://technet.microsoft.com/en-us/library/cc966413.aspx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply