SQLTeam.com | Weblogs | Forums

Timeoute expired. The timeout period elapsed prior to completion


#1

Hi.
I get this error from SQL "sql server timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding"
I have a table with 52000 rows. For about 2 weeks almost everyday i have to rebuild the index. I have only one clustered index. Once the index is rebuilded the application is working. But next day i find that fragmentation is 45% again.
Can someone give me a permanent solution for this issue?
Thank you

TableName tbl_SLA_Activities
IndexName PK_tbl_SLA_Activities
index_id 1
page_count 4589
index_type_desc CLUSTERED INDEX
avg_fragmentation_in_percent 45.60906516
fragment_count 2113

TABLE [dbo].[tbl_SLA_Activities](
[ActivityId] [int] IDENTITY(1,1) NOT NULL,
[PrestatorProdusId] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Description] nvarchar NULL,
[AddedOn] [datetime] NOT NULL,
[AddedBy] nvarchar NOT NULL,
[UpdatedOn] [datetime] NULL,
[UpdatedBy] nvarchar NULL,
[Tarif] [numeric](18, 10) NULL,
CONSTRAINT [PK_tbl_SLA_Activities] PRIMARY KEY CLUSTERED
(
[ActivityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_SLA_Activities] WITH CHECK ADD CONSTRAINT [FK_tbl_SLA_Activities_tbl_SLA_PrestatorProdus] FOREIGN KEY([PrestatorProdusId])
REFERENCES [dbo].[tbl_SLA_PrestatorProdus] ([ID])
GO

ALTER TABLE [dbo].[tbl_SLA_Activities] CHECK CONSTRAINT [FK_tbl_SLA_Activities_tbl_SLA_PrestatorProdus]
GO


#2

Can you post the SQL for the query that is causing the time out please. My guess is that your query needs better / additional indexes

Did you create the table definition recently? or is it from some time ago? (If it is old maybe something has changed the FILL FACTOR - presumably the Fill Factor [on the Clustered index] is 100%?


#3

the table is old. it worked fine until 2,3 weeks ago. and there are not many rows. 52000.
i'm not sure witch query is causing the problem. i am assuming that is the storage procedure that saves data. but i'm not sure.


#4

Odd situation. My best guess is that the fill factor is very high and the nvarchar column(s) are being increased in length after the row is inserted.

Based on the very limited info available so far, I'd suggest the following:
ALTER INDEX PK_tbl_SLA_Activities ON tbl_SLA_Activities REBUILD WITH ( FILLFACTOR = 95 );

EXEC sp_tableoption 'tbl_SLA_Activities', 'large value types out of row', 'ON'

If you want to really review the index usage more fully, run the script below and post the two query results as columns, not just a long string of text for each row.

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8;

IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL
    DROP TABLE dbo.#index_usage

CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    used_mb decimal(9, 1) NOT NULL,
    rows bigint NULL,
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    UNIQUE CLUSTERED ( object_id, index_id )
    )

DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @list_filegroup_size_summary bit
DECLARE @include_schema_in_table_names bit
DECLARE @table_name_pattern sysname
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint

--NOTE: listing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @list_missing_indexes_summary = 0
SET @list_filegroup_size_summary = 0
SET @include_schema_in_table_names = 0
SET @table_name_pattern = 'tbl_SLA_Activities'
SET @order_by = 1
SET @format_counts = 3
SET @debug = 0

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

DECLARE @is_compression_available bit
DECLARE @sql varchar(8000)

IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'
OR CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%'
    SET @is_compression_available = 0
ELSE
    SET @is_compression_available = 1

SET @sql = '
INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
SELECT
    part.object_id, 
    part.index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    ISNULL(MIN(part.data_compression), 0) AS min_compression,
    ISNULL(MAX(part.data_compression), 0) AS max_compression,' END + '
    MAX(au.total_pages) / 128.0 AS alloc_mb,
    MAX(au.used_pages) / 128.0 AS used_mb,
    MAX(part.rows) AS rows
FROM sys.partitions part
CROSS APPLY (
    SELECT SUM(au2.total_pages) AS total_pages, SUM(au2.used_pages) AS used_pages
    FROM sys.allocation_units au2
    WHERE
        au2.container_id = part.partition_id
) AS au
GROUP BY
    part.object_id, 
    part.index_id
'
IF @debug >= 1
    PRINT @sql
EXEC(@sql)

UPDATE [is]
SET approx_max_data_width = index_cols.approx_max_data_width
FROM #index_specs [is]
INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id, 
        SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
        SELECT ic.object_id, ic.index_id, ic.column_id
            --,object_name(ic.object_id)
        FROM sys.index_columns ic
        WHERE
            ic.object_id > 100
        UNION
        SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
            --,object_name(i_nonclus.object_id)
        FROM sys.indexes i_nonclus
        CROSS APPLY (
            SELECT ic_clus2.column_id
                --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
            FROM sys.index_columns ic_clus2
            WHERE
                ic_clus2.object_id = i_nonclus.object_id AND
                ic_clus2.index_id = 1 AND
                ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
        ) AS ic_clus
        WHERE
            i_nonclus.object_id > 100 AND
            i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id    
) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

UPDATE ispec
SET size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
    SELECT object_id, ROW_NUMBER() OVER(ORDER BY alloc_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
        SELECT object_id, SUM(alloc_mb) AS alloc_mb, MAX(rows) AS rows
        FROM #index_specs
        GROUP BY object_id
    ) AS ispec_allocs        
) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

IF @list_missing_indexes = 1
BEGIN
    SELECT
        IDENTITY(int, 1, 1) AS ident,
        DB_NAME(mid.database_id) AS Db_Name,
        CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
        ispec.size_rank, ispec.alloc_mb,
        CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
             WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
             WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
             ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
        CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id /*, mid.database_id*/) + '.' 
             ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,        
        user_seeks, user_scans, cj1.max_days_active, unique_compiles, 
        last_user_seek, last_user_scan, 
        CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
        CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
        system_seeks, system_scans, last_system_seek, last_system_scan,
        CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
        CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
        mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/
    CROSS JOIN (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
    ) AS cj1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig /*WITH (NOLOCK)*/ ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs /*WITH (NOLOCK)*/ ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    CROSS APPLY (
        SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
        SELECT ispec.alloc_mb, ispec.size_rank
        FROM dbo.#index_specs ispec
        WHERE
            ispec.object_id = mid.object_id AND
            ispec.index_id IN (0, 1)
    ) AS ispec
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1 
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern        
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name,
        CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
        Table_Name,
        equality_columns, inequality_columns,
        user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    IF @list_missing_indexes_summary = 1
    BEGIN
        SELECT 
            derived.Size_Rank, derived.alloc_mb,
            derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks, 
            ISNULL((SELECT SUM(user_seeks)
             FROM #index_missing im2
             CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
             WHERE im2.Size_Rank = derived.Size_Rank AND
                 LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
            ), 0) AS Inequality_Seeks,
            derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
            derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
        FROM (
            SELECT 
                Size_Rank, MAX(alloc_mb) AS alloc_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column, 
                SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
                MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
                MIN(max_days_active) AS Max_Days_Active,
                MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
                (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
                MAX(ds.ItemNumber) AS Equality#
            FROM #index_missing
            CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
            WHERE equality_columns IS NOT NULL
            GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
        ) AS derived
        ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC        
    END --IF
END --IF



PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

-- list index usage stats (seeks, scans, etc.)
SELECT 
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    --ispec.drive AS drv,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb, 
    --ispec.alloc_mb AS alloc_mb_raw,
    CAST(ispec.alloc_mb / 1000.0 AS decimal(9, 4)) AS alloc_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN '' 
         ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [uniq?],
    REPLACE(i.name, ca1.table_name, '~') AS index_name,
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.' 
         ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,
    dps.row_count AS row_count_raw,
    i.index_id,
    ispec.approx_max_data_width AS [data_width],
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE 
        WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date 
        WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date 
        ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,    
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    i.fill_factor,
    ius2.row_num,
    CASE 
        WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
        WHEN ispec.max_compression = 2 THEN 'Page'
        WHEN ispec.max_compression = 1 THEN 'Row'
        WHEN ispec.max_compression = 0 THEN ''
        ELSE '(Unknown)' END AS max_compression,
    FILEGROUP_NAME(i.data_space_id) AS main_fg_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
INTO #index_usage
FROM sys.indexes i /*WITH (NOLOCK)*/
INNER JOIN sys.objects o /*WITH (NOLOCK)*/ ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
        THEN 1 ELSE 0 END AS has_clustered_index
) AS cj2
LEFT OUTER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('') 
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius /*WITH (NOLOCK)*/ ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats /*WITH (NOLOCK)*/
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys /*WITH (NOLOCK)*/
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id

LEFT OUTER JOIN (
    SELECT *
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
) AS dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca1
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND 
     o.name NOT LIKE 'sys%' 
    )
ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name, 
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, 
    key_cols

SELECT *
FROM #index_usage
ORDER BY ident

IF @list_filegroup_size_summary = 1
BEGIN
    SELECT
        LEFT(df.physical_name, 1) AS drive,
        FILEGROUP_NAME(au_totals.data_space_id) AS filegroup_name,
        au_totals.total_mb AS total_fg_mb,
        au_totals.used_mb AS used_fg_mb,
        au_totals.total_mb - au_totals.used_mb AS free_fg_mb,
        CAST(df.size / 128.0 AS decimal(9, 2)) AS file_size_mb
    FROM (
        SELECT
            au.data_space_id,
            CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 2)) AS total_mb, 
            CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 2)) AS used_mb
        FROM sys.allocation_units au
        INNER JOIN sys.filegroups fg ON 
            fg.data_space_id = au.data_space_id
        GROUP BY au.data_space_id WITH ROLLUP
    ) AS au_totals
    INNER JOIN sys.database_files df ON 
        df.data_space_id = au_totals.data_space_id
    ORDER BY filegroup_name, drive
END /*IF*/

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL

#5

I'm probably being thick, but how does a fill factor less than 100% help with an index on IDENTITY?


#6

As I stated above in my earlier comment:

the nvarchar column(s) are being increased in length after the row is inserted.

Technically it would be after enough rows were initially inserted to fill the page, then later the nvarchar column(s) length increased.

P.S.:
That's why I recommended the:
EXEC sp_tableoption 'tbl_SLA_Activities', 'large value types out of row', 'ON'
command: in case it was the "Description" column causing the repeating fragmentation.


#7

Thanks for the explanation Scott. I'm definitely in need of some help to understand this though, sorry about that ...

So ... I have a row with ID = 150. Let's assume that each clustered index page holds 100 entries, so it is in Clustered Index Page 2 along with the other IDs from 101 to 200. I'm also assuming FILL FACTOR 100% and no rows deleted, so Index page 2 is full.

The record size is then increased and the row updated. Clearly it won't fit back where it came from. What happens at that point? (I'd never really thought about it before, in terms of a Clustered Index ...). I suppose I had assumed that the row would be written "somewhere else" and the index updated with the new location, but the ID won't change of course, so still same number of entries in that index page. The bit I'm not understanding is why that would effect fragmentation (in a way that FILL FACTOR would fix).

I found an interesting description of Clustered Indexes by Martin Smith here:

and I had a fiddle with his sample code to try to induce fragmentation by updating an NVarchar(MAX) randomly / hugely but I didn't seem to be able to induce any fragmentation. Maybe my code was wrong ... whether I set 'large value types out of row' ON, or OFF, I still got alloc_unit_type_desc = LOB_DATA values returned from sys.dm_db_index_physical_stats ... so probably something goofy I did.

I can post my test code here if it is of interest.


#8

i ran the above script on TEST server and is working. i get the results:

On PRODUCTION server i get the error:
Msg 195, Level 15, State 10, Line 143
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 158
Incorrect syntax near the keyword 'AS'.

it is about COMPATIBILITY_LEVEL = 80 in my db


#9

From yesterday I don't have any changes (updates, insert, delete) in my table,
But this morning i found avg_fragmentation_in_percent=45%
So i had to rebuild it again :frowning:


#10

Long shot: Do you have a scheduled SHRINK on the database ?

(Or AUTOSHRINK turned on perhaps)


#11

I don't have


#12

If you have another day on which you know there will be no changes to the data in the table, use DBCC IND and DBCC PAGE to examine the storage of the table before and after the fragmentation. These are undocumented commands but well-documented and widely used.

I would also look through the scheduled tasks via SQL Agent, or if you use other scheduling software via those, to see if there are any jobs that can cause the table to be affected.


#13

I wonder if it would be worth putting a trigger on the table, just saving RowCount and Date/Time to a Logging table, to help find if something else is updating rows in the table. Unless there is an easier way to find external tasks that update the table?


#14

Yep, sorry, OBJECT_SCHEMA_NAME giving an error is because of LEVEL 80. Just remove that part, since it's not actually being used anyway.

...
        CASE WHEN @include_schema_in_table_names = 1 THEN 'dbo.' ELSE
...
    CASE WHEN @include_schema_in_table_names = 1 THEN 'dbo.' ELSE 
...

#15

The record size is then increased and the row updated. Clearly it won't fit back where it came from. What happens at that point? (I'd never really thought about it before, in terms of a Clustered Index ...).

SQL Server does a page split: it creates another data page, copies roughly 4,000 bytes of data to the new page, and leaves the other ~4,000 bytes on the existing page. That is the fragmentation.


#16

Thanks Scott. Should I not be seeing the fragmentation with dm_db_index_physical_stats ?

I don't want to pollute this thread I'll post my test code in a new thread and hopefully someone can tell me why it isn't causing fragmentation.

http://forums.sqlteam.com/t/fragmentation-test-of-clustered-index-with-large-lob-updates/6271