Identifying High Row count tables in SQL Server – New and Improved

A big part of good database maintenance is having a fair idea of where your tables are at in terms of numbers of rows and primary keys. I took a stab at writing a generic script to return all the tables with their row counts with or without a primary key (PK) a few years back. I revisited that script and made some improvements. One of the features I added was a middle select that identifies the tables that have high turn over. This is identified, admittedly arbitrarily, by filtering for those whose row count is less than 10% of its PK value. In this way I can be alerted to those tables that I might want to resync so I don’t reach the 2 billion maximum for integer identities. It unerringly points to my ‘scratch’ tables that are generally wiped out after each use.


declare @tbltmpStats table (
Identity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Rows int,
Identity_Max int)
declare @objectid int;
declare @objectname nvarchar(200);
declare @sql1 nvarchar(4000)
declare @objectcursor cursor

set @objectcursor=cursor for select o.object_id from sys.objects o
where type='u'
and objectproperty(o.object_id, N'TableHasIdentity')=1
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = 'select ''' + cast(@objectname as nvarchar(200)) + ''', count(*), max(identitycol) from ' + @objectname
insert into @tbltmpstats (Table_Name, Rows, Identity_Max)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
update @tbltmpstats set Identity_Max = 0 where Rows = 0

select Table_Name, Rows, Identity_Max,(case (Identity_Max) when 0 then Null else (cast(Rows as decimal (10,2)) / cast(Identity_Max as decimal (10,2))) end) as [Rows/Identity_Max]
from @tbltmpstats
order by Rows desc,Identity_Max desc

--(Rows / Identity_Max) less than 10%
select 'Candidate for resyncing Primary Key: ' as Watch, Table_Name from @tbltmpstats where Identity_Max > 0 and (cast(Rows as decimal (10,2)) / cast(Identity_Max as decimal (10,2)))<0.1

declare @tbltmpStats2 table (
NonIdentity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Rows int
)
set @objectcursor=cursor for select o.object_id from sys.objects o
where type='u'
and objectproperty(o.object_id, N'TableHasIdentity')=0
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = 'select ''' + cast(@objectname as nvarchar(200)) + ''', count(*) from ' + @objectname
insert into @tbltmpstats2 (Table_Name, Rows)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
select Table_Name, Rows from @tbltmpstats2 order by Rows desc
deallocate @objectcursor

That, of course, begs the question why I’m not using temporary tables. An experienced DBA will tell you that there are individual answers to that question. I could say something like “Because!” or “It depends!” but if that’s still not clear then the following might give you a better insight into proper DBA attitude:
Dilbert.com

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , , . Bookmark the permalink.

One Response to Identifying High Row count tables in SQL Server – New and Improved

  1. tgrignon says:

    My wife made a comment to the feed on my Facebook account which I include here:

    zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz… Huh? So that’s why you need to bike 1.5 hours a day!!! Guess that’s why, when I say,with anticipation and affection, “What are you thinking, Terry?”, you pause, take a moment or two before shaking off that glazed look and eventually…answer… 😉 LOL!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s