How to interpret results from the sp_spaceused command with regards to indexes

Question

If I need to review indexes of more than 2000 tables, where do I start given the information from the sp_spaceused command?

I’m investigating Indexes on tables, but am not quite sure what to make of the results for the IndexSize when I exec the sp_spaceused stored procedure in SQL.

Firstly, can I use the ratio between IndexSize and DataSize to make a call on wheter or not the indexes are optimal? For example, if my DataSize for a table is 31 261 768KB and the IndexSize is 41 682 120KB, I divide indexSize/DataSize *100 and get a ratio of 133. Is what I’m doing correct? If it is correct, is an IndexSize-ratio of more than 100% bad?

What would a good ratio then be?

Thanks,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ I need to add a bit more information.

The application is Microsoft Dynamics Ax 4.0. Although developers can add new indexes, system indexes can't be deleted.

We are currently in a situation where there are allot of custom-indexes which does not add any value (indexes on blank fields, indexes on amount fields, etc.). I am investigating those as part of a code cleanup process.

But because there are thousands of tables to work through I need a starting point. My first concern is identifying the custom-indexes that does not add value, and for this I thought about using the sp_spaceused procedure.

score 1 · Answer 1 · answered Apr 01 '11 at 12:32

Checking the ratio of index size to data size is a terrible metric to use for this.

The only thing that should drive index creation or modification is performance. This will depend in large part on the activity in the table (a lot of SELECTs, a lot of INSERTS/UPDATEs, some combination?) and the makeup of the table.

Unfortunately there is no easy answer to this. Indexing is one of the most complicated aspects of DB design.

I recommend you do some reading on this.

Check out Kimberly Tripp's blog here.
She worked for MS for quite a while and her husband (Paul Randall) wrote the DBCC procedures in SQL Server 2005.

Gail Shaw also has some good articles in her blog.

Thanks, Kimberly Tripp's blog is very useful. – Celeste Apr 04 '11 at 12:43 — Celeste, Apr 04 '11 at 12:43

score 1 · Accepted Answer · answered Apr 04 '11 at 14:12

Performance Analyzer for Microsoft Dynamics can be used to analyze expensive and long running queries, missing clustered indexes, incorrect and missing indexes, hidden clustered index scans, etc. in AX DB.

A way to eliminate the amount of unnecessary indexes is to search for indexes that are a left key subset of another index on the same table. Unless the subset key is unique, its usefulness is subsumed of the superset key. To get a list of such indexes you can run the following query:

SELECT *
FROM INDEX_STATS_CURR_VW O
WHERE INDEX_DESCRIPTION NOT LIKE '%UNIQUE%'
AND EXISTS
(
    SELECT * FROM INDEX_STATS_VW I
    WHERE I.RUN_NAME = O.RUN_NAME
    AND I.TABLE_NAME = O.TABLE_NAME
    AND I.INDEX_KEYS <> O.INDEX_KEYS
    AND I.INDEX_KEYS LIKE O.INDEX_KEYS + ',%'
    AND O.USER_SEEKS = 0
)
ORDER BY TABLE_NAME, INDEX_KEYS

To get an overview of all indexes that haven't been used during the complete monitoring period you could run the following query:

SELECT TABLE_NAME,
    INDEX_NAME,
    INDEX_DESCRIPTION,
    INDEX_KEYS,
    INCLUDED_COLUMNS,
    SUM(USER_SEEKS) AS USER_SEEKS,
    SUM(USER_SCANS) AS USER_SCANS,
    SUM(USER_LOOKUPS) AS USER_LOOKUPS,
    SUM(USER_UPDATES) AS USER_UPDATES
FROM INDEX_STATS_VW
WHERE INDEX_DESCRIPTION NOT LIKE '%UNIQUE%'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_DESCRIPTION, INDEX_KEYS, INCLUDED_COLUMNS
HAVING SUM(USER_SEEKS) = 0
AND SUM(USER_SCANS) = 0
AND SUM(USER_LOOKUPS) = 0
ORDER BY 9 DESC

You could also identify queries that use an index seek to filter data:

SELECT TOP 100 * FROM HIDDEN_SCANS_CURR_VW
ORDER BY TOTAL_ELAPSED_TIME DESC

The following would display 10 most expensive queries ordered by average logical reads from a SQL Server DMV perspective:

SELECT TOP 10
    SQL_TEXT,
    QUERY_PLAN,
    TOTAL_ELAPSED_TIME,
    AVG_ELAPSED_TIME,
    MAX_ELAPSED_TIME,
    AVG_LOGICAL_READS,
    EXECUTION_COUNT
FROM QUERY_STATS_CURR_VW
ORDER BY AVG_LOGICAL_READS DESC

You'd also need to have a look at other parameters like execution count (how many times queries were executed).

If you wanted to have an overview of the AX queries running longer than 1000 ms you could run the following query:

SELECT CONVERT(nvarchar,CREATED_DATETIME,101) AS CREATED_DATE,
    DATEPART (hh, CREATED_DATETIME) AS HOUR_OF_DAY,
    COUNT (CREATED_DATETIME) AS EXECUTION_COUNT,
    SUM (SQL_DURATION) AS TOTAL_DURATION,
    AVG (SQL_DURATION) AS AVERAGE_DURATION
FROM AX_SQLTRACE_VW
WHERE SQL_DURATION > 1000 and CREATED_DATETIME > '04/01/2011'
GROUP BY CONVERT(nvarchar, CREATED_DATETIME, 101), DATEPART (hh, CREATED_DATETIME)
ORDER BY CREATED_DATE, HOUR_OF_DAY

Hope that helps.

Hi. I presume INDEX_STATS_CURR_VW is a view? It is not recognised, I get an "invalid object....." error. I'm using SQL 2005. — Celeste, Apr 04 '11 at 15:00
Have you installed the Performance Analyzer (see the link above)? — 10p, Apr 04 '11 at 20:00
Not yet, don't have rights (our organisation outsourced that part of the business). Thanks allot for you help! — Celeste, Apr 05 '11 at 07:10

score 0 · Answer 3 · answered Apr 01 '11 at 12:44

I'm not sure what you are trying to achieve. You would optimise a query, not an index. But too many indexes can hinder write performance. I recommend that you look at the DMV (Dynamic Managment Views) that are provided in 2005.

For example select * from sys.dm_index_usage_stats will help identify indexes which are not used.

There are a list of DMVs which are related to indexes on BOL.

http://msdn.microsoft.com/en-us/library/ms187974%28v=SQL.90%29.aspx

How to interpret results from the sp_spaceused command with regards to indexes

3 Answers3