June Question: What is your best advice for boosting index performance?
Question of the Month | June 1, 2010 | 1:00 amPost your responses to the above SQL Aloha Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
I’ll pick the winner that offers what I think is the most original, useful and complete answer to the question. If some of the responses are very similar, then I will select the one that was posted first. The winner will be announced on this page the first week of July. Good luck!
For more information, click on the Question of the Month tab above.
| This Month’s Prizes
|
The prizes this month are an Amazon.com voucher worth US$50.00 and a license for HyperBac for SQL Server with one year of support and upgrades, worth US$994.00
With HyperBac for SQL Server, you can use your existing native maintenance plans or T-SQL commands to create compressed, encrypted SQL Server backups. You can also back up to ZIP for greater flexibility on restore. More information is available on the HyperBac website. |
- Winner of the June SQL Aloha Contest
- September Question: Tell us about your DBA interview experiences: good and bad.
- Winner of the July Question of the Month
- January Question: What feature(s) of SQL Server would you like to learn more about?
- April Question: What advice would you offer a novice DBA in order to help them along in their career?
RSS
Twitter
Linked In
This is an easy one! …. Filtered Index!
Based on your review of the data… cardinality analysis is important for this to work… however the 2008 feature of filtered index’s is a HUGE performance gain on highly selective fields. If 80% of your data has a “Y”, and 20% has “N” adding a filter on that field to the index can eliminate HUGE amounts of data from the index seek before it even starts.
Use with caution… but applied correctly you will see a HUGE upside to query performance!
Boosting Index Performance:
It Depends. Indexes are one of the more trickier areas of performance tuning.
High-Level Items that need to be considered:
* Does an Index fully cover the query in question?
* Are the statistics current? Remember, a filtered index may contain a reduced set of values, but it may not change enough to have current statistics and may need to be manually updated.
* Does the Index need to be Reorganized or Rebuilt? Statistics will be automatically updated (If enabled) each time the index changes by 10%, but the on-disk storage structure does not get automatically Reorganized or Rebuilt and the continued addition and deletion of rows will fragment the index, even a Clustered-Index, due to the way SQL Server manages storage space
* If the Index is not the Clustered-Index, does it contain (as part of the Index or as Included Columns) all columns needed to satisfy the query ? Are all needed columns covered by the index? If the Index covers the needed columns for the operation, then the SQL Engine will not need to query the actual data-row.
* Has a query “Tipping Point” ( http://sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx ) been triggered by a non-covering index?
After the above items have been attended to, if there is a specific query which has a performance issue, I would review it to ensure it and the expected index are still compatible as indexes and queries have a tendency to change over time and not always in step with each other.
I would also run the query with the option for Actual Execution Plan output to see how the engine is using (or not using) the expected index. It may be the conditional clauses in the index actually cause the query engine to use a different path to the requested columns (Stale statistics come into play on this).
These are some topics to consider along with little items like FROM-Clause order and the Logical Order of Precedence in the query engine.
What a “Can of worms”.
While there is much you can do to increase the performance of an index directly in SQL such as reindexing, defragging, making sure it covers everything you need, etc; one of the oft overlooked aspects of index tuning is the hardware.
By placing specific indexes on different disks you can fine tune your performance. Perhaps you have a data model where only one of your tables is constently read while others are written to. Then you may wish to place the indexes and tables that are read often on different disks than the ones that are written to often.
To increase performance but maintain “price performance” you can place often used and read indexes on smaller faster RAID arrays (such as SAS drives), while placing your other data on reliable but less expensive RAID arrays (such as SATA2 drives).
Another, but similiar reason, is to spread the load of your database, if you have many indexes often used, moving them to seperate disks can help spread the load.
My tip is to consider indexes on Computed columns.
So, imagine you often see:
WHERE YEAR(OrderDate) = @someYear
…then create a computed column for YEAR(OrderDate), and put an index on it. The engine will recognise the expression in the query as matching a computed column, and you’ll see sargability achieved!
(and of course to remember the included columns)
My two cents on this is( actually its three cents):
a.) Start at the big bang, i.e., application design. Make sure your tables are normalized and you have indeed the correct columns chosen for the indexes and have a clustered index on each and every table.
b.) Make sure that you have all the indexes and they are indeed being used using the DMVs.
c.) Do a reorganize and rebuilds as needed
Voila, you have indexes that perform better…
Addendum:
Separate your non-clustered indexes from your table and place them on a different filegroup (which should be logically be placed on a different disk, but I have seen the worst)…
1. First and foremost is to make sure you have covering indexes available to support all high-volume, high-performance query, so that all columns can be drawn from the index without having to do a lookup from the primary table.
2. Pay attention to the sequence of the columns. Vital statistics are kept on the distribution of values within the first column of the index, and somewhat lesser information is kept on values in the second column. If you find your queries doing a lot of index scans instead of index seeks, even a covering index will run slowly.
2. Where all columns can’t be included due to disk space constraints or other maintenance reasons, try to at least keep an index that covers all the columns for in the queries’ JOINs or WHERE clauses.
3. Consider partitioning indexes over large tables. I have a general rule of thumb that if the table is large enough to partition, so is an index over that table.
4. Use the new filtered index functionality where appropriate. If you can filter out most of the rows in the table at the index level prior to a query it can be a boost. But be sure to test your queries to make sure they actually use the filtered index.
5. Don’t forget about indexed views. If you can afford the disk space and maintenance overhead, they can take the JOIN load away.
6. Last but not least, pay careful attention to the sort order of the indexes. If there is a usual ORDER BY order in which queries are run (for example: region/customer/sales date descending), then having an index already in that sequence can give you the sort for “free” at query runtime, which can be a major boost to speed.
First, do no harm
I think the best performance comes by way of testing. Hopefully you’re on SQL 2005 or higher where you can get some useful guidance from the DMVs.
Too often I see junior level folks willing to create an index without much regard to actual need. Workloads change, servers change, needs change–it’s good to look at this from a routine maintenance perspective like mowing the grass.
Using the following DMVs figure out what’s going on. Here are a few of my faves:
1) sys.dm_db_index_usage_stats
2) sys.dm_db_missing_index_details
3) sys.dm_db_missing_index_groups
4) sys.dm_db_missing_index_group_stats
These joined back to sys.indexes and other tables can give pretty good insight into what actions to take.
Once the evidence has been gathered on what needs to be done, then gradually add the changes and revisit the above DMVs to be sure you’ve gotten it right.
Rinse and Repeat.
One area often overlooked is that of unused indexes.
Unused indexes are a performance lag when it comes to modifying data; each index that is affected by updates/deletes/inserts will have to be rebuilt.
Luckily we have a DMV that can help us – sys.dm_db_index_usage_stats
From this DMV we can get an understanding of the “hits” an index gets, i.e. how many times it has been used, by summing (user_seeks + user-scans + user_lookups), vs the “updates” (user_updates)
An index that has many “updates” but few “hits”, may be one for review. I say “may”, as you would still have to ensure that it wasn’t that one critical index that is used for year-end reporting, and you would have to decide whether it was better to create the index for the time it was required, and then drop it ; or keep the index as-is.
Check fragmentation and reindex/reorganize regularly.
Make sure the Fill Factor is appropriate for the index.
Make sure all indexes are actually useful. I’ve seen people add 1 index for each column, even if the selectivity is low, and they rarely query that column. Too many indexes on a table with a lot of modifications is not good. In an OLTP database, you need to be more selective on the indexes you add.
Use unique indexes wherever possible as you can cheat with statistics. If you have a unique index (clustered or not), say the columns are A,B,C, and you always provide all 3 columns, statistics are moot FOR THAT COMBINATION. SQL Server knows that you will only ever get one row for that combination. This is powerful, but can cause issues if you use anything less than the unique combination. I tend to use this for permanent temp tables and tables within stored procedures.
Determine the queries of the overall workload that cause the most logical reads. Create (covering) indexes for those queries if necessary.
The top queries can be determined with traces or from DMO sys.dm_exec_query_stats.
Check the improvement of each individual query.
This contest is over, and the winner will be announced soon.