Request a Demo

SQL Upkeep

Need Support – Request a Callback

Improving SQL Server Performance

Several factors can affect SQL Server performance. This document describes some of the steps that can be taken to improve performance.

SQL Server Edition

  • Use SQL Server 2012, 2008 or 2005 rather than SQL Server 2000 if possible
  • Always apply the latest service pack available for your version of SQL Server
  • Some editions of SQL Server have limited memory and CPU usage, so use the Standard Edition or higher if possible. For example, SQL Server 2008 Express Edition is limited to using a single processor and only 1 GB of memory
  • Please refer to microsoft.com for further information on SQL

System Processor

  • Always use the fastest possible CPU
  • Use a multi-core processor if possible
  • Configure your SQL server to make use of all available CPU cores on the machine
  • Use an edition of SQL Server which can use all available CPUs

64-bit Systems

  • If you have a 64 bit machine, install the 64 bit version of Windows OS and SQL Server. This will allow the SQL Server to access large amounts of memory where available (more than 4 GB)
  • Note that most 32 bit systems are limited to a maximum of 4 GB of memory

System Memory

  • Install more memory (RAM) on the SQL Server machine
  • On 32 bit systems, install a maximum of 4 GB of memory
  • You can install more than 4 GB on 64 bit systems
  • Use an edition of SQL Server which can make use of all available memory

Server Role

  • Use a dedicated server for the SQL database if possible
  • Avoid sharing the server with other functions such as Exchange Server or Domain Controller

Network Speed

  • Network speed affects the speed at which users can access data and files
  • Installing a 1000 MB network instead of a standard 100 MB network will make a huge difference to network access speed

Hard Disk

  • If possible store the SQL data files on a separate hard disk from the main system disk. (Using a separate hard disk controller for the second drive can also make a difference in performance)
  • Always use a NTFS partition to store SQL data (avoid FAT32 partitions)
  • Regularly defragment the data disk
  • Use the fastest possible hard disk controller and disk that you can

Re-building SQL Indexes

  • Regularly re-building the File Stream data indexes can help with speed of searches. This can be done easily from the Administration functions in File Stream
  • Try to re-index the data on a weekly or fortnightly basis

Truncating Log Files

  • Regularly backing up the SQL data and truncating the log files helps keep the files to a manageable size which helps with performance
  • The built-in backup function in File Stream Administration or in the Scheduler will automatically carry out log file truncation
  • If you use some other direct method of backing up your SQL data, then you should run the built-in backup feature periodically to ensure that the log files do not become uncontrollably large in size.