As of May 31, 2023, we have updated our Code of Conduct.

Questions tagged [sp-blitzindex]

sp_BlitzIndex is a stored procedure that checks the performance and usefulness of the indexes in a single Microsoft SQL Server database.

Filter by
Sorted by
Tagged with
0 votes
1 answer
31 views

Index optimization across multiple instances

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 ...
lit's user avatar
  • 221
0 votes
1 answer
108 views

sp_blitzIndex Usage stats vs. Op Stats

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 ...
Bart's user avatar
  • 1
1 vote
0 answers
52 views

One-time archiving of SQL's Index DMV contents for later deep analysis

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....
BradC's user avatar
  • 9,762
0 votes
2 answers
325 views

Does sp_BlitzIndex uses Query Store data?

SQL Server 2019 When running sp_BlitzIndex does the result generated is using Query Store data? Trying to understand if Query Store affects sp_BlitzIndex report. sp_BlitzIndex has multiple ...
Ben's user avatar
  • 111
6 votes
1 answer
356 views

sp_BlitzIndex missing index gone after removing unused indexes

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 ...
Motorcycle Racer's user avatar
3 votes
1 answer
380 views

Index usage Brent Ozar script

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&...
dba_maroc's user avatar
0 votes
1 answer
54 views

Skip events on sp_BlitzIndex

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 ...
Mateus de Aviz's user avatar
0 votes
1 answer
98 views

Trying to deploy sp_blitzIndex - but fails

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.....
mrSpear's user avatar
  • 281
5 votes
1 answer
142 views

Is there any reason to have nonclustered indexes on a table with only 1 page in the clustered index?

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 ...
BCM's user avatar
  • 723
0 votes
2 answers
485 views

Need advise on Indexaphobia: High value missing index with High Impact. When index is already there

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. ...
Learning_DBAdmin's user avatar
2 votes
1 answer
107 views

Primary Key Index on a non-filtered column has a very high total lock wait time

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 ...
Nicholas Head's user avatar
0 votes
1 answer
119 views

When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?

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 ...
AgentKnipe's user avatar
-1 votes
1 answer
149 views

Store result of sp_blitzindex in Database

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 ...
Thorsten Bertram's user avatar
4 votes
1 answer
182 views

sp_BlitzIndex "Missing Index" section has columns but no values/results

when I run sp_BlitzIndex @Version = '7.5', @VersionDate = '20190427', on my Microsoft SQL Server 2017 v14.0.3048.4, I have some tables where the missing indexes reports one column Finding with the ...
Karl Kvool's user avatar
3 votes
1 answer
2k views

Aggressive Under-Indexing and no data for missing index

I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every ...
Learning_DBAdmin's user avatar
0 votes
2 answers
174 views

sp_BlitzIndex performance concerns in production

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 ...
Chris Tucker's user avatar
1 vote
3 answers
465 views

Suggestion on Missing Index Creation

I have been using sp_blitzindex and it is very very helpful(Thanks to Brent Ozar and Team). I executed this procedure against my database and below is the finding for one table falling in Indexaphobia ...
Learning_DBAdmin's user avatar
2 votes
1 answer
462 views

Several questions about sp_BlitzIndex

When sp_BlitzIndex does index suggestions, the question in my head is as below: Yes the suggested index would bring a lot of speed benefit when doing select statements. But there are also another ...
Furkan Gözükara's user avatar
3 votes
1 answer
493 views

SQL Server 2016, temporal tables and compressed indexes

We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only). It has come up with 33 issues, all of which are "Abnormal Psychology: ...
Lewis Veale's user avatar
1 vote
0 answers
28 views

sp_blitzindex randomly fails when run through invoke-sqlcmd

I'm using sp_blitzindex v6.7 on Sql Server 2016(13.0.4451.0), I am intermittently having issues with running it through powershell using invoke-sqlcmd. It happens consistently with the same databases ...
Wil's user avatar
  • 929
2 votes
1 answer
667 views

Insert results of spBlitzIndex stored procedure into table

I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results. Here's what I've tried: INSERT INTO ...
DForck42's user avatar
  • 3,018
2 votes
1 answer
561 views

sp_BlitzIndex -- 'High value" vs 'High Impact' [closed]

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 ...
Robert H's user avatar
2 votes
1 answer
922 views

How to investigate "aggressive indexes"

Having run sp_BlitzIndex on my database I've found a number of "Aggressive Indexes" (https://www.brentozar.com/blitzindex/sp_blitzindex-aggressive-indexes/). Not only but I am finding that the number ...
Dale K's user avatar
  • 357
0 votes
1 answer
127 views

Unexpected (not helpful) result on first run of sp_BlitzIndex [closed]

I just ran sp_BlitzIndex for the first time and got 1 row: Priority=-1, Finding=sp_BlitzIndex(TM) v5.7 - September 01, 2017: All Databases, Database Name=NULL, Details: schema.table.index(indexed)=...
xWayne's user avatar
  • 9
1 vote
1 answer
217 views

sp_blitzindex Index Evaluation Results

Need some help with the results from the sp_blitzindex. Below is one of the suggestion but not able to understand the comment. http://BrentOzar.com/go/AbnormalPsychology dbo.ACCT_RECV_ADDRESS ...
Manu's user avatar
  • 11
0 votes
2 answers
4k views

sp_blitzindex output

I have latest version of sp_blitzindex. Would like to store results in table. Runs no output, anyone doing this already? Running with parameters below: EXEC dbo.sp_BlitzIndex @databasename = '...
tcstl's user avatar
  • 1
2 votes
1 answer
162 views

sp_BlitzIndex gives self-contradictory recommendations

I've used the Brent's sp_BlitzIndex and received the interesting results. For the first run it advises me to create an index. I've done it and executed it again. This time it has told that the newly ...
ago7's user avatar
  • 29
4 votes
1 answer
5k views

Indexes with High Lock waits - how to fix

I've been running dbo.sp_BlitzIndex and have 4 somewhat similar indexes on the main table in my database. Each one has a high number of waits and escalation attempts. I'm not sure where these are ...
MikeC's user avatar
  • 75
3 votes
1 answer
519 views

sp_BlitzIndex compilation

I currently use SQL Server 2012 and have BlitzIndex 4.4 not infrequently the execution plan will say I have a missing index on a table. BlitzIndex will often not say any indexes are missing on that ...
siberiaal's user avatar
0 votes
1 answer
395 views

sp_BlitzIndex only results in one row (with priority -1) [closed]

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 ...
user3750023's user avatar
4 votes
1 answer
199 views

sp_BlitzIndex v 4.5 (2016-11-15) failing with collation error [closed]

Posting this in case anyone else runs into this issue... (fix below) After updating to SQL 2016 SP1, I also updated to the latest version of the sp_Blitz procs. Unfortunately, sp_BlitzIndex now blows ...
Clayton's user avatar
  • 41
4 votes
1 answer
431 views

"Syntax error or access violation" error with nested square brackets in column alias

When trying to create sp_BlitzIndex I got the error "Syntax error or access violation" I narrowed this down to column alias names like: SELECT ..., index_definition AS [Definition: [...
Kristen's user avatar
  • 160
1 vote
2 answers
419 views

Database failed to process. Divide by zero error encountered running sp_BlitzIndex

I am seeing a divide by zero message while running sp_BlitzIndex. I am running Microsoft SQL Server 2008 (SP3) - 10.0.5775.0 (X64) Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service ...
scott's user avatar
  • 11
2 votes
1 answer
218 views

Error while running stored Proc sp_BlitzIndex (TM) v4.2 - September 03, 2016

I am getting below error while running "sp_BlitzIndex(TM) v4.2 - September 03, 2016". My SQL server version is: Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) ...
PrateekG's user avatar
2 votes
1 answer
219 views

sp_BlitzIndex finding "Unknown" index

I am going through cleaning up one of our databases, and after deleting an unused index and adding a primary key to the ARPayments table, sp_BlitzIndex is now reporting an index dbo.ARPayment.Unknown (...
dub stylee's user avatar
2 votes
1 answer
725 views

sp_BlitzIndex - "Cannot Insert the value null into column 'index_sanity_id'..."

Hi I'm getting this error message from the latest version of sp_BlitzIndex on one of my test servers: database failed to process. Cannot insert the value NULL into column 'index_sanity_id', table ...
Bill's user avatar
  • 23
3 votes
1 answer
463 views

Filter sp_BlitzIndex output

Is it possible to filter results of sp_BlitzIndex to show only unused and duplicate indexes? If so, how? I haven't tried anything yet. Was just giving a thought of inserting sp output in a table ...
SQLPRODDBA's user avatar
  • 1,830
5 votes
1 answer
1k views

Why are Op Stats all 0's and yet Usage Stats show 29 seeks

Why are Op Stats all 0's and yet Usage Stats show 29 seeks. This was returned from Brent Ozar's sp_blitzindex. Usage Stats Op Stats Reads: 29 (29 seek) Writes:14 0 ...
David G's user avatar
  • 51
2 votes
1 answer
4k views

How to start indexing a new data warehouse

I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds. I plan to use an index tuning tool (...
Craig's user avatar
  • 501
14 votes
2 answers
2k views

How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

I downloaded the SQL Server First Aid Kit from Brent Ozar's website. When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in ...
Erik's user avatar
  • 4,730
1 vote
1 answer
522 views

Index cleanup/usage help

My question is this: When running sp_blitzindex, I get a lot of these 2 warnings/suggestions: Index Hoarder: Unused NC index and Feature-Phobic Indexes: Potential filtered index (based on ...
bill's user avatar
  • 13
4 votes
2 answers
1k views

Are scripts like sp_BlitzIndex and the others accurate on restored backups?

I am a developer trying to get a better grasp of things. I am wondering if using the scripts from BrentOzar.com will work on restored backups? For example if I have the DBA give a backup of ...
john67z's user avatar
  • 43
4 votes
2 answers
2k views

Using sp_BlitzIndex - Diagnosed as "Index Hoarder: Addicted to nulls"

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
Matthew Clemente's user avatar