The Ultimate Index Width Stats Reporter

,

Util_IndexWidth

By Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports index length stats for indexes who's lengths exceeding parameterized thresholds (defaults to 0 - get all) for

total length and seek length, optionally filtering schemas and tables.

Required Input Parameters:

none

Optional Input Parameters:

@SchemaName sysname='' Filters schemas. Can use LIKE wildcards. All schemas if blank. Accepts LIKE Wildcards.

@TableName sysname='' Filters tables. Can use LIKE wildcards. All tables if blank. Accepts LIKE Wildcards.

@MaxLength int=0 Limits output to indexes with sum of all column max lengths exceeding this (OR with @SeekMaxLength)

@SeekMaxLength int=0 Limits output to indexes with sum of seek column max lengths exceeding this (OR with @MaxLength)

Usage

EXECUTE Util_IndexWidth 'dbo', 'order%', @MaxLength=250, @SeekMaxLength=100

Copyright:

Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.

Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify

it under the terms of the GNU Lesser General Public License as

published by the Free Software Foundation, either version 3 of the

License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU Lesser General Public License for more details.

see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

IF OBJECT_ID('dbo.Util_IndexWidth', 'P') IS NOT NULL DROP PROCEDURE dbo.Util_IndexWidth
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_IndexWidth
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports index length stats for indexes who's lengths exceeding parameterized thresholds (defaults to 0 - get all) for
total length and seek length, optionally filtering schemas and tables.

Update 2009-01-14:
	Added IndexDepth and FillFactor output columns

Required Input Parameters:
	none

Optional Input Parameters:
	@SchemaName sysname=''		Filters schemas.  Can use LIKE wildcards.  All schemas if blank.  Accepts LIKE Wildcards.
	@TableName sysname=''		Filters tables.  Can use LIKE wildcards.  All tables if blank.  Accepts LIKE Wildcards.
	@MaxLength int=0			Limits output to indexes with sum of all column max lengths exceeding this (OR with @SeekMaxLength)
	@SeekMaxLength int=0		Limits output to indexes with sum of seek column max lengths exceeding this (OR with @MaxLength)

Usage
	EXECUTE Util_IndexWidth 'dbo', 'order%', @MaxLength=250, @SeekMaxLength=100

Copyright:
	Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
	Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

	This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/

CREATE PROCEDURE dbo.Util_IndexWidth
	@SchemaName sysname='',
	@TableName sysname='',
	@MaxLength int=0,
	@SeekMaxLength int=0
AS

SELECT
	sys.schemas.schema_id, sys.schemas.name AS schema_name,
	sys.objects.object_id, sys.objects.name AS object_name,
	sys.indexes.index_id, ISNULL(sys.indexes.name, '---') AS index_name,
	partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.object_id, sys.indexes.name, 'IndexDepth') AS IndexDepth,
	sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
	sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint, sys.indexes.is_unique,
	TotalColumnCount, SeekColumnCount, IncludeColumnCount,
	TotalMaxLength, SeekMaxLength, IncludeMaxLength,
	TotalLargestColLength, SeekLargestColLength, IncludeLargestColLength
FROM
	(
		SELECT
			sys.objects.object_id, sys.indexes.index_id,
			COUNT(*) AS TotalColumnCount,
			SUM(CASE WHEN sys.index_columns.is_included_column=0 THEN 1 ELSE 0 END) AS SeekColumnCount,
			SUM(CASE WHEN sys.index_columns.is_included_column=1 THEN 1 ELSE 0 END) AS IncludeColumnCount,
			SUM(sys.columns.max_length) AS TotalMaxLength,
			SUM(CASE WHEN sys.index_columns.is_included_column=0 THEN sys.columns.max_length ELSE 0 END) AS SeekMaxLength,
			SUM(CASE WHEN sys.index_columns.is_included_column=1 THEN sys.columns.max_length ELSE 0 END) AS IncludeMaxLength,
			MAX(sys.columns.max_length) AS TotalLargestColLength,
			MAX(CASE WHEN sys.index_columns.is_included_column=0 THEN sys.columns.max_length ELSE 0 END) AS SeekLargestColLength,
			MAX(CASE WHEN sys.index_columns.is_included_column=1 THEN sys.columns.max_length ELSE 0 END) AS IncludeLargestColLength
		FROM
			sys.indexes
			JOIN sys.objects ON sys.indexes.object_id=sys.objects.object_id
			JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
			JOIN sys.index_columns ON sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
			JOIN sys.columns ON sys.index_columns.object_id=sys.columns.object_id AND sys.index_columns.column_id=sys.columns.column_id
			JOIN sys.types ON sys.columns.user_type_id=sys.types.user_type_id
		WHERE
			sys.objects.type='u'
			AND sys.schemas.name LIKE CASE WHEN @SchemaName='' THEN sys.schemas.name ELSE @SchemaName END
			AND sys.objects.name LIKE CASE WHEN @TableName='' THEN sys.objects.name ELSE @TableName END
		GROUP BY sys.objects.object_id, sys.indexes.index_id
		HAVING SUM(sys.columns.max_length)>@MaxLength OR SUM(CASE WHEN sys.index_columns.is_included_column=0 THEN sys.columns.max_length ELSE 0 END)>@SeekMaxLength
	) AS index_columns
	JOIN sys.indexes ON sys.indexes.object_id=index_columns.object_id AND sys.indexes.index_id=index_columns.index_id
	JOIN (
		SELECT
			object_id, index_id, SUM(row_count) AS Rows,
			CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
		FROM sys.dm_db_partition_stats
		GROUP BY object_id, index_id
	) AS partitions ON sys.indexes.object_id=partitions.object_id AND sys.indexes.index_id=partitions.index_id
	JOIN sys.objects ON index_columns.object_id=sys.objects.object_id
	JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
ORDER BY TotalMaxLength DESC, sys.objects.name, index_columns.index_id
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Rate

5 (4)

Share

Share

Rate

5 (4)