By David Wiseman (Administrator)Created 11 Feb 2011, Modified 01 Mar 2011
My Rating:
Vote
Rating:
Not Rated
Views:10377
Downloads:41
Source:

Defrag Indexes For Database

Language:  T-SQL

Compatibility

SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes
Description

This script will defrag all the indexes in a database that are above a specified fragmentation threshold. You can control the level of fragmentation that triggers a REORGANIZE and the level of fragmentation that triggers a REBUILD. You can also choose to rebuild your indexes online and sort in tempdb.


Notes

What is Index Fragmentation?

Index fragmentation is categorised in two types internal and external.  Fragmentation also occurs at the file system level, but this is separate from index fragmentation which is the main focus of this article.  The types of fragmentation are discussed in the following sections.

Internal Fragmentation

The level of internal fragmentation in an index is referring to the "fullness" of the index pages.  In some cases it might be desirable to have a certain amount of internal fragmentation as it can improve the performance of inserts/updates. This is because there is room available in the index pages so that new data can be inserted without incurring the performance penalty of a page split.  A page split is what occurs when there is no room on an index page to insert new data and SQL server moves approximately half the data to a new page to make room for the new data.
 
An index that has heavy internal fragmentation will take up additional disk space and incur additional I/O when data is read from the index. Indexes with low internal fragmentation are more compact and more efficient when reading data from the index, but might incur some additional overhead for inserts and updates. 
 
You can set a fill factor to introduce internal fragmentation into your indexes, which you might want to do for certain tables that receive large volumes of insert/update operations.  You wouldn't want to lower the fill factor for indexes on tables that receive relatively few inserts/updates or indexes where new data is logically inserted at the end of an index.  It's quite common to have tables with an identity column and these are often used as the table's primary key and clustered index.  In this case, new data is always inserted at the end of the index so introducing a lower fill factor is only going to have a negative impact on read performance and increase the storage requirements for the index.  You might also have a table that is using a UNIQUEIDENTIFIER as a primary key/clustered index with new values been generate by the NEWID() function.  In this case, new data is inserted randomly throughout the index causing a large number of page splits and poor performance with too high a fill factor.  Note: If you use the UNIQUEIDENTIFIER data type, a better option is to use the NEWSEQUENTIALID() function so that it behaves similar to an identity column where data is inserted at the end of the index. 
 
The fill factor value is specified as a percentage between 1 and 100, with a default server-wide setting of 0.  A fill factor of 0 is the same as a fill factor of 100, where leaf level pages are filled to capacity.  A fill factor value of 80 would leave approximately 20% space free at the leaf level of the index, potentially reducing the amount of page splits and improving insert/update performance.  At the same time this would also increase the size of the index by about 20% and reduce read performance. The fill factor setting is applied when the index is created or rebuilt, but is not maintained as data changes in the index.  If SQL Server was to maintain the fill factor, this would incur additional page splits which would defeat the purpose of the fill factor setting.  If you start out with a high fill factor, the value might also be lowered over time due to page splits.
 
In most situations there is no need to change the server level default setting for the fill factor.  As mentioned previously there might be certain indexes that would benefit from a lower fill factor and this can be set on an index by index basis by rebuilding the index with a lower fill factor.  It's worth noting that in most cases data will be read from the index much more than it is written to so a high fill factor is often a good choice. 

External Fragmentation

External fragmentation refers to the amount of out-of-order leaf pages in an index, normally expressed as a percentage.  Unlike internal fragmentation, there is no benefit in the index leaf pages been out-of-order and a percentage as close to zero as possible is desired.  External fragmentation also occurs as a result of page splits, which maintain the logical order of the index with previous and next pointers but not the physical order.  Out of order pages are not necessarily a problem for all query types but they do reduce the performance of ordered scans, where data in the index needs to be fetched back in logical order.  For example, if you have an index on a date column, a query requesting the range from 1st Jan to 31st of Jan might result in an ordered scan. 

File System Fragmentation

File system fragmentation occurs at the o/s level and it's completely separate from the internal and external fragmentation that is reported by SQL Server.  It's possible for SQL Server to report very low levels of internal and external fragmentation and for your database files to be severely fragmented at the file system level.  It's also possible for the reverse to be true where the file system reports very low levels of fragmentation, but the internal structure of your database is heavily fragmented.  If you perform an o/s level defragmentation, the internal contents of your database files will remain unchanged including the fragmentation levels of your index.  Defragmenting indexes in SQL Server won't improve the file system fragmentation levels of your database files. 
 
It's worth noting that file system fragmentation can largely be avoided by proper management of the growth of your database files.  You should avoid growing your database files in small increments and pre-allocate space in your database files rather than relying on auto growth.  You should keep your database files on dedicated volumes to avoid the drives been fragmented by other processes.  If you use database snapshots, I strongly advise that these are allocated their own drive separate from your other database files.

Index Maintenance

Indexes can become fragmented over time, both in terms of internal an external fragmentation, resulting in a degradation in performance.  There are two different methods you can use to reduce fragmentation and improve the performance of your indexes; an index rebuild or an index reorganize.  An index reorganize will defragment the leaf levels of clustered an nonclustered indexes by physically moving data so that the physical order of the index matches the logical order of the index.  The process is considered an "online" operation as it doesn't hold any long-term locks that might cause blocking in your database. 
 
An index rebuild is the same as dropping and re-creating the index.  By default this is considered an offline operation as long term locks are held during the index rebuild process, which can cause severe blocking in your database.  If you are using the Enterprise, Developer or Evaluation editions of SQL Server, you have the option to rebuild your indexes online.  Locks are still used during the online index rebuild, but they are held for a much shorter period of time.  There are some limitations that apply to online index rebuilds that need to be considered.  For example, online index rebuilds are not supported for XML indexes or indexes that contain LOB data (image, text, NVARCHAR(MAX), VARCHAR(MAX) etc).  They are also not supported on partitioned indexes and disabled indexes.
 
If the level of fragmentation is high (>30%), you should consider rebuilding your indexes and for lower fragmentation levels, you should consider an index reorganize.

How Often?

You should always attempt to perform index maintenance out of hours or use an online method of index maintenance during periods of very low system activity if your databases are online 24x7.  Your maintenance windows will dictate to a certain extent when and how often you are able to perform index maintenance. 
 
It's worth noting that index fragmentation occurs as a result of insert, update and delete statements and you don't need to perform index maintenance more frequently than the data changes. You might have some databases that are read only or ones that are updated infrequently.  For example, if you have a data warehouse (reporting database) that is updated on a monthly basis, it's unnecessary to perform weekly index rebuilds as the database is essentially read only between data loads.  In the case of a data warehouse, it might be appropriate to perform index maintenance after loading new data into the database. 

Index Maintenance Strategy

If your maintenance window allows for it, the easiest option is simply to rebuild all the indexes in your database.  You could do this using a database maintenance plan or using a script similar to the one below:
 
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT N'ALTER INDEX ALL ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + N' REBUILD
'

FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

exec
sp_executesql @SQL
 
The script doesn't specify a fill factor so the fill factor that was set when the index was created or last rebuilt will be used.  This is normally the best option to use for index maintenance scripts that rebuild all of your indexes as you might want to tailor this value for individual indexes as required. 
 
If your database is large, it's likely to take a considerable amount of time to rebuild all your indexes so it's often better to take a more targeted approach.  One option is to  assess the levels of fragmentation by using the "sys.dm_db_index_physical_stats" dynamic management function and decide which indexes to rebuild based on the levels of fragmentation.  For example, the following query returns fragmentation statistics for all the indexes in the current database:
 
SELECT
s.name as schema_name,
            so.name as table_name,
            si.name as index_name,
            partition_number,
            index_type_desc,index_depth,
            index_level,
            avg_fragmentation_in_percent,
            fragment_count,
            avg_fragment_size_in_pages,
            page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , 'LIMITED') ips
JOIN sys.indexes si on ips.object_id = si.object_id and ips.index_id = si.index_id
JOIN sys.objects so on ips.object_id = so.object_id
JOIN sys.schemas s on so.schema_id = s.schema_id
 
The "avg_fragmentation_in_percent" column gives you the level of external fragmentation and it's this value that it used for the index maintenance stored procedure in this article to decide which indexes to reorganize or rebuild.  You can find more information about the "sys.dm_db_index_physical_stats" dynamic management function here.

Index Maintenance Stored Procedure

The stored procedure accompanying this article makes use of the  sys.dm_db_index_physical_stats function mentioned previously to identify the levels of external fragmentation in your indexes.  Indexes are then rebuilt or reorganized based on the thresholds you specify to the stored procedure.  The parameters for the stored procedure are described in the table below:
 
Parameter Name Description
@ReorganizeThresold The value specified here indicates the minimum level of fragmentation that will trigger index maintenance.  The type of index maintenance would be an index reorganize unless the level of fragmentation also exceeds the @RebuildThreshold. 
@RebuildThreshold If the level of fragmentation is greater than the rebuild threshold an index rebuild is performed instead of a reorganize.  If you use a value greater than 100 for the rebuild threshold, the stored procedure will always use an index reorganize instead of a rebuild.
If you set the reorganize threshold to the same value as the rebuild threshold, a rebuild will always be used unless the online option is also specified.  If the online option is specified a reorganize would be used in those cases where it is not possible to perform an online rebuild.
@DatabaseName This is the name of the database where you want to defragment your indexes.
@Online If you are running the Enterprise, Developer or Evaluation editions of SQL Server, this option can be used to perform online index rebuilds.  If it's not possible to do an online index rebuild, a reorganize will be used instead.
@Debug This parameter can be set to 1 if you want to see what commands would be issued by the stored procedure without running them.  In most cases this parameter should be ignored or set to the default value of 0.
@SortInTempDB This parameter affects index rebuild operations by including the SORT_IN_TEMPDB=ON option.  This option can be used to improve the performance of index rebuilds if the storage of the tempdb database is appropriately designed.
@PageCountThreshold It can sometimes be useful to exclude small indexes from your index maintenance scripts.  By default the script will exclude indexes with a page count less than 512 (4MB). 
 
Although the stored procedure has a number of parameters, most of them have default values.  In it's simplest form you can execute the script just by passing in the name of the database you want to defragment:

EXEC dbo.DefragIndexesForDB @DatabaseName='AdventureWorks'

This is equivalent to the statement below:
 
EXEC dbo.DefragIndexesForDB 
      @ReorganizeThreshold=15,
      @RebuildThreshold=30,
      @DatabaseName='AdventureWorks',
      @Online =1
 
In addition to the stored procedure, two tables are also created to track your index maintenance; index_maintenance_batch and index_maintenance_log.  A single row is inserted into the index_maintenance_batch table every time you execute the DefragIndexesForDB stored procedure.  This tracks information such as the start time, end time and parameter options chosen for the index maintenance.  A row is inserted into the index_maintenance_log table for every index rebuild or reorganize operation and is linked to the index_maintenance_batch table by the batch_id column.
 
 
 
Code

Line Numbers: On  Off      Plain Text
/* Prerequsites
	Requires dbo.fnSplitString function:
	http://www.wisesoft.co.uk/scripts/t-sql_split_string_function_while_loop.aspx
	or
	http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
*/
-- Remove objects from DB if they already exists (uncomment if required)
/*
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[index_maintenance_log]') AND type in (N'U'))
DROP TABLE [dbo].[index_maintenance_log]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[index_maintenance_batch]') AND type in (N'U'))
DROP TABLE [dbo].[index_maintenance_batch]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DefragIndexesForDB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DefragIndexesForDB]
GO
*/
CREATE TABLE dbo.index_maintenance_batch(
	batch_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_index_maintenance_batch PRIMARY KEY(batch_id),
	start_time DATETIME NOT NULL,
	end_time DATETIME NULL,
	reorganize_threshold TINYINT NOT NULL,
	rebuild_threshold TINYINT NOT NULL,
	database_name SYSNAME NOT NULL,
	is_online BIT NOT NULL,
	[sort_in_tempdb] BIT NOT NULL,
	page_count_threshold INT NOT NULL,
	excluded_tables NVARCHAR(MAX)NULL
)
GO
CREATE TABLE dbo.index_maintenance_log(
	log_id INT IDENTITY(1,1) CONSTRAINT PK_index_maintenance_log PRIMARY KEY(log_id),
	batch_id INT NOT NULL CONSTRAINT FK_index_maintenance_log_index_maintenance_batch FOREIGN KEY REFERENCES dbo.index_maintenance_batch(batch_id),
	[schema_name] SYSNAME NOT NULL,
	[object_name] SYSNAME NOT NULL,
	index_name SYSNAME NOT NULL,
	index_type_desc NVARCHAR(60) NOT NULL,
	partition_number INT NOT NULL,
	avg_fragmentation_in_percent float NOT NULL,
	is_rebuild bit NOT NULL,
	is_online bit NOT NULL,
	start_time DATETIME NOT NULL,
	end_time DATETIME NULL
)
GO
CREATE PROC [dbo].[DefragIndexesForDB](
	-- Threshold to perform index maintenance. Fragmentation levels below this value will be ignored.
	@ReorganizeThreshold TINYINT=15,
	-- Threshold to rebuild indexes rather than reorganize. 
	-- If you don't want to use rebuild, set the value to >100.  
	-- If you want to rebuild rather than reorganize, set the value to the same as the ReorganizeThreshold
	@RebuildThreshold TINYINT=30,
	-- Database to defrag
	@DatabaseName SYSNAME,
	-- If specified all rebuilds will be done online.  In cases where that is not possible, the index will be reorganized, regardless of the RebuildThreshold
	-- The online option is only available in enterprise, developer and evaluation editions of SQL Server.  Set the rebuild threhold greater than 100 to use a reorganize instead.
	-- Note: It's strongly recommended to perform index maintenance out of hours, even with the online option set to 1
	@Online BIT=1,
	-- If specified, index rebuild statements will be printed and won't be run
	@Debug BIT=0,
	-- Option to sort index in tempdb
	@SortInTempDB BIT=1,
	-- Used to exclude small indexes.  
	@PageCountThreshold INT=512, -- Default value = 512 pages/4MB
	-- Option to exclude tables from index maintenance
	-- Should be a comma separated string of tables names to exclude
	-- e.g. 'dbo.MyExcludedTable,dbo.MyExcludedTable2,dbo.MyExcludedTable3'
	@ExcludeTables NVARCHAR(MAX)=NULL
)
/*  Created:	13/10/2009
	Updated:	01/03/2011
	Version:	1.02
	Author:		David Wiseman
	Website:	http://www.wisesoft.co.uk
	Purpose:	Procedure to defrag indexes for a given database, based on levels of fragmentation specified.
	Notes:		Ignores small indexes (less than 512 pages/4MB) and disabled indexes.
				Requires dbo.fnSplitString function:
				http://www.wisesoft.co.uk/scripts/t-sql_split_string_function_while_loop.aspx
				or
				http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
	Example:
	
	/*		Reorganize all indexed in "AdventureWorks" database with an avg_fragmentation_in_percent between 15 and <30.
			Rebuild indexes in "AdventureWorks" database with an avg_fragmentation_in_percent between greater than or equal to 30
			Perform index maintenance online - either a REBUILD(WITH ONLINE=ON) or a REORGANIZE.  Note: Indexes might use a REORGANIZE
			instead of a REBUILD to satisfy the online requirement. Also, you need enterprise, developer or evaluation editions of
			SQL server to use the online option.
	*/
	EXEC dbo.DefragIndexesForDB
		@ReorganizeThreshold=15,
		@RebuildThreshold=30,
		@DatabaseName='AdventureWorks',
		@Online =1
		
	The thresholds and options above are also the default values so the statement could be simplified to:
	
	EXEC dbo.DefragIndexesForDB @DatabaseName='AdventureWorks'
*/
/*	Updated:	10/02/2011
	Version:	1.01
	Author:		David Wiseman
	Changes:	Code refactoring for initial release.
*/
/*	Updated:	01/03/2011
	Version:	1.02
	Author:		David Wiseman
	Changes:	Added option to exclude tables.
				Modified logging so row is inserted before index is reorganized/rebuilt and end_time is updated afterwards.
				Ensures we have a record of all attempted index maintenance, even if it doesn't complete. Also added missing
				index_name data from index_maintenance_log table.
				
*/
AS
SET NOCOUNT ON;
CREATE TABLE #Frag(
	[object_id] INT,
	index_id INT,
	[object_name] SYSNAME,
	[schema_name] SYSNAME,
	index_name SYSNAME,
	partition_number INT,
	avg_fragmentation_in_percent FLOAT,
	index_type_desc NVARCHAR(60)
);

DECLARE @ObjectID INT;
DECLARE @IndexID INT;
DECLARE @IndexName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @SchemaName SYSNAME;
DECLARE @PartitionNumber INT;
DECLARE @PartitionCount INT;
DECLARE @Frag FLOAT;
DECLARE @IndexType NVARCHAR(60);
DECLARE @IndexSQL NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @BatchID INT;
DECLARE @IsRebuild BIT;
DECLARE @IsOnline BIT;
DECLARE @CanRebuildOnline BIT;
DECLARE @EditionID INT;

SET @EditionID = CONVERT(INT,SERVERPROPERTY ('EditionID'));

-- Check input paramters are valid
IF DB_ID(@DatabaseName) IS NULL
BEGIN;
	RAISERROR('Invalid database name specified',11,1);
	RETURN;
END;
IF @ReorganizeThreshold IS NULL
BEGIN;
	RAISERROR('@ReorganizeThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @RebuildThreshold IS NULL
BEGIN;
	RAISERROR('@RebuildThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @Online IS NULL
BEGIN;
	RAISERROR('@Online parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @Debug IS NULL
BEGIN;
	RAISERROR('@Debug parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @SortInTempDB IS NULL
BEGIN;
	RAISERROR('@SortInTempDB parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @ReorganizeThreshold >= 100
BEGIN
	RAISERROR('The @ReorganizeThreshold value should be less than 100',11,1);
	RETURN;
END
IF @RebuildThreshold < @ReorganizeThreshold 
BEGIN;
	RAISERROR('The @RebuildThreshold value should be greater than or equal to the @ReorganizeThreshold',11,1);
	RETURN;
END;
IF @PageCountThreshold IS NULL
BEGIN;
	RAISERROR('@PageCountThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;

IF @Debug = 0
BEGIN;
	INSERT INTO dbo.index_maintenance_batch(
		start_time,
		reorganize_threshold,
		rebuild_threshold,
		database_name,
		is_online,
		[sort_in_tempdb],
		page_count_threshold,
		excluded_tables
	)
	SELECT GETDATE() as start_time,
		@ReorganizeThreshold,
		@RebuildThreshold,
		@DatabaseName,
		@Online,
		@SortInTempDB,
		@PageCountThreshold,
		@ExcludeTables;
END;

SET @BatchID = SCOPE_IDENTITY();

SET @SQL = 'INSERT INTO #Frag(object_id,index_id,object_name,schema_name,index_name,partition_number,avg_fragmentation_in_percent,index_type_desc)
SELECT ps.object_id,
	ps.index_id,
	o.name,
	s.name,
	si.name,
	ps.partition_number,
	ps.avg_fragmentation_in_percent,
	ps.index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),NULL, NULL, NULL , ''LIMITED'') ps
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.objects o ON ps.object_id = o.object_id 
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.schemas s ON o.schema_id = s.schema_id
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes si on ps.index_id = si.index_id and ps.object_id = si.object_id
WHERE ps.index_id >0
AND ps.page_count > @PageCountThreshold --Exclude small indexes
AND ps.avg_fragmentation_in_percent >= @ReorganizeThreshold
AND si.is_disabled = 0 -- Ignore disabled indexes.
AND index_type_desc IN(''NONCLUSTERED INDEX'',''CLUSTERED INDEX'',''XML INDEX'',''PRIMARY XML INDEX'')' 
+ CASE WHEN @ExcludeTables IS NULL THEN '' ELSE
'
AND NOT EXISTS(SELECT 1
				FROM dbo.fnSplitString(@ExcludeTables,'','') ss 
				WHERE (s.name + ''.'' + o.name) = ss.value
				)' END + '
ORDER BY o.name,ps.index_type_desc';

-- Get index fragmentation stats for specified database
EXEC sp_executesql @sql,N'@DatabaseName SYSNAME,@ReorganizeThreshold INT,@PageCountThreshold INT,@ExcludeTables NVARCHAR(MAX)',
						@DatabaseName,@ReorganizeThreshold,@PageCountThreshold,@ExcludeTables;

DECLARE cFrag CURSOR FAST_FORWARD
	FOR SELECT [object_id],index_id,[object_name],[schema_name],index_name,partition_number,avg_fragmentation_in_percent,index_type_desc
	FROM #Frag;
OPEN cFrag;

FETCH NEXT FROM cFrag INTO @ObjectID,@IndexID,@ObjectName,@SchemaName,@IndexName,@PartitionNumber,@Frag,@IndexType;
WHILE @@FETCH_STATUS = 0
BEGIN;
	-- Check if edition of SQL Server supports online indexing
	IF @EditionID IN(1804890536, -- Enterprise
					-2117995310, -- Developer
					610778273) --Enterprise Evaluation
	BEGIN
		SET @CanRebuildOnline = 1
	END
	ELSE
	BEGIN
		SET @CanRebuildOnline = 0
	END
	
	-- Get Partition Count
	SET @SQL = N'SELECT @PartitionCount = COUNT(*)
				FROM ' + QUOTENAME(@DatabaseName) + '.sys.partitions
				WHERE [object_id] = @objectid 
				AND index_id = @indexid'
				
	EXEC sp_executesql @SQL,N'@ObjectID INT,@IndexID INT,@PartitionCount INT OUT',@ObjectID,@IndexID,@PartitionCount OUT
						
    
    SET @IndexSQL = 'ALTER INDEX ' +  QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName);
 		
	IF @IndexType = 'NONCLUSTERED INDEX' 
	BEGIN;
		-- Check for LOB data types.  Online Rebuilds not supported for LOB data
		SET @SQL = 'IF EXISTS(select * 
								FROM ' + QUOTENAME(@DatabaseName) + '.sys.index_columns ic
								JOIN ' + QUOTENAME(@DatabaseName) + '.sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
								JOIN ' + QUOTENAME(@DatabaseName) + '.sys.types t ON c.user_type_id = t.user_type_id
								where ( t.name IN (''xml'',''text'', ''ntext'',''image'')
										 OR (t.name IN (''varchar'',''nvarchar'',''varbinary'')
											AND c.max_length = -1)
										)
								AND ic.object_id = @ObjectID 
								and ic.index_id = @IndexID
								)
							SET @CanRebuildOnline = 0';
		EXEC sp_executesql @SQL,N'@IndexID int,@ObjectID int,@CanRebuildOnline bit OUT',@IndexID,@ObjectID,@CanRebuildOnline OUT;
	
	END;
	IF @IndexType = 'CLUSTERED INDEX'
	BEGIN;
		-- Check for LOB data types.  Online Rebuilds not supported for LOB data
		SET @SQL =  'IF EXISTS(
					-- This will check is a table contains lob types
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables 
					WHERE lob_data_space_id >0
					AND object_id = @ObjectID
					UNION ALL
					-- Also need to account for indexed views. (Also works with tables)
					-- Ensure we don''t have text,ntext, image or xml columns
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.columns c
					JOIN ' + QUOTENAME(@DatabaseName) + '.sys.types t ON t.system_type_id = c.system_type_id
					WHERE OBJECT_ID = @ObjectID
					AND t.name IN(''text'',''ntext'',''image'',''xml'')
					UNION ALL
					-- ensure we don''t have varchar(max), nvarchar(max) or vibinary(max) columns
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.columns
					WHERE OBJECT_ID = @ObjectID
					AND max_length = -1

					)
					SET @CanRebuildOnline=0';
		EXEC sp_executesql @SQL,N'@ObjectID int,@CanRebuildOnline bit OUT',@ObjectID,@CanRebuildOnline out;
					
	END;
	-- Online index rebuild not supported for XML indexes
	IF @IndexType='XML INDEX'
	BEGIN;
		SET @CanRebuildOnline = 0;
	END;
	-- Online index rebuild not supported for partitioned indexes
	IF @PartitionCount >1
	BEGIN;
		SET @CanRebuildOnline=0;
	END;

	IF @CanRebuildOnline = 1 AND @Online =1 AND @Frag >= @RebuildThreshold
	BEGIN
		-- Online rebuild is performed if the online option is requested, online rebuild is supported and the fragmentation level is greater than the 
		-- rebuild threshold
		SET @IndexSQL = @IndexSQL + ' REBUILD' + 
									CASE WHEN @PartitionCount >1 THEN ' PARTITION = ' + CAST(@PartitionNumber as nvarchar(10)) ELSE '' END + 
										' WITH(ONLINE=ON' + 
											CASE WHEN @SortInTempDB = 1 THEN ',SORT_IN_TEMPDB=ON' ELSE '' END + 
											')';
		SET @IsOnline=1;
		SET @IsRebuild=1;
	END
	ELSE IF @Online = 0 AND @Frag >= @RebuildThreshold
	BEGIN
		-- An "offline" rebuild is performed if the fragmentation level is greater than the rebuild threshold and the user didn't request the online option
		SET @IndexSQL = @IndexSQL + ' REBUILD' +
									CASE WHEN @PartitionCount >1 THEN ' PARTITION = ' + CAST(@PartitionNumber as nvarchar(10)) ELSE '' END + 
									CASE WHEN @SortInTempDB=1 THEN ' WITH(SORT_IN_TEMPDB=ON)' ELSE '' END;
		SET @IsOnline=0;
		SET @IsRebuild=1;
	END
	ELSE
	BEGIN
		-- Reorganize if fragmentation is lower than rebuild threshold or online mode is requested, but is not supported 
		-- (e.g. not running SQL Enterprise, Developer or Evaluation, index contains LOB data etc.)
		SET @IndexSQL = @IndexSQL + ' REORGANIZE';
		SET @IsRebuild=0;
		SET @IsOnline=1;
	END
	
	-- If debug mode is specified, just print the code for the index maintenance
	IF @Debug = 1
	BEGIN;
		PRINT @IndexSQL;
	END;
	ELSE
	BEGIN;
		DECLARE @LogID INT
		INSERT INTO dbo.index_maintenance_log(
			batch_id,
			[schema_name],
			[object_name],
			index_name,
			index_type_desc,
			partition_number,
			avg_fragmentation_in_percent,
			is_rebuild,
			is_online,
			start_time
		)
		SELECT @BatchID,
			@SchemaName,
			@ObjectName,
			@IndexName,
			@IndexType,
			@PartitionNumber,
			@Frag,
			@IsRebuild,
			@IsOnline,
			GETDATE();
		
		SET @LogID = SCOPE_IDENTITY()

		-- Run Index Rebuild
		EXEC sp_executesql @IndexSQL;
		
		UPDATE dbo.index_maintenance_log
			SET end_time = GETDATE()
		WHERE log_id = @LogID;
			
	END;
	
	FETCH NEXT FROM cFrag INTO @ObjectID,@IndexID,@ObjectName,@SchemaName,@IndexName,@PartitionNumber,@Frag,@IndexType;
END;

CLOSE cFrag;
DEALLOCATE cFrag;

IF @Debug=0
BEGIN
	-- set end time
	UPDATE index_maintenance_batch 
		SET end_time = GETDATE()
	WHERE batch_id = @BatchID;

	-- display stats for index maintenance
	SELECT imb.batch_id,
		imb.start_time,
		imb.end_time,
		CONVERT(CHAR(8),DATEADD(s,DATEDIFF(s,imb.start_time,imb.end_time),0),108) AS total_time,
		CONVERT(CHAR(8),DATEADD(ms,ISNULL(SUM(DATEDIFF(ms,iml.start_time,iml.end_time)),0),0),108) AS index_rebuild_reorg_time,
		COUNT(iml.log_id) as index_rebuild_reorg_count,
		SUM(CASE WHEN iml.index_type_desc = 'CLUSTERED INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as clustered_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'CLUSTERED INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as clustered_index_reorgs,
		SUM(CASE WHEN iml.index_type_desc = 'NONCLUSTERED INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as nonclustered_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'NONCLUSTERED INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as nonclustered_index_reorgs,
		SUM(CASE WHEN iml.index_type_desc = 'XML INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as xml_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'XML INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as xml_index_reorgs
	FROM dbo.index_maintenance_batch imb 
	LEFT JOIN dbo.index_maintenance_log iml on imb.batch_id = iml.batch_id
	WHERE imb.batch_id = @BatchID
	GROUP BY imb.batch_id,
		imb.start_time,
		imb.end_time;
END

DROP TABLE #Frag;


GO

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      

Posted On: 8/18/2017 4:06:56 AM

20170819 junda michael kors canada michael kors outlet online ugg boots clearance michael kors outlet swarovski crystal canada goose outlet ray ban sunglasses longchamp outlet online coach outlet online christian louboutin outlet cheap ray bans michael kors outlet online ray ban sunglasses outlet coach outlet online nike air huarache kobe 9 elite coach outlet coach outlet online michael kors outlet store roshe run polo ralph lauren tory burch outlet stores michael kors outlet ralph lauren polo michael kors outlet online cheap oakley sunglasses fred perry polo coach outlet online tods outlet online christian louboutin sale ralph lauren outlet ugg boots clearance cheap oakley sunglasses jerseys wholesale ugg boots outlet prada outlet online nfl jersey cheap ray ban sunglasses polo pas cher uggs outlet polo outlet online coach outlet online coach outlet online michael kors outlet online polo ralph lauren nba jerseys nike shoes cheap jordans oakley sunglasses wholesale snapbacks wholesale air max 90 michael kors handbags clearance max 1 coach outlet snapbacks wholesale mulberry handbags mulberry bags oakley sunglasses sale burberry outlet sale michael kors outlet online polo outlet ugg outlet,ugg outlet store,uggs outlet,ugg outlet online clearance,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale nhl jerseys canada goose outlet store nike trainers uk mbt shoes christian louboutin outlet Basketball Shoes cheap ray ban sunglasses fitflops uk coach outlet online coach factory outlet asics,asics israel,asics shoes,asics running shoes,asics israel,asics gel,asics running,asics gel nimbus,asics gel kayano canada goose jackets on sale tory burch outlet online ferragamo shoes sale ferragamo outlet links of london jewellery oakley sunglasses sale pandora outlet cheap ray ban sunglasses ed hardy outlet oakley sunglasses wholesale coach outlet true religion outlet uk canada goose outlet michael kors handbags coach factory outlet longchamp handbags polo ralph lauren canada goose jackets men michael kors uk cazal outlet adidas nmd runner christian louboutin ugg boots clearance,ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet uggs outlet mulberry handbags coach factory outlet ugg outlet store oakley sunglasses wholesale oakley sunglasses wholesale ralph lauren?uk true religion outlet true religion outlet mont blanc pens michael kors outlet clearance ray ban sunglasses nike shoes ugg clearance michael kors handbags uggs outlet cheap jordans free shipping fitflops uk coach outlet store online canada goose outlet ferragamo shoes coach outlet coach outlet air max trainers michael kors outlet stores cheap oakley sunglasses canada goose outlet online canada goose outlet store michael kors outlet online true religion outlet store coach outlet ugg outlet online michael kors outlet clearance adidas shoes uggs outlet michael kors outlet online kobe shoes longchamp handbags air max 90 chrome hearts outlet ugg outlet stores cheap nfl jerseys coach outlet online michael kors outlet clearance michael kors handbags outlet michael kors uk birkenstock shoes ralph lauren uk christian louboutin shoes cheap oakley sunglasses cheap ray ban sunglasses adidas nmd air force 1 shoes swarovski jewellery tory burch outlet online coach outlet cheap jordan shoes longchamp handbags oakley sunglasses wholesale cheap oakley sunglasses polo outlet stores adidas shoes michael kors outlet christian louboutin christian louboutin uk ugg clearance ralph lauren uk coach outlet online canada goose outlet oakley sunglasses coach outlet store online pandora jewelry chrome hearts online store swarovski outlet burberry outlet store ralph?lauren mbt shoes outlet true religion canada true religion jeans sale vans outlet coach outlet store online adidas shoes canada goose jackets for women kate spade handbags coach outlet online coach factory outlet christian louboutin sale foamposite shoes hermes belts cheap ray bans coach outlet online uggs outlet ugg boots australia polo outlet ugg boots outlet nfl jersey wholesale michael kors outlet online oakley sunglasses wholesale cheap michael kors handbags mlb jerseys prada outlet cheap jordans fitflops nike free running mlb jerseys michael kors outlet clearance pandora jewellery nike outlet store online air max 90 herve leger dresses salomon outlet michael kors outlet clearance oakley sunglasses cheap soccer jerseys ugg outlet ralph lauren polo nike shoes outlet nike air huarache adidas uk store christian louboutin shoes longchamp outlet cheap uggs outlet,cheap uggs for sale,cheap ugg boots,cheap uggs for kids,cheap uggs for women,cheap uggs bailey bow,cheap uggs china,cheap uggs for men,cheap uggs for girls,cheap uggs boots for women ugg boots,ugg boots outlet,cheap uggs boots,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots clearance sale women,ugg boots clearance outlet,ugg boots for men,ugg boots for kids ugg outlet store coach outlet store online uggs outlet lebron shoes ugg outlet canada goose jackets pandora outlet thomas sabo charms mont blanc outlet adidas outlet ralph lauren uk pandora outlet polo outlet store michael kors outlet clearance coach outlet online coach outlet online max 90 coach outlet clearance nike air max 1 michael kors outlet ralph lauren outlet oakley sunglasses wholesale puma shoes cheap uggs mac tools cheap jerseys wholesale kate spade uk canada goose jackets outlet mulberry outlet,mulberry handbags outlet ralph lauren true religion jeans air max 90 michael kors outlet clearance ray ban sunglasses uk ysl outlet online polo outlet stores ralph lauren outlet cheap football shirts nike outlet store online ugg boots clearance michael kors outlet clearance michael kors outlet clearance longchamp handbags christian louboutin shoes cheap ray ban sunglasses ray ban sunglasses uk coach outlet store online ralph lauren michael kors outlet clearance ray-ban sunglasses longchamp outlet ralph lauren coach outlet nike store uk ugg outlet,ugg outlet sale,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store canada goose coats coach outlet coach outlet online coach outlet michael kors outlet online christian louboutin shoes tory burch sandals michael kors outlet online coach factory outlet kd shoes ugg outlet clearance,ugg outlet online clearance,ugg outlet,uggs outlet,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale oakley sunglasses wholesale michael kors outlet nhl jerseys wholesale coach factory outlet michael kors outlet ray ban sunglasses hermes outlet store mulberry bags new balance outlet coach outlet clearance fitflops sale clearance jordan shoes coach factory outlet jordan 4 true religion jeans outlet canada goose uk ray ban sunglasses wholesale coach outlet cheap ray ban sunglasses michael kors outlet online michael kors outlet clearance soccer jerseys wholesale reebok shoes uggs outlet ferragamo shoes nike air max 90 michael kors handbags outlet air max shoes coach outlet coach outlet store online prada outlet cheap jordans for sale michael kors bags ugg boots clearance polo outlet coach outlet online michael kors handbags nike roshe run polo ralph lauren ferragamo outlet polo ralph lauren cheap oakley sunglasses mulberry handbags ugg boots outlet michael kors outlet store cheap oakley sunglasses coach outlet store online reebok shoes bottega veneta outlet online ugg outlet clearance polo ralph lauren outlet coach outlet michael kors outlet clearance cheap oakley sunglasses michael kors outlet online christian louboutin shoes coach factory outlet coach outlet hermes outlet ugg outlet online ralph lauren outlet ralph?lauren?polo?shirts canada goose jackets mac outlet ralph lauren shirts burberry canada mcm backpacks prada sunglasses for women ugg boots for women,ugg boots,ugg boots outlet,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet ray ban wayfarer Nike Free shoes michael kors handbags true religion outlet yeezy boost 350 oakley sunglasses wholesale nfl jerseys nfl jersey wholesale nike shoes uk ugg boots clearance canada goose jackets ralph lauren outlet stores cheap nfl jerseys yeezy boost nike uk store coach outlet clearance nfl jerseys oakley sunglasses wholesale ralph lauren outlet true religion jeans marc jacobs sale fitflops sale clearance longchamp bags michael kors outlet coach outlet oakley sunglasses sale lacoste outlet true religion outlet polo ralph lauren outlet fitflops michael kors outlet clearance michael kors handbags tory burch handbags nike air max 90 hermes birkin bag coach outlet online michael kors outlet online true religion uk adidas shoes adidas outlet tory burch outlet cheap jordan shoes ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet cheap oakley sunglasses kate spade canada goose outlet lebron shoes mulberry outlet pandora jewelery true religion jeans pandora charms burberry outlet store oakley sunglasses cheap ray ban sunglasses mulberry uk cheap oakley sunglasses cheap oakley sunglasses kate spade outlet online nike trainers cheap oakley sunglasses kate spade handbags michael kors outlet polo outlet polo ralph lauren outlet birkenstock shoes nike shoes for women ralph lauren polo coach factory outlet canada goose coach outlet soccer cleats coach outlet store online oakley sunglasses true religion jeans sale cheap oakley sunglasses michael kors outlet online coach outlet clearance supra shoes sale nba jerseys wholesale swarovski uk mulberry outlet coach handbags outlet swarovski jewelry valentino shoes mulberry handbags sale michael kors outlet online christian louboutin outlet canada goose jackets ugg outlet michael kors outlet clearance christian louboutin shoes pandora bracelet los angeles lakers swarovski crystal tory burch outlet online ray-ban sunglasses oakley sunglasses wholesale lacoste polo michael kors uk polo ralph lauren outlet adidas wings shoes coach factory outlet coach outlet online ugg outlet,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale polo ralph lauren outlet coach outlet online cheap jerseys wholesale coach outlet online coach factory outlet coach outlet online cheap nba jerseys nike outle giuseppe zanotti shoes michael kors outlet nike outlet online ralph lauren outlet christian louboutin sale nike roshe run coach factory outlet ugg boots clearance tory burch outlet online nike blazer pas cher ralph lauren outlet kate spade uk canada goose jackets snapback hats canada goose outlet swarovski jewelry ecco outlet polo ralph lauren oakley sunglasses sale true religion jeans cheap jordan shoes michael kors outlet clearance coach outlet online fitflops sale clearance uggs outlet cheap oakley sunglasses cheap ray ban sunglasses lacoste pas cher basketball shoes,basketball sneakers,lebron james shoes,sports shoes,kobe bryant shoes,kobe sneakers,nike basketball shoes,running shoes,mens sport shoes,nike shoes canada goose outlet cheap ray ban sunglasses coach outlet store online ugg boots outlet michael kors outlet online cheap oakley sunglasses polo ralph lauren ferragamo outlet polo ralph lauren michael kors wallets for women nike outlet ray ban sunglasses canada goose jackets lacoste shirts cheap oakley sunglasses cheap mlb jerseys coach outlet coach outlet store online oakley sunglasses wholesale cheap jordan shoes michael kors outlet online ugg outlet,ugg outlet clearance,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet online store,ugg outlet sale,ugg outlet coupons,ugg outlet boots nike air max true religion outlet polo ralph lauren oakley sunglasses wholesale camisetas futbol baratas mbt shoes outlet michael kors outlet clearance ferragamo shoes ray bans michael kors outlet clearance coach factory outlet coach outlet store michael kors outlet clearance ralph lauren outlet coach outlet air max 2015 fitflops sale clearance ugg outlet store pandora charms sale clearance ralph lauren michael kors uk nike trainers fitflops cleveland cavaliers jerseys coach outlet cheap oakley sunglasses michael kors outlet clearance cheap nike shoes cheap ray ban sunglasses kobe bryants shoes converse shoes sale coach factory outlet cheap oakley sunglasses coach outlet online michael kors factory outlet christian louboutin sale ray ban sunglasses outlet pandora outlet ralph lauren outlet


Posted On: 8/18/2017 4:07:12 AM

20170819 junda michael kors canada michael kors outlet online ugg boots clearance michael kors outlet swarovski crystal canada goose outlet ray ban sunglasses longchamp outlet online coach outlet online christian louboutin outlet cheap ray bans michael kors outlet online ray ban sunglasses outlet coach outlet online nike air huarache kobe 9 elite coach outlet coach outlet online michael kors outlet store roshe run polo ralph lauren tory burch outlet stores michael kors outlet ralph lauren polo michael kors outlet online cheap oakley sunglasses fred perry polo coach outlet online tods outlet online christian louboutin sale ralph lauren outlet ugg boots clearance cheap oakley sunglasses jerseys wholesale ugg boots outlet prada outlet online nfl jersey cheap ray ban sunglasses polo pas cher uggs outlet polo outlet online coach outlet online coach outlet online michael kors outlet online polo ralph lauren nba jerseys nike shoes cheap jordans oakley sunglasses wholesale snapbacks wholesale air max 90 michael kors handbags clearance max 1 coach outlet snapbacks wholesale mulberry handbags mulberry bags oakley sunglasses sale burberry outlet sale michael kors outlet online polo outlet ugg outlet,ugg outlet store,uggs outlet,ugg outlet online clearance,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale nhl jerseys canada goose outlet store nike trainers uk mbt shoes christian louboutin outlet Basketball Shoes cheap ray ban sunglasses fitflops uk coach outlet online coach factory outlet asics,asics israel,asics shoes,asics running shoes,asics israel,asics gel,asics running,asics gel nimbus,asics gel kayano canada goose jackets on sale tory burch outlet online ferragamo shoes sale ferragamo outlet links of london jewellery oakley sunglasses sale pandora outlet cheap ray ban sunglasses ed hardy outlet oakley sunglasses wholesale coach outlet true religion outlet uk canada goose outlet michael kors handbags coach factory outlet longchamp handbags polo ralph lauren canada goose jackets men michael kors uk cazal outlet adidas nmd runner christian louboutin ugg boots clearance,ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet uggs outlet mulberry handbags coach factory outlet ugg outlet store oakley sunglasses wholesale oakley sunglasses wholesale ralph lauren?uk true religion outlet true religion outlet mont blanc pens michael kors outlet clearance ray ban sunglasses nike shoes ugg clearance michael kors handbags uggs outlet cheap jordans free shipping fitflops uk coach outlet store online canada goose outlet ferragamo shoes coach outlet coach outlet air max trainers michael kors outlet stores cheap oakley sunglasses canada goose outlet online canada goose outlet store michael kors outlet online true religion outlet store coach outlet ugg outlet online michael kors outlet clearance adidas shoes uggs outlet michael kors outlet online kobe shoes longchamp handbags air max 90 chrome hearts outlet ugg outlet stores cheap nfl jerseys coach outlet online michael kors outlet clearance michael kors handbags outlet michael kors uk birkenstock shoes ralph lauren uk christian louboutin shoes cheap oakley sunglasses cheap ray ban sunglasses adidas nmd air force 1 shoes swarovski jewellery tory burch outlet online coach outlet cheap jordan shoes longchamp handbags oakley sunglasses wholesale cheap oakley sunglasses polo outlet stores adidas shoes michael kors outlet christian louboutin christian louboutin uk ugg clearance ralph lauren uk coach outlet online canada goose outlet oakley sunglasses coach outlet store online pandora jewelry chrome hearts online store swarovski outlet burberry outlet store ralph?lauren mbt shoes outlet true religion canada true religion jeans sale vans outlet coach outlet store online adidas shoes canada goose jackets for women kate spade handbags coach outlet online coach factory outlet christian louboutin sale foamposite shoes hermes belts cheap ray bans coach outlet online uggs outlet ugg boots australia polo outlet ugg boots outlet nfl jersey wholesale michael kors outlet online oakley sunglasses wholesale cheap michael kors handbags mlb jerseys prada outlet cheap jordans fitflops nike free running mlb jerseys michael kors outlet clearance pandora jewellery nike outlet store online air max 90 herve leger dresses salomon outlet michael kors outlet clearance oakley sunglasses cheap soccer jerseys ugg outlet ralph lauren polo nike shoes outlet nike air huarache adidas uk store christian louboutin shoes longchamp outlet cheap uggs outlet,cheap uggs for sale,cheap ugg boots,cheap uggs for kids,cheap uggs for women,cheap uggs bailey bow,cheap uggs china,cheap uggs for men,cheap uggs for girls,cheap uggs boots for women ugg boots,ugg boots outlet,cheap uggs boots,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots clearance sale women,ugg boots clearance outlet,ugg boots for men,ugg boots for kids ugg outlet store coach outlet store online uggs outlet lebron shoes ugg outlet canada goose jackets pandora outlet thomas sabo charms mont blanc outlet adidas outlet ralph lauren uk pandora outlet polo outlet store michael kors outlet clearance coach outlet online coach outlet online max 90 coach outlet clearance nike air max 1 michael kors outlet ralph lauren outlet oakley sunglasses wholesale puma shoes cheap uggs mac tools cheap jerseys wholesale kate spade uk canada goose jackets outlet mulberry outlet,mulberry handbags outlet ralph lauren true religion jeans air max 90 michael kors outlet clearance ray ban sunglasses uk ysl outlet online polo outlet stores ralph lauren outlet cheap football shirts nike outlet store online ugg boots clearance michael kors outlet clearance michael kors outlet clearance longchamp handbags christian louboutin shoes cheap ray ban sunglasses ray ban sunglasses uk coach outlet store online ralph lauren michael kors outlet clearance ray-ban sunglasses longchamp outlet ralph lauren coach outlet nike store uk ugg outlet,ugg outlet sale,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store canada goose coats coach outlet coach outlet online coach outlet michael kors outlet online christian louboutin shoes tory burch sandals michael kors outlet online coach factory outlet kd shoes ugg outlet clearance,ugg outlet online clearance,ugg outlet,uggs outlet,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale oakley sunglasses wholesale michael kors outlet nhl jerseys wholesale coach factory outlet michael kors outlet ray ban sunglasses hermes outlet store mulberry bags new balance outlet coach outlet clearance fitflops sale clearance jordan shoes coach factory outlet jordan 4 true religion jeans outlet canada goose uk ray ban sunglasses wholesale coach outlet cheap ray ban sunglasses michael kors outlet online michael kors outlet clearance soccer jerseys wholesale reebok shoes uggs outlet ferragamo shoes nike air max 90 michael kors handbags outlet air max shoes coach outlet coach outlet store online prada outlet cheap jordans for sale michael kors bags ugg boots clearance polo outlet coach outlet online michael kors handbags nike roshe run polo ralph lauren ferragamo outlet polo ralph lauren cheap oakley sunglasses mulberry handbags ugg boots outlet michael kors outlet store cheap oakley sunglasses coach outlet store online reebok shoes bottega veneta outlet online ugg outlet clearance polo ralph lauren outlet coach outlet michael kors outlet clearance cheap oakley sunglasses michael kors outlet online christian louboutin shoes coach factory outlet coach outlet hermes outlet ugg outlet online ralph lauren outlet ralph?lauren?polo?shirts canada goose jackets mac outlet ralph lauren shirts burberry canada mcm backpacks prada sunglasses for women ugg boots for women,ugg boots,ugg boots outlet,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet ray ban wayfarer Nike Free shoes michael kors handbags true religion outlet yeezy boost 350 oakley sunglasses wholesale nfl jerseys nfl jersey wholesale nike shoes uk ugg boots clearance canada goose jackets ralph lauren outlet stores cheap nfl jerseys yeezy boost nike uk store coach outlet clearance nfl jerseys oakley sunglasses wholesale ralph lauren outlet true religion jeans marc jacobs sale fitflops sale clearance longchamp bags michael kors outlet coach outlet oakley sunglasses sale lacoste outlet true religion outlet polo ralph lauren outlet fitflops michael kors outlet clearance michael kors handbags tory burch handbags nike air max 90 hermes birkin bag coach outlet online michael kors outlet online true religion uk adidas shoes adidas outlet tory burch outlet cheap jordan shoes ugg boots,ugg boots outlet,ugg boots for women,ugg boots on sale,ugg boots clearance,ugg boots for men,ugg boots for kids,ugg boots clearance sale women,ugg boots clearance outlet cheap oakley sunglasses kate spade canada goose outlet lebron shoes mulberry outlet pandora jewelery true religion jeans pandora charms burberry outlet store oakley sunglasses cheap ray ban sunglasses mulberry uk cheap oakley sunglasses cheap oakley sunglasses kate spade outlet online nike trainers cheap oakley sunglasses kate spade handbags michael kors outlet polo outlet polo ralph lauren outlet birkenstock shoes nike shoes for women ralph lauren polo coach factory outlet canada goose coach outlet soccer cleats coach outlet store online oakley sunglasses true religion jeans sale cheap oakley sunglasses michael kors outlet online coach outlet clearance supra shoes sale nba jerseys wholesale swarovski uk mulberry outlet coach handbags outlet swarovski jewelry valentino shoes mulberry handbags sale michael kors outlet online christian louboutin outlet canada goose jackets ugg outlet michael kors outlet clearance christian louboutin shoes pandora bracelet los angeles lakers swarovski crystal tory burch outlet online ray-ban sunglasses oakley sunglasses wholesale lacoste polo michael kors uk polo ralph lauren outlet adidas wings shoes coach factory outlet coach outlet online ugg outlet,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet coupons,ugg outlet boots,ugg outlet online store,ugg outlet sale polo ralph lauren outlet coach outlet online cheap jerseys wholesale coach outlet online coach factory outlet coach outlet online cheap nba jerseys nike outle giuseppe zanotti shoes michael kors outlet nike outlet online ralph lauren outlet christian louboutin sale nike roshe run coach factory outlet ugg boots clearance tory burch outlet online nike blazer pas cher ralph lauren outlet kate spade uk canada goose jackets snapback hats canada goose outlet swarovski jewelry ecco outlet polo ralph lauren oakley sunglasses sale true religion jeans cheap jordan shoes michael kors outlet clearance coach outlet online fitflops sale clearance uggs outlet cheap oakley sunglasses cheap ray ban sunglasses lacoste pas cher basketball shoes,basketball sneakers,lebron james shoes,sports shoes,kobe bryant shoes,kobe sneakers,nike basketball shoes,running shoes,mens sport shoes,nike shoes canada goose outlet cheap ray ban sunglasses coach outlet store online ugg boots outlet michael kors outlet online cheap oakley sunglasses polo ralph lauren ferragamo outlet polo ralph lauren michael kors wallets for women nike outlet ray ban sunglasses canada goose jackets lacoste shirts cheap oakley sunglasses cheap mlb jerseys coach outlet coach outlet store online oakley sunglasses wholesale cheap jordan shoes michael kors outlet online ugg outlet,ugg outlet clearance,uggs outlet,ugg outlet online clearance,ugg outlet store,ugg outlet online store,ugg outlet sale,ugg outlet coupons,ugg outlet boots nike air max true religion outlet polo ralph lauren oakley sunglasses wholesale camisetas futbol baratas mbt shoes outlet michael kors outlet clearance ferragamo shoes ray bans michael kors outlet clearance coach factory outlet