SQL Server Basic Performance Issue Part-1

SQL Server performance can be degrade by few factors and here we will investigate some of the common areas and look at some of the tools that helps to identify issues and review how to fix these performance issues.

Following topics will be covered:
1. Blocking
2. Deadlocks
3. I/O, CPU and Memory
4. Query Tuning Bookmark Lookups
5. Query Tuning Index Scans

---

1. Blocking
There are several ways you can identify the blocking in SQL Server:
a) sp_who2
Execute sp_who2 in SQL Server query analyser.

b) DMVs

SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests 
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 
AND blocking_session_id > 0
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections 
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id 
                    FROM sys.dm_exec_requests 
                    WHERE blocking_session_id > 0)

c) Report - All Blocking Transactions
Another option is to use the built in reports in SSMS. Right click on the SQL Server instance name and select Reports > Standard Reports > Activity - All Block Transactions.

d) Activity Monitor
In SSMS, right click on the SQL Server instance name and select Activity Monitor. In the Processes section you will see information similar to below. Here we can see similar information as sp_who2, but we can also see the Wait Time, Wait Type and also the resource that SPID 60 is waiting for.