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.

SQL Server Reporting Services Migration

Migrate Reporting Services Database to another Instance for 2008/2012

Let suppose there are two SQL Server Instances:
SQLServerA
SQLServerB
Here we are migrating Reports from
Source Server - SQLServerA to Target Server - SQLServerB

1). Backup the encryption key and the RS Databases - ReportServer & ReportServerTempdb database from SQLServerA
2). Stop the reporting services in SQLServerB
3). Restore these databases on SQLServerB on with target reporting database name (ReportServerTempdb & ReportServer)
4). Start reporting services on SQLServerB
5). Reset the database connection to ReportingServices on the target machine using Microsoft Reporting Services Configuration Manager
6). Restore the encryption key on SQLServerB
---Restore the encryption key from the backup which you have taken in step 1

After once you open the URL of target server, you might get an error stated -
Scale-out deployment configuration error:
This is because when doing step6 the old server will be added for scale-out deployment on the target machine. If the source and target machine are using different licenses of Reporting Services you might encounter issues that some features are not supported when migrating to a less featured sql server license.

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Normally that should not impose a problem since you would be able to remove the old server from scale-out deploment from the list in Microsoft Reporting Services Configuration Manager.

Solution:
7). On the SQLServerA
Run this command in Query analyser

For SQL2008/2008R2

SELECT * from ReportServer.dbo.Keys

For SQL2012

SELECT * from ReportServer2012.dbo.Keys

and make note of the InstallationId value for the non-null record
8). On ServerB server,
Run this command in Query analyser

For SQL2008/2008R2

SELECT * from ReportServer.dbo.Keys

For SQL2012

SELECT * from ReportServer2012.dbo.Keys

and you should see 3 records or more. One null record, and other records that have values in the MachineName field (these should be the old and new servers name). The InstallationId value from previous step should be in there with the old server's name
9). On the SQLServerB server, delete the record that matches the old server's InstallationId.
for example in this case:
Run this command in Query analyser

DELETE FROM [ReportServer].[dbo].[Keys]
WHERE MachineName = 'SQLServerA'

How to configure URL:
Default URL:
https://servername/Reports/Pages/Folder.aspx

Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx

Named instance like ServerA\Dev
http:// ServerB/Reports_Dev/Pages/Folder.aspx

restart the reporting services later and check your reporting services.