SQL Server Performance

Return wasted space from Improper CHAR/NCHAR Usage

Discussion in 'Contribute Your SQL Server Scripts' started by LearnSqlServer.com, Aug 29, 2006.

  1. LearnSqlServer.com New Member

    If anyone has time or test databases with CHAR/NCHAR usage, I'm trying to "modernize" a SQL 7.0/2000 script (below) and want some input on it. <br /><br />Also, any ideas on improving it would be welcome. I've run on some 200 table databases and it runs quickly but I'd like some feedback.<br /><br />Thanks!<br /><br />Scott Whigham<br /><br /><hr noshade size="1"><pre id="code"><font face="courier" size="2" id="code"><br /><br />CREATE PROC dbo.DBA_GetFixedDatatypeUsageStats (<br />@MinimumRowsRequiredToInclude INT = 1000 -- Only work with tables having "n" or greater number of rows<br />, @MinimumSizeOfCharColumnToConsider INT = 10 -- Setting this to 10 would say, "Only process columns of CHAR(10) or higher"<br />)<br />AS<br />/*<br /> Author: Scott Whigham from<a target="_blank" href=http://www.LearnSqlServer.com/>http://www.LearnSqlServer.com/</a><br /><br /> Description: This proc returns information about any columns that use CHAR/NCHAR as their datatypes. The returned info includes<br />the amount of actual data used versus the amount of space the data is taking up (returning wasted space) and breaks the rows<br />into groupings that show how many rows are efficient or inefficient. <br /><br /> Misc Notes: I worked with a client one time who had all of their primary keys CHAR(25). We ran a similar script and it showed they had<br />over 40GB of wasted space in a 400GB database!<br /> <br /> Versions: SQL Server 2005<br /> <br /> Creation Date: August 29, 2006<br /><br /> For more scripts like this one, visit<a target="_blank" href=http://forums.learnsqlserver.com/codesamples.aspx>http://forums.learnsqlserver.com/codesamples.aspx</a><br /><br />==================== Algorithm ===============================<br />Step 1: Create temporary tables <br />-- Can't use a table variable here because it goes out of scope when the dynamic SQL is executed<br />Step 2: Get all the columns using CHAR/NCHAR with a size &gt; @MinimumSizeOfCharColumnToConsider<br />-- Insert these into #TableInfo<br />Step 3: Loop through #TableInfo and calculate details<br />Step 4: Strip out the qualification info to make it easier to view resultsFinal Step: Drop temp objects<br />*/<br /><br />/**************************************************************************************<br />Step 1: Create temporary tables <br />-- Can't use a table variable here because it goes out of scope when the dynamic SQL is executed<br />**************************************************************************************/<br />CREATE TABLE #TableInfo (<br />LineId INT IDENTITY(1,1) NOT NULL PRIMARY KEY<br />, DatabaseName SYSNAME NOT NULL DEFAULT ( DB_NAME () )<br />, TableName SYSNAME NOT NULL -- Qualified with [TableName]<br />, SchemaName SYSNAME NOT NULL -- Qualified with [SchemaName]<br />, ColumnName SYSNAME NOT NULL -- Qualified with ColumnName<br />, Datatype SYSNAME NOT NULL<br />, Length INT NOT NULL<br />, Storage_Bytes INT NOT NULL<br />, ColumnOrder INT NOT NULL<br />, NumberOfRows INT NOT NULL -- not exact since we use sys.sysindexes.rowcnt but it keeps us from running COUNT(*) against all tables!<br />, HasBeenProcessed BIT DEFAULT (0) NOT NULL<br />)<br /><br />CREATE TABLE #TableDatatypeUsage (<br />LineId INT NOT NULL -- , CONSTRAINT FK FOREIGN KEY (LineId) REFERENCES #TableInfo (LineId) ON DELETE CASCADE -- not allowed on temp tables)<br />, RowsWith_GoodSpaceUtilization INT<br />, RowsWith_MediocreUtilization INT<br />, RowsWith_InefficientUtilization INT<br />, RowsWith_VeryInefficientUtilization INT<br />)<br /><br />SET NOCOUNT ON<br /><br />DECLARE @SqlString NVARCHAR(2000)<br />, @Table SYSNAME<br />, @Schema SYSNAME<br />, @Column SYSNAME<br />, @Datatype SYSNAME<br />, @Length INT<br />, @CurrentRow INT<br />, @CurrentRow_String NVARCHAR(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> -- EXEC cannot use CAST/CONVERT to convert INT to VARCHAR in a dynamic string<br /><br />/**************************************************************************************<br />Step 2: Get all the columns using CHAR/NCHAR with a size &gt; @MinimumSizeOfCharColumnToConsider<br />-- Insert these into #TableInfo<br />**************************************************************************************/<br />INSERT #TableInfo (TableName, SchemaName, ColumnName, Datatype, Length, Storage_Bytes, ColumnOrder, NumberOfRows)<br />SELECT '[' + TABLE_NAME + ']', '[' + TABLE_SCHEMA + ']', '[' + COLUMN_NAME + ']', DATA_TYPE, CHARACTER_MAXIMUM_LENGTH<br />, 0 AS Storage_Bytes<br />, ORDINAL_POSITION<br />, ( SELECT rowcnt FROM sys.sysindexes i JOIN sys.tables t<br />ON i.id = t.object_id<br />WHERE i.indid=1 AND t.object_id = OBJECT_ID(TABLE_NAME) <br />) AS NumberOfRows<br />FROM INFORMATION_SCHEMA.COLUMNS <br />WHERE DATA_TYPE IN ('char', 'nchar')<br /> AND CHARACTER_MAXIMUM_LENGTH &gt; @MinimumSizeOfCharColumnToConsider<br />AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsTable') = 1<br />AND OBJECT_ID(TABLE_NAME) IN ( <br />SELECT t.object_id<br />FROM sys.sysindexes i JOIN sys.tables t<br />ON i.id = t.object_id<br />WHERE i.indid=1 AND rowcnt &gt; @MinimumRowsRequiredToInclude<br />)<br />ORDER BY TABLE_NAME, ORDINAL_POSITION<br /><br />/**************************************************************************************<br />Step 3: Loop through #TableInfo and calculate details<br />**************************************************************************************/<br />SELECT @CurrentRow = MIN(LineId), @CurrentRow_String = CAST(@CurrentRow AS VARCHAR) FROM #TableInfo WHERE HasBeenProcessed = 0<br />WHILE @CurrentRow IS NOT NULL<br />BEGIN<br />SELECT @Schema = SchemaName, @Table = TableName, @Column = ColumnName<br />FROM #TableInfo <br />WHERE LineId = @CurrentRow<br /><br />SET @SqlString = '<br />UPDATE #TableInfo <br />SET Storage_Bytes = ISNULL ( (SELECT SUM(LEN(' + @Column + ')) FROM ' + @Schema + '.' + @Table + ') , 0 )<br />FROM #TableInfo <br />WHERE LineId = ' + @CurrentRow_String<br />EXEC (@SqlString)<br /><br /> INSERT #TableDatatypeUsage <br />( LineId, RowsWith_GoodSpaceUtilization, RowsWith_MediocreUtilization, RowsWith_InefficientUtilization, RowsWith_VeryInefficientUtilization)<br /> EXEC ( '<br /> SELECT ' + @CurrentRow_String + ' AS LineId<br />, SUM ( CASE WHEN PercentageFull BETWEEN 75 AND 100 THEN 1 ELSE 0 END) AS RowsWith_GoodSpaceUtilization<br /> , SUM ( CASE WHEN PercentageFull BETWEEN 50 AND 74 THEN 1 ELSE 0 END) AS RowsWith_MediocreUtilization<br /> , SUM ( CASE WHEN PercentageFull BETWEEN 25 AND 49 THEN 1 ELSE 0 END) AS RowsWith_InefficientUtilization<br /> , SUM ( CASE WHEN PercentageFull &lt; 25 THEN 1 ELSE 0 END) AS RowsWith_VeryInefficientUtilization<br /> FROM (<br /> SELECT ''' + @Schema + ''' AS SchemaName, ''' + @Table + ''' AS TableName<br />, ROUND(CAST(LEN(' + @Column + ') AS NUMERIC) / CAST(DATALENGTH(' + @Column + ') AS NUMERIC), 2) * 100<br /> AS PercentageFull<br /> FROM ' + @Schema + '.' + @Table + '<br /> ) AS x <br />GROUP BY SchemaName, TableName')<br /><br />UPDATE #TableInfo SET HasBeenProcessed = 1 WHERE LineId = @CurrentRow<br />SELECT @CurrentRow = MIN(LineId), @CurrentRow_String = CAST(@CurrentRow AS VARCHAR) FROM #TableInfo WHERE HasBeenProcessed = 0<br />END<br /><br />/**************************************************************************************<br />Step 4: Strip out the qualification info to make it easier to view results<br />**************************************************************************************/<br />SELECT <br />REPLACE(REPLACE(ht.SchemaName, '[', ''), ']', '') AS SchemaName<br />, REPLACE(REPLACE(ht.TableName, '[', ''), ']', '') AS TableName<br />, REPLACE(REPLACE(ht.ColumnName, '[', ''), ']', '') AS ColumnName<br />, ht.NumberOfRows AS NumberOfRows<br />, ht.DataType<br />, ht.Length<br />, CAST ( ht.NumberOfRows * ht.Length / 1024.0 AS DECIMAL(10,2) ) AS MaxPossibleSizeOfColumn_KB<br />, CAST ( ht.Storage_Bytes / 1024.0 AS DECIMAL(10,2) ) AS ActualSizeOfDataInColumn_KB<br />-- AmountOfWastedSpace = MaxPossibleSizeOfColumn_KB - ActualSizeOfDataInColumn_KB<br />, CAST ( ht.NumberOfRows * ht.Length / 1024.0 AS DECIMAL(10,2) ) <br />- CAST ( ht.Storage_Bytes / 1024.0 AS DECIMAL(10,2) ) AS AmountOfWastedSpace_KB<br />, CAST ( <br />CASE <br />WHEN (ht.Storage_Bytes / 1024.0 ) &gt; 0 THEN<br />CASE <br />WHEN 100 - ROUND(ht.Storage_Bytes / (NULLIF(ht.NumberOfRows, 0) * ht.Length) * 100, 2) &lt;= 0 THEN 0<br />ELSE 100 - ROUND(CAST(ht.Storage_Bytes AS DECIMAL) / (NULLIF(ht.NumberOfRows, 0) * ht.Length) * 100, 2)<br />END<br />ELSE 0<br />END AS DECIMAL(10,2)<br />) AS PercentWastedSpace<br />, dt.RowsWith_GoodSpaceUtilization<br />, dt.RowsWith_MediocreUtilization<br />, dt.RowsWith_InefficientUtilization<br />, dt.RowsWith_VeryInefficientUtilization<br />FROM #TableInfo ht JOIN #TableDatatypeUsage dt<br />ON ht.LineId = dt.LineId<br />ORDER BY ht.ColumnName<br /><br />/**************************************************************************************<br />Final Step: Drop temp objects<br />**************************************************************************************/<br />DROP TABLE #TableDatatypeUsage<br />DROP TABLE #TableInfo<br />GO<br /><br />/*******************************<br /></font id="code"></pre id="code"><br />Scott Whigham<br /><br />Check out<a target="_blank" href=http://www.LearnSqlServer.com/VideoTutorials/>http://www.LearnSqlServer.com/VideoTutorials/</a> - SQL Server 2005 and 2000 Tutorials<br /><br />*******************************/
  2. LearnSqlServer.com New Member

    The formatting didn't come out so great. Anyone who wants a copy of the actual SQL script can drop byhttp://www.learnsqlserver.com/ContactUs.aspx and write it in. I'll send you a .sql file with nicer formatting.

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  3. FrankKalis Moderator

    That's the parser (and the style sheet, of course) of the Snitz forum package that something do strange things to a posting. The code tags work quite nicely most of the time, but sometimes blanks are removed.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Probably we will have another solution for the script section here shortly. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page