Skip to content
Archive of posts filed under the Maintenance category.

Rebuild and Reorganize Fragmented Indexes

This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention.

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(258);
DECLARE @objectname nvarchar(258);
DECLARE @indexname [...]

Using UPDATE STATISTICS for All Tables

There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.

EXEC sp_updatestats

The other way, is to use the UPDATE STATISTICS command. This command gives [...]

Query Which Tables are Partitioned

List out which tables are partitioned, and what partition scheme and partition function they use:

select t.name as TableName, ps.name as PartitionScheme,
ps.data_space_id, pf.name as PartitionFunction, pf.function_id
from sys.tables t
join sys.indexes i on t.object_id = i.object_id
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.partition_functions pf on ps.function_id = pf.function_id
where i.index_id < 2

For a given partition function, list each of [...]

Reindex All Tables in a Database

Code for reindexing all tables in a database using dbreindex

Find Table Fragmentation

DECLARE @Database VARCHAR(255)
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)

SET @Database = ‘SQLServerPlanet’
SET @TableName = ‘Users’
SET @IndexName = NULL

SELECT
avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(@Database)
,OBJECT_ID(@TableName)
,OBJECT_ID(@IndexName)
,NULL
,NULL
)

Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should not be in quotes.
[/sql]
–Just the table
DBCC SHOWCONTIG (tablename)

–Table with the index
DBCC SHOWCONTIG (tablename, indexname)

–Do not [...]