Skip to content

Improve SQL Server performance with a simple query

July 26, 2010
tags:

This is probably embarrassingly simple to a SQL professional, and it’s been around since SQL 2005, but I only discovered it today and it made a noticeable difference to my production databases so I’m going to share it just in case it’s new to anyone else.

The query

Run this query on SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database, based on the workload of the server since the SQL service last restarted. (The query just lists the indexes, it doesn’t create them!)

SELECT    'CREATE INDEX IX_Auto_' +
           CONVERT(varchar(max), MID.index_handle) +
           ' ON ' +
           [statement] +
            ' (' +
           COALESCE(equality_columns + ', ' + inequality_columns, equality_columns, inequality_columns) +
           ')' +
           ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_statement,
           CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,
           [statement] AS table_name,
           equality_columns,
           inequality_columns,
           included_columns,
           last_user_seek,
           avg_total_user_cost,
           user_seeks,
           avg_user_impact
FROM       sys.dm_db_missing_index_details MID
JOIN      sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle
JOIN      sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle
ORDER BY  avg_total_user_cost * user_seeks * avg_user_impact DESC

The results

Running the query will return results of the following form, with indexes that have the largest potential improvement first.

Column Description
create_statement A SQL statement to create the proposed index.
potential_saving The relative query cost saving of this index.
table_name The fully qualified name of the table that the index applies to.
equality_columns Comma-separated list of columns that contribute to equality predicates of the form table.column = constant_value.
inequality_columns Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form table.column > constant_value.
included_columns Comma-separated list of columns needed as covering columns for the query. These are columns that aren’t used as key columns for the index, but are included for quick retrieval by queries.
user_seeks Number of seeks caused by user queries that the index could have been used for.
last_user_seek Date and time of last seek caused by user queries that the index could have been used for.
avg_total_user_cost Average cost of the user queries that could be reduced by the index.
avg_user_impact Average percentage benefit that user queries could experience if this index was implemented. The value means that the query cost would on average drop by this percentage if this index was implemented.

How it works

Every time the SQL query optimiser analyses a query, it works out the best indexes for the filtering it will need to do. If these indexes don’t exist, it nevertheless remembers that they would have been useful, including how much quicker the query would have been any how many times they would have been used. After the database has been up and running for a while, SQL Server will have a pretty good idea of which indexes would make a significant difference to your live workload.

The query above uses the Missing Index dynamic management objects to list all these indexes in order of potential query cost saving.

Caveats

  • Obviously if you don’t understand SQL indexes at all, it’s best not to fiddle with them. While there is a very good chance that the top few suggested indexes will be beneficial, there’s always a chance that you could seriously degrade write performance, or that you’ll lock the table for a prolonged period while the index is created.
  • Don’t create every index in the list! It’s better to create them one at a time and measure the improvements – after a while the savings will become more trivial and it’s more likely that the decrease in write performance will negate the query benefits.
  • Not every possible index that might help will be returned. See Limitations of the Missing Indexes Feature.

Further reading

Before you dive in, I strongly recommend having a quick read through Finding Missing Indexes in SQL Server Books Online.

Which Google jQuery hosted link to use?

July 21, 2010
tags:

As you probably know, the best way to load jQuery on an internet website is to get it from Google’s CDN. There are three main reasons for this:

  1. Parallelism – Browsers limit the number of simultaneous connections to a single server to avoid overload. Any resource that you fetch from a different server than your own will starting loading immediately, rather than being queued up alongside the images, scripts, CSS etc coming from your main server.
  2. Speed – Google’s CDN has many servers in different physical locations, and your user will be automatically directed to the server that will serve their request most quickly.
  3. Caching – Most importantly, if the user has already been to a site that uses the same version of hosted jQuery, their browser will already have a local copy and won’t need to download it at all.

However, it’s important to make sure that you use the right URL to load jQuery. Let’s suppose my site works with jQuery 1.4.2 (the latest version at the time of writing). I can actually use three different URLs to get it:

  • http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js – This is version 1.4.2, as you would expect. If you’ve tested against 1.4.2 and don’t want any surprises, this is the one for you.
  • http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js – This is the latest revision of version 1.4, so while it loads 1.4.2 at the moment, your site will be automatically upgraded to 1.4.3 when it’s released. However, if and when 1.5 is released you won’t get it, which protects you from any breaking changes that might be introduced.
  • http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js – This is the latest revision of version 1. You’ll always get the latest and greatest jQuery (until they release version 2), but you do need to keep an eye out for breaking changes. This is the default setting in DotNetNuke when you enable hosted jQuery, so if you’re using this setting then you may want to give your site a quick test when a new version of jQuery is released to the Google CDN (subscribe to Google AJAX API Alerts to know when this happens).

Also worth noting if you’re fine tuning for performance is that if you specify the exact version number then jQuery will be cached in the browser for up to a year, giving the best possible performance. If you only specify a partial version then the browser will still check the Google server every hour to see if there’s a new version available, even though it won’t actually download it if nothing’s changed.