August 5, 2009 at 12:21 pm
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,
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
August 5, 2009 at 10:14 pm
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
August 6, 2009 at 4:46 am
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.
August 7, 2009 at 4:36 am
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
August 7, 2009 at 4:56 am
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
August 10, 2009 at 4:07 am
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
August 11, 2009 at 8:08 am
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
August 11, 2009 at 9:15 am
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
August 11, 2009 at 4:38 pm
TheSQLGuru (8/11/2009)
Search web for SQL Server 2005 Waits and Queues, a marvelous best practices white paper from Microsoft.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply