Feeds:
Posts
Comments

Archive for December, 2010

On the previous post I covered some suggestions regarding performance related to the disk subsystem. The second part of performance analysis I’ll cover is related to the indexes and its utilization, as that’s also one of the big performance improvements that can easily be done.

Analyzing the Existing Indexes

One of the things you should always look inside SQL is how your indexes are being used (or unused). The best way to achieve this is using the DMVs the SQL 2005 and above provides. Using those DMCs, you can sneak into which indexes are missing, the usage of the existing ones or which ones are unused.
In order to have better results, it’s recommended that you let the server run for a reasonable amount of time first (restarting the SQL engine clears the content of the DMVs), as these are statistics prepared by SQL server itself. It’s hard to put a value to the “Reasonable Time” as that depends on the SQL usage, but as a rule of thumb you can wait for 3~4 weeks before considering using the information DMVs provides.

The Missing Indexes

Perhaps one of the scripts I use the most, the following code will give an idea of the missing indexes, together with the estimated improvement the index can have if created. Of course, you should take care on how you use this, as if you create an index you may introduce a performance issue elsewhere, so it’s really really suggested that you create the indexes on a test DB first and that a smoke test gets executed against that just to confirm, at least, that not only you are improving things but also not adding future nightmares.
So, the script I use if the following:

SELECT
db_name(database_id) as ‘DBName’
,gs.unique_compiles
,gs.user_seeks
,gs.last_user_seek
,gs.avg_total_user_cost
,gs.avg_user_impact
,d.[object_id]
,d.equality_columns
,d.inequality_columns
,d.included_columns
,d.statement as ‘Table’
FROM SYS.DM_DB_MISSING_INDEX_GROUPS
G JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS
ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON
G.INDEX_HANDLE = D.INDEX_HANDLE
order by DBName asc, (gs.unique_compiles + gs.user_seeks) desc

The result of the above query can be, for instance, the following:

If you take a look into that results, you can see the engine thinks only three indexes are missing (based on its own statistics) and it shows the columns that it should contain together with and compiles, user seeks and impact (improvement). Next, you can create those indexes and analyze your database performance.

Index Usage

Another interesting script I use is the following. It’ll show how your indexes are used on the database, ordering the results having the less used on top.

select
object_name(s.object_id) as ‘Table Name’,
s.object_id,
i.name as ‘Index Name’,
i.index_id as ‘Index ID’,
user_seeks,
user_scans,
user_lookups,
user_updates
from sys.dm_db_index_usage_stats s, sys.indexes i
where objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

If you run this on your database, you can see how many times your indexes were used, and that will give you an idea of the less used ones. If you analyze that and compare it with the cost of having that index in place, you can end up removing indexes or moving the most used to its own disk system, for instance.

Unused Indexes

Finally, I run the following script to get the list of indexes that we not used at all since the server started:

SELECT
OBJECT_NAME(B.OBJECT_ID) AS TableName,
B.NAME as IndexName
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE ‘S’
ORDER BY 1, 2

You should take extra and special care on taking decisions based on this results, as it can harm your DB. Here it’s important that you have a lot of statistical information and that you have also some deep knowledge of the application and processes using that DB.
For instance, if you have a process that creates a quarterly report, which of course runs quarterly and use one index of that DB, and you analyze only one month of statistical information, that index will be shown as never used, and if you drop it, when your process runs to generate the report, it’ll not find the index so it may take longer, cost a lot more to the engine and / or even fail.

Conclusion

Indexes are very complex and require a lot of analysis of trained people. This DMVs will give you a lot of information, but people need to make a decision based on that information, so if it’s not well interpreted, then the result won’t be the expected one. Remember to always run the query that will benefit from the index creation before and after the creation of it, showing the Execution Time on both runs so you can compare if the index created really improves your performance. Also use the Database Engine Tuning Advisor (DTA) as that will also give you extra information on indexes, like if it needs to be clustered or non-clustered, the order, etc. This DMVs are not a replacement for the DTA but a complement.

Advertisements

Read Full Post »