About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Friday, June 28, 2013

Problems of not having query hints allowed in Views

So, working with Tableau, you're not given the option to use stored procedures, and I don't like to put queries inline within the code, so I create views for the reports to pull data from. The problem I have is that the query I'm running, is doing a count, which ends up doing in parallel; giving erroneous totals. No problem, not the first time I ran into this problem, just use OPTION (MAXDOP 1).

The problem with that, is Views are not allowed to have query hints. So the next possible solution would be to call the option with the view:

SELECT * FROM [dbo].[View1]
OPTION (MAXDOP 1);

This would work, but since I'm using Tableau; it just errors out when I try to add the option.

So, is there another path? The next possible, semi-working option is to create a "plan guide" for the query. (More info found at: http://msdn.microsoft.com/en-us/library/ms179880.aspx)

Example:
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT * FROM [dbo].[View]',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';

This works, if the query executed is the exact same as in the statement. So, if there is an extra space, or different formatting on the query this guide would not be called. Luckily, I'm the only one reports developer, this should be fine -- as long as no one messes with the query and that I have ALTER permissions on the databases that this will be placed on.

There are problems with this solution, but first to see the plan guides, you can use this query:

SELECT * FROM sys.plan_guides

Since, only 1 plan guide is allowed to be enabled for a query, and worst of all -- the possible future errors:
"Trying to drop or modify a function, stored procedure, or DML trigger that is referenced by a plan guide, either enabled or disabled, causes an error. Trying to drop a table that has a trigger defined on it that is referenced by a plan guide also causes an error."

So, if you ended up using the Object type, instead of the Sql type you will surely need to know how to drop a plan:

EXEC sp_control_plan_guide N'DROP', N'Guide1'


I still have a feeling in my bones that there is an easier way, or at least there should be. It shouldn't have to be this ugly.