February 18, 2014 at 3:08 pm
Hi All,
We have script that loops each index in the database and for every Index, it feteches its Logical, Physical fragmentation values, and depending on certain Threshold it Rebuild's those Indexes.
The way we get the fragmentaion values is using the DMF sys.dm_db_index_physical_stats. We pass in the parameters DATABASE_ID, OBJECT_ID, INDEX_ID for every index in the loop. We do not have any partitioned Indexes, so I left the PARTITION_ID parameter to NULL. I am using the "DETAILED" mode, as this is the only mode that will give me "Physical Fragmentaion"(Avg Page Space Used Percent), I only get the fragmentaion report for the leaf level as so, I have a condition "WHERE Index_Level = 0".
Problem:
This script was working well for more than a 7 months. It started having issues suddenly for the last two weeks. This week it is has been running for the past 48 hours(Still Running). The DMF/DMV is stilll running to fetch the index stats.
Observations:
I have looked at dm_tran_lock DMV at diffrent points in time and observed that the DMF has been working on a Table "T1", for the past 48 hours. It is baffeling that is working on the same table for such period of time.
Looked at the waitype for the session. It is OLEDB.
It has 4 indexes on that table including the Clustred Index.
Below are the Index Partition Statistics:
TableNameIndexName partition_id reserved_page_count row_count allocation_unit_id Allocation_Unit_type
T1T1_ClustredIndex 72057595598602240 180559 16482128 72057595676590080 IN_ROW_DATA
T1T1_NonClustredIndex_1 72057595598667776 99120 16482128 72057595676655616 IN_ROW_DATA
T1T1_NonClustredIndex_2 72057595598733312 47367 16482128 72057595676721152 IN_ROW_DATA
T1T1_NonClustredIndex_3 72057595582283776 62186 16482128 72057595660271616 IN_ROW_DATA
Looking at thepartiton stats I really doubt if the problem is caused due to the size of the Index. As we can see above clustred index has 180,000 pagecount. Previuosly I have tested the script for Indexes double the size.
It doest not have any LOB, ROW_OVERFLOW pages all are IN_ROW pages.
Looking at all the above I did not know what else to look for. I did not find any thing on the internet that has a similar issue. Can any one let me know if some has faced a similar issue, and can any one of you direct me in the right direction?
Imporatant Note: Alter Index operation is not the issue. The issue is with the DMF.
February 18, 2014 at 3:27 pm
How is your memory looking on that server?
Do you have any signs of some memory pressure?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 3:29 pm
OLEDB waittype indicates it's waiting for something outside the SQL Server. How are you executing the scipt? Through an OLEDB connection in SSIS?
http://www.confio.com/logicalread/sql-server-oledb-wait-type/
February 18, 2014 at 4:10 pm
How is your memory looking on that server?
Do you have any signs of some memory pressure?
I have run below 2 queries for looking at the memeory details.
SELECT
((SI.physical_memory_in_bytes/1024.0)/1024.0)/1024.0 AS PhysicalMemoryInstalled
FROM
sys.dm_os_sys_info SI
SELECT
pc.counter_name,
(pc.cntr_value/1024.0)/1024.0 AS Value
FROM
sys.dm_os_performance_counters PC
WHERE
PC.counter_name LIKE '%Total Server Memory%'
Results are as follows:
PhysicalMemoryInstalled
127.9969520568847656
counter_name Value
Total Server Memory 118.454391479492
I do not see any transactions that are waiting for Memory Grant.
I also do not see any memory related Wait Types aggregated in the TOP aggregated waits on the system.
OLEDB waittype indicates it's waiting for something outside the SQL Server. How are you executing the scipt? Through an OLEDB connection in SSIS?
No I am not using any kind of application code or SSIS. It is simpe T-SQL script that is triggred by a SQL agent Job.
According to Paul randalls Blog OLEDB wait type is not necessarly caused an external client application or a Linked Server. He says that DMV/DMFs also accumulate OLEDB wait types. Pardon me if I misinterpreted that line. Below is the Paul's statement from SQL Skills website (http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/).
OLEDB:
As its name suggests, this is a wait for something communicating using OLEDB – e.g. a linked server. However, OLEDB is also used by all DMVs and by DBCC CHECKDB, so don’t assume linked servers are the problem – it could be a third-party monitoring tool making excessive DMV calls. If it *is* a linked server, go to the linked server and do wait stats analysis there to figure out what the performance issue is there.
Thanks,
Nawaz.
February 18, 2014 at 7:17 pm
Can you run the dmf to get index frag info for a larger table alone? While this one is running?
Wondering if it just decided to get hung and stalled.
I have seen it decide to never return when employed through a cursor. But that is not to say that the cursor you are running is running the same way.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 9:13 pm
Hi All,
The issue has been solved. I am extremely sorry for all the confusion I caused. There was a bug in my script that caused it go in a infinite loop.
Basically, My script does not look at Leaf Level alone. It also looks at other non leaf levels other than the ROOT node. In my previous post I had mentioned that the script looks only the Leaf level, I told so to keep things simple. So the problem was with my script going in an infinite loop. I have corrected that.
Below is the script for Reference. The line that caused the Bug is made Bold and Underlined. Let me know if you see any other glitches.
/*============================================================================
Summary: Script to Re-Build Indexes excluding LOB Indexes.
============================================================================*/
SET NOCOUNT ON
/*===============================================================================================================
Set the below threshholds as required.
================================================================================================================*/
DECLARE @avgFragThresholdAtLeaf INT = 15
DECLARE @avgFragThresholdAtNonLeaf INT = 15
DECLARE @avgSpaceUsedThresholdAtLeaf INT = 85
DECLARE @avgSpaceUsedThresholdAtNonLeaf INT = 85
DECLARE @pageCountThresholdAtLeafForExternalFrag INT = 200
DECLARE @pageCountThresholdAtLeafForInternalFrag INT = 10
DECLARE @pageCountThresholdAtNonLeafForExternalFrag INT = 100
DECLARE @pageCountThresholdAtNonLeafForInternalFrag INT = 10
/*================================================================================================================*/
SELECT
IDENTITY(INT, 1,1) AS LineId,
SCHEMA_NAME(O.schema_id)+'.'+O.name AS TableName,
O.[object_id],
I.name AS IndexName,
I.index_id,
I.fill_factor
INTO
#tempAllIndexesExcludingLOB
FROM
SYS.objects O
JOIN SYS.indexes I ON O.object_id = I.object_id
JOIN SYS.dm_db_partition_stats PS ON PS.object_id = I.object_id AND PS.index_id = I.index_id
WHERE
OBJECTPROPERTY(O.object_id, 'isMSShipped') = 0
AND I.name NOT IN
(
SELECT DISTINCT
I.name
FROM
SYS.objects O
JOIN SYS.indexes I ON O.object_id = I.object_id
JOIN SYS.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
JOIN SYS.allocation_units AU ON P.partition_id = AU.container_id
AND OBJECTPROPERTY(O.object_id, 'isMSShipped') = 0 AND O.name <> 'sysdiagrams'
AND AU.type IN(2, 3)
)
AND O.name <> 'sysdiagrams'
AND PS.row_count <> 0
AND I.index_id > 0
AND O.schema_id <> SCHEMA_ID('dbo')
ORDER BY
O.name, I.index_id ASC
--SELECT * FROM #tempAllIndexesExcludingLOB
DECLARE @lineID INT, @tableName VARCHAR(500), @indexName VARCHAR(500), @objectID BIGINT, @indexID INT, @fillFactor INT,@wasRebuilt BIT
DECLARE @avgFragInPercent INT, @avgSpaceUsedInPercent INT, @indexDepth SMALLINT, @pageCountAtLevel BIGINT, @indexLevel SMALLINT
DECLARE @avgFragInPercent_Cnt INT = 0, @avgSpaceUsedPercent_Cnt INT = 0
DECLARE @sql VARCHAR(5000)
CREATE TABLE #tempQualifiedIndex
(
LineID INT,
TableName VARCHAR(500),
ObjectID BIGINT,
IndexName VARCHAR(500),
IndexID INT,
AvgFragInPercent INT,
AvgSpaceUsedInPercent INT,
IndexDepth SMALLINT,
WasRebuilt BIT,
IndexLevel SMALLINT,
Cause VARCHAR(500),
[PageCountAtLevel] BIGINT,
CreatedDate DateTime
)
SELECT TOP(1)
@lineID = LineId,
@tableName = TableName,
@objectID = object_id,
@indexName = IndexName,
@indexID = index_id,
@fillFactor = fill_factor
FROM
#tempAllIndexesExcludingLOB
ORDER BY
LineId ASC
SET @wasRebuilt = 0
WHILE((SELECT COUNT(1) FROM #tempAllIndexesExcludingLOB) > 0)
BEGIN
--Getting The Index Fragmentation Details at Index Level 0
SELECT
@avgFragInPercent = DPS.avg_fragmentation_in_percent,
@avgSpaceUsedInPercent = DPS.avg_page_space_used_in_percent,
@pageCountAtLevel = DPS.page_count,
@indexDepth = DPS.index_depth,
@indexLevel = DPS.index_level
FROM
SYS.dm_db_index_physical_stats(DB_ID(), @objectID, @indexID, NULL, 'DETAILED') AS DPS
WHERE
DPS.index_level = 0
IF @fillFactor = 0
SET @fillFactor = 90
--Check for External Fragmentation And Page Count at Index Level 0
IF(@avgFragInPercent > @avgFragThresholdAtLeaf AND @pageCountAtLevel > @pageCountThresholdAtLeafForExternalFrag)
BEGIN
SET @sql = 'ALTER INDEX '+@indexName+' ON '+@tableName+' REBUILD WITH(ONLINE = ON, FILLFACTOR = '+CAST(@fillFactor AS VARCHAR)+', SORT_IN_TEMPDB = ON)'
EXEC (@sql)
--If Rebuilt Here
SET @wasRebuilt = 1
PRINT @sql
INSERT INTO #tempQualifiedIndex
SELECT
@lineID AS LineID,
@tableName AS TableName,
@objectID AS ObjectID,
@indexName AS IndexName,
@indexID AS IndexID,
@avgFragInPercent AS AvgFragInPercent,
@avgSpaceUsedInPercent AS AvgSpaceUsedInPercent,
@indexDepth AS IndexDepth,
@wasRebuilt AS WasRebuilt,
@indexLevel AS IndexLevel,
'AvgFragmentationInPercent at leaf level > '+CAST(@avgFragThresholdAtLeaf AS VARCHAR) AS [Cause],
@pageCountAtLevel,
GETDATE()
SET @avgFragInPercent_Cnt = @avgFragInPercent_Cnt+1
END
--Verify if the Index was already re-built and then Check for Internal Fragmentation And Page Count at Index Level 0
IF(@wasRebuilt = 0 AND @avgSpaceUsedInPercent < @avgSpaceUsedThresholdAtLeaf AND @pageCountAtLevel > @pageCountThresholdAtLeafForInternalFrag)
BEGIN
SET @sql = 'ALTER INDEX '+@indexName+' ON '+@tableName+' REBUILD WITH(ONLINE = ON, FILLFACTOR = '+CAST(@fillFactor AS VARCHAR)+', SORT_IN_TEMPDB = ON)'
EXEC (@sql)
--If Rebuilt Here
SET @wasRebuilt = 1
PRINT @sql
INSERT INTO #tempQualifiedIndex
SELECT
@lineID AS LineID,
@tableName AS TableName,
@objectID AS ObjectID,
@indexName AS IndexName,
@indexID AS IndexID,
@avgFragInPercent AS AvgFragInPercent,
@avgSpaceUsedInPercent AS AvgSpaceUsedInPercent,
@indexDepth AS IndexDepth,
@wasRebuilt AS WasRebuilt,
@indexLevel AS IndexLevel,
'AvgPageSpaceUsedInPercent at leaf level is < '+CAST(@avgSpaceUsedThresholdAtLeaf AS VARCHAR) AS [Cause],
@pageCountAtLevel,
GETDATE()
SET @avgSpaceUsedPercent_Cnt = @avgSpaceUsedPercent_Cnt + 1
END
--If the index was not rebuilt above, then check for fragmentation at other index levels
IF(@wasRebuilt = 0)
BEGIN
--Excluding the root index level, check for fragmentations on the other index levels
WHILE(@indexDepth-1 > 1 AND @wasRebuilt = 0)
BEGIN
SELECT
@avgFragInPercent = DPS.avg_fragmentation_in_percent,
@avgSpaceUsedInPercent = DPS.avg_page_space_used_in_percent,
@pageCountAtLevel = DPS.page_count,
@indexDepth = DPS.index_depth,
@indexLevel = DPS.index_level
FROM
SYS.dm_db_index_physical_stats(DB_ID(), @objectID, @indexID, NULL, 'DETAILED') DPS
WHERE
DPS.index_level = @indexDepth-2
--Check For External Fragmentation And Page Count at the Non-Leaf index Level
IF(@avgFragInPercent > @avgFragThresholdAtNonLeaf AND @pageCountAtLevel > @pageCountThresholdAtNonLeafForExternalFrag)
BEGIN
SET @sql = 'ALTER INDEX '+@indexName+' ON '+@tableName+' REBUILD WITH(ONLINE = ON, FILLFACTOR = '+CAST(@fillFactor AS VARCHAR)+' , SORT_IN_TEMPDB = ON)'
EXEC (@sql)
--If Rebuilt
SET @wasRebuilt = 1
PRINT @sql
INSERT INTO #tempQualifiedIndex
SELECT
@lineID AS LineID,
@tableName AS TableName,
@objectID AS ObjectID,
@indexName AS IndexName,
@indexID AS IndexID,
@avgFragInPercent AS AvgFragInPercent,
@avgSpaceUsedInPercent AS AvgSpaceUsedInPercent,
@indexDepth AS IndexDepth,
@wasRebuilt AS WasRebuilt,
@indexLevel AS IndexLevel,
'AvgFragmentationInPercent at non leaf level > '+CAST(@avgFragThresholdAtNonLeaf AS VARCHAR) AS [Cause],
@pageCountAtLevel,
GETDATE()
SET @avgFragInPercent_Cnt = @avgFragInPercent_Cnt+1
END
--Verify if the Index was already re-built and then Check for Internal Fragmentation And Page Count at Non-Leaf Level
IF(@wasRebuilt = 0 AND @avgSpaceUsedInPercent < @avgSpaceUsedThresholdAtNonLeaf AND @pageCountAtLevel > @pageCountThresholdAtNonLeafForInternalFrag)
BEGIN
SET @sql = 'ALTER INDEX '+@indexName+' ON '+@tableName+' REBUILD WITH(ONLINE = ON, FILLFACTOR = '+CAST(@fillFactor AS VARCHAR)+' , SORT_IN_TEMPDB = ON)'
EXEC (@sql)
--If Rebuilt
SET @wasRebuilt = 1
PRINT @sql
INSERT INTO #tempQualifiedIndex
SELECT
@lineID AS LineID,
@tableName AS TableName,
@objectID AS ObjectID,
@indexName AS IndexName,
@indexID AS IndexID,
@avgFragInPercent AS AvgFragInPercent,
@avgSpaceUsedInPercent AS AvgSpaceUsedInPercent,
@indexDepth AS IndexDepth,
@wasRebuilt AS WasRebuilt,
@indexLevel AS IndexLevel,
'AvgPageSpaceUsedInPercent at non leaf level < '+CAST(@avgSpaceUsedThresholdAtNonLeaf AS VARCHAR) AS [Cause],
@pageCountAtLevel,
GETDATE()
SET @avgSpaceUsedPercent_Cnt = @avgSpaceUsedPercent_Cnt + 1
END
----------------------
SET @indexDepth = @indexDepth - 1
END
END
------------------
DELETE FROM #tempAllIndexesExcludingLOB
WHERE LineId = @lineID
SELECT TOP(1)
@lineID = LineId,
@tableName = TableName,
@objectID = [object_id],
@indexName = IndexName,
@indexID = index_id,
@fillFactor = fill_factor
FROM
#tempAllIndexesExcludingLOB
ORDER BY
LineId
SET @wasRebuilt = 0
END
SELECT @avgFragInPercent_Cnt AS RebuiltDueToFrag, @avgSpaceUsedPercent_Cnt AS RebuiltDueToSpaceUsed
SELECT
LineID,
TableName,
ObjectID,
IndexName,
IndexID,
AvgFragInPercent,
AvgSpaceUsedInPercent,
IndexDepth,
WasRebuilt,
IndexLevel,
Cause,
[PageCountAtLevel],
CreatedDate
FROM
#tempQualifiedIndex
ORDER BY
TableName, IndexId
DROP TABLE #tempAllIndexesExcludingLOB
DROP TABLE #tempQualifiedIndex
SET NOCOUNT OFF
Regards,
Nawaz.
February 19, 2014 at 7:28 am
Glad to hear that you have it fixed and working now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply