Im using blitz index to identify some indexing issues. Im concerned that I am reading the output incorrectly. In my output, im seeing reads, compared to writes in the usage column, and then im seeing a lot more reads in the op stats column. Which column tells me how many times the index has been used for returning data?

I'm trying to perform some housekeeping activities on a very large SQL 2016 database, it's total size was about 10TB. This database has been running for many years without any actual housekeeping done. Apps guys have several jobs which will move datas from tables in this database to another database for archiving purposes. Fun Fact, both database sits on the SAME box! Ok, so initially I ran blitzfirst(v8.21) and I notice there's findings of Forwarded Fetches/Sec high with 46,000 forward fetches recorded. Then I proceeded to execute blitzindex & the top priority from this shows Index Hoarder: Unused NC index with High Writes, which all referring to NC indexes belonging to a single table. As I am not part of the team which design and create this database from the beginning, I'm unable to confirm if this indexes are indeed not required. For this indexes will there be any issue if I just proceed to disable it? Also for heaps, based on blitzindex's result, there's quite a number of heap tables & heap with NC index table found. Will it be helpful if I suggest the DB owner to consider creating clustered index to it?

I've added this SP to my database and it returns useful results. The part I'm not sure about is the usage numbers. I'm new to Azure SQL but I'm told by the old hands that the server never gets restarted so I'm wondering why the number of uses is so low. The numbers look like they only cover the last 24 hours, at most. Can someone explain to me why that would be? I'd love to get the usage numbers over a much longer period to get a really complete idea of which indices give the most impact.

I cannot successfully add the sp_BlitzIndex store procedure for Microsoft SQL Server 2014 (SP3-CU3-GDR) and earlier versions. I think there is something related to the version. Because I was able to add it for SQL Server 2022 version. Can you help me ? PS: I don't know the exact version. I downloaded it as First Responder Kit a few days ago.

I am running v8.13 - February 15, 2023 of the Brent Ozar sp_BlitzIndex function against SQL Server 2017 Web Edition. Whenever I run on a specific database as below , I get the "A transport-level...." type error after about a minute. EXEC master.dbo.sp_BlitzIndex @DatabaseName='XXXX', @SchemaName='dbo', @TableName='yyyyy'; If I run the same with a different DatabaseName, but the same table, it runs OK. We have several customers all with the same database structure. The message tab is as below :

Inserting data into #IndexColumns for clustered indexes and heaps
Inserting data into #IndexColumns for nonclustered indexes
Inserting data into #IndexSanity
Checking partition count
Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats
Inserting data into #IndexPartitionSanity
Inserting data into #MissingIndexes
Inserting data into #ForeignKeys
Inserting data into #ForeignKeys
Gathering Computed Column Info.
Gathering Trace Flag Information
Gathering Temporal Table Info
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
Updating index_sanity_id on #IndexPartitionSanity
Inserting data into #IndexSanitySize
Determining index usefulness
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.
Update #IndexSanity for filtered indexes with columns not in the index definition.
@TableName specified, giving detail only on that table.
Msg 121, Level 20, State 0, Line 1
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Any ideas on what I can try and check/change to get it to complete ?

There is a database that is replicated from INST-1, where it is created, to multiple other instances. Instance INST-1 Database SALES Schema dbo Table ATABLE INST-1.SALES.dbo.ATABLE The SALES database is replicated to multiple other instances. The other instances are like "marts" from which other groups gather information. INST-2.SALES.dbo.ATABLE INST-3.SALES.dbo.ATABLE INST-4.SALES.dbo.ATABLE The replication tool copies the entire SALES database, including indexes, to the other instances. Each instance will have differing utilization that would suggest different indexes. Since the replication tool does not support a different set of indexes on each instance, producing an optimal set of indexes on INST-1 may degrade performance on the other instances. Is there a good way to use sp_BlitzIndex to analyze across all instances?

Sorry, I may be missing something super-obvious here, but I've googled this and asked some colleagues and am not finding a satisfactory answer. I've been using sp_BlitzIndex for a few months now along with some of my other regular index tuning tools and have found it to be a super-useful tool to add to my SQL toolbox. But I do not quite understand the difference between 'High value' and 'High Impact' as in this example below: 'Indexaphobia: High value missing index with High Impact' At first I thought they meant roughly the same thing -- it was identifying a missing index that would be both highly valuable and have a great impact on query performance. But that didn't quite make sense to me -- why have two separate terms to indicate what was pretty much the same thing? So a colleague suggested that the term 'impact' was referring to the impact on db write operations. So 'high impact' in this case meant the index might cost more than other indexes when it came to updates and deletes. But I've read some of Brent's comments, and, if I'm understanding him correctly, I don't think my colleague's interpretation is quite right either. Can someone please enlighten me on this? I'd really like to understand the difference before testing out some of the suggestions sp_BlitzIndex is making to me. Is 'high impact' good or bad? If it's good, then what's the difference between 'high value' and 'high impact'? Thanks very much in advance!! Robert

The 'Op stats' are the numbers that give us the best information about index usage but can be reset at any time. If I look to the number of writes on the Clustered index (20.716), and I have approximately the same database usage every day, Is it then safe to conclude that numbers in the 'Op stats' haven't been reset for about 6 days? My argumentation for this is: Writes on the clustered index happen on insert, updates and deletes. (20.716) My server has been up for 16.67 days. I have 7590 updates in the 'Op stats' (insert, updates, deletes) 16.67/20716*7590 = 6.1 So, my conclusion is, the 'Op stats' haven't been reset for the last 6 days. Would you tend to agree with me on this or am I missing something?

I had planned to do some in-depth index performance analysis on a key production database next week, but realized that it will be restarted during maintenance this weekend, which resets all these DMVs. How can I safely preserve the contents of all the [relevant index-related DMVs](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/index-related-dynamic-management-views-and-functions-transact-sql?view=sql-server-ver15) ? sys.dm_db_column_store_row_group_physical_stats sys.dm_db_index_operational_stats sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.dm_db_missing_index_details sys.dm_db_missing_index_columns sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats Is it sufficient to simply SELECT INTO a holding table for each of these? Are there any of these I really don't need? What about sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats, which take a bunch of parameters for database/object/index_id? Are these parameters just filters? If I run it with (NULL, NULL, NULL, NULL) will that truly return all rows? Or are they like sp_BlitzIndex where narrowing to a specific table/index gives you *more* information about that specific object? Related question about sp_BlitzIndex: can I use sp_BlitzIndex to analyze my archived DMV data, stored in other tables? Or does it always run against the actual system DMVs? (I found a [similar question](https://dba.stackexchange.com/questions/294963/preserve-prod-dmv-metadata-for-offline-analysis) , but that one seemed focused on ongoing analysis, not my one-time situation.)

SQL Server 2019 1. When running sp_BlitzIndex does the result generated is using Query Store data? Trying to understand if Query Store affects sp_BlitzIndex report. 2. sp_BlitzIndex has multiple suggestions in its report, I'm trying to understand what steps should follow sp_BlitzIndex to verify the need for such an index. Should it follow both or one of these two? sp_BlitzCache/sp_BlitzQueryStore An example would be the usage of one of the rows - 603 uses; Impact: 100.0%; Avg query cost: 74.4459 On paper that looks great, before I copy&paste the TSql of the index, How can I understand which queries exactly would have used the suggested index? (so-called 603 uses). Kind a lost myself during the index tuning as it feels like every command just redirects to another, not sure where I should stop and act haha. Ty

I recently watched the sp_Blitzindex video and testing the tool found most of our tables had a combination of missing indexes and also indexes which have hundreds of writes and no reads. I think that means we should remove those with no reads. Anyway we removed the first one and then ran the sp_Blitzindex againand the missing index wasn't shown. Does this mean removing the not needed index resets the cache? Should we do it the otehr way around? Add the missing first, then delete the redundant indexes? Attached screenshots to illustrate what happened.

I'm using sp_blitzIndex to check the health of my database indexation, but I don't understand every column used in the output. I'm talking specifically about "Usage Stats" and "Op Stats". I find many indexes with zero Reads but a thousand writes (example: Reads: 0 Writes: 42,597). Does it mean that the index isn't used at all ? At least not for SELECT statements ? Is it safe to remove it or disable it at first ? PS: My SQL instance hasn't been rebooted for very long time.

Is there any way to skip some results on sp_BlitzIndex? Like has done on sp_Blitz, using the @SkipChecksTable. For example, i have some index thats are not in use, but, those indexes are UNIQUE and i cannot drop then. I just want to ignore these indexes in the result

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.

I'm trying to deploy the sp_blitzIndex procedure from Brent Ozar. I'm running it in a query window, in SSMS and get the following error: Msg 208, Level 16, State 1, Line 1 Invalid object name 'tempdb..SQLServerCheckup_2A98B846_4179_496B_AAF8_60B405E3ED68_waits'. Starting run. sp_BlitzIndex(TM) v7.99999 - December 11, 2020 This is being done on the master DB. I'm running SQL Server 2019 developer edition on my local machine. Any suggestions on a fix?

I have a small but actively queried table in SQL Server that has 94 rows that are frequently read and\or updated. The clustered index safely fits into 1 8KB page and has significant empty space on that page. This leads me to believe that any future updates won't push it onto a second page. In addition to the clustered index, over the years several nonclustered indexes have been added. My question is, on a conceptual level, **can the nonclustered indexes improve SELECT performance?** If SQL Server can't read less than 1 data page at a time, then any nonclustered index read could be just as easily satisfied by reading the single page for the clustered index or am I misunderstanding the concept? I don't wan to include the table definition, but here's some stats from sp_BlitzIndex on that table showing it's use:

ClusteredIndex		Reads: 1 (1 scan) Writes:180,544,146
Nonclustered Index	Reads: 0 Writes:103
Nonclustered Index	Reads: 63,425,182 (57,447,576 seek 5,977,606 scan) Writes:180,544,146
Nonclustered Index	Reads: 150,953,542 (150,953,542 seek) Writes:180,233,055
Nonclustered Index	Reads: 0 Writes:311,091

The clustered index doesn't seem to get read, but it I would think it could without the other indexes. Further interesting detail, sp_BlitzLock shows 39 deadlocks in this database and all 39 involved this tiny table, which I find interesting.

I am trying to understand the true meaning of Missing index for this particular table when I already have index on it and how to address it. I have a heavily used table and is of approximately 2.5GB. Since its heavily used, a bit hesitant to create index which are not very much required(debatable). This table was heap earlier, recently it is changed to table after changing primary key to clustered from non-clustered. When I run sp_blitzindex with database name or with this table, it gives result as below: Mostly it suggests to create index on APT_ID column and include suggests LOGID, RECEIVE_TIME and few other columns. If we look at table definition primary key is defined at LOGID and RECEIVE_TIME. And also we have a NC index on APT_ID column. Table DDL is as below: CREATE TABLE [dbo].[TXN_LOG]( [LOGID] [int] IDENTITY(1,1) NOT NULL, [RECEIVE_TIME] [varchar](15) NOT NULL, [APT_ID] [int] NOT NULL, [VAR32_01] [varchar](32) NULL, [VAR32_02] [varchar](32) NULL, . . . [ERROR_CODE] [varchar](20) NULL, [MESSAGE_ID] [varchar](40) NULL, [END_POINT_ID] [varchar](50) NULL, [NODE_ID] [varchar](40) NULL, [TIMEOUT_NETWORK_ID] [int] NULL, [TXN_SUMMARY] [numeric](1, 0) NULL, [D_FLAG] [numeric](1, 0) NULL, CONSTRAINT [PKTXN_LOG] PRIMARY KEY CLUSTERED ( [LOGID] ASC, [RECEIVE_TIME] ASC )) GO NC Index on this table is : CREATE NONCLUSTERED INDEX [IDX_TXN_LOG_1] ON [dbo].[TXN_LOG] ( [APT_ID] ASC ) INCLUDE([RECEIVE_TIME]) GO Current usage of index doesn't seem to support NC index as write is higher than read count. Sincere apologies for attaching images in the question. Appreciate if I can get some expert advice on this. Thanks in advance. > Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4 > (X64) Dec 12 2019 21:46:15 Copyright (c) Microsoft Corporation > Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 > (Build 9600: ) (Hypervisor)

I'm seeing the > Aggressive Indexes: Total lock wait time > 5 minutes (row + page) with > short average waits warning via **BlitzIndex** on one of our tables, and I cannot quite understand what could be happening. The index in question is related to a primary key on our table:

ALTER TABLE [Authentication].[Tokens] 
ADD CONSTRAINT [PK_Authentication.Tokens] 
PRIMARY KEY CLUSTERED ( [TokenID] )

And for completeness, here's the gist of the table structure:

CREATE TABLE [Authentication].[Tokens](
	[TokenID] [int] IDENTITY(1,1) NOT NULL,
... other columns here)

BlitzIndex reports the following stats: Reads: 10,066,849 (2,259,000 seek 934,476 scan 6,873,373 lookup) Writes:1,399,277, 314 rows; 1.1MB I'm somewhat confused. We don't query/filter off of this column (like SELECT ... WHERE TokenID = 1). My only guess is maybe the table is being read quite often, and lots of queries are slamming against each other using the default SQL locking strategy? I'm open to any advice or feedback here, for a table that seems simple and benign.

No real concern but I'm looking for some information of possible impact of running it on a busy server. Should I Be concerned? Would there be any loss of fidelity in the results by running it on a restored instance? Thank you.

I#ve some trouble with sp_blitzindex and @outputdatabase. My Setup: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor) sp_BlitzIndex(TM) v7.6 - July 02, 2019 I would like to store the results in table, running following parameters exec sp_blitzindex @databasename = @dbName, @OutputDatabaseName = 'DBATool', @OutputSchemaName = 'dbo', @OutputTableName = 'Blitz_index' Any ideas what I'm doing wrong? Thanks for your feedback!