If you are using SQL Server on premise and/or administer your own SQL deployments there are probably some configuration settings that you skipped or might not have noticed that can have a serious impact on your overall applications performance.
I mainly work with the performance around SQL for Microsoft Dynamics applications, and find that the most common issues are the ones below. Although it is sometimes not the case, the majority of things you will see are listed and should help with your performance issues.
#1 – Over Allocation of Virtual Hosts
First and foremost almost always your network and operations teams will tell you that they run VMWare and that everything gets the resources it needs at all times. VMWare, or similar virtual engine will monitor and allocate exactly what is needed as resources are used, and lastly you shouldn’t worry about it.
In some cases this is true, but if the servers are over allocated then everything on that particular host will start to experience “pressure” that will trickle into all servers on the host.
You need to make sure that you communicate the essential dedicated resources that are required for optimal user performance and make sure that you at least have adequate head space on the virtual host to operate.
#2 – Check the Power Plan Settings on your SQL Server(s)
By default, your OS will most likely be set to a “balanced” power plan. This is great for a desktop OS such as your laptop while you are on the road and you want to use less energy to extend your battery life.
This is a horrible idea though for your SQL server environment. In short, you want your SQL server to Dynamically control your memory and CPU usage which it cannot do if the host is not allowing it to.
The setting for the SQL Server Power plan should always be at high performance whenever possible so that there is no configuration bottlenecks down the road or spinning up or holding back resources that should be allocated to the Server.
#3 – Make sure that your database, log, and tempdb directories are configured on separate drives
Ideally your network team can help you create partitions on your SAN so that you can get the max benefit from distribution of labor.
As an added benefit it is much better from a recovery standpoint in the event that things go wrong. Just as you wouldn’t want everything related to SQL server on a single location to fail and to lose all data.
#4 – Set Your Max Memory Configuration Settings in Microsoft SQL
If you haven’t set a max memory configuration you could actually be hitting the upper threshold of your servers memory limits and reaching into memory that could be reserved for the OS or additional applications that also need to run on the server.
When possible you should determine your workload that you need for the SQL server and set your max memory settings to something that actually makes sense. By the way if you didn’t realize SQL server now comes configured by default with 2TB of memory to use as the default maximum memory settings.
#5 – Dial up your Cost Threshold(s) for Parallelism
By default SQL is still configured for a cost threshold of parallelism of 5, this default is still the same as when SQL 2005 rolled out and has not changed.
You should experiment depending on the types of workloads that you run on the server of a cost threshold of around 25-40 (in most cases) as 5 will not cut it it so you should definitely adjust this on your server configurations.
#6 – SQL Adhoc Workload Optimization
I still have no idea why this one is never enabled by default. This will work even better for those environments that run queries every once in a while and there is no immediate need to store an entire execution plan if it is used so rarely.
SQL is very efficient at not only querying for data, but understanding how to look information up in the most efficient way possible. The setting in SQL is a boolean value that in my opinion should be set to True by default.
#7 – Use Backup Compression
If you are backing up your databases using SQL Servers native backup engine to a secured location on your network you should consider modifying your backup to use compression.
Note: You may additionally have a 3rd party solution that you already use for backup compression. That is ok as well as long as you are using some form of backup compression.
#8 – Make sure that you are trimming logs after backing up
This mainly happens when you are running the database in full mode which will store everything that happens in the DB in the log.
If you haven’t made a plan to trim and archive data you could have additional data size being allocated on your disk store that doesn’t need to be.
In short these are some of the primary main things you can check when working through some simple configuration tweaks.
These can help improve your overall performance of your on premise SQL deployments for your Microsoft Dynamics applications.