MS SQL Server Performance Tuning
An often neglected, but important area of database management involves factors that can affect MS SQL Server Performance. The most common factors are:
-
Whether or not the application server is dedicated to MS SQL functions. – Running MS SQL on a Domain server, Terminal Server, or an Exchange Mail Server is not recommended.
-
The version of MS SQL that you are running. – Some lower priced versions of MS SQL Server limit both the size of the database and may restrict the total number physical processors that can be used with MS SQL processes.
-
The Servers CPU processor. – Running MS SQL on older a single core processor could result in poor overall query processing. You should have beefier server for this task.
-
Server Ram – The amount of physical RAM that the computer has and how much of it is dedicated specifically to MS SQL. – Most newer 64 Bit servers support much higher amounts of RAM. This is a critical element of MS SQL performance tuning.
-
The amount of swap space dedicated to the server. – While this is often set dynamically, I have seen situations where this has been set too low especially when there are situations that involve low amounts of physical RAM being installed in the computer, or where more RAM was installed, but the swap space on the server was never increased.
-
Utilities – Whether or not there is a routine to Rebuild Indexes, Reorganize Data and Index Pages, Update Statistics, and how frequently these utilities are run.
For more information you can also review my previous blog entitled "Creating MS SQL Maintenance Plans." Also, see Maintenance Plans and Automating Administration Across an Enterprise in the MS SQL Help and/or contact the CCS Retail Systems Support Department for further assistance.