What is Tempdb contention?

 

Tempdb contention is a performance issue which occurs when multiple sessions try to access same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.

Few common reasons of tempdb contention are given below:

1. If multiple big/heavy queries are running on the server simultaneously, that can create TempDB contention.

2. TempDB contention occurs if we use multiple temporary tables in our queries or SP. If the query is using multiple indexes, it'll also help to raise TempDB contention.

3. If multiple transactions are using snapshot isolation or performing online index operations simultaneously, one of reason to create TempDB contention.

How to address TempDB contention issue:-

1. Monitor TempDB usage using profiler, perfmon tool, DMVs etc.

2. Work on query optimisation to enhance performance of query. Use CTE in plan of Temporary Tables.

3. If possible, increase no. of tempdb data files as well as size of all files should be same. Also increasing file size will help to avoid this issue.

4. Another point is to use snapshot isolation when necessary and needed.   

Rebuild System Database in SQL Server

 

The rebuild process of system databases is a complex and risky task in the SQL server.

This should be handle with care & should be performed by experience database administrator.

so it's important to carefully follow the necessary steps. Steps to rebuild the system databases in SQL Server is given below:

1. Check whether you have latest db backup of all system & user databases.

2. If possible, take help of Windows Team to take Cohesive/Snapshot backup for safer side.

3. Stop SQL Server services as well as SQL Server Agent.

4. Change the directory which has SQL server Installation media.

5. Run the following command in command prompt.

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer

/SQLSYSADMINACCOUNTS=<AccountNameWithAdminRights> /SAPWD=password /SQLCOLLATION=collation

6. Press enter to start installation process.

7. During the installation process, select option to rebuild system databases.

8. Now need to select authentication mode and specify sa password.

Next Step.....Coming Soon...

How to Move System Database

 

To move system databases from one place to another is a complex process that requires proper planning and implementation steps. Step-by-step implementation plane to move system databases from one place to another is given below:

1. Proper Migration Plan is required.

2. If possible, include backup team to take cohesive / snapshot at their end.

3. Drop mail to application team for Pre-activity intimation.

4. Drop mail to monitoring team to include the server in maintenance mode.

5. Run the pre-health check for server as well as databases, services, drive spaces etc.