SQL Server Stored Procedures in Tableau – Part 3 – Execution Plan

This series of posts attempt to look at the pros and cons of using Stored Procedures as data access method for Tableau reports. The points made here are valid as of Tableau 8.2.

Part 1 gives a general introduction and identifies areas where some of SProcs advantages become irrelevant when it comes to Tableau

Part 2 looks at metadata considerations

Part 3 looks at Execution Plan considerations

Part 4 looks at how Tableau uses stored procedures to answer different queries

Execution Plan Considerations

T-SQL is a declarative language; i.e. the language only asks for what is wanted and doesn’t specify how it should be done. In a typical RDBMS, this task of how to do it is left to the Query Optimizer. The Optimizer looks at the various indexes, number of rows present, server’s hardware configuration among other information and prepares a plan on how to do the task at hand.

When a query first comes to the database (and after it passes the syntax checks, algebrizer etc.), the database engine checks if an execution plan for that query has already been created or not. If a plan already exists, then the engine re-uses that execution plan. If no plan exists, then the query optimizer creates a new plan for the query at hand and will re-use this plan for future requests (unless specifically asked not to).

Plan generation is an important step and, for complex queries, can sometimes eat up considerable time and resources before the actual execution can begin (and if the actual plan generated is not good enough, can result in a inefficient execution). Thus it is important to re-use execution plans. (It should be noted that re-use of execution plans can sometimes result in an issue known as “Parameter Sniffing” and can result in inefficient execution.)

When a stored procedure is first executed, SQL Server generates a plan for that procedure based on the input parameters (if any) and other considerations. If another call is made to the database to execute the same procedure but with different input parameters, SQL Server skips generating a plan and re-uses the same plan it created earlier, thus resulting in some savings (both time wise and resources wise). However, if ad-hoc queries are coming to the database, SQL Server typically creates a new plan for each such statement if the only difference is in the literals of the queries. Thus, plans are generated unnecessarily resulting in wastage of resources. Some of this can be addressed by optimizing the database for ad-hoc usage and/or using Plan Guides.

Sample Tableau Query Execution

SQL Server has the following sprocs available to prepare, execute  and unprepare a query.

  1. sp_prepare – Used to only prepare the query; i.e. create an execution plan
  2. sp_prepexec – Used to both prepare the query and execute it on the same call
  3. sp_execute – Used to execute a query using a previously prepared plan
  4. sp_unprepare – Used to disassociate the query plan from the query

A typical ad-hoc query executed by Tableau follows this pattern:

<code>

declare @p1 int
exec sp_prepexec @p1 output, NULL, N’SELECT Col_A FROM Table_A WHERE Col_B = 10′

exec sp_unprepare @p1

</code>

 

And a sproc query executed by Tableau follows this pattern:

<code>

declare @p1 int
exec sp_prepexec @p1 output, NULL, N’EXEC dbo.Get_Data_From_TableA @col_b = 10′

exec sp_unprepare @p1

</code>

Let us say that queries are being run to get data for two values of Col_B, say Col_B = 10 or Col_B = 20. In such a case, the queries are as follows:

<code>

declare @p1 int
exec sp_prepexec @p1 output, NULL, N’SELECT Col_A FROM Table_A WHERE Col_B = 10′
exec sp_unprepare @p1

— followed by
declare @p1 int
exec sp_prepexec @p1 output, NULL, N’SELECT Col_A FROM Table_A WHERE Col_B = 20′
exec sp_unprepare @p1

</code>

<code>

declare @p1 int
exec sp_prepexec @p1 output, NULL, N’EXEC dbo.Get_Data_From_TableA @col_b = 10′
exec sp_unprepare @p1

— followed by
declare @p1 int
exec sp_prepexec @p1 output, NULL, N’EXEC dbo.Get_Data_From_TableA @col_b = 20′
exec sp_unprepare @p1

</code>

In the above two scenarios, unless other things are done, SQL Server will prepare a new plan when the query exec sp_prepexec @p1 output, NULL, N’SELECT Col_A FROM Table_A WHERE Col_B = 20′ comes to the database. Whereas, SQL Server will re-use the plan when then query exec sp_prepexec @p1 output, NULL, N’EXEC dbo.Get_Data_From_TableA @col_b = 20′ comes to the database. Even though sp_unprepare is called in the sproc scenario, SQL Server doesn’t remove the plan from its cache and is able to match it to the next sproc call. However, in the ad-hoc scenario, SQL Server is not able to re-use the previous plan even though the only difference is in the literal (10 vs 20).

Thus, generally, stored procedures present a better option when execution plans are considered. Please note that plans are re-compiled even when using Stored Procedures in some cases. Such as when RECOMPILE statement is used or if temp tables are present in the code or plans being dropped from memory due to memory pressure or server restarts and so forth.

Also, SQL Server allows the ad-hoc queries also to reuse existing plans. This can be achieved by setting the PARAMETERIZATION option to FORCED and making use of Plan Guides. However, care should be taken to consider other workloads and queries running on SQL Server when considering forced parameterization.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s