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. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); DECLARE @objectname nvarchar(258); DECLARE @indexname nvarchar(258); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); — ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work’) DROP TABLE work; — conditionally select from the function, converting object and index IDs — to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO work FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; — Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM work; — Open the cursor. OPEN partitions; — Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; — 30 is the decision point at which to switch — between […]
Continue reading ...
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. [cc lang=”sql”] EXEC sp_updatestats [/cc] The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.SalesOrderDetail — Update a specific index on a table UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail — Update one column on a table specifying sample size UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT [/cc] Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system. The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent. [cc lang=”sql”] DECLARE @MaxDaysOld int DECLARE @SamplePercent int DECLARE @SampleType nvarchar(50) SET @MaxDaysOld = 0 SET @SamplePercent = NULL –25 SET @SampleType = ‘PERCENT’ –‘ROWS’ BEGIN TRY DROP TABLE #OldStats END TRY BEGIN CATCH SELECT 1 END CATCH SELECT RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1)) ,TableName = OBJECT_SCHEMA_NAME(st.object_id) + ‘.’ + OBJECT_NAME(st.object_id) ,StatName = st.name ,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1) INTO #OldStats […]
Continue reading ...
List out which tables are partitioned, and what partition scheme and partition function they use: [cc lang=”sql”] 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 [/cc] For a given partition function, list each of the boundary values [cc lang=”sql”] select r.boundary_id, r.value from sys.partition_range_values r join sys.partition_functions pf on r.function_id = pf.function_id where pf.name = ‘fnQuarters’ — partition function name order by r.value [/cc]
Continue reading ...
Code for reindexing all tables in a database using dbreindex
Continue reading ...
[cc lang=”sql”] 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 ) [/cc] 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. [cc lang=”sql”][/cc] –Just the table DBCC SHOWCONTIG (tablename) –Table with the index DBCC SHOWCONTIG (tablename, indexname) –Do not block anything (run during prod hours) DBCC SHOWCONTIG (tablename, indexname) WITH FAST –(2005) [cc lang=”sql”][/cc]
Continue reading ...
