SSRS Cancel Report Still Running In The Background

There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database. This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case.

This is a common problem with users accessing reports. There is a way to stop the query from running in the background by using the KILL command, however you will need a DBA to do this for you unless you have permission to do this your self.

Hope this helps other who are struggling with the same issue 🙂

SSRS Reporting Server Recycle Time

Many people complain about their reports taking a long time to load when they access them first thing in the day. This is due to the reporting server’s recycle time. What happens is SSRS regularly restarts the application domain after a specific time period. Upon first request to the report server after the application domain has been restarted, SSRS needs to load all the settings and this takes quite a long time.

The default recycle time for the application domain is 720 minutes. This can be increased to a more reasonable time, however this does still not solve the problem. The first request to the report server will still take a long time to process.

If you would like to increase the recycle time you can do this in the rsreportserver.config file. The file can be located in “C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer”

You can search for the below line of code in the config file and increase the time:

<RecycleTime>720</RecycleTime>

The better solution would be to schedule a PowerShell script that refreshes the report server twice a day. This will make the access to the report server for the first user fast. They will no longer have to wait long for the report to load.

There are two PowerShell scripts that you can use. The first script will access the report server:

$root = 'https://Hostname/reports/'
Invoke-Webrequest $root

The second script will start up IE and open up a report that is deployed on the server. You can specify which report to open up.

v=$root = 'http://Hostname/reports/report/ReportNameToAccess'
$IE=new-object -com internetexplorer.application
$IE.navigate2("$root")
$IE.visible=$true

You can schedule either of these scripts to run with Windows Task Scheduler.

Hope this helps you with speeding up the load time of the reports!

Persistent

Parameter Sniffing in Microsoft SQL Server

Parameter Sniffing in SQL Server is SQL Server’s effort to reduce the CPU overhead by caching the query execution plan and using it for all similar queries instead of recompiling the query each time at execution.

The way SQL Server chooses the best plan is by cost estimation. The query plan takes into consideration the cardinality estimation based on the input parameters and the help of statistics.

The optimizer creates an execution plan that sniffs the parameter values. The problem arises when the query uses a previously generated plan optimized for a different data distribution.

I have listed a few methods to correct parameter sniffing below with examples. First lets create a test database with some test data.

/*******************************************
 * Create Database
 *******************************************/
USE MASTER
GO

CREATE DATABASE ProductDB
ON 
( NAME = 'Product_dat',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Productdata.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Product_log',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Productlog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

ALTER DATABASE [ProductDB] SET RECOVERY SIMPLE
GO

/*******************************************
 * Create tables
 *******************************************/
USE ProductDB
GO

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
  DROP TABLE dbo.Product
GO

CREATE TABLE Product
  (
   ProductID INT IDENTITY(1,1) NOT NULL,
   ProductName VARCHAR(50) NOT NULL,
   ProductColour VARCHAR(50) NOT NULL,
   Cost MONEY NOT NULL,
   ProductCategoryID INT NOT NULL,
   PRIMARY KEY CLUSTERED (ProductID)
  )

IF OBJECT_ID('dbo.ProductCategory', 'U') IS NOT NULL
  DROP TABLE dbo.ProductCategory
GO

CREATE TABLE ProductCategory
  (
   ProductCategoryID INT IDENTITY(1,1) NOT NULL,
   CategoryDescription VARCHAR(50) NOT NULL,
   PRIMARY KEY CLUSTERED (ProductCategoryID)
  )

CREATE INDEX IX_Product_ProductCategoryID 
ON Product(ProductCategoryID)

/*******************************************
 * Insert test data
 *******************************************/
INSERT INTO [dbo].[ProductCategory]
(
	CategoryDescription
)
SELECT 'Bicycle'
GO

INSERT INTO [dbo].[ProductCategory]
(
	CategoryDescription
)
SELECT 'Bicycle Accessories'
GO

INSERT INTO [dbo].[Product] 
( 
     [ProductName]
    ,[ProductColour]
    ,[Cost]
    ,[ProductCategoryID]
)

SELECT	 'Mountain Bike'
		,'Red'
		,899
		,1
GO

INSERT INTO [dbo].[Product] 
( 
     [ProductName]
    ,[ProductColour]
    ,[Cost]
    ,[ProductCategoryID]
)
SELECT	 'Helmet'
		,'Orange'
		,79
		,2
GO

Disable parameter sniffing for a specific query
You can use a trace flag as a hint in the query to change the behaviour of the query optimizer. You do this by adding the QUERYTRACEON hint to the OPTION clause.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_QUERYTRACEON_Hint 
@ProductCategoryID   INT
AS

SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(QUERYTRACEON 4136)
GO

Create stored procedures using WITH RECOMPILE
A recompile will create a new execution plan with new parameters. This is not the best solution as the recompilation will increase CPU load and this could be problematic in heavy concurrent systems. If the problem is a single query within the stored procedure then recompiling the entire stored procedure is not the best approach, rather correct the problematic query.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_With_Recompile 
@ProductCategoryID   INT
WITH RECOMPILE
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
GO

Use SQL Server hints such as OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR)
As mentioned above previously you should rather correct the problematic query than recompile the entire stored procedure. You can do this by using the hint RECOMPILE.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Recompile_Hint
@ProductCategoryID   INT
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(RECOMPILE)
GO

Using the OPTIMIZE FOR hint will allow you to set a parameter value to use as a reference for optimization. If you are using SQL Server 2008 or above then you can use OPTIMIZE FOR UNKNOWN if you are not sure which parameter values the stored procedure will use to execute.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Optimize_Unknown_Hint
@ProductCategoryID   INT
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(OPTIMIZE FOR UNKNOWN )
GO

Use dummy variables in stored procedures
You will assign the input parameter to a local variable and the query will use this variable instead of the parameter.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Dummy_Variable
@ProductCategoryID   INT
AS
DECLARE @Dummy    INT

SELECT @Dummy = @ProductCategoryID

SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @Dummy
GO