Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enterprise Edition SQL Server 2005 Performance issue on Multi Processor Server


Enterprise Edition SQL Server 2005 Performance issue on Multi Processor Server

Author
Message
Mohid Mirza Javaid
Mohid Mirza Javaid
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 8
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
MusabUmair
MusabUmair
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 619
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
Mohid Mirza Javaid
Mohid Mirza Javaid
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 8
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.
MusabUmair
MusabUmair
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 619
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
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11288 Visits: 11354
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



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3682 Visits: 3829
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
TheSQLGuru
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7081 Visits: 8397
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 at GMail
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3682 Visits: 3829
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11288 Visits: 11354
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



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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