0

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.

0

3 Answers 3

1

This is a known bug. The pull request found here claims to have a fix. Said fix seems reasonable to my eyes. Either wait on it getting merged or use the code from the PR and you should be able to install it.

Also,

PS: I don't know the exact version.

It is very easy to find the version in the code of the version that you have downloaded. Just open it up and look.

0
0

I updated the script and I added the updated sp_BlitzIndex store procedure. When ı run the store procedure, ı received the below error.

Do you have any idea?

Starting run. sp_BlitzIndex(TM) v8.19 - February 22, 2024 Create temp tables. Adding UQ index on #IndexSanity (database_id, object_id, index_id) Number of databases to examine: 1 Checking partition counts to exclude databases with over 100 partitions Starting loop through databases

master starting at 2024-03-13 12:08:15 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 Failure populating temp tables. Last @dsql: DECLARE @d VARCHAR(19) = CONVERT(VARCHAR(19), GETDATE(), 121) RAISERROR (N'start getting data into #dm_db_partition_stats_etc at (null)',0,1, @d) WITH NOWAIT; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; INSERT INTO #dm_db_partition_stats_etc ( database_id, object_id, sname, index_id, partition_number, partition_id, row_count, reserved_MB, reserved_LOB_MB, reserved_row_overflow_MB, lock_escalation_desc, data_compression_desc ) SELECT 1 AS database_id, ps.object_id, s.name as sname, ps.index_id, ps.partition_number, ps.partition_id, ps.row_count, ps.reserved_page_count * 8. / 1024. AS reserved_MB, ps.lob_reserved_page_count * 8. / 1024. AS reserved_LOB_MB, ps.row_overflow_reserved_page_count * 8. / 1024. AS reserved_row_overflow_MB, le.lock_escalation_desc, par.data_compression_desc

        FROM    [master].sys.dm_db_partition_stats AS ps  
                JOIN [master].sys.partitions AS par on ps.partition_id=par.partition_id
                JOIN [master].sys.objects AS so ON ps.object_id = so.object_id
                           AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
                           AND so.type <> 'TF' /*Exclude table valued functions*/
                JOIN [master].sys.schemas AS s ON s.schema_id = so.schema_id
                    OUTER APPLY (SELECT st.lock_escalation_desc
                                 FROM [master].sys.tables st
                                 WHERE st.object_id = ps.object_id
                                     AND ps.index_i...

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 2369 master database failed to process. Invalid column name 'hobt_id'.

0

I was able to fix it by changing the select statement against dm_db_index_operational_stats to do NULL as hobt_id since hobt_id isn't a valid column in that DMV for version earlier than 2016 (my client is also on 2014). Line number that I'm seeing in the SP is 1584. Should be the 3rd occurrence of the word "hobt". (Not to be confused with hobbit, which I did more times than I care to admit)

...select os.database_id
             , os.object_id
             , os.index_id
             , os.partition_number
             , NULL as hobt_id
             , os.leaf_insert_count
             , os.leaf_delete_count
             , os.leaf_update_count
             , os.range_scan_count....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.