SQL Performance

SME is a database oriented application and relies on performance of SQL Server.

For smaller installations, basic server configuration is no problem. However for companies that have many users and heavy load, it's important that the SQL Server is optimized. Here are the best practices for SQL server setup in a high volume environment:

1. Run full version of SQL Server rather than SQL Express. Note SQL Express is fine for small installations for example up to 10 to 20 SME users.
2. Dedicated Server for SQL: Move ALL applications off sql server and have sql as a dedicated virtual or physical server.
   1. In an ideal environment the SQL server should not be used for any applications other than SQL. We have many companies running SQL/SME/and QB from the SQL server without performance impacts. No other applications should be installed on the SQL server with the exception of the above and necessary maintenance utilities.
3. Three drives for this dedicated SQL server as follows:
   1. C drive should only contain the sql server application.
   2. Drive 2 should be a dedicated drive for the main sql database
   3. Drive 3 should be a dedicated drive for temp tables and logs. Recommend that this is a SSD or Hybrid drive for speed. It does not need to be RAID.
      1. Separate drive for the Temp databases and Logs – in our experience systems running a SSD, or hybrid SSD/traditional combined drive, for the temp database and logs experience better performance since they are the SQL components with the highest read/write drive demands. They do not need to be part of the raid. Some companies choose to keep the temp databases and logs on the raid but set them up in a separate partition.
4. Increase number of processor cores either as physical or virtual cores.
5. All clients must be using the latest SQL Native Client for the version of SQL you are running. In your case use the latest 2008 native client. This is especially true for any machine still running the XP operating system. You can download that from here:
   1. Use for all 64 bit machines: http://high5software.com/downloads/SQL/SQL2008/sqlncli_x6408.msi
   2. Use for all 32 bit machines: http://high5software.com/downloads/SQL/SQL2008/sqlncli_x8608.msi
   3. Open the firewall ports on clients to the server port TCP 1433 and UDP 1434
   4. Set exceptions in all A/V software for the sme5.exe on the server and, depending on the A/V software, add the ports noted above
   5. Do not install the 2012 Native Client as that will cause issues on the XP machines
6. Network: We strongly suggest you run an analysis tool, such as Wireshark, on your network and benchmark connectivity between the SQL server and all clients to assure there are no issues. We’ve had many calls regarding degraded performance that resolved out to either faulty network cards, bad cabling, and failing switches. We also suggest you do not use wireless connections since in our experience they have a significant impact on performance.