Document Management: 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
- 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
- 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
- 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
- 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 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
- 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.
If you have any questions regarding the technical side of our software, please do not hesitate to get in touch.
Experience the Benefits
Filestream’s customer-focussed products are developed by an expert team who have been helping businesses improve their document management processes for more than 30 years.
Adherence to GDPR Compliance
Secure Software (Including Encryption)
Store your documents and data with 100% security thanks to full encryption using 256 AES algorithms. Your files are ready to be accessed anywhere in the world by those who should be – while keeping out those who shouldn’t.
Integration to Existing Systems (Including Accounts)
Document processing capabilities (Including Workflow)
Need more information? Speak to one of our experts.