Question :
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.
Answer :
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