Who is this for?
If you are maintaining servers, you may want to read these tricks. If you are just 'using' the MCe/MCxLE – you will likely find this document boring and no interest to you.
If you are running on a SaaS server like Asset Pro Solutions Inc. (Maintenance Connection Canada) or Maintenance Connection Inc.'s SaaS server, these tricks are already being used.
So this document is for you if you are maintaining your own, typically on premise, server.
How much detail?
Obviously there are literally 1000's of detailed tricks you can do. But there are some VERY simple ones that can make a huge difference, and these have stood the test of time.
SQL Server setup HD tip
Performance Issue? Use SSD drives.
SSD drives are so much faster than old fashioned HDD's and Hybrid drives that you will be hard pressed to find a more substantial upgrade if you are not running on as SSD yet.
I know in the early 2000's they had reliability problems. But they are much better now (2015) and you should have good backups whether you are running on as SSD or an HDD – so if there is a serious problem, that's why you have backups.
Performance Issue? Add memory!
In general, if you have less than 16GB of memory, the cost is very low and it can double or more the speed of the server. In general, you should upgrade your server to 16 or 32GB before spending the same money on any other upgrade.
The results almost always give you better increases than any other upgrade and the cost is so low these days that it seems unwise to run any SQL Server with less than 16GB memory. And if it happens that your server is one of the exceptions, the fact that memory is so cheap means that this is a very low risk trial.
Performance issue: Set your SQL Server HD up correctly:
If you are installing, or have control over a SQL Server, SQL Server writes in 64k chunks.
Just tell the Hardware/SAN person, when they go to set up ANY new drive for SQL Server, that it needs to be formatted with a 1024 KB starting offset, and a 64 KB block size.
Some suggest that since Windows 2008 this has been the normal setup, others indicate there are still (2014) problems with hard drive IT creating the drives with a different setup.
Getting this wrong reportedly will give you about a 30% decline in performance on your SQL Server since all reads and writes are done this way, and if the starting offset or block size are wrong, you will cause multiple physical writes for every logical write.
For more info:
May 2013:
April 2014:
There may be newer information, if you come across some, let us know. But basically, we aren't looking at this issue anymore, we just make sure we follow the specifications.