This post is a follow-up to Thomas LaRock’s excellent article here, so first go read that. I’ll wait.
You back? Good.
As most of you know tables missing clustered indexes (CI) are fairly bad performance-wise, but why? Even disregarding the fact that SQL Azure actually requires a CI for each and every table in the cloud database, and hence you may be “tripped up” on this in the future. More importantly, SQL Server sequentially reads the data in a CI one extent at a time. Brad McGehee states, “This makes it very easy for the disk subsystem to read the data quickly from disk, especially if there is a lot of data to be retrieved.” Heaps cause the server to do so much extra work due to unordered nature of NCI’s or having no indexes at all, that performance is bound to suffer.
My goal here is to extend the functionality of Thomas’ little script to do two additional things which I find extremely helpful. One, have the script work all at once for every database on a particular server. Certainly this is not an issue for those of you in environments with one database per server, but I’d bet that’s more the exception than the rule for most of us.
Secondly, I find it very useful to have a row count included to assist me with deciding on exactly how to proceed with the data provided. While I don’t disagree with the need to potentially trash any tables you find with 0 rows, that cleanup project is for another day. I’m concerned today with missing CI’s on tables that truly need them. Unfortunately, row count is not included in either of the joined tables in Thomas’ script. I solve that problem by creating a temp table filled from sp_spaceused and join it to the original resultset.
A couple caveats for you before checking out this script. Firstly, being that I work in an environment that’s 95% SQL Server 2000, this script was written specifically for 8.0. Now, I did indeed test it on 2005 and it does work, but I’d bet a lot of coin there are much easier ways of doing this using DMV’s and other new features. Secondly, the script has a hard time with alternate schemas besides ‘dbo’. While I’m fully aware the script can probably be changed to take current schema into account (perhaps from the ‘information_schema.tables’ system view), I don’t have the need to in my environment so honestly, I just never took the time to change the script to handle alternate schemas. Third, don’t hassle me for using cursors. I don’t use them in production, but am not averse to using them carefully in maintenance scripts that get run “occasionally.” I know all about the performance implications but not really an issue here for a script that takes less than a second to run…and on an ad-hoc basis at that.
Thanks again to the SQLRockstar for inspiring me to clean this up and post it out there for all.
Have a Grateful day…Troy
USE master
DECLARE cur1 CURSOR FOR
SELECT [name] FROM sysdatabases
WHERE [name] NOT IN ('tempdb','master','msdb','model')
DECLARE @db VARCHAR(75)
DECLARE @sql1 NVARCHAR(2000)
OPEN cur1
FETCH NEXT FROM cur1 INTO @db
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @sql1 = N'SET NOCOUNT ON
-- CREATE TEMP TABLE TO HOLD ALL TABLE NAMES
CREATE TABLE #so (dbName VARCHAR(75),
tableName VARCHAR(75),
tableRows INT)
-- FILL FROM sysobjects
INSERT INTO #so
SELECT ''' + @db + ''' [db], name [table], 0
FROM ' + @db + '.dbo.sysobjects
INNER JOIN ' + @db + '.information_schema.tables ist ON ist.table_name = ' + @db + '.dbo.sysobjects.name
WHERE xtype = ''u''
AND id NOT IN
(SELECT id FROM ' + @db + '.dbo.sysindexes WHERE indid = 1)
AND ist.table_schema = ''dbo''
AND ' + @db + '.dbo.sysobjects.name NOT IN (''cdata'',''cglobal'')
-- CREATE CURSOR TO ITERATE TABLE NAMES AND RUN sp_spaceused AGAINST THEM
DECLARE cur2 cursor for
SELECT tableName FROM #so
OPEN cur2
DECLARE @tn VARCHAR(75)
DECLARE @sql2 NVARCHAR(1000)
FETCH NEXT FROM cur2 INTO @tn
WHILE @@FETCH_STATUS <> -1
BEGIN
-- CREATE TEMP TABLE TO HOLD ROW AMOUNTS (AND OTHER STUFF WE DON'T CARE ABOUT NOW)
CREATE TABLE #su
(tableName varchar(100),
NOR varchar(100),
RS varchar(50),
DS varchar(50),
IxS varchar(50),
US varchar(50))
-- FILL THE NEW TEMP TABLE
SET @sql2 = N''INSERT INTO #su EXEC ' + @db + '.dbo.sp_spaceused '''''' + @tn + ''''''''
EXEC sp_EXECUTESQL @sql2
-- JOIN THE 2 TEMP TABLES TOGETHER TO UPDATE THE FIRST WITH THE ROW NUMBER COUNT
UPDATE #so
SET tableRows = su.NOR
FROM #su su
INNER JOIN #so so ON so.tableName = su.tableName
WHERE ISNULL(su.NOR,0) <> 0
-- CLEAN UP
DROP TABLE #su
-- ITERATE
FETCH NEXT FROM cur2 INTO @tn
END
-- CLEAN UP
CLOSE cur2
DEALLOCATE cur2
-- OUR OUTPUT!!
SELECT * FROM #so
WHERE tableRows > 0
-- CLEAN UP
DROP TABLE #so'
-- EXECUTE THE ENTIRE BLOCK OF DYNAMIC SQL
EXEC SP_EXECUTESQL @sql1
-- ITERATE
FETCH NEXT FROM cur1 INTO @db
END
-- CLEAN UP
CLOSE cur1
DEALLOCATE cur1
