pivot information: limit

Showing posts with label limit. Show all posts

Wednesday, March 21, 2012

please - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 serverSQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the help
Changing the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>

please - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the helpChanging the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>

please - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the helpChanging the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
--
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>sql

Friday, March 9, 2012

PK still shows fragmentation after ALTER INDEX.. REBUILD

I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can’t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

SELECT object_id AS [Object_Id], CAST('' AS sysname) AS [Table_Nm], index_id AS [Index_Id], CAST('' AS sysname) AS [Index_Nm], avg_fragmentation_in_percent AS [Frag],0 AS [IndexRebuilt_Ind], GetDate() AS [Start_DtTm], GetDate() AS [End_DtTm], '00:00:00' AS [Duration]

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

IF ((SELECT MIN([Index_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id) = 1) AND ((SELECT COUNT([Object_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id AND [Frag] >= @.ip_Frag_Limit) > 0)

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

INSERT [DBA_IndexStatistics] SELECT [Object_Id], [Table_Nm], [Index_Id], [Index_Nm], [Frag], [IndexRebuilt_Ind], [Start_DtTm], [End_DtTm], [Duration] FROM #Tmp_Index_Stats WHERE [IndexRebuilt_Ind] = 1 ORDER BY [Duration] DESC

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

|||No, I realized that. If you look at the code block for the Clustered Indexes, there is a line commented out that runs the ALTER INDEX ALL ON... However, this still shows that the Primary Key Clustered Indexes retain the same fragmentation levels.

PK still shows fragmentation after ALTER INDEX.. REBUILD

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

PK still shows fragmentation after ALTER INDEX.. REBUILD

How are you measuring the extent of the fragmentation?

Thanks

ray

|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||

Can you please post the results of the DBCC SHOWCONTIG statement?

Thanks

Ray

|||

Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set

TableID ,TableName, IndexID ,IndexName ,Fragmentation

87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333

Results from: DBCC SHOWCONTIG (87671360,1)

DBCC SHOWCONTIG scanning 'MyTable' table...

Table: 'MyTable' (87671360); index ID: 1, database ID: 10

TABLE level scan performed.

- Pages Scanned................................: 6

- Extents Scanned..............................: 6

- Extent Switches..............................: 5

- Avg. Pages per Extent........................: 1.0

- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]

- Logical Scan Fragmentation ..................: 83.33%

- Extent Scan Fragmentation ...................: 83.33%

- Avg. Bytes Free per Page.....................: 387.8

- Avg. Page Density (full).....................: 95.21%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.

SET NOCOUNT ON;

DECLARE @.v_Object_Id int;

DECLARE @.v_Index_Id int;

DECLARE @.v_Schema_Nm nvarchar(130);

DECLARE @.v_Object_Nm nvarchar(130);

DECLARE @.v_Index_Nm nvarchar(130);

DECLARE @.v_Rebuild_Stmt nvarchar(2000);

DECLARE @.v_Frag_Flt float

DECLARE @.v_Start_DtTm datetime;

DECLARE @.v_End_DtTm datetime;

DECLARE @.v_Duration_DtTm datetime;

--Uncomment if you are running the code and not the procedure

DECLARE @.ip_Frag_Limit Int

SET @.ip_Frag_Limit = 30

IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))

BEGIN

PRINT 'You cannot execute this procedure in this database'

RETURN;

END

-- We want records in a permenant table so we see if it exists from a previous run

-- If it does exist, then we TRUNCATE it, otherwise, we create it

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[DBA_IndexStatistics]

ELSE

CREATE TABLE [dbo].[DBA_IndexStatistics](

[Object_Id] int NULL,

[Table_Nm] varchar(255) NULL,

[Index_Id] int NULL,

[Index_Nm] varchar(255) NULL,

[Frag] float NULL,

[IndexRebuilt_Ind] BIT NULL,

[Start_DtTm] datetime NULL,

[End_DtTm] datetime NULL,

[Duration] varchar(20) NULL

) ON [PRIMARY]

-- Load our temporary working table that will contain all of our index information

INTO #Tmp_Index_Stats

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

WHERE Index_ID > 0

ORDER BY [Object_Id],[Index_Id];

-- Create a cursor to run through each distinct table object_id from our working table

DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]

OPEN cur_Object

FETCH NEXT FROM cur_Object INTO @.v_Object_Id

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure

BEGIN

-- If true then rebuild the Clustered Index only

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = 1;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1

END

ELSE

-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits

BEGIN

-- Get a list of NonClustered indexes for this one table object_id

DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]

OPEN cur_Index

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.

IF @.v_Frag_Flt >= @.ip_Frag_Limit

-- If index is more fragmented than our limit then rebuild this index

BEGIN

SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @.v_Object_Id;

SELECT @.v_Index_Nm = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;

-- Set Start time before rebuild

SET @.v_Start_DtTm = GetDate()

SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';

EXEC sp_executesql @.v_Rebuild_Stmt

--PRINT @.v_Rebuild_Stmt

-- Set End Time and Duration after rebuild

SET @.v_End_DtTm = GetDate()

SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)

-- Update values in our temp table that we will store in the permenant table.

UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id

END

FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt

END

CLOSE cur_Index

DEALLOCATE cur_Index

END

FETCH NEXT FROM cur_Object into @.v_Object_Id

END

CLOSE cur_Object

DEALLOCATE cur_Object

-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.

-- Drop our temporary table

DROP TABLE #Tmp_Index_Stats

SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC

|||

One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.

Would that explain the behaviour you are seeing?

Wednesday, March 21, 2012

please - limit on # of databases - large number of databases - man

please - limit on # of databases - large number of databases - man

please - limit on # of databases - large number of databases - man

Friday, March 9, 2012

PK still shows fragmentation after ALTER INDEX.. REBUILD

PK still shows fragmentation after ALTER INDEX.. REBUILD

PK still shows fragmentation after ALTER INDEX.. REBUILD

pivot information

Blog Archive

About Me