Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 8 March 2018

Microsoft SQL Server Buffer Cache Hit Ratio too low

Microsoft SQL Server Buffer Cache Hit Ratio too low

Occurs when the buffer cache hit ratio falls below the Buffer Cache Hit Ratio lower bound threshold. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read them from the disk. The percentage is calculated as the total number of successful cache hits divided by the total number of requested cache lookups since the last cycle of the resource model.
Each instance of Microsoft SQL Server has a buffer cache. Data remains in the buffer cache until it has not been referenced for some time and the database needs the buffer area to record more data. Data is written back to disk only if it is modified.
Because reading from the cache is less expensive than reading from disk, you want this percentage to be high. After your system maintains a steady state of operation, this metric should achieve rates of 90 or higher. You can increase the buffer cache hit ratio by increasing the amount of memory available to Microsoft SQL Server.
Microsoft SQL Server uses dynamic memory allocation to tune itself. The ideal scenario is for the database server to run on a dedicated machine with dynamic memory allocation in effect. When needed, Microsoft SQL Server increases memory allocation if there is more than 5 megabytes of unallocated memory available. It concedes memory if unallocated memory available falls below 5 megabytes.
When the indication occurs often enough to trigger an event, the event delivers a message to the Tivoli Enterprise Console in the following format:
<application_label>: Microsoft SQL Server <MSSQLServerName> - Buffer Cache Hit 
Ratio is <MSSQLBufferCacheHitRatio>, which is below threshold of <LowerBound>.
If you have Tivoli Business Systems Manager configured for your system, Tivoli Enterprise Console forwards the message to Tivoli Business Systems Manager.
You can check the health of this resource model in the IBM Tivoli Monitoring Web Health Console. For more information, see the IBM Tivoli Monitoring Web Health Console documentation.
The indication has the following attributes:
The registered object's Tivoli Management Environment (TME) class.
The registered object's Tivoli Management Environment (TME) label.
The registered object's Tivoli Management Environment (TME) object identifier.
The managed resource's version; for example, v7, or 2k.
The value set as the minimum allowable for the specified threshold.
The percentage of the buffer cache hits to total requests over the lifetime of an instance. The percentage calculates how often a requested page is found in the buffer cache without requiring disk access. The buffer cache is the portion of the instance that holds pages of data. All user processes that are connected to the instance share access to the buffer cache.
The instance name of Microsoft SQL Server.
This indication has the following threshold:
  • Buffer Cache Hit Ratio lower bound

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',
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?

CREATE TABLE #FragmentedIndexes
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT

INSERT INTO #FragmentedIndexes
 DB_NAME(DB_ID()) AS DatabaseName
 , AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , 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
SET @RebuildIndexesSQL = ''
 @RebuildIndexesSQL = @RebuildIndexesSQL +
 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;'
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes