Not sure why, but when I run
sp_BlitzIndex (v4.5), it always results in only one row (even if set
@GetAllDatabases = 1). This is the “header” row that shows Priority = -1. What am I doing wrong? I don’t see any errors in the results or message tabs. I’m using SQL 12.0.5000.0
Below is a copy of the messages:
Starting run. sp_BlitzIndex(TM) v4.5 - November 15, 2016 Create temp tables. CCLocalDemo Inserting data into #IndexColumns for clustered indexes and heaps Inserting data into #IndexColumns for nonclustered indexes Inserting data into #IndexSanity Updating #IndexSanity.key_column_names Updating #IndexSanity.partition_key_column_name Updating #IndexSanity.key_column_names_with_sort_order Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql) Updating #IndexSanity.include_column_names Updating #IndexSanity.include_column_names_no_types (for create tsql) Updating #IndexSanity.count_key_columns and count_include_columns Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats Inserting data into #IndexPartitionSanity Updating index_sanity_id on #IndexPartitionSanity Inserting data into #IndexSanitySize Adding UQ index on #IndexSanity (database_id, object_id, index_id) Inserting data into #MissingIndexes Inserting data into #ForeignKeys Updating #IndexSanity.referenced_by_foreign_key Update index_secret on #IndexSanity for NC indexes. Update index_secret on #IndexSanity for heaps and non-unique clustered. Populate #IndexCreateTsql. Populate #PartitionCompressionInfo. Update #PartitionCompressionInfo. Gathering Computed Column Info. @Mode=0 or 4, we are diagnosing. check_id 1: Duplicate keys check_id 2: Keys w/ identical leading columns. check_id 11: Total lock wait time > 5 minutes (row + page) with long average waits check_id 12: Total lock wait time > 5 minutes (row + page) with short average waits check_id 20: >=7 NC indexes on any given table. Yes, 7 is an arbitrary number. check_id 21: >=5 percent of indexes are unused. Yes, 5 is an arbitrary number. check_id 22: NC indexes with 0 reads. (Borderline) check_id 23: Indexes with 7 or more columns. (Borderline) check_id 24: Wide clustered indexes (> 3 columns or > 16 bytes). check_id 25: Addicted to nullable columns. check_id 26: Wide tables (35+ cols or > 2000 non-LOB bytes). check_id 27: Addicted to strings. check_id 28: Non-unique clustered index. check_id 30: No indexes with includes check_id 31: < 3 percent of indexes have includes check_id 32: filtered indexes and indexed views check_id 33: Potential filtered indexes based on column names. check_id 40: Fillfactor in nonclustered 80 percent or less check_id 40: Fillfactor in clustered 80 percent or less check_id 41: Hypothetical indexes check_id 42: Disabled indexes check_id 43: Heaps with forwarded records or deletes check_id 44: Large Heaps with reads or writes. check_id 45: Medium Heaps with reads or writes. check_id 46: Small Heaps with reads or writes. check_id 47: Heap with a Nonclustered Primary Key check_id 50: Indexaphobia. check_id 60: XML indexes check_id 61: Columnstore indexes check_id 62: Spatial indexes check_id 63: Compressed indexes check_id 64: Partitioned check_id 65: Non-Aligned Partitioned check_id 66: Recently created tables/indexes (1 week) check_id 67: Recently modified tables/indexes (2 days) check_id 68: Identity columns within 30 percent of the end of range check_id 69: Column collation does not match database collation check_id 70: Replicated columns check_id 71: Cascading updates or cascading deletes. check_id 80: Most scanned indexes (index_usage_stats) check_id 81: Top recent accesses (op stats) check_id 90: Outdated statistics check_id 91: Statistics with a low sample rate check_id 92: Statistics with NO RECOMPUTE check_id 93: Statistics with filters check_id 99: Computed Columns That Reference Functions check_id 100: Computed Columns that are not Persisted. Insert a row to help people find help Returning results.
V4.1 is the last working version. It is an issue in v4.2 – v4.8(current). It works for some databases, but not all, when run with no parameters. For example, it works in master, but fails in a Dynamics GP company database. @Mode = 4 still works fine though.
I tested versions 4.1 – 4.8 on SQL Server 12.0.4100.1 and 12.0.5511.0