SQL Indexes and Plans, Oh My!
Date: 2015-06-17 10:46:22
Before we start
Indexes are unique and magical to each system. How each system is used will determine if indexes are needed or not. Also, we probably need to completely, routinely, re-evaluate the application’s usage, as user’s patterns change, and holistically implement those changes into the deployment of the application.
And here we go…
Well, before we get into Indexes we need to discuss Tables, maybe Pages too...
Pages & Extents
Tables – Heap & Clustered
- A page is the smallest collection of data that SQL holds. It is 8KB in size. Frequently people will say: try to keep a table smaller than 8KB. This is to fit the entire row of the table onto a single page. All database files (MDF, LDF, etc…) are divided into Pages.
- An Extent is 8 Physically Contiguous Pages. That is, it’s 8 pages that sit right next to each other on the hard drive.
How do I tell if a table is a Heap?
- MSDN definition for a Heap: a table without a clustered index. Now that’s helpful, isn’t it? A Heap is a table without any order. A Heap can get fragmented, its rows spread across multiple pages of the database. But a Heap cannot be defragmented.
- Clustered Index is a table that is Ordered in a specific manner, i.e. by column(s). It uses that Order to reference all the other columns of the Table. There can be only 1 Clustered Index per Table because the Clustered Index is the Table. Clustered Indexes can be get fragmented just like Heaps, but they can be defragmented.
How do I tell if this table Heap is a problem?
- The Sys.Indexes system table contains a listing of all indexes for a database. This will give us information as to whether the Table is a Heap or a Clustered Index. There is a system table named Sys.Tables. It does not indicate the type of table.
SELECT DB_NAME(DB_ID()) DatabaseName, OBJECT_NAME(OBJECT_ID) TableName, [Name] IndexName, Type_Desc IndexType FROM sys.indexes
- Also, from within SQL Server Management Studio (SSMS) you can
- Expand the Database
- Expand the Tables folder
- Expand the Table in question
- Expand the Indexes folder
- Review the contents of the Indexes folder. Is there an index with (Clustered) in its name? If not, this table is a Heap.
How do I fix a Heap?
- A Heap may have problems when it is fragmented across the hard drive, i.e. scattered across multiple pages on multiple extents. A Heap will get fragmented as rows are Deleted and Inserted and Deleted and Inserted, repeatedly over time.
- Does it take a long time to perform a basic Select, even when there are only a small number of rows in the table?
- Check how many rows are in the table
- Method 1: Use the Sys.Partitions system table to check the Rows
SELECT DB_NAME(DB_ID()) DatabaseName,OBJECT_NAME(i.OBJECT_ID) TableName, i.[Name] IndexName, i.Type_Desc IndexType, p.[Rows] RowNumber FROM sys.partitions p INNER JOIN sys.indexes i ON p.object_id = i.object_id
- Method 2: Use SELECT COUNT(*)
- The second method will take longer than the first. You are scanning the whole table with the second method. But if the COUNT(*) method returns relatively quickly then likely this table Heap is not a problem.
- Perform a SELECT * from the table. If the select takes “forever” and it doesn’t have many rows then perhaps that Heap is having an issue.
Indexes – Clustered & Non-clustered
- There is the million dollar question. And the answer: You add a Clustered Index! This can be done temporarily and then removed. Or left in place so the table can be defragmented with a nightly/weekly job. Note that adding a Clustered index will entirely rebuild the table, i.e. it read all rows of the table and re-write it to a different part of the database. It may take some time if the table is large.
And… What is the INCLUDE for?
- An Index is a mini-table that references the main table. It’s used for quick searches. It is like a Table of Contents.
- A table can have multiple non-clustered indexes but it can only have 1 clustered index.
- Clustered Indexes store and sort the table rows based on their “Key” column value(s)
- Non-Clustered Indexes are stored separately from the table data row (Heap or Clustered). It contains pointers back to the original table data. Adding Non-Clustered indexes can & will increase the size of the table. They aren’t really “duplicating” the entire table data information, but they are kind of duplicating the specific data column/row information….
- The more Indexes that are created, the more Insert/Update/Delete work needs to be done. If there are 2 indexes then there could be 2 Inserts, 1 for each Index depending on what the index covers.
So… If something runs fast for Customer A but slow for Customer B? (assumes different databases for each customer)
- There are 2 parts of an Index, the Equality (or Inequality) and the Include
- The Equality has the columns that will be referenced in the WHERE Clause. Equality columns are also Ordered (Ascending or Descending). The Order is important.
- The Include has the columns that will be referenced by the SELECT Clause. Included columns are not always needed.
We need to create a Missing Index!!
- It could be that the Table Data and/or Indexes are different.
- If Customer A has 100 rows but Customer B has 1,000,000 rows then the query or report will run slower for Customer B. But Customer B might benefit with a new “more different” Index anyways.
- The Indexes can be compared by reviewing them from within SSMS. Expand to the Tables folder and expand the Indexes folder on both customers. Visually review.
- To view the Index structure in more detail
- Right-click the Index
- Choose Script Index as…
- Create to..
- New Query Editor Window
- Note SQL will not let you create an index with the same name that already exists in the same database, in case you accidently execute
- If you determine that Customer B does not have the same Index that Customer A has, you can copy the create statement to Customer B and execute
- Fair warning, this should likely be done after hours. Large tables will take a lot of time to create an index. It will Lock/Block query activity to said table because it’s in the process of creating an index.
SQL Execution Plans (Estimated & Actual)
- Alright everything is slow, and it’s not that the CPU is slow, or there isn’t enough RAM, or the Disk isn’t “bad”… Maybe we need a new Index, but how do I know? We can check the SQL Query to see if it needs an index. Or we can check the Missing Index DMV to see if, in general, the system thinks we need an index.
Cached SQL Execution Plans
- Every SQL query (statement, procedure, trigger, function, view, whatever) has a Query Plan.
- A Query Plan contains all the tables and indexes the query will use to go get the customer data that you want.
- In SSMS you can display the Estimated Execution Plan or the Actual Execution Plan by pushing 1 or two buttons on the Query Editor window
- The Estimated Execution Plan is shown before you execute the query
- The Actual Execution Plan is shown after you execute the query
- Both Execution plans can show you valuable information about your query, like missing indexes!
- Note that both those Execution Plans likely specific to your SSMS Query Window. To see what the Execution Plan is for the Application or Report that is running slow you will need to look in the Cached Plans
Missing Index Dynamic Management View
- SQL caches (saves) query plans in memory. These plans can be viewed with the following dynamic management view: sys.dm_exec_cached_plans
- Note that Databases in SQL 2000 Compatibility Mode (80) are more difficult to figure out, and will not be elaborated on here…
- Note this query will take a little bit of time if searching through the plan text for key words…
SELECT DB_NAME(DB_ID()) DBName, OBJECT_NAME(qp.objectid) AS [ObjectName], cp.objtype ObjectType, cp.usecounts, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
--OBJECT_NAME(qp.objectid) = '%' AND --If you want to look for a specific query
--CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%' AND --if you want to look for a missing index
dbid = DB_ID()
- The Query Plan is an XML field that can be clicked on from within SSMS and the reviewed. If it needs a missing index, the query plan will show that information
- The following query can be run to see the Missing Indexes. There are other “larger” queries that also make the “Create Index” statement for you and calculate the impact differently, but what fun is that…
SELECT DB_NAME(mid.database_id) DBName, mid.statement, migs.avg_user_impact, mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
Note: sometimes SQL can by tricksy and show you a missing index, but there is already an index with those columns and maybe even with the same name. Remember, this is a Microsoft product, and is probably a Feature to some MS Developer, not a Bug.
- Pages and Extents https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
- Heaps https://msdn.microsoft.com/en-us/library/hh213609.aspx
- Clustered/NonClustered Indexes https://msdn.microsoft.com/en-us/library/ms190457.aspx
- Sys.Indexes https://msdn.microsoft.com/en-us/library/ms173760.aspx
- Sys.Tables https://msdn.microsoft.com/en-us/library/ms187406.aspx
- Sys.Partitions https://msdn.microsoft.com/en-us/library/ms175012.aspx
- Create Index https://technet.microsoft.com/en-us/library/ms188783(v=sql.105).aspx
- Index with Included Columns https://technet.microsoft.com/en-us/library/ms190806(v=sql.105).aspx
- Display Graphical Execution Plans https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx
- Cached Query Plans https://msdn.microsoft.com/en-us/library/ms187404.aspx
- SQL & Plan Handles http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/09/2-0-sql-handle-and-plan-handle-explained.aspx
- Using Missing Index Information to Create Index Statements https://technet.microsoft.com/en-us/library/ms345405(v=sql.105).aspx
- Missing Index DMVs http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx