When it comes to using SQL Server for your application databases, there are a lot of settings and configurations available that can improve environment speeds. While this might not make your super-slow query become instantaneous, it should improve the overall performance of your SQL Server in most scenarios. Without further ado, here are some tweaks you can make to your SQL Server to improve speed.
Reducing SQL Memory Bloat
If your application uses an ORM, like Entity Framework, you can potentially fill up a lot of SQL memory with SQL plans. This means that less of your data is stored in memory. If you are wondering why that’s a big deal, think of it this way. If SQL Server has to read table data from disk, it can go thousands of times slower. So how can you free up some of that memory and get more data in cache?
Optimize for Ad Hoc Workloads
In SQL Server 2008, a new setting was added for reducing single use plans. This setting is turned off by default, but it is pretty straight-forward to turn on. Note that this won’t work for everyone, so your mileage may vary. To read more on seeing if this will work for you, there is a great write-up by Kimberly Tripp here. Also, for actually setting the setting, look at the SQL Server documentation here.
Compress that Data for More Memory
Another good way to get more of your data in your precious cache is to compress that data. If you have a large table taking up a lot of space, you can potentially shrink the space that it takes up with a couple different levels of compression. The first is row level compression. This will make some of your data smaller before storage. If you have an integer column that only has values up to 255, for example, SQL will store it in one byte instead of 4. The other type is page-level compression. This will not only apply row-level compression, but will also use a compression algorithm on all rows stored in a page. There is a lot more CPU overhead with this type of compression, but if your CPUs are idle and you are out of memory, this is a very good trade-off.
Will It Help you?
In order to test if compressing a table will yield benefits for you, there is a built in function from SQL Server called “sp_estimate_data_compression_savings”. To find out how much savings you will get with this for a particular table, here is the documentation for its usage.
Keep a Runaway Query in Check
Sometimes your application will have a large run-away query that will take all of your CPU and affects the whole application. This query might not have to run fast, but the application can’t go down while it runs. To prevent this, you can reduce the number of CPUs a single query can run and therefore allow other queries from your app continue to run. This setting is called MAXDOP or max degree of parallelism. If you are running a multi-user application with a lot of updates, you will want to set this to less than the number of logical processors you have on the box. If this is a big problem for you, you can start with setting it to half your CPUs and reducing farther down if you don’t see the results you want. For example, if your SQL Server has 8 logical processors, try a MAXDOP of 4. If a single query is still taking up to much CPU, try a MADOP setting of 2. To read more on how to change this setting, visit the SQL Server documentation here.
If you find this article helpful and you’d like to find out more things you can do to improve your SQL Server performance, please follow the link below to contact me. With 15 years of experience working with application databases, I’ve made slow database queries run 100x faster and vastly improved page load times. If you follow my proven process to improve your database, I guarantee you will achieve similar results.