Query plan for partitioned views not running as they should.

Question

Query plan for partitioned views not running as they should.

josjonkmans

SSC Veteran

Points: 279
More actions
March 29, 2015 at 8:04 am

#316750

I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].
UNION ALL SELECT * FROM [server2].[database].[dbo].
UNION ALL SELECT * FROM [server3].[database].[dbo].
5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan. Maybe I forgot something, or something is changed in 2014 and a new approach is needed but I'm a little stuck here.
Did someone experience the same thing and if so, how did you fix this?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Unless synonyms or pass-through views are in play, I'm pretty sure that you need to use the 4 part naming convention to "talk" across servers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

josjonkmans SSC Veteran Points: 279 More actions · Answer 2

Jeff, I did use a 4 name naming convention in my setup (little mistake in my post but corrected that one). The problem still exists, I've also set the Lazy Schema Validation options but its seems it's still querying the other servers.

josjonkmans SSC Veteran Points: 279 More actions · Answer 3

I did find a solution for the problem, I was using an SQL account for my linkedserver connections. When I changed it to use the "Be made using the current security context" option and setting up the manual spn (because I'm running the services under domain accounts) it is running correctly. This seems to fix the problem, still finding it strange that the query plans get messed up when changing the security context for the linked servers.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

josjonkmans (3/30/2015)
I did find a solution for the problem, I was using an SQL account for my linkedserver connections. When I changed it to use the "Be made using the current security context" option and setting up the manual spn (because I'm running the services under domain accounts) it is running correctly. This seems to fix the problem, still finding it strange that the query plans get messed up when changing the security context for the linked servers.

The note on this page:

https://technet.microsoft.com/en-us/library/ms175537(v=sql.105).aspx

“To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.”

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

josjonkmans SSC Veteran Points: 279 More actions · Answer 5

@ChrisM@Work

I've checked it by making the database user sysadmin on every linked server (testing environment so dont worry 😛 ) and ran the script again. Even with sysadmin rights the problem occurs so it seems not to be related to userrights.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Wish I could come over and play with this. The check constraint should allow the optimizer to eliminate servers from the choice. The fact that security might have changed the behavior makes me wonder about the ANSI settings. That, might, maybe, could influence the check constraint (lots of weasel words).

And parameter sniffing (a good thing normally) shouldn't matter that much, although, if it does, using a hard value like the string you passed is exactly the same thing as parameter sniffing (using an actual value against statistics to determine cardinality). And using recompile with a local variable is something of a waste of time since you'll always get average plans. Either recompile (so you get every value sniffed individually), or a local variable (so you get averages instead of specific values), each independently.

I'm not sure what's going on. Can you post the execution plans?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

josjonkmans SSC Veteran Points: 279 More actions · Answer 7

Thanks for your reply Grant,

I understand that normal parameter sniffing behavior is oke for most cases but sometimes it will result in bad performance when using partitioned views in stored procedures. Normally I would re-declare the parameters again and pass the external parameter values into the new internal parameters to avoid this behavior and reduce the recompilations. I just used those option to test this specific issue 🙂

In this case (to keep it simple) I just created a very basic query for the sake of easy testing like this:

SELECT *

FROM [JJ_Maintenance].[Reporting].[vw_Backup_DW]

WHERE [server] = 'HIVECL01N01'

Even then the problem still occurs on the same environment. The only difference is the linked server settings as I described in the posts above. I've attached the execution plans where the faulty_plan is the plan generated when authenticating with an SQL Login and the correct_plan is based on the connection where I use the "Be made using the current security context" option for the linked server. The rest of the linked server settings are identical.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

I am just not sure. I'm not seeing indications for why it's going wrong. Double check that the constraint has the WITH CHECK option in 2014. Other than that... I don't have a good guess at the moment.