HELP! How much disk and ram is enough??? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

HELP! How much disk and ram is enough???

Hi all, I’m sure this question has probably been asked before, but I searched and did not come up with any real answers. I’m sort of desperate for a solution here so I’ll ask the question. This is related to a performance issue I posted in the Performance tuning for DBA’s forumhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9127 I’m still no closer to solving this problem, but I was looking at the server configuration and the physical box (windows 2000, sp4 SQL server 7.0) has: 2 Gig of RAM
A data drive formatted to 67 Gig capacity that is 75% full (15 Gig free)
Processor is a Xeon 2.6 Ghz Some quick background of the problem: When a user posts some production orders in a small batch process, the entire system grinds to a hault for other users. i.e Rest of application is very, very slow, database access is slow, etc. (see post in other forum for more info) My basic question is aimed at the ram and drive stats. Is 2 GIG of Ram enough? Is the remaining drive space enough? This sever operates an ERP package and supports about 110 users, not necessarily all connected at once. I know I may be asking a question that is very subjective, but even if you can answer me with a general things-to-think-about response that would be very helpful. Thanks! This is a great site. Lot’s of sharp, helpful people here. Karl
What is the edition of sQL Server?
What is sQL memory setting?
How about database scheduled jobs such as reindexing and consistency [email protected] Take help of following links to trouble-shoot:
http://www.sql-server-performance.com/sql_server_performance_audit8.asp
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224587
KABhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589&sd=tech Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Also, what is the size of your DB and what is the size of the tables used for the production orders batch process? What types of disks are you using, what config? I read in documentation that RAID can break down performance wise if the drive is over 80% full, so you are close. Have you set up a PerfMon trace for memory, processor, queue, page life, etc. during the 5 minutes of the process?Is the processor hyperthreaded? It seems like it may be a combination of hardware (2GB of memory can be low) and design. I agree with all the experts in here about how critical design is and that it is even more important than hardware, however I also agree that sometimes it is cheaper to add hardware then have a developer fix the problem with the app.
quote:Originally posted by satya What is the edition of sQL Server?
What is sQL memory setting?
How about database scheduled jobs such as reindexing and consistency [email protected] Take help of following links to trouble-shoot:
http://www.sql-server-performance.com/sql_server_performance_audit8.asp
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224587
KABhttp://support.microsoft.com/default.aspx?scid=kb;en-us;243589&sd=tech Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

As stated, SQL server 7.0
Good question on the memory setting…I’ll get back to you.
No scheduled jobs as mentioned as of yet. Those are definately going to be added; don’t know why they were not done in the first place by the last guy. As for disk space…is being 75% full ok? or should there be a greater margen of free space? Thanks, Karl
quote:Originally posted by rweinstein Also, what is the size of your DB and what is the size of the tables used for the production orders batch process? What types of disks are you using, what config? I read in documentation that RAID can break down performance wise if the drive is over 80% full, so you are close. Have you set up a PerfMon trace for memory, processor, queue, page life, etc. during the 5 minutes of the process?Is the processor hyperthreaded? It seems like it may be a combination of hardware (2GB of memory can be low) and design. I agree with all the experts in here about how critical design is and that it is even more important than hardware, however I also agree that sometimes it is cheaper to add hardware then have a developer fix the problem with the app.

Thanks, all good questions and things to think about…I’ll check these out, set up some performance monitoring and get more info… Karl
You need to state whether it is a standard edition or enterprise edition, run SELECT @@VERSION for results.
It seems the performance is dented due to no scheduled jobs to optimize indexes and update statistics, so you need to get them asap in order to gain the performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Originally posted by satya You need to state whether it is a standard edition or enterprise edition, run SELECT @@VERSION for results.
It seems the performance is dented due to no scheduled jobs to optimize indexes and update statistics, so you need to get them asap in order to gain the performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Ahhh…ok, I ran SELECT @@VERSION and came up with Microsoft SQL Server 7.00 – 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Looks like the Standard version…and yes, the scheduled jobs will be setup. Thanks, Karl
No it is standard version of Windows NT and to return edition for SQL you may use SELECT CONVERT(char(20), SERVERPROPERTY(‘Edition’)) (I’m not sure whether this will work with SQL ver.7) It seems the SQL is not patched upto the levels of service pack which is SP4 on version 7, so test and deploy that too as well. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Microsoft SQL Server 7.00 – 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft CorporationStandard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) This indicates sQl server is standard edition and not windows. If issue is reproducable then run PSSDiag
]]>