SQL Server Online Rebuild Index Bug

 

What do you do if you have a very large table and you can’t take an outage window to rebuild the clustered index offline?

·         Exclude the clustered index from your index maintenance jobs

·         Configure it to only have REORGANIZE operations, not REBUILDS

·         Rebuild Index Online

As Microsoft’s documentation says:

We recommend performing online index operations for business environments that operate 24 hours a day, seven days a week, in which the need for concurrent user activity during index operations is vital.

 

How online index operations work

To allow for concurrent user activity during an index data definition language (DDL) operation, the following structures are used during the online index operation: source and preexisting indexes, target, and for rebuilding a heap or dropping a clustered index online, a temporary mapping index.

·         During an online index operation, such as creating a clustered index on a nonindexed table (heap), the source and target go through three phases: preparation, build, and final.

·         The source is the original table or clustered index data.

·         An online index rebuild starts against an index

·         The preexisting indexes are available to concurrent users for select, insert, update, and delete operations.

·         The target or target is the new index (or heap) or a set of new indexes that is being created or rebuilt. Users insert, update, and delete operations to the source are applied by the Database Engine to the target during the index operation. The target index isn't used until the index operation is committed. Internally, the index is marked as write-only.

·         A modification query runs before the index rebuilds query. At the end of the index rebuild, it tries to get a schema modification lock. It can’t, because the modification query is still running

·         The index rebuild’s super-high-level lock request then causes a massive pile-up behind it– blocking even queries that want to do dirty reads, even NOLOCK can be blocked

The following illustration shows the process for creating an initial clustered index online. The source object (the heap) has no other indexes. The source and target structure activities are shown for each phase; concurrent user SELECT, INSERT, UPDATE, and DELETE operations are also shown. The preparation, build, and final phases are indicated together with the lock modes used in each phase.

 

                                                      

 

Side-Effects of  online Rebuild  index

·         Blocking -. In order to complete Online Rebuild Index operation, SQL server  need a very high level of lock: a schema modification lock (SCH-M) which results in blocking scenarios.

·         Offline Rebuild Index -SQL Server Online Index Rebuild sometimes happens offline without warning and it blocks both read and write queries against the table for long periods.

 

Workaround to avoid blocking

ALTER INDEX [INDEX_NAME]

ON [TABLENAME]

REBUILD WITH ( FILLFACTOR = 80,ONLINE = ON

 (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));

• WAIT_AT_LOW_PRIORITY – is the new setting

• MAX_DURATION – is used to specify the maximum time in minutes to wait until an action

• ABORT_AFTER_WAIT – is used to abort the operation if it exceeds the wait time
• NONE – is to wait for the lock with regular priority
• SELF – exits the online index operation
• BLOCKERS – kills transactions blocking the index rebuild

There’s no pileup! “LOW_PRIORITY” really does mean low priority – the “NOLOCK” query is able to get a shared schema lock and move forward.

 

SQL Code:

CREATE TABLE [dbo].[IndexMaintenanceLog](

                [IndexName] [varchar](200) NULL,

                [TableName] [varchar](200) NULL,

                [CommandText] [varchar](1500) NULL,

                [Status] [varchar](200) NULL,

                [ErrorMessage] [varchar](1500) NULL,

                [CreatedON] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[ IndexOptimize]

     @UseResumable BIT = 0,

    @UseLowPriorityWait BIT = 1

 

AS

BEGIN

    SET NOCOUNT ON;

               

    DECLARE @sql NVARCHAR(MAX), @IndexName SYSNAME, @TableName SYSNAME, @SchemaName SYSNAME, @Frag FLOAT, @IndexTypeDesc NVARCHAR(60);

 

    SELECT

        s.name AS SchemaName,

        t.name AS TableName,

        i.name AS IndexName,

        ips.avg_fragmentation_in_percent AS Fragmentation,

        i.type_desc AS IndexTypeDesc

    INTO #IndexStats

    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips

    INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id

    INNER JOIN sys.tables t ON i.object_id = t.object_id

    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

   

    AND i.name IS NOT NULL

    AND ips.page_count > 100 -- Skip small indexes

    AND ips.index_level = 0 -- Only leaf level

    ORDER BY ips.avg_fragmentation_in_percent DESC;

 

    DECLARE index_cursor CURSOR FOR

    SELECT SchemaName, TableName, IndexName, Fragmentation, IndexTypeDesc FROM #IndexStats;

 

    OPEN index_cursor;

 

    FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag, @IndexTypeDesc;

 

    WHILE @@FETCH_STATUS = 0

    BEGIN

        BEGIN TRY

            IF @Frag >= 30

            BEGIN

                SET @sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH ( FILLFACTOR = 80,ONLINE = ON ('

 

                IF @UseLowPriorityWait = 1

                    SET @sql += 'WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)';

 

                IF @UseResumable = 1

                    SET @sql += ', RESUMABLE = ON, MAX_DURATION = 15 MINUTES';

 

                SET @sql += '));';

            END

            ELSE IF @Frag >= 10

            BEGIN

                SET @sql = N'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE;';

            END

            ELSE

            BEGIN

                FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag, @IndexTypeDesc;

                CONTINUE;

            END

 

                                                PRINT @sql

            EXEC sp_executesql @sql;

 

            INSERT INTO IndexMaintenanceLog (IndexName, TableName, CommandText, Status,CreatedOn)

            VALUES (@IndexName, @TableName, @sql, 'Success',getdate());

        END TRY

        BEGIN CATCH

            INSERT INTO IndexMaintenanceLog (IndexName, TableName, CommandText, Status, ErrorMessage,CreatedOn)

            VALUES (@IndexName, @TableName, @sql, 'Failure', ERROR_MESSAGE(),getdate());

        END CATCH;

 

        FETCH NEXT FROM index_cursor INTO @SchemaName, @TableName, @IndexName, @Frag, @IndexTypeDesc;

    END

 

    CLOSE index_cursor;

    DEALLOCATE index_cursor;

 

    SET NOCOUNT OFF;

END

 

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/how-online-index-operations-work?view=sql-server-ver16

 

https://www.brentozar.com/archive/2015/01/testing-alter-index-rebuild-wait_at_low_priority-sql-server-2014/

 

Image Similarity Search using Vector embeddings and Cosine similarity

 Image embeddings capture visual features (shapes, colors, objects, textures)

Image embedding models are typically CNN or vision transformer based

Image Embedding Generation:

• Instead of embedding text, we embed images using a pre-trained vision model
• Popular models include Vision Transformers (ViT), ResNet, EfficientNet, or CLIP
• The model extracts features from images and converts them to dense vector representations

Core Similarities with Text Search:

• Both methods convert unstructured data (text/images) into vector representations
• Both use similarity metrics (typically cosine similarity) to find closest matches
• Both can be stored in vector databases like AstraDB for efficient retrieval

Applications:

• Product recommendations (visually similar products)
• Reverse image search
• Finding duplicate or near-duplicate images
• Content moderation (finding similar inappropriate content)
• Medical image analysis (finding similar cases)

Python code -

import os

import pandas as pd

import numpy as np

from sklearn.metrics.pairwise import cosine_similarity

from PIL import Image

import torch

from transformers import AutoImageProcessor, AutoModel

# Set device

device = torch.device("cuda" if torch.cuda.is_available() else "cpu")

# Initialize image embedding model - using ViT (Vision Transformer)

model_name = "google/vit-base-patch16-224"

processor = AutoImageProcessor.from_pretrained(model_name)

model = AutoModel.from_pretrained(model_name).to(device)

# Function to get image embedding

def get_image_embedding(image_path):

    image = Image.open(image_path)

    inputs = processor(images=image, return_tensors="pt").to(device)

    with torch.no_grad():

        outputs = model(**inputs)

    # Use CLS token as the image embedding

    embedding = outputs.last_hidden_state[:, 0, :].cpu().numpy().flatten()

    return embedding

# List of image paths

image_paths = [

    "Indigo Nation Men Plain Orange Shirts.jpg",

    "ADIDAS Men Navy Blue Shirts.jpg",

    "Indigo Nation Men Price catch Blue Shirts .jpg",

    "Puma Men's Foundation Grey Polo T-shirt.jpg",

    "Indigo Nation Men  Bling Pink Shirts.jpg"

]

# Query image

query_image = "Indigo Nation Men Plain Orange Shirts.jpg"

# Get embedding for the query image

query_embedding = get_image_embedding(query_image)

# Store all embeddings and their corresponding images

all_embeddings = []

for image_path in image_paths:

    embedding = get_image_embedding(image_path)

    all_embeddings.append(embedding)

# Convert to numpy arrays for similarity calculation

query_embedding_np = np.array(query_embedding).reshape(1, -1)

all_embeddings_np = np.array(all_embeddings)

# Calculate cosine similarity between query and all images

similarities = cosine_similarity(query_embedding_np, all_embeddings_np).flatten()

# Create a DataFrame to display results

results = pd.DataFrame({

    'Image': image_paths,

    'Similarity Score': similarities

})

# Sort by similarity score in descending order

results = results.sort_values('Similarity Score', ascending=False)

print(f"Query image: {query_image}")

print("\nSimilarity Search Results:")

print(results)

# Find the most similar image

most_similar_idx = np.argmax(similarities)

print(f"\nMost similar image: \"{image_paths[most_similar_idx]}\" with similarity score: {similarities[most_similar_idx]:.4f}")

Text Similarity Search using Vector embeddings and Cosine similarity

This code shows how to find semantically similar text using vector embeddings and cosine similarity.

HuggingFaceEmbedding from llama_index is imported to generate text embeddings

BAAI/bge-small-en-v1.5 -  lightweight  embedding model used to convert each text in the weather_descs  list  to vector embeddings.

Cosine similarity helps identify texts with similar meaning by measuring how "aligned" their vector representations are

Python Code -

import os

import pandas as pd

import numpy as np

from sklearn.metrics.pairwise import cosine_similarity

from llama_index.embeddings.huggingface import HuggingFaceEmbedding

# Set TensorFlow environment variable to suppress warnings

os.environ["TF_ENABLE_ONEDNN_OPTS"] = "0"

# Initialize embedding model

embed_model = HuggingFaceEmbedding(model_name="BAAI/bge-small-en-v1.5")

# List of Texts

weather_descs = [

    "The weather cold today",

    "Today is Monday",

    "Today is first Sunday of winter",  

    "Today is Sunday"

]

# Query text

query = "It is freezing today"

# Get embedding for the query

query_embedding = embed_model.get_text_embedding(query)

# Store all embeddings and their corresponding texts

all_embeddings = []

for weather_desc in weather_descs:

    # Get embeddings for the current text

    embedding = embed_model.get_text_embedding(weather_desc)

    all_embeddings.append(embedding)

# Convert to numpy arrays for similarity calculation

query_embedding_np = np.array(query_embedding).reshape(1, -1)

all_embeddings_np = np.array(all_embeddings)

# Calculate cosine similarity between query and all texts

similarities = cosine_similarity(query_embedding_np, all_embeddings_np).flatten()

# Create a DataFrame to display results

results = pd.DataFrame({

    'Text': weather_descs,

    'Similarity Score': similarities

})

# Sort by similarity score in descending order

results = results.sort_values('Similarity Score', ascending=False)

print("Query:", query)

print("\nSimilarity Search Results:")

print(results)

# Find the most similar text

most_similar_idx = np.argmax(similarities)

print(f"\nMost similar text: \"{weather_descs[most_similar_idx]}\" with similarity score: {similarities[most_similar_idx]:.4f}")

Output :

Troubleshooting TempDB Growth in SQL Server

 

Why TempDB Grows and Doesn’t Reset Easily

TempDB Usage Causes: TempDB is a global workspace for SQL Server, used for sorting, hashing, temporary tables, table variables, versioning (snapshot isolation or read-committed snapshot), and many internal If a query requires more memory than the server has allocated (for example, a misestimated join or sort), it will spill to TempDB, writing work tables to disk. Large temporary tables or result sets can also consume significant TempDB space. All these factors can cause TempDB to grow rapidly during heavy queries or long transactions.

Continuous Growth: In this case, a long-running linked server query likely caused internal work tables (spools, hash join overflow, sort runs) in TempDB, which kept allocating pages. TempDB will continue to grow until the operation finishes or TempDB runs out of disk. If nothing intervenes, the TempDB data files remain at the high-water mark size even after the query completes (though the space inside may become free).

No Auto-Shrink by Default: TempDB does not automatically shrink on its own. The only time TempDB’s size resets is at SQL Server startup, when it recreates TempDB from the model database. However, it uses the last configured size (which includes any growths that occurred) as the new initial In our scenario, after the first growth event, TempDB’s files were permanently larger. So even after a restart, TempDB started at that large size – it did not revert to the original smaller size. The space can be reclaimed only by explicitly shrinking the TempDB files or by altering the database file sizes.

Why TempDB Didn’t Shrink on Restart: As explained by Microsoft, when SQL Server restarts, “tempdb is re-created ... and reset to its last configured size The configured size is the last explicit size that was set by a file size changing operation (ALTER DATABASE ... MODIFY FILE or DBCC SHRINKFILE/SHRINKDATABASE)”. In practice, this means any auto-growth or manual growth persists. In our case, TempDB had grown to accommodate the large query, so on restart it was created at that same large size. Only after manually shrinking or altering TempDB would subsequent restarts use a smaller size.

Space Not Freed During Operation: During the long query, attempts to shrink TempDB would likely fail (as the question scenario suggests) because the work tables or allocations were still active. The error about a “worktable on hold” indicates TempDB space was tied up by an active operation, blocking shrinking. Thus, the DBA had to restart SQL Server to break free of the operation and then shrink TempDB to reclaim space

In summary, TempDB grew large because of an expensive operation and stayed large because SQL Server preserved that size setting. This is normal behavior. The key is to identify what caused the growth (the linked server query, in this case) and address that root cause rather than relying on restarts or shrinks.

Common Factors causing TempDB Growth

1.        Large Sort Operations

o    Queries with ORDER BY clauses on large result sets

o    Group By operations requiring intermediate sorting

o    Join operations on non-indexed columns requiring sort operations

2.      Hash Operations

o    Hash joins on large tables

o    Hash aggregates for GROUP BY without appropriate indexes

o    Hash spills to disk when memory grant is insufficient

3.      Row Versioning

o    Transactions using READ_COMMITTED_SNAPSHOT or SNAPSHOT isolation levels

o    Long-running transactions preventing version cleanup

o    Heavy DDL operations under versioning isolation levels

4.      Table Variables and Temporary Tables

o    Extensive use of table variables or #temp tables

o    CTEs that materialize large result sets

o    Nested temporary tables in stored procedures

5.      Index Operations

o    Creating or rebuilding indexes on large tables

o    Statistics updates on large tables

o    Online index operations

6.      DBCC Operations

o    DBCC CHECKDB and similar maintenance tasks

o    Index defragmentation operations

7.       Parallelism

o    Parallel execution plans for complex queries

o    High MAXDOP settings forcing parallel operations

8.      Query Spills

o    Memory grant underestimation causing operations to spill to TempDB

o    Memory pressure forcing in-memory operations to disk

9.      LOB Data Operations

o    Operations on large BLOB/TEXT/XML columns

o    Intermediate storage of large LOB data

10.    Excessive Workspace Memory Usage

o    Window functions on large datasets

o    Recursive CTEs with many iterations

o    Complex analytical functions

11.      Linked Server/Distributed Queries

o    When you use a four-part name in a query (e.g. SELECT ... FROM LocalTable JOIN LinkedServer.RemoteDB.dbo.Table ...), SQL Server’s optimizer may decide to copy entire remote tables into the local TempDB[1] for processing.

o    A linked server query ended up scanning a large remote table and sending millions of rows across the network one by one, ultimately copying the entire table. This kind of behavior would consume enormous TempDB space for intermediate storage (e.g. sorting or hashing that data).

Step-by-Step Troubleshooting TempDB growth Guide

●     Monitor TempDB Space: First, confirm that TempDB is indeed the database consuming the space. You can run sp_spaceused in TempDB or query the DMV sys.dm_db_file_space_usage to see space used by TempDB. For example, SELECT (SUM(user_object_reserved_page_count) + SUM(internal_object_reserved_page_count))*8/1024 AS Tempdb_MB_Used FROM sys.dm_db_file_space_usage;. This will show how many MB are in use. If TempDB is nearly full or unusually large, it’s a red flag.

●     Check for Recent Growth Events: Look at the SQL Server log (or Extended Events if configured) for any messages about TempDB file auto-growth. An auto-growth event will indicate when and how much TempDB expanded. This can correlate with the timing of a query. Also check Windows event logs or alerts if any were triggered for low disk space on the TempDB drive.

●     Identify Sessions Using TempDB Heavily:Use dynamic management views to find which session is consuming TempDB space. Two useful DMVs are sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. These can tell you how many pages each session has allocated in TempDB (both user and internal objects). For example, a query like:

SELECT

    s.session_id,

    r.status,

    (t.internal_objects_alloc_page_count + t.user_objects_alloc_page_count) AS pages_used,

    DB_NAME(r.database_id) AS database_context,

    r.command, r.sql_text

FROM sys.dm_db_task_space_usage AS t

JOIN sys.dm_exec_requests AS r

    ON t.session_id = r.session_id AND t.request_id = r.request_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text

JOIN sys.dm_exec_sessions AS s ON t.session_id = s.session_id

WHERE (t.internal_objects_alloc_page_count + t.user_objects_alloc_page_count) > 0

ORDER BY pages_used DESC;

●     Alternatively, you can use community tools like sp_WhoIsActive (by Adam Machanic) which, when run with appropriate parameters, will list TempDB usage per session. For example, EXEC sp_WhoIsActive @get_tempdb_info=1; will include TempDB allocation for each active query.

Take Emergency Action (If Needed):
 If the system is at risk of crashing due to no space (TempDB or log completely full), you need to free resources by Killing the Offending Session

After the session is gone, TempDB will still be at its grown size (files won’t shrink automatically),you can attempt a manual shrink of TempDB:
USE tempdb;

DBCC SHRINKFILE('tempdev', <desired_size_mb>);

Keep in mind shrinking TempDB is typically a last resort and may cause fragmentation; it’s usually better to leave it if you have the disk space, and adjust the initial size for next startup.

TempDB Configuration Best Practices

Proper TempDB configuration can alleviate some issues and prevent TempDB from becoming a bottleneck or single point of contention:

●     Multiple Data Files: It’s a best practice to use multiple TempDB data files to reduce allocation contention on system pages (PFS, GAM, SGAM). The latest Microsoft guidance suggests one TempDB data file per logical CPU, up to 8 files For example, if your server has 16 cores, start with 8 TempDB files; if it has 4 cores, use 4 files. Having 8 equally sized files often suffices for most workloads . Only add more than 8 if you still observe contention (manifested by PAGELATCH_UP/EX waits on TempDB pages)

●     Preallocate Adequate Size: Configure the initial size of TempDB data files to accommodate typical usage peaks. This avoids frequent auto-growths, which can be expensive. For example, if TempDB tends to use up to 20 GB during heavy operations, set the total initial size of files to 20 GB (spread across files, e.g., 8 files of 2.5 GB each) to start with. This way, SQL Server doesn’t have to pause to grow the files during a heavy query.

●     Autogrowth Settings: Set TempDB files to auto-grow in reasonable chunks (not too small, not too large). Avoid percentage growth settings, as they become inefficient (10% of a 20 GB file is 2 GB, which might be fine, but 10% of a 100 GB file is 10 GB which might be too large a jump and take long to allocate). A fixed growth size of a few hundred MB to a couple GB is typically recommended.

●     Uniform Size and Growth: Ensure all TempDB data files are the same size and have the same growth settings. SQL Server uses a proportional fill algorithm for TempDB files, and if one file is smaller, it can become a hotspot. Monitor after any auto-growth that all files remain equal – if one file grew, manually grow the others to match if needed.

●     TempDB on Fast Storage: Place TempDB on one of the fastest storage volumes available, separate from critical user databases. TempDB is highly I/O intensive for many workloads, so using SSD/NVMe storage for it can significantly improve performance. This doesn’t prevent growth issues per se, but it makes TempDB operations faster and more resilient.

●     TempDB Logging: TempDB has its own log file. Usually one log file is enough (don’t create multiple log files – that doesn’t improve throughput). But do set the TempDB log’s initial size and autogrowth appropriately as well. As noted in one best practice guide, configure TempDB’s log with a large initial size so that on restart it doesn’t start tiny and then have to grow.

●     Regular Maintenance: You generally should not need to shrink TempDB on a schedule. Instead, set it up correctly and let it be. If you encounter an unexpected growth (as in this scenario), afterwards you might shrink it back to normal size during a maintenance window. But avoid setting TempDB to auto-shrink or frequently shrinking it, as that can interfere with performance and will likely just grow again.

Monitoring and Alerting for TempDB Growth

●     SQL Server Agent Alerts: Leverage SQL Server’s built-in alerting mechanism. You can configure an alert on specific error numbers:

○     Alert on Error 1105 (TempDB filegroup full)

sp_add_alert @name='TempDB Full', @message_id=1105, @severity=17, @delay_between_responses=0, @notification_message='TempDB full - investigate session using TempDB'

 

○     You can also set up a performance condition alert. For example, an alert when TempDB data file usage > 80% for over 5 minutes. There is a performance counter Free Space in Tempdb (KB) or you can use a custom condition via a job

●     Continuous Monitoring of TempDB usage: Consider implementing a job that regularly checks TempDB usage and logs the top consumers.

 

●     OS Level Monitoring: Don’t forget to monitor disk space at the OS level. Ensure your infrastructure monitoring checks the drive where TempDB resides and the drive for log files. If free space drops below a threshold (say 15%), alerts should go out. In our scenario, an alert on the disk filling up might have been a final warning before things went completely south.