Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Enterprise Edition SQL Server 2005 Performance issue on Multi Processor Server Expand / Collapse
Author
Message
Posted Wednesday, August 5, 2009 12:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 11, 2009 9:27 AM
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
Post #765750
Posted Wednesday, August 5, 2009 10:14 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:56 AM
Points: 65, Visits: 521
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
Post #765941
Posted Thursday, August 6, 2009 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 11, 2009 9:27 AM
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.
Post #766072
Posted Friday, August 7, 2009 4:36 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:56 AM
Points: 65, Visits: 521
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
Post #766811
Posted Friday, August 7, 2009 4:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #766822
Posted Monday, August 10, 2009 4:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,868, Visits: 3,214
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #767713
Posted Tuesday, August 11, 2009 8:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 4,350, Visits: 6,162
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
Post #768628
Posted Tuesday, August 11, 2009 9:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,868, Visits: 3,214
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #768707
Posted Tuesday, August 11, 2009 4:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #769016
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse