Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.

Unused Heaps? - Quick Tips: Day 10

Don't forget! This coming Monday is the last Monday to win a free month of Pluralsight! All it takes is a single comment on the post that day and some way to get your E-Mail address to give you the code. The Monday following that, December 30th, will have a different prize. I will be giving away a free book and mailing it to your house. If you live out of the continental US, I will still send you a 25$ Amazon card to be used however you want. It's at least something to ring in the new year. ^.^

A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.

--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc

This will work for multiple databases. Be careful though. This will take some time to run on larger systems.

--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'

Alternately, you can also see what heap tables you have that are in use and how big they are.

--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc



Comments

Leave a comment on the original post [www.dbaonthego.com, opens in a new window]

Loading comments...