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(3000) 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 DONT 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 ORDER BY tableRows DESC -- 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