Statistics on database space usage

The company's database servers are getting increasingly short of space, and the maximum number of databases is 400 GB. There are four or five databases above 100 GB. Of course, we should be glad that the rapid growth of data shows that our business is developing well, but it is undeniable that our application design also has some problems. For example, unauthorized indexing, excessive redundancy, or the system does not consider clearing historical data that exceeds the value period during design.

 

The following script is used to obtain the space usage of each table/index in the database.

Code

With Pa
(
Select P. object_id, P. index_id, A. type_desc as pagetype_desc, A. total_pages, A. used_pages, A. data_pages
From SYS. partitions P join SYS. allocation_units
On P. partition_id = A. container_id
),
Indexes
(
Select object_id, index_id, object_name (object_id) as tbname, name as indexname, type_desc as tbtype_desc
From SYS. Indexes
Where object_id> = 100
),
RESULT
(
Select I. *, P. pagetype_desc, P. total_pages, P. used_pages, P. data_pages
From Pa P inner join indexes I
On P. object_id = I. object_id and P. index_id = I. index_id
)

Select * from result order by total_pages DESC

 

 

The following script is used to calculate the index usage.

 

 

Code
Declare @ dbid int
Select @ dbid = db_id ()
Select objectname = object_name (S. object_id), S. object_id, indexname = I. Name, I. index_id
, User_seeks, user_scans, user_lookups, user_updates
From SYS. dm_db_index_usage_stats,
SYS. Indexes I
Where database_id = @ dbid and objectproperty (S. object_id, 'isusertable') = 1
And I. object_id = S. object_id
And I. index_id = S. index_id
Order by (user_seeks + user_scans + user_lookups + user_updates) ASC

 

 

 

Create view SYS. dm_db_index_usage_stats
Select database_id, object_id, index_id,
User_seeks, user_scans, user_lookups, user_updates,
Last_user_seek, last_user_scan, last_user_lookup, last_user_update,
System_seeks, system_scans, system_lookups, system_updates,
Last_system_seek, last_system_scan, last_system_lookup, last_system_update
From OpenRowSet (Table logindexstats)
Where status = 0

This is the definition of SYS. dm_db_index_usage_stats.
View references #6/F | keep walking select B. Name, A. * From SYS. dm_db_index_usage_stats a inner join sysindexes B
On (A. object_id = B. ID) order by A. user_seeks DESC

This also comes with the name for your reference. Deleting indexes that are neither scanned nor searched is