USE <databasename>
GO
SELECT OBJECT_NAME(object_id),
[object_id],
index_id,
index_level,
page_count,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('<databasename>'),NULL,NULL,NULL,DEFAULT)
WHERE page_count > 0
AND page_count <= 16
GO
If the system is still in implementation phase, most likely the data is quite crappy, insufficient, or with just a small set of test records, they're not helping in decision making. With the list of tables returned from the above query, do further analysis and ask yourself:
- Is the table potentially growing larger over time?
- Are the data quite static? (avoid doing EntireTable cache on small table, but has high change rate)
- Is the table (unique) index correctly set? (Eg. to the ::find() method or other query's unique criteria)
There's one thing that I'm not quite sure yet, that is whether the 128k limit is per index or per table.
You can test it out by capturing the trace and look at it with Trace Parser.
*NOTE: The query took around 30 minutes to run on my system (~500GB database), you might want to choose a better timing to run it.
To run the query against one single table, replace the second parameter to the object Id of the table.
From: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),NULL,NULL,NULL,DEFAULT)
To: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),OBJECT_ID('<tablename>'),NULL,NULL,DEFAULT)
*NOTE: The query took around 30 minutes to run on my system (~500GB database), you might want to choose a better timing to run it.
To run the query against one single table, replace the second parameter to the object Id of the table.
From: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),NULL,NULL,NULL,DEFAULT)
To: sys.dm_db_index_physical_stats(DB_ID('<databasename>'),OBJECT_ID('<tablename>'),NULL,NULL,DEFAULT)