Tag Archives: Stored Procedure

Making SSRS reports faster: get rid of Parameter Sniffing

Is your SSRS report running slowly? Are you using a stored procedure to pull the data and pass them to report? If your answer to these questions is yes, then you are a victim of SQL Server’s Parameter Sniffing.
The first question is, what is Parameter Sniffing? It refers to SQL Server’s effort to reduce CPU overhead by using the same query execution plan for all similar queries instead of compiling the query each time it is being executed. As long as the queries would have really returned the same plan, this is a big performance winner. SQL Server internally tries to automatically turn simple non-parameterized user queries into parameterized queries to take advantage of this performance gain.
Parameter use, especially in more complex scenarios, can also cause performance issues. If the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. This section describes how this process works in more detail.
So, what to do to prevent being a victim of this process? It is very easy to achieve: all you need to do is to declare local parmeter(s) inside your stored procedure’s code and assign the values passed to the SP to the newly introduced parameters. for example, lets say we have a sp with following definition:

CREATE PROCEDURE [SP_Test_ParameterSniffing]
@CustomerID INT
AS
BEGIN

SELECT *
FROM Customer c
WHERE c.CustomerID = @CustomerID

END

All you need to do is to add a new parameter to your SP code and assign the value passed to the SP to theis new parameter and use it in your WHERE clause:

CREATE PROCEDURE [SP_Test_ParameterSniffing]
@CustomerID INT
AS
BEGIN

DECLARE @CustomerID2 INT;
SET @CustomerID2 = @CustomerID;
SELECT *
FROM Customer c
WHERE c.CustomerID = @CustomerID2

END

This will cause the original query execution plan to be bypassed and have no effect on the query performance.


SQL Server 2000 stored procedure and SSIS 2008 OLE DB Source

What is the first thing that comes to your mind as a Microsoft BI expert, when you think of a data migration tool? Undoubtedly SSIS!

As you all know, SSIS is a great tool for migrating data from legacy systems and databaseas into newer and more robust places and repositories to better serve knowledge and data workers.
Yesterday, I was assigned to a task which was about getting the list of custoemrs from SQL Server 2000 into SQL Server 2008, using SSIS 2008. It didn’t seem something extraordinary at first: all I needed to do was to get an OLE DB source into my Data Flow Task, have a lookup table to filter existing records in the table, and pass the new rows into the destination table. I then realized that not all the customers need to be migrated, and there is a stored procedure written in SQL Server 2000 to return the list of the customers that should be migrated. The SP works this way: gets the Id of a principal as input, and extracts the list of customers for the principal based on some pre-defined business logic.
What I needed to be able to do was to pass the principal ID as my package’s parameter to SP, and use the result returned by SP in my source query’s WHERE clause. Passing parameter to package and then use it in t-sql query is nothing difficult. It gets messy when you need to use that parameter in a nested query: using IN keyword in the main query and using SP in the internal query to build a temporary table for IN keyword.
I have only one word for doing this thing: Impossible!
I tried different types of calling a SP in another query’s WHERE clause. I even got the SP’s code and put it into my t-sql query. But as you may know, SSIS’s OLE DB source does not support parameters in nested queries.
So, I tried to design a strategy for doing this. A strategy to get rid of the parameterized nested query.
What I did is, I added a Execute SQL Task to my package, which gets executed before my Data Flow task. The role of this Execute SQL Task is to get the list of customer IDs from the source database by executing the SP, and store them in a temporary table in destination SQL Server 2008 using this piece of code:

IF Object_Id('TemporaryTablet', 'U') IS NOT NULL
DROP TABLE TemporaryTable
CREATE TABLE TemporaryTable(CustID int)
INSERT INTO TemporaryTable EXECUTE dbo.Sql2000SP ? --? is the parameter passed to SP

Then, I connect this Execute SQL Task to my Data Flow task. This way, the new Data Flow task is not paramtereized anymore because the parameter, which is the principal ID, is used in previous step to pull the list of customers into the temporary table using Execute SQL Task. This is the code that gets data in my OLE DB Source:

SELECT Column1, Column2, Column3
FROM OriginalTable
WHERE CC.CustId IN (SELECT * FROM TemporaryTable)

Bingo! It works without any problem: there is no parameter to be passed to SP or the nested query to replicate SP’s logic in OLE DB Source! 🙂