Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

sp_BlitzIndex is a free performance check stored procedure for Microsoft SQL Server. It reviews the dynamic management views (DMVs) for a single database in your server, and gives you a humorist psychologist's perspective - things like hoarding, multiple personalities, and phobias.

It's designed for developers and performance tuners - people who have the ability to change the indexes in their database in order to make it go faster. Each line has a URL you can copy/paste into your browser for more details about that warning. If you have a question about the warning, start with that URL first before asking questions here.

Tagging recommendation:

The sp-blitzindex tag is for support with the script. If you have a question about a particular SQL Server feature or error, and you happen to find it by using sp_BlitzIndex, you don't have to use this tag. (sp_BlitzIndex surfaces all kinds of schema nastiness.)

There are separate tags for sp_Blitz, sp_BlitzCache, and the other free stored procs from the same company.

47 questions
14
votes
2 answers

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 as the Azure database server level admin and I…
Erik
  • 4,833
  • 4
  • 28
  • 57
6
votes
1 answer

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 means we should remove those with no…
Motorcycle Racer
  • 63
  • 2
5
votes
1 answer

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 that page. This leads me to believe that any future…
BCM
  • 733
  • 5
  • 13
5
votes
1 answer

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 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;
David G
  • 51
  • 3
4
votes
2 answers

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 production, then restore that on my dev box, then run the…
john67z
  • 43
  • 3
4
votes
2 answers

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 their place. I know that there are cases where…
Matthew Clemente
  • 43
  • 6
4
votes
1 answer

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 value "No Missing Indexes". This is perfect. But in…
Karl Kvool
  • 41
  • 1
4
votes
1 answer

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 coming from. I don't have missing indexes. I don't…
MikeC
  • 75
  • 1
  • 5
4
votes
1 answer

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

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 up in the 'Gathering Computed Column Info.'…
Clayton
  • 41
  • 1
4
votes
1 answer

"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: [Property]] ColumnName {datatype maxbytes}], …
Kristen
  • 160
  • 5
3
votes
1 answer

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". I find many indexes with zero Reads but a thousand writes…
dba_maroc
  • 31
  • 1
  • 1
  • 2
3
votes
1 answer

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 now and then, we get issue of blocking and this…
Learning_DBAdmin
  • 3,924
  • 19
  • 39
3
votes
1 answer

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: Compressed indexes", and they are all on the…
Lewis Veale
  • 33
  • 2
3
votes
1 answer

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 table. question: How are both of them determining…
siberiaal
  • 41
  • 2
3
votes
1 answer

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 and then filter it there. But I am sure there must…
SQLPRODDBA
  • 1,928
  • 2
  • 33
  • 48
1
2 3 4