2007-05-24 14:28 | 只看楼主
树型| 收藏| 小 中 大 1

在SQLServer2000里探测表浪费的数据空间量

在SQLServer2000里探测表浪费的数据空间量

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

Sp_wasted_space将处理数据库里的每一列，然后打印出所有字符串列的一个报告，打印的信息如下：

* 列存储的最大长度
* 每一列存储的平均长度
* 每一列浪费的空间数
* 怎样去提升性能的提升

在master数据库里创建这个存储过程。既然存储过程读取你的数据库所以不需要参数。仅仅放在目标数据库里运行它就行。

仅在SQLServer2000里编译通过。

在大数据库上运行会花费一些时间，所以建议你在数据库的一个备份上运行。

SET QUOTED_IDENTIFIER OFF
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_WASTED_SPACE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_WASTED_SPACE]
GO
create PROC SP_WASTED_SPACE
as
/*
---------------------------------------------------------------------------
作者: Brian Knight
日期 : 2002-03-12
版本 : 1.0
更新 : 无
输入参数 : 无
用法: 在master数据库里创建，然后在任何一个数据库上运行sp_wasted_space。
翻译整理：王成辉
---------------------------------------------------------------------------
*/

SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE  @TABLE_NAME varchar(255),
  @COLUMN_NAME varchar(255),
  @CHARACTER_MAXIMUM_LENGTH int,
  @SQL VARCHAR(1000),
  @DB_NAME VARCHAR(255)
create table #COLUMN_WASTED (
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255),
DATA_TYPE varchar(10),
CHARACTER_MAXIMUM_LENGTH int,
CHARACTER_USED_LENGTH int,
CHARACTER_AVG_LENGTH int,
CHARACTER_WASTED_LENGTH int
)

SET @DB_NAME = (SELECT db_name())

SET  @SQL = "insert into #column_wasted (TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)"
SET @SQL = @SQL + "(select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from "+@DB_NAME
SET @SQL = @SQL + ".information_schema.columns where DATA_TYPE in ('varchar', 'nvarchar', 'text',"
SET @SQL = @SQL + "'char', 'nchar') and table_name <> 'dtproperties' and table_name <> 'syssegments')"
--PRINT @SQL
EXEC (@SQL)

DECLARE CUR_COLUMN_WASTED CURSOR
KEYSET
FOR SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM #COLUMN_WASTED

OPEN CUR_COLUMN_WASTED
FETCH NEXT FROM CUR_COLUMN_WASTED INTO @TABLE_NAME, @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @SQL = "UPDATE #COLUMN_WASTED
SET CHARACTER_USED_LENGTH = (select ISNULL(max(datalength("+@column_Name+")),0) from "+@table_name+")
WHERE TABLE_NAME = '"+@table_name+"'
AND COLUMN_NAME = '"+@COLUMN_NAME+"'"
EXEC (@SQL)
SET @SQL = "UPDATE #COLUMN_WASTED
SET CHARACTER_AVG_LENGTH = (select ISNULL(avg(datalength("+@column_Name+")),0) from "+@table_name+")
WHERE TABLE_NAME = '"+@table_name+"'
AND COLUMN_NAME = '"+@COLUMN_NAME+"'"
EXEC (@SQL)


END
FETCH NEXT FROM CUR_COLUMN_WASTED INTO @TABLE_NAME, @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH
END
CLOSE CUR_COLUMN_WASTED
DEALLOCATE CUR_COLUMN_WASTED

UPDATE #COLUMN_WASTED
SET CHARACTER_WASTED_LENGTH = CHARACTER_MAXIMUM_LENGTH - CHARACTER_USED_LENGTH

SELECT * FROM #COLUMN_WASTED order by TABLE_NAME, COLUMN_NAME
IF (SELECT COUNT(*) FROM #COLUMN_WASTED WHERE DATA_TYPE = 'text') > 0
BEGIN
PRINT 'There are text columns in this database.'
PRINT 'HINT 1: Anything that uses below 8060 characters MAY be able to be converted into a VARCHAR field.'
PRINT 'HINT 2 : Consider placing small text columns to ROWS IN TEXT where the CHARACTER_AVG_LENGTH is less than 250 characters . Here are a list of those columns and their maximum space used:'
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_USED_LENGTH FROM #COLUMN_WASTED WHERE DATA_TYPE = 'text' order by TABLE_NAME, COLUMN_NAME
END
drop table #COLUMN_WASTED
go
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。