SQL blocking & Header Blocker

Problem

In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID's in SQL Server? Is there only one way to find out which spids are blocking? Are there commands I can run against multiple SQL Server versions? Check out this tip to learn more about locking and blocking.

Solution

Whenever a user contacts the DBA team indicating a process looks hung or a process is not proceeding, checking for database blocking makes a great deal of sense. Blocking happens when one database connection holds a lock and a second connection needs a lock on the same obect. This forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.

You can simple check the blocking by executing below command. 

Sp_who2 active

Below 2 scripts are useful when you are managing the large database environment and facing multiple blocking issues on databases. sometimes there are multiple blocking on database and you need to find out the header blocker and need that data with developer then below scripts are very useful.

--[Script 1] - To find the blocking****************************************

select a.spid,a.blocked,SUBSTRING(text, ( r.statement_start_offset / 2 ) + 1, 

( ( CASE WHEN r.statement_end_offset <= 0 

THEN DATALENGTH(text) 

ELSE r.statement_end_offset END - 

r .statement_start_offset ) / 2 ) + 1) AS statement_text,text, r.percent_complete,DATEDIFF (  

MINUTE , a.last_batch , GETDATE() ) [Min],DATEDIFF ( SECOND , a.last_batch , GETDATE() ) Sec  

,a.lastwaittype,r.start_time ,a.status,r.command,DB_NAME(a.dbid)  

DB,a.program_name,a.loginame,a.hostname,b.client_net_address Client_IP,b.client_tcp_port  

Client_Port,b.local_net_address Local_IP,b.local_tcp_port 

Local_Port,a.cpu,a.physical_io,a.memusage,a.waittime

--,a.last_batch,query_plan 

from

sys.sysprocesses a left join sys.dm_exec_connections b on(a.spid =b.session_id) left join 

sys.dm_exec_requests r on (a.spid=r.session_id) and (b.connection_id =r.connection_id )  

cross

apply sys.dm_exec_sql_text(r.sql_handle) 

cross

apply sys.dm_exec_query_plan(r.plan_handle) 

where

a.status not in('sleeping','background') and 

a.spid<>@@spid order by a.cpu desc

--[Script 2] To find the Header blocker****************************************

  select hostname,blocked, * from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0

and blocked not in (select spid from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0) order by waittime desc

select hostname, blocked,* from master.. sysprocesses with(nolock) where spid in

(select blocked from master.. sysprocesses with(nolock) where  spid<>blocked and blocked<>0 and blocked not in 

(select spid from master.. sysprocesses with(nolock) where spid<>blocked and blocked<>0))

[Note] - You can combinedly run both the scripts to find out the blocking sessions and header blocker at the same time

DB Growth Report

Problem

At some point you are probably going to be asked how quickly your SQL Server database is growing. You could try to create a report based on auto growth's that occurred, but if the data file was initially set large enough no auto growths would be triggered. The T-SQL script in this tip will attempt to give you a pretty good answer to address the growth of the database by looking at backup sizes.

Solution

In this tip I’m going to present you a T-SQL script that presents the data growth for a particular database and its delta compared to the previous month. The script will work only if you have enough historical data in your msdb database, because it makes all the calculations based on the database backup information history.

T-SQL Script

-- Transact-SQL script to analyse the database size growth using backup history.
DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();  -- Include in the statistic all backups from today
SET @months = 12;           -- back to the last 6 months.

;WITH HIST AS
   (SELECT BS.database_name AS DatabaseName
          ,YEAR(BS.backup_start_date) * 100
           + MONTH(BS.backup_start_date) AS YearMonth
          ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
          ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
          ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
    FROM msdb.dbo.backupset as BS
         INNER JOIN
         msdb.dbo.backupfile AS BF
             ON BS.backup_set_id = BF.backup_set_id
    WHERE NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND BF.file_type = 'D'
          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
    GROUP BY BS.database_name
            ,YEAR(BS.backup_start_date)
            ,MONTH(BS.backup_start_date))
SELECT MAIN.DatabaseName
      ,MAIN.YearMonth
      ,MAIN.MinSizeMB
      ,MAIN.MaxSizeMB
      ,MAIN.AvgSizeMB
      ,MAIN.AvgSizeMB 
       - (SELECT TOP 1 SUB.AvgSizeMB
          FROM HIST AS SUB
          WHERE SUB.DatabaseName = MAIN.DatabaseName
                AND SUB.YearMonth < MAIN.YearMonth
          ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
        ,MAIN.YearMonth

Output Of Script 

Note:- This snapshot is for reference which shows only master database data, but the script will gives you user database data

DBCC (Database Console Command)

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

Informational statements

• DBCC INPUTBUFFER
• DBCC SHOWCONTIG
• DBCC OPENTRAN
• DBCC OUTPUTBUFFER
• DBCC PROCCACHE
• DBCC SHOW_STATISTICS
• DBCC SQLPERF
• DBCC TRACESTATUS
• DBCC USEROPTIONS

Validation statements

• DBCC CHECKALLOC
• DBCC CHECKCATALOG
• DBCC CHECKCONSTRAINTS
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKIDENT
• DBCC CHECKTABLE

Maintenance statements

• DBCC CLEANTABLE
• DBCC DBREINDEX
• DBCC DROPCLEANBUFFERS
• DBCC FREEPROCCACHE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• DBCC UPDATEUSAGE

Miscellaneous statements

• DBCC dllname (FREE)
• DBCC HELP
• DBCC FLUSHAUTHCACHE
• DBCC TRACEOFF
• DBCC FREESESSIONCACHE
• DBCC TRACEON
• DBCC FREESYSTEMCACHE
• DBCC CLONEDATABASE (Applies to: SQL Server 2014 (12.x) Service Pack 2 and later versions.)

How to check index fragmentation and resolve it

Index Fragmentation

In this article, you will learn how to identify and resolve Index Fragmentation in SQL Server. Index fragmentation identification and index maintenance are important parts of the database maintenance task. Microsoft SQL Server keeps updating the index statistics with the Insert, Update or Delete activity over the table. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation.

* Find Index Fragmentation status using the T-SQL statement

SELECT t.name as Table_name,
b.name as Index_name,ps.index_type_desc as Index_type,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id INNER JOIN sys.tables as t on b.OBJECT_ID = t.OBJECT_ID
WHERE   b.name not like '%null%' --(this condition for not in heap indexes)
--and t.name ='UserRight'---(For single table index fragmentation , enter a table name)
and ps.avg_fragmentation_in_percent > 0

*REBUILD INDEX

INDEX REBUILD always drops the index and reproduce it with new index pages. This activity can be run in parallel using an online option (Enterprise Edition) with the ALTER INDEX command, which does not affect the running requests and tasks of a similar table.

 REBUILD Index can be set online or offline using the below SQL commands:

 --Basic Rebuild Command

 ALTER INDEX Index_Name ON Table_Name REBUILD

 --REBUILD Index with ONLINE OPTION

 ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)

If a user performs the REBUILD INDEX offline, then the object resource (Table) of the index won’t be accessible till the end of REBUILD process completion. It affects numerous other transactions as well, which are associated with this object. Rebuild index operation recreates the index. Therefore, it generates new statistics and appends the log records of the index in the database transaction log file too.

REBUILD clustered index over the table affects other indexes of the table as well because the REBUILD clustered index rebuilds the non-clustered index of the table as well. Perform rebuild operation on all indexes of the table or database together; a user can use DBCC DBREINDEX() command.
 

DBCC DBREINDEX ('DatabaseName', 'TableName');

*REORGANIZED INDEX

The REORGANIZE INDEX command reorders the index page by expelling the free or unused space on the page. Ideally, index pages are reordered physically in the data file. REORGANIZE does not drop and create the index but simply restructure the information on the page. REORGANIZE does not have any offline choice, and REORGANIZE does not affect the statistics compared to the REBUILD option. REORGANIZE performs online always.

 ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE

To perform the REORGANIZE index operation on all indexes of the table or database together, the user can use the DBCC INDEXDEFRAG() command:

DBCC INDEXDEFRAG('DatabaseName', 'TableName');

REBUILD option is more useful with the ONLINE option when the database is not available to take index maintenance in off-peak hours.

*Conclusion

Index Fragmentation is an internal fragmentation in the data file. Core parameters of quick performance of your database are the Database Architecture, Database Design, and Query writing. A Good index design with maintenance always boosts the query performance in the database engine.

Data File Movement in Sql Server

*Purpose of file Movement

You may need to move the DB file’s on other drive location due to following reasons.

1] In case of drive storage is full and you are not able to extend the drive storage.
2] The R/W speed of drive is very low and facing high disk I/O for the drive.
3] Log file and data files are in same location, As per best practices which should not be on same drive.

*Pre-requisites

In case a database is being used by any Windows services or other resources, these must be stopped in order to allow altering SQL database files.
Also, any existing connections to a database must be closed. Before the first step, 
make sure to locate the appropriate MDF and LDF files for a database you want to work with. By default, these names are in the following format:
Database_name_Data.mdf – for MDF file
Database_name_log.ldf – for LDF file
The above mentioned format does not need to be necessarily used, so make sure you are targeting correct files.
run 'Sp_helpfile' on DB to check the file location
Take database full backup for safe side. (if possible)

*Moving database files to another location 

Run the following SQL script to set a new location for SQL database files:

Use [Master]
Go
ALTER DATABASE AdventureWorks2014   
    MODIFY FILE ( NAME = AdventureWorks2014_Data,   
                  FILENAME = 'E:New_locationAdventureWorks2014_Data.mdf');  
GO

 *Run the following SQL script to take a SQL database offline:

Use [Master]
Go
ALTER DATABASE AdventureWorks2014 SET OFFLINE;  
GO

This is important in order to perform the next step. If a database is being used by any application, this step cannot be accomplished, 
unless all connections to a database are closed.

* Move the physical files to new location

Move MDF /LDF files of the specific SQL database to a new location specified in the statement above. 
This means to simply cut mentioned files from the existing location and to move them to a newly specified one.

Important note: Make sure that SQL Server can access the specified location. Otherwise, access denied error will occur when you start the DB services.
Also if the file size is large (in TB) use copy paste method and rename the old file once file is copied on new location to avoid any corruption during copy-paste.

*Run the following SQL script to take a SQL database Online

Use [Master]
Go
ALTER DATABASE AdventureWorks2014 SET OFFLINE;  
GO

*POA

1] Take DB Backup and note down the DB files location.
2] Run file movement command on DB
3] Make the DB offline
4] Move the physical file into new path
5] Make the DB online

Shrink TempDB Without SQL Server Restart

Question:

How to Shrink TempDB Without SQL Server Restart?

Answer: 

Some times we are not able to shrink the tempdb files following simple shrink commands. And DBA directly suggesting for SQL services restart. Which shows lack of experience.

We can run below script and shrink the tempdb files whithout taking sql services restart.

Warning: These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely necessary.

[Script] 

Use [Tempdb] CHECKPOINT;

GO

DBCC DROPCLEANBUFFERS;

GO 

DBCC FREEPROCCACHE;

GO

DBCC FREESYSTEMCACHE ('ALL');

GO 

DBCC FREESESSIONCACHE;

GO

DBCC SHRINKFILE (TEMPDEV, 256);   --- file size in MB

GO

DBCC SHRINKFILE (TEMP1, 256); 

*DBCC DROPCLEANBUFFERS

Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.

*DBCC FREEPROCCACHE

Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.

*DBCC FREESYSTEMCACHE

This operation is similar to FREEPROCCACHE, except it affects other types of caches.

*DBCC FREESESSIONCACHE

Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.

* DBCC SHRINKFILE

DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.

Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the blocking and DBCC operation will fail , and possibly corrupt your tempdb !