Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 21 February 2018

ERROR 1222 : Lock request time out period exceeded


ERROR 1222: Lock request time out period exceeded.

It says find the offending transaction and terminate it and run the query again. Though sometimes there is a requirement that we cannot terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

  • Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locksdynamic management views.
  • If the transaction is still holding the lock, terminate that transaction if appropriate.
  • Execute the query again.
Alternate Fix/WorkAround/Solution:
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.
4) To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5) If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.
6) also check whether SQL audit features enabled or not
If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock in less time.


Wednesday, 31 January 2018

How to Check Index Fragmentation on Indexes in a Database

The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation.

SELECT dbschemas.[nameas 'Schema',
dbtables.[nameas 'Table',
dbindexes.[nameas 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULLNULLNULLNULLAS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the 'where' clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.
For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient.

The following table summarizes when to use each one:
Reference Values (in %)Action SQL statement
avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD

Automate INDEX rebuild based on fragmentation results?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes
(
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT
)

INSERT INTO #FragmentedIndexes
SELECT
 DB_NAME(DB_ID()) AS DatabaseName
 , ss.name AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , i.name AS IndexName
 , s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
 @RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes