July 22, 2012 at 12:37 am
Hi All
I'm observing my Index usage using the following DMCV's
sys.dm_db_index_usage_stats;
sys.dm_db_index_operational_stats;
sys.dm_db_index_physical_stats
If I see a high number of Index Scans from the sys.dm_db_index_usage_stats, is this an indication of High Fragmentation or just the way the Queries are written ?
Thanks
July 22, 2012 at 1:02 am
Index seeks are better than Index scans but then it is also subjective to the actual data generally and in 99.9 % cases the optimizer comes up with the best execution plan.
Regarding your question I would like to believe that a lot will depend on how the query is written( good or bad)
in any case it won't hurt to check the index fragmentation by using the following query which would provide fragmentation details for all the objects in a particular database.
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('Databasename'), NULL, NULL, NULL , NULL);
Please keep in mind that this query may take a while to run if the database in question in large in size.
Therefore if you want the fragmentation related data only for a particulr table you can execute the following query:
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('Databasename'), OBJECT_ID('TableName'), NULL, NULL, NULL);
The queries mentioned above are listed on the following URL, this URL also provides additionally useful information.
July 22, 2012 at 3:14 am
The index fragmentation has a negative impact on index scans.but choosing index scans by optimizer is not due to index fragmentation.in some conditions optimizer uses index scans and this the most efficient way to fulfill the query for example suppose we have a salesHistory table with millions of records and we have a clustered index on SaleDate column. If the query is searching several columns of the records between 2010 and 2011 and there too many records in this range, scanning the clustered index is the most efficient way but if you're looking for records with orderQuantiy=10000 (assuming there is only a few records with this value for the column) and there's no other nonclustered index on this column then the optimizer again scans the clustered index which is not an efficient way. So using index scans by the optimizer depends on the query nature and the indexes available and the index statistics.
Pooyan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply