indexes - cleanup

Sam 1,476

Hi All,

Need some suggestions on how to disable or drop some of the unused indexes?

Regards,

Sam

Sam 1,476 Reputation points

2024-06-19T05:12:00.4166667+00:00
Recently we are having issues with disk space requirements.

After monitoring db file growth for a week,we found the index file is growing rapidly (100gb-500gb). Other data files and log file space usage is stable. So, we wanted to clean up some of the unused indexes. So, what is the criteria.

Based on my knowledge, we can disable those indexes which have low reads vs high writes. I need more suggestions on how to go about cleaning up some of those unused indexes. I use below glenn berry DMV little modified to find out unused indexes.

Attaching the excel sheet which contains index usage stats data. please suggest some solutions.

Note: Application team has ddl_admin permissions to create objects. As per vendor database specifications, they need those permissions.

Questions :

Can we straight away disable those indexes whose reads = 0 but writes are high ?

When to disable when to drop those unused indexes?

Our index maintenance jobs runs over the weekend. When to disable or drop to have clean stats? by Friday?

Do we need to collect more data before disabling or dropping?

Some of the indexes have 0 Rowcount but i can see writes in that indexes , reads = 0 , so what does it mean? PFA screenshot
Sam 1,476 Reputation points

2024-06-19T05:12:33.52+00:00

index info.txt
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Sam 1,476 Reputation points

2024-06-19T05:14:02.94+00:00

rename the index info.txt to index info.zip.
Sam 1,476 Reputation points

2024-06-19T05:14:26.1533333+00:00

index with rowcount =0 but has writes , what does it mean?
Sam 1,476 Reputation points

2024-06-19T05:15:51.9233333+00:00
Can we straight away disable those indexes whose reads = 0 but writes are high ?

When to disable when to drop those unused indexes?

Our index maintenance jobs runs over the weekend. When to disable or drop to have clean stats? by Friday?

Do we need to collect more data before disabling or dropping?

Some of the indexes have 0 Rowcount but i can see writes in that indexes , reads = 0 , so what does it mean?

Accepted answer

Erland Sommarskog 112.7K Reputation points MVP

2024-06-19T21:51:09.5133333+00:00

Your whole approach seems wrong to me. If there is an app team that has db_ddladmin and can create indexes, then you as as the DBA cannot go bezeerk and drop indexes like crazy. You need to work together with the app team, at least if it is in your organisation. (You mentioned that it is a vendor database, so if the app team is working for the vendor, it gets a little more complicated.)

Dropping unused indexed is unlikely to save that much space, although it is a good idea to check for redundant indexes, particularly on large tables.

If the database is growing like you describe, you need to make sure that there is disk enough. With the data in your other post, it seems that in 2-3 weeks you will have filled up the disk and the system will come to a standstill. To avoid a standstill, you need to act now. You can spend one day or two with trying to understand from where all that growth is coming from (and as I said, you need to work with the app team). But at some point, you need to get a sign-off from management to move to a bigger VM.
Please sign in to rate this answer.
Erland Sommarskog 112.7K Reputation points MVP

2024-06-19T21:58:43.66+00:00

By the way, here are some queries you can use to see which are the biggest table. A good idea is to run them daily, so you can see where the growth is:

SELECT s.name AS schema_name, o.name AS object_name, i.name AS index_name, i.index_id, isnull(a2.type_desc, a1.type_desc) AS alloc_type, p.rows, isnull(a2.total_pages, a1.total_pages) * 8192 / 1000000 AS total_MB, isnull(a2.used_pages, a1.used_pages) * 8192 / 1000000 AS used_MB FROM sys.schemas s JOIN sys.objects o ON s.schema_id = o.schema_id JOIN sys.indexes i ON o.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN sys.allocation_units a1 ON a1.container_id = p.hobt_id AND a1.type IN (1,3) LEFT JOIN sys.allocation_units a2 ON a2.container_id = p.partition_id AND a2.type = 2 ORDER BY total_MB DESC ; WITH CTE AS ( SELECT s.name AS schema_name, o.name AS object_name, i.index_id, isnull(a2.type_desc, a1.type_desc) AS alloc_type, p.rows, isnull(a2.total_pages, a1.total_pages) * 8192 / 1000000 AS total_MB, isnull(a2.used_pages, a1.used_pages) * 8192 / 1000000 AS used_MB FROM sys.schemas s JOIN sys.objects o ON s.schema_id = o.schema_id JOIN sys.indexes i ON o.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN sys.allocation_units a1 ON a1.container_id = p.hobt_id AND a1.type IN (1,3) LEFT JOIN sys.allocation_units a2 ON a2.container_id = p.partition_id AND a2.type = 2 ) SELECT schema_name, object_name, COUNT(DISTINCT index_id) AS NoOfIndexes, MAX(rows) AS no_of_rows, SUM(total_MB) AS total_MB, SUM(used_MB) AS used_MB, SUM(CASE WHEN alloc_type <> 'LOB_DATA' THEN total_MB END) AS size_sans_LOB, SUM(CASE WHEN alloc_type = 'LOB_DATA' THEN total_MB END) AS size_LOB FROM CTE GROUP BY schema_name, object_name ORDER BY total_MB DESC ; WITH CTE AS ( SELECT s.name AS schema_name, o.name AS object_name, p.rows, (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = o.object_id) AS colcnt, isnull(a1.total_pages, 0) * 8192 / 1000000 AS regular_MB, isnull(a2.total_pages, 0) * 8192 / 1000000 AS LOB_MB, isnull(a1.used_pages, 0) * 8192 / 1000000 AS used_regular_MB, isnull(a2.used_pages, 0) * 8192 / 1000000 AS used_LOB_MB FROM sys.schemas s JOIN sys.objects o ON s.schema_id = o.schema_id JOIN sys.indexes i ON o.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN sys.allocation_units a1 ON a1.container_id = p.hobt_id AND a1.type IN (1,3) LEFT JOIN sys.allocation_units a2 ON a2.container_id = p.partition_id AND a2.type = 2 WHERE i.index_id IN (0, 1) ) SELECT schema_name, object_name, MAX(colcnt) AS no_of_columns, MAX(rows) AS no_of_rows, SUM(regular_MB + LOB_MB) AS total_MB, SUM(used_regular_MB + used_LOB_MB) AS used_MB, SUM(regular_MB) AS regular_MB, SUM(LOB_MB) AS LOB_MB, SUM(SUM(regular_MB)) OVER () FROM CTE GROUP BY schema_name, object_name ORDER BY total_MB DESC, no_of_rows DESC

Sam 1,476 Reputation points

2024-06-20T07:25:38.1+00:00

Thank you sir. All I can sense is, this has to be carefully dealt with a proper index performance tuner or consultant.

Just want to ask, is there any easy pickings from the index usage stats which can straight away disabled ( low hanging fruits i.e. non-clustered indexes which are nt part of any unique keys or primary keys ) without causing any side-effects to database performance [or] do we need to do home work such as tables which are taking most disk space and then check what indexes laid down on those specific tables and what application queries are using against tables and then try disabling 1-2 indexes based on zero selects but heavy writes and then monitor performance and disk space?

Sam 1,476 Reputation points

2024-06-20T07:38:41.6933333+00:00

I totally agree sir. we are working with the app team and we are looking for dropping all the backups tables which were created as part of Release deployments. Also, we are collecting reports which shows tables by size in desc order and seeing if they can DELETE / TRUNCATE large log tables and also archiving of transactional tables.

At the same, we already spoke to management and they've okay rebuilding new VMs and move some dbs to the new VM which has less dependencies.

Sam 1,476 Reputation points

2024-06-20T07:53:36.1866667+00:00

Sir,

I have executed the query and I got below results. it gives me 3 outputs. How can this information helpful. i see u've breakdown by allocation unit IN_ROW_DATA and LOB_DATA? How this information can be helpful for me and what can be done?

Also, in the 3rd result set , what is the last column mean and what is regualar_MB mean? is it free space ?

Sam 1,476 Reputation points

2024-06-20T08:02:57.7133333+00:00

Another 3 followup questions

1 . When do we see index size is greater than actual data size of the table? is that because of creation of too many non-clustered duplicate indexes ? yes. dev team has ddl_admin role.

2. when u see data as below what can I infer? for example,

reads = 0

writes = 10000

rowcount = 0

is there a query through I can sure shot disable non-clustered unused indexes excluding the unique primary key indexes and just get some free space out straight away?

Erland Sommarskog 112.7K Reputation points MVP

2024-06-20T21:29:56.1233333+00:00

Whether you need to distinguish between LOB-data and non-LOB-data depends very much on your database. Some databases have occasional LOB columns which account for a minor fraction of the database. A couple of years I worked with a database that was 450 GB in size. The actual "real" non-LOB data was less than 3 GB!

In a situation where the LOBs are a substantial part, you may want to consider alternate strategies for that data. Then again, I am not fond of the idea of storing files on a disk space somewhere and only storing the file name in the database.

"Regular-MB" is the size of non-LOB data. The nameless column is the total size of all non-LOB data.

1 . When do we see index size is greater than actual data size of the table? is that because of creation of too many non-clustered duplicate indexes ?

That does not have to be too many. All depending on your workload, they may be needed. But there may be redundant indexes. That is, you may have an index on (a, b) and another index on (a, b, c). Or you may have these two indexes:

(a, b, c) INCLUDE (e, f) (a, b, c) INCLUDE (g, h)

It may be better to consolidate this into a single index.

2. when u see data as below what can I infer? for example,

Well, I don't know from where you got that result set. (I can guess it is sp_blitzindex, but I don't it by heart.)

is there a query through I can sure shot disable non-clustered unused indexes excluding the unique primary key indexes and just get some free space out straight away?

Disabling indexes will not give you any free space. The indexes will still be there. But you will improve performance for writes, since the indexes will not be updated. To reclaim space, you must drop them entirely.

As I've said before, I don't think that dropping indexes will give you that much space. Looking over retention, log tables etc is likely to give much more.

Sam 1,476 Reputation points

2024-06-21T16:49:09.6266667+00:00

so, i just customized glenn and brent ozar's unused/bad indexes script . Thats where i got the data

IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL

BEGIN

DROP TABLE #tmp

END

declare @a varchar(30); --machine restart date

declare @b varchar(30); --sql server restart date

declare @c varchar(30); --no of days since restart

SELECT @a=convert(varchar(30),Dateadd(s, ( ( -1 ) * ( osd.[ms_ticks] / 1000 ) ) , Getdate()),109),

@b=convert(varchar(30),osd.sqlserver_start_time,109) FROM sys.[dm_os_sys_info] osd;

select @c =RTRIM(CONVERT(CHAR(3),DATEDIFF(second,login_time,getdate())/86400)) + ':' +

RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400/3600)),2) + ':' +

RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600/60)),2) + ':' +

RIGHT('00'+RTRIM(CONVERT(CHAR(2),DATEDIFF(second,login_time,getdate())%86400%3600%60)),2)

from sys.sysprocesses --sysprocesses for SQL versions <2000

where spid = 1

-- glenn berry

-- Possible Bad NC Indexes (writes > reads) (Query 67) (Bad NC Indexes)

-- Look for indexes with high numbers of writes and zero or very low numbers of reads

-- Consider your complete workload, and how long your instance has been running

-- Investigate further before dropping an index!

-- consider rowcount > 10000

SELECT

@a AS Machinerestartdatetime,

@b as sqlserver_start_time,

@c AS [Days:Hours:Minutes:Seconds],

getdate() as CaptureDt,

@@servername as ServerName,

DB_NAME() AS DatabaseName,

SCHEMA_NAME(o.schema_id) +'.'+OBJECT_NAME(s.OBJECT_ID) AS TableName,

--SCHEMA_NAME(o.[schema_id]) AS [Schema Name],

--OBJECT_NAME(s.[object_id]) AS [Table Name],

i.index_id,

i.name AS [Index Name],

i.[type_desc],

s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],

s.user_updates AS [Total Writes],

s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],

[Row Count] = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)

, CASE

WHEN s.user_updates < 1 THEN 100 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates

END AS reads_per_write -- The Reads_per_write should be atleast > 50%

,CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 END * 8 / 1024 AS IndexSizeMB ,s.last_user_seek AS LastSeek ,s.last_user_scan AS LastScan ,s.last_user_lookup AS LastLookup ,s.last_user_update AS LastUpdate ,i.is_disabled,

--i.is_hypothetical,

i.fill_factor,

i.has_filter,

'ALTER INDEX '+i.name+' ON '+SCHEMA_NAME(o.schema_id)+'.'+OBJECT_NAME(s.OBJECT_ID)+' DISABLE;' as 'disable statement',

'DROP INDEX ' + QUOTENAME(i.name)+' ON '+QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'+QUOTENAME(OBJECT_NAME(s.object_id))+';' as 'drop statement'

into #tmp

FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON s.[object_id] = i.[object_id]

AND i.index_id = s.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON i.[object_id] = o.[object_id]

LEFT JOIN (SELECT OBJECT_ID, index_id, SUM(used_page_count) AS usedpages,

SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) AS pages FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

AND s.database_id = DB_ID()

AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)

AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'

AND i.is_primary_key = 0 --do not check primary keys

AND i.is_unique_constraint = 0 --do not check unique constraints

AND i.is_unique = 0 --do not check unique indexes

--AND (ius.user_seeks+ius.user_scans+ius.user_lookups) < 1 --only return unused indexes

--AND OBJECT_NAME(i.OBJECT_ID) = 'tableName'--only check indexes on specified table

--AND i.name = 'IX_Your_Index_Name' --only check a specified index

--ORDER BY [Total Reads] ASC, [Total Writes] DESC OPTION (RECOMPILE);

select

[Machinerestartdatetime], [sqlserver_start_time], [Days:Hours:Minutes:Seconds] as [dd hh:mi:ss],--since how many days sql restarted [CaptureDt], [ServerName], [DatabaseName], [TableName], [index_id], [Index Name], [type_desc], [Total Reads], [Total Writes], [Difference], [Row Count], --[reads_per_write], cast(round([reads_per_write],0,2) as numeric(36)) [IndexSizeMB], [LastSeek], [LastScan], [LastLookup], [LastUpdate], [is_disabled], [fill_factor], [has_filter], [disable statement], [drop statement] --into sqldba_utils..cmx_ors_index_usage_stats_Aug112023

from #tmp

ORDER BY [Total Reads] ASC, [Total Writes] DESC OPTION (RECOMPILE);

Sam 1,476 Reputation points

2024-06-21T16:50:31.8866667+00:00

sir, does DBCC CLEANTABLE will be off any help where LOB columns are more in a table and does it free any free space?

Erland Sommarskog 112.7K Reputation points MVP

2024-06-21T22:16:46.2933333+00:00

sir, does DBCC CLEANTABLE will be off any help where LOB columns are more in a table and does it free any free space?

Have you checked the Learn topic for DBCC CLEANTABLE? What does it say about LOBs? I ask, because I don't know on the top of my head myself. So one of us has to read the manual. I don't think it does, but I am not sure.

If you have fragmented LOBs, ALTER INDEX REORGANIZE is the best choice, as it does LOB compaction by default.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Olaf Helper 45,106 Reputation points

2024-06-19T05:53:25.1566667+00:00

How do you define "unused indexes"?

Primary keys may be "unused", but defines business logic = uniqueness of data.
Please sign in to rate this answer.
Sam 1,476 Reputation points

2024-06-20T07:32:53.1933333+00:00

Okay. I didn't thought in that angle. My definition or my understanding is, when I create a non-clustered index on a table and if it is not being used for selects/reads but there are are millions of writes, those are bad indexes. again, this is just my opinion. So, ur saying , if a non-clustered unique index created to enforce uniqueness of row in a table, then such index will have writes but no reads or less reads. so, we should not be touching those indexes. that is what I understood from your lines. so, is there a way to filter out / exclude them and give us only unused indexes which can disabled or dropped at a later stage. for that , what information , we should be collecting on daily or weekly basis?

Erland Sommarskog 112.7K Reputation points MVP

2024-06-20T21:12:54.35+00:00

There are columns for this in sys.indexes, is_primary_key and is_unique_constraint. Of course, this assumes that they are actually implemented as constraints. (Which they should be, but not everyone play by the rules.)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

indexes - cleanup

1 additional answer

Your answer