Covering Data Consulting, LLC, Minneapolis, MN

From the blog

3 Commonly Overlooked Speed Boosts for SQL Server

car-1593838_640

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.

Performance Help

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.

Contact now »

Leave a Reply

Your email address will not be published.

Services

Performance Tuning

When your employees can’t work because of a poor performing application, your business can lose thousands of dollars in productivity. Worse yet, you can lose customers on a client application. Since a large portion of application performance issues trace back to the database, you need a professional who can quickly[…]

Learn More

Database Design

Your data is some of your most important assets, yet some businesses are hand-cuffed by poorly performing databases. Application development is difficult and many developers are tempted to have tools auto-create the databases. Don’t compromise your future data needs by skimping on your database design. We can work with your[…]

Learn More

SQL Server Health Checks

You know your database server has problems, but you don’t know where to start.  The sys-admins blame the code and the developers blame the hardware.  We can come in and identify your issues and work with your staff to get the database working again.  Plus, we will provide detailed information[…]

Learn More

Database Solution Architecture

You’ve done the indexing and performance tuning.  You’ve run out of options, but you still need more out of your database.  We can help you maximize the use of your existing database instance to save money and get you back on track. Contact now »

Learn More