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
Comments
- Anonymous
March 20, 2013
Thanks for the heads up for this DMV's hidden (or overlooked by me) feature Batuhan! - Anonymous
May 13, 2014
can you please post the same script for sql server 2005 - Anonymous
February 10, 2016
Hey, Just one thing to clarify. You said "DMVs are kept in the memory. If SQL SERVER is restarted, the history of the DMV will be lost.". However, on the MSDN they say: "The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL). " It means data may not persist if you run the job once per day. Is that correct? Thanks, Marcin