Tuesday, February 27, 2018

How to check database index status ? whether database index requires rebuild or reorganize ?

Take connection to SQL Server and execute below query

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000 ORDER BY indexstats.avg_fragmentation_in_percent DESC

Above query will give result-set with % of fragmentation on what table and its Index-name.

Anything over 30% is bad but on a small table still would not have a major impact.

Microsoft guidelines is

5% and < = 30% ALTER INDEX REORGANIZE

30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

For reference click here

How to Fix it ?

Execute Below Query

For example to rebuild all the indexes on the SalesOrderDetail table, use the following statement:

ALTER INDEX ALL ON Sales.SalesOrderDetail REBUILD

To reorganize the index you can use below command.

ALTER INDEX ALL ON Sales.SalesOrderDetail REORGANIZE

Note : you can change table name as per your need.

For more interesting database related updates you can follow me on below link.

Wednesday, February 7, 2018

How to find queries creating lock in Microsoft SQL Server Database ?

1. Take connection to database and use below query

USE Master

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0

2. With above query you would get session ID , Wait duration and blocking session id.
Use below query to fetch what query is creating problem

DBCC inputbuffer (Session_ID)

3. With above you should be able to get the exact query causing the problem, now as a DBA you can report this to your development team and if required KILL it using below query

Kill Session_ID

For more interesting database related updates you can follow me on below link.