How to find busy tables in SQL Server?

There is a performance issue on the system. You check the sys.dm_os_wait_stats and you see that PAGEIOLATCH wait type is at the top. You know that it is related to disk performance. It may be because of the high IO issue or disk related issue itself. You collect the performance counters “Avg. disk sec/Read” and “Avg. disk sec/Write” and they are above the average of best practice 8ms. You check the sys.dm_io_virtual_file_stats and you see the busy data files for the database. You can take some of those files to another disk drive to spread out the IO load.

The question is “If you have a few busy tables which are in the same file group, The IO performance will be improved after moving those files to another drive?”

The answer is No. It is recommended to keep those busy tables in separate files. How can you do that? You can create different file groups and each file group has its own files. Then you can create tables or create clustered indexes or rebuild clustered indexes on those file groups to move those busy tables to separate files.

Another question is “How can you find the busy tables in SQL Server?”

A DMV (dynamic management view) called sys.dm_db_index_operational_stats can be used for this purpose.

Detailed information about this DMV can be taken from the link below

https://msdn.microsoft.com/en-us/library/ms174281.aspx

You can use the columns leaf_insert_count, leaf_update_count, leaf_delete_count, range_scan_count to get the access hit for the objects. This DMV keeps the cumulative data and returns the access method activity for each partition of a table or index in the database. So if a table has 2 indexes, the query returns 3 rows, 2 rows for indexes and 1 row for HEAP table or CLUSTERED index. You should consider the below items:

DMVs are kept in the memory. If SQL SERVER is restarted, the history of the DMV will be lost.

You should have a good history to talk about the access hits. You can create a job which runs everyday and get the data from DMV to static table. You can overwrite the static data. But if SQL SERVER is restarted, it shouldn’t be overwritten otherwise you can lose your static history data. If it is restarted, the data from DMV should be added to the static data.

A good script has been created for you. You can create a job with the name “SQLAdmin_TableAccessHistory” and schedule it for every day.

 USE master;
GO
if not exists (select name from sys.databases where name = 'SQLAdmin')
    create database SQLAdmin
go
use SQLAdmin
go
if not exists (select name from sys.objects where name = 'TableAccessHistory')
    create table TableAccessHistory (
        TableAccessHistoryID int identity (1,1),
        database_id int,
        database_name sysname,
        object_id int,
        object_name sysname,
        insert_count bigint,
        update_count bigint,
        delete_count bigint,
        select_count bigint
        constraint [PK_TableAccessHistory] primary key clustered
        (TableAccessHistoryID ASC)
    )
go
--use AdventureWorks2008
--go
declare @service_start_time datetime
declare @job_last_success_time datetime --it is the job scheduled to collect historic data from sys.dm_db_index_operational_stats
declare @database_id int
declare @database_name sysname
declare @sql as varchar(4000)

--test
--set @service_start_time = '2013-03-06 16:00:00'
--prod
select @service_start_time = sqlserver_start_time
from sys.dm_os_sys_info
--print @service_start_time

--test
--set @job_last_success_time = '2013-03-06 02:00:00' --last successfull time
--prod
select top 1 
@job_last_success_time = convert(datetime, rtrim(run_date)) 
+ ((run_time/10000 * 3600) 
+ ((run_time%10000)/100*60) 
+ (run_time%10000)%100) / (86399.9964 )
from msdb.dbo.sysjobhistory jh
join msdb.dbo.sysjobs j on jh.job_id = j.job_id
where j.name = 'SQLAdmin_TableAccessHistory' 
and jh.step_id = 0 and jh.run_status = 1
order by instance_id desc
--print @job_last_success_time 

set @database_id = db_id('SQLAdmin')

declare dbname_cursor CURSOR FOR
SELECT name, database_id from sys.databases 
where database_id not in (1,2,3,4, @database_id) and state=0

OPEN dbname_cursor

FETCH NEXT FROM dbname_cursor
INTO @database_name, @database_id

WHILE @@FETCH_STATUS = 0
BEGIN
    
    set @sql = '
        set nocount on
        declare @object_id int
        declare @object_name sysname
        declare @insert_count bigint
        declare @update_count bigint
        declare @delete_count bigint
        declare @select_count bigint

        declare TableAccessHistory_cursor CURSOR FOR
        SELECT ios.object_id, object_name(ios.object_id, ios.database_id) object_name, 
        leaf_insert_count insert_count, 
        leaf_update_count update_count, 
        leaf_delete_count delete_count, 
        range_scan_count select_count
        FROM  sys.dm_db_index_operational_stats (' + convert(varchar(5),@database_id) + ', NULL, NULL, NULL) ios
        join  [' + @database_name  + '].sys.objects o on ios.object_id = o.object_id
        where ios.index_id in (0, 1)
        and o.type = ''U'' and o.is_ms_shipped = 0
        
        OPEN TableAccessHistory_cursor

        FETCH NEXT FROM TableAccessHistory_cursor
        INTO @object_id, @object_name, @insert_count, @update_count, @delete_count, @select_count

        WHILE @@FETCH_STATUS = 0
        BEGIN
            if exists (select TableAccessHistoryID from TableAccessHistory
                        where database_id = ' + convert(varchar(5),@database_id) + ' and object_id = @object_id) 
                begin
                    if convert(datetime,''' + convert(varchar(30), @service_start_time) + ''') < convert(datetime, ''' + convert(varchar(30), @job_last_success_time) + ''')
                        begin
                            --print ''1''
                            update TableAccessHistory
                            set insert_count = @insert_count,
                            update_count = @update_count,
                            delete_count = @delete_count,
                            select_count = @select_count
                            where database_id = ' + convert(varchar(5),@database_id) + ' and object_id = @object_id
                        end
                    else
                        begin
                            --print ''2''
                            update TableAccessHistory
                            set insert_count = insert_count + @insert_count,
                            update_count = update_count + @update_count,
                            delete_count = delete_count + @delete_count,
                            select_count = select_count + @select_count
                            where database_id = ' + convert(varchar(5),@database_id) + ' and object_id = @object_id
                        end
                end
            else
                    insert into TableAccessHistory values
                    (' + convert(varchar(5),@database_id) + ', ''' + @database_name + ''', @object_id, @object_name, 
                     @insert_count, @update_count, @delete_count, @select_count)


           FETCH NEXT FROM TableAccessHistory_cursor
           INTO @object_id, @object_name, @insert_count, @update_count, @delete_count, @select_count
        END

        CLOSE TableAccessHistory_cursor
        DEALLOCATE TableAccessHistory_cursor
        '
    --print @sql
    exec (@sql)

    FETCH NEXT FROM dbname_cursor
    INTO @database_name, @database_id
END

CLOSE dbname_cursor
DEALLOCATE dbname_cursor

Result

You can identify the busy tables using the script below

 select database_name, object_name, 
sum(insert_count+ update_count+delete_count+select_count) as access_hit
from SQLAdmin.dbo.TableAccessHistory
group by database_name, object_name
order by database_name asc, access_hit desc

After identifying those busy tables

If there is no clustered index on the table, create a clustered index on different File Group which has different files

If there is a clustered index on the table, REBUILD index on different File Group which has different files

And monitor the performance