I ran the DMV.sql by 徐海蔚, and got some errors:
...
(4 row(s) affected)
Msg 207, Level 16, State 1, Line 3
Invalid column name 'operational_'.
...
(4 row(s) affected)
Msg 207, Level 16, State 1, Line 3
Invalid column name 'operational_'.
(174 row(s) affected)
Using print @cmd, I finally tracked it down to an issue related to the insufficient size of the @cmd variable. The @cmd was declared with NVARCHAR(1000) in the script.
For some databases with a long name like ReportServer$SQL2005Instance1 and ReportServer$SQL2005Instance1TempDB in this example, the entire @cmd is larger than 500 characters. That's why the ORDER BY part was chopped off, which leads the above error.
INSERT INTO
Table_Index_Access_Type_2
SELECT DB_ID('ReportServer$SQL2005Instance1') ,
'ReportServer$SQL2005Instance1'
,
'object'
= o.object_id ,
o.index_id
,
'usage_reads'
= user_seeks +
user_scans + user_lookups ,
'operational_reads'
= range_scan_count
+
singleton_lookup_count ,
range_scan_count ,
singleton_lookup_count ,
'usage
writes' = user_updates ,
'operational_leaf_writes'
= leaf_insert_count
+
leaf_update_count + leaf_delete_count ,
leaf_insert_count ,
leaf_update_count ,
leaf_delete_count ,
'operational_leaf_page_splits'
= leaf_allocation_count ,
'operational_nonleaf_writes'
= nonleaf_insert_count
+
nonleaf_update_count + nonleaf_delete_count ,
'operational_nonleaf_page_splits'
= nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID('ReportServer$SQL2005Instance1'),
NULL,
NULL, NULL) o ,
sys.dm_db_index_usage_stats
u
WHERE u.database_id
= DB_ID('ReportServer$SQL2005Instance1')
AND
u.object_id = o.object_id
AND
u.index_id = o.index_id
ORDER BY operational_reads DESC
,
operational_
(123 row(s) affected)
(34 row(s) affected)
(4 row(s) affected)
INSERT INTO
Table_Index_Access_Type_2
SELECT DB_ID('ReportServer$SQL2005Instance1TempDB') ,
'ReportServer$SQL2005Instance1TempDB'
,
'object'
= o.object_id ,
o.index_id
,
'usage_reads'
= user_seeks +
user_scans + user_lookups ,
'operational_reads'
= range_scan_count
+
singleton_lookup_count ,
range_scan_count ,
singleton_lookup_count ,
'usage
writes' = user_updates ,
'operational_leaf_writes'
= leaf_insert_count
+
leaf_update_count + leaf_delete_count ,
leaf_insert_count ,
leaf_update_count ,
leaf_delete_count ,
'operational_leaf_page_splits'
= leaf_allocation_count ,
'operational_nonleaf_writes'
= nonleaf_insert_count
+
nonleaf_update_count + nonleaf_delete_count ,
'operational_nonleaf_page_splits'
= nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID('ReportServer$SQL2005Instance1TempDB'),
NULL, NULL,
NULL) o ,
sys.dm_db_index_usage_stats
u
WHERE u.database_id
= DB_ID('ReportServer$SQL2005Instance1TempDB')
AND
u.object_id = o.object_id
AND u.index_id = o.index_id
ORDER BY operational_
Solution:
Simply increase the @cmd to a larger size.
