SQL Server Performance Forum – Threads Archive

Optimum Config Parameters

Hi Gurus, Please let me know for a Database of Size 4 GB(Complete), What should be the
Ideal Configuration Parameters that I have to SET in a SQL Server. I would like to SET All those Parameters Values available for
Maximum Performance.
Thanks raj

You have to provide more informations like:
Hardware configuration (memory, cpu’s, disks).
Software: OS and SP, SQL server and SP.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

And also it depends on how the application is developed and the clients usage.
In general its suggested to leave the default SET settings in SQL server. 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:In general its suggested to leave the default SET settings in SQL server.

Agree about server settings. However, I would set db and log sizes equal to estimated max sizes.

Hi Gurus, We have Pentium with single 2.4 Ghz CPU,Two SCSI Hard Disks, and 1 GB RAM.
Windows-2000 server os and sql server 2000. We have a Informatica based Appication,Which runs at every half an hour.
No of Database three.
No of users 100+ on the same server. Questions: How can I allocate db Buffers,Redo Buffers,Kind of say Increase or Decrease
those buffers. Like in Init Parameter file of the Oracle. Thanks
raj

unlike udb or oracle, sqlserver "selftunes" by default.
Only change the settings if prooven the defaults don’t perform to your needs.

You need a tool to perform STRESS testing on the database when using the application, LOAD RUNNER or WINRUNNER are the suitable tools. And all those internal buffer settings must be tested before deploying on the production database server. One more thing, if you’re not getting any performance problems then better to leave the settings AS IS and perform regular schedule of DBCC REINDEX and intermittent RECOMPILE of Stored PRocedures and UPDATE STATISTICS for the volatile tables for optimum performance. One golden rule is set DYNAMIC MEMORY settings to SQL Server. 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.

Hi Gurus,
Can you Pleas eloborate on the " DYNAMIC MEMORY Settings to SQL Server". Is it something that a DBA has to Set Or It happens by default
when we Install the Sql Server. Thanks
raj

By default sqlserver is configured to use dynamic mamory allocation.
It will grab as many ram as it needs, and will only free it if requested by others at the server.
However, you can set limits (min and max) as to what volume of ram sqlserver can have at its service.
With Enterprise manager, rightclick the servername and then select properties.
Then you’ll see a tab memory. Here you can manage these parameters.
If not prooven it hurts, just leave it to the default settings.

For information on memory counters refer to thishttp://www.sql-server-performance.com/performance_monitor_counters_memory.asp link and this threadhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6801#31033 for relevant information. HTH 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.

]]>