Indexes exist to make data selection faster, so the focus of this article is on ways you can select the best indexes for faster data retrieval. This is done in a two-step process.
- Step One: Gathering Information
- Step Two: Taking Actions on the Information Gathered
Indexing can be quite a challenging task if you are not familiar with your databases, the relationships between tables, and how queries are written in stored procedures and embedded SQL. How many companies you have worked for have a proper ERD diagram of their databases and have followed the textbook method style of programming? In the real world, time is often limited, resulting in poor SQL Server database performance.
If you have been tasked with optimizing a database’s performance (at least to a respectable level), or you want to be proactive with your databases to prevent potential future performance issues, following these steps should help you in tuning tables, just as they have helped me. These steps are applicable at any stage of project, even if a deadline is just around the corner.
Step One (Gathering Information)
Interact with the people who know about the database and its table structures. If you know it already, that’s great. This is very important and makes your life easier.
1) Identify key tables, based on:
- Static tables (often called master tables).
- Highly transactional tables.
- Tables used within a lot ofstored procedures or embedded SQL.
- Tables with an index size greater then its data’s size. You can use sp_spaceused with the table name to find table space usage.
- Top 10 or 15 big size tables. See a prior year database if available or applicable. The idea is to identify the largest tables in the database after it is in production. There is a script available at http://www.sqlteam.com/item.asp?ItemID=282 that you may find useful.
2) Identify the most frequently called stored procedures/queries and list all of the tables used by them.
3) Get the SQL Profiler trace of :
- Production site (if available/applicable). Running a trace on the production box during typical activity is worth the effort and will be fruitful in later analysis.
- Testing site (if one is available/applicable).
- Otherwise, get if from your development server.
It is advisable to write down information you collect in a document for later retrieval.
4) Before we dive into analyzing the information gathered, here are few things to keep in mind while tuning your tables:
- To see the Query/Execution plans of queries, highlight them in SQL Query Analyzer (isqlw.exe) and select Display Estimated Query Plan (Cntl+L) from the Query menu. If you want to see the query plan of a stored procedure, select Show Execution Plan (Cntl+k) and execute the stored procedure. Also, turn on the “Set Statistics IO on “ command. Examining Query/Execution plans can be a bit time consuming. But you will find it easier if you really understand the database and its tables before you begin.
- You need to have a good foundation on how clustered and non-clustered indexes work. See this URL for a good foundation on this topic: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp