December 1, 2010 at 3:35 am
I'm stuck on a query I don't seem to be able to tune.
Everything seems to be related to a predicate on the replication function ISPALUSER.
Here's my query:
SELECT [SALES_AGENCY].[IdSalesAgency],
[SALES_AGENCY].[SalesAgencyCode],
msp.partition_id
FROM [dbo].[SALES_AGENCY] [SALES_AGENCY],
dbo.MSmerge_partition_groups msp
WHERE (msp.publication_number = 1
AND ((([SalesAgencyCode] = msp.[HOST_NAME_FN] )))
)
AND ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
OR PERMISSIONS(404912514) & 0x1b <> 0
)
This is the table script for SALES_AGENCY (user table):
CREATE TABLE [dbo].[SALES_AGENCY](
[IdSalesAgency] uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY CLUSTERED,
[SalesAgencyCode] nvarchar(25) NOT NULL,
[Description] nvarchar(200) NOT NULL,
[IdLanguage] uniqueidentifier NOT NULL,
[IdAddress] uniqueidentifier NOT NULL,
[Status] nvarchar(3) NOT NULL,
CONSTRAINT [AK_UK_SALES_AGENCY_SALES_AG] UNIQUE NONCLUSTERED
(
[SalesAgencyCode] ASC,
[IdSalesAgency] ASC
)
)
And this is the table script for MSmerge_partition_groups (system table, created during merge replication setup):
CREATE TABLE [dbo].[MSmerge_partition_groups](
[partition_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[publication_number] [smallint] NOT NULL,
[maxgen_whenadded] [bigint] NULL,
[using_partition_groups] [bit] NULL,
[is_partition_active] [bit] NOT NULL,
[HOST_NAME_FN] [nvarchar](260) NULL,
)
CREATE NONCLUSTERED INDEX [ncMSmerge_partition_groups_HOST_NAME_FN] ON [dbo].[MSmerge_partition_groups]
(
[HOST_NAME_FN] ASC
)
CREATE NONCLUSTERED INDEX [nc1MSmerge_partition_groups] ON [dbo].[MSmerge_partition_groups]
(
[publication_number] ASC,
[HOST_NAME_FN] ASC
)
I don't understand why I get a different (and suboptimal) plan when the replication predicates are added to the query, while I get a nice seek + nesteed loop plan leaving out the repl predicates.
I linked the two plans:
http://sqlconsulting.it/static/with_repl_predicates.sqlplan
http://sqlconsulting.it/static/without_repl_predicates.sqlplan
Can anyone shed some light?
How can
AND ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
OR PERMISSIONS(404912514) & 0x1b <> 0
)
change the plan so deeply?
-- Gianluca Sartori
December 1, 2010 at 8:13 am
I don't see ISPALUSER in BOL. Can you script that function out for me?
December 1, 2010 at 8:15 am
Gianluca Sartori (12/1/2010)
How can
AND ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1OR PERMISSIONS(404912514) & 0x1b <> 0
)
change the plan so deeply?
Look at the order of your parens, ANDs, and ORs.
I think it's counting & as a bitwise And.
December 1, 2010 at 8:21 am
Yup. It is not my code, anyway. This has been generated by the merge replication setup.
I find lots of (system) views with this filter, named msmerge_whatever.
What I don't understand is why this filter, that doesn't seem to be related to data in the tables, forces an index scan.
fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
checks if current user is in the replication role (MSmerge_PAL_role)
PERMISSIONS(404912514) & 0x1b <> 0
checks if user has permissions on object
None of these predicates need to access the tables, so why a scan?
-- Gianluca Sartori
December 1, 2010 at 8:23 am
Brandie Tarvin (12/1/2010)
I don't see ISPALUSER in BOL. Can you script that function out for me?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 1, 2010 at 8:28 am
Gianluca Sartori (12/1/2010)
None of these predicates need to access the tables, so why a scan?
Because you've got them all lumped together??? To my eyes, the code should say:
AND ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
OR ( PERMISSIONS(404912514) & 0x1b <> 0)
)
Or am I misunderstanding the WHERE clause?
EDIT FYI: When I try to look at your plans, I get the following error: "The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
Switch from current encoding to specified encoding not supported. Error processing resource 'http://sqlconsulting.it/static...
<?xml version="1.0" encoding="utf-16"?>"
...I'm also reading up on that function now.
December 1, 2010 at 8:39 am
Brandie Tarvin (12/1/2010)
Gianluca Sartori (12/1/2010)
None of these predicates need to access the tables, so why a scan?Because you've got them all lumped together??? To my eyes, the code should say:
AND ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
OR ( PERMISSIONS(404912514) & 0x1b <> 0)
)
Or am I misunderstanding the WHERE clause?
Thanks, Brandie. Anyway, I don't think that adding the parentheses that way can change the expression.
However, it's not my code, it's Microsoft's. I can't change it, I just have to deal with it and (hopefully) understand and tune it.
Try looking at the query plan for:
SELECT 1
WHERE ({fn ISPALUSER('23320088-BB3F-46AC-905D-0F2A95181934')} = 1
OR ( PERMISSIONS(404912514) & 0x1b <> 0)
)
You will see just a constant scan and a filter. No tables, no indexes accessed.
Why does it force a scan on my index?
-- Gianluca Sartori
December 1, 2010 at 8:41 am
Brandie Tarvin (12/1/2010)
EDIT FYI: When I try to look at your plans, I get the following error: "The XML page cannot be displayedCannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
Thanks for pointing it out. Try right-clicking and "save as...". The browser seems to detect it's an XML file and tries to display it instead of opening it with SSMS.
I uploaded them to my personal site because attachments don't work for me on SSC. Go figure.
-- Gianluca Sartori
December 1, 2010 at 8:43 am
WayneS (12/1/2010)
Brandie Tarvin (12/1/2010)
I don't see ISPALUSER in BOL. Can you script that function out for me?
Thank you, Wayne!
It also took me to Paul Ibson's site, that looks pretty cool.
Unfortunately, it doesn't solve my issue. :crying:
-- Gianluca Sartori
December 1, 2010 at 8:53 am
Maybe you have the plans misnamed, but "Without_repl_predicates" shows an Index scan (IndexScan Ordered="true") and the other plan shows the opposite??
Anyway, while I'm playing with this, try doing the first statement without the second. And the second statement without the first. See if there's any change.
Also, take heed that PERMISSIONS() is deprecated. Take a shot at using FN_MY_PERMISSIONS or HAS_PERMS_BY_NAME instead. See what that does to your query plan.
December 1, 2010 at 9:02 am
I tried downloading the plans from the URLs in my first post and they contain:
with_repl_predicates.sqlplan --> index scan
without_repl_predicates.sqlplan --> index seek
I already tried running the query without the ISPALUSER and without the PERMISSIONS parts. Nothing changed.
I know that PERMISSIONS is deprecated, but I cannot change it.
-- Gianluca Sartori
December 1, 2010 at 9:34 am
Gianluca Sartori (12/1/2010)
WayneS (12/1/2010)
Brandie Tarvin (12/1/2010)
I don't see ISPALUSER in BOL. Can you script that function out for me?Thank you, Wayne!
It also took me to Paul Ibson's site, that looks pretty cool.
Unfortunately, it doesn't solve my issue. :crying:
It is kinda surprising - as thorough as that blog is, and it didn't cover performance or query plans.
You might be able to plan around with generating the query plan without the function calls, and then store that plan for use by these queries?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 1, 2010 at 10:23 am
WayneS (12/1/2010)
You might be able to plan around with generating the query plan without the function calls, and then store that plan for use by these queries?
I don't know how I could do it. Plan guides?
Unfortunately I can't play with hints or the like, beacause I can't modify the views.
-- Gianluca Sartori
December 1, 2010 at 11:01 am
Maybe I'm off base here, but could the scan be caused by SQL Server pulling the data and then checking the permissions for each record to see if the user has the perms to see it? Instead of checking the permissions once and being done?
If you could alter that, I'd recommend testing by creating an IF statement to check the perms before doing the query. But if you can't change any of that, then the suggestion is useless. Still, it might be an interesting thing to see what the execution plan says in that particular case.
December 1, 2010 at 12:40 pm
Gianluca Sartori (12/1/2010)
WayneS (12/1/2010)
You might be able to plan around with generating the query plan without the function calls, and then store that plan for use by these queries?I don't know how I could do it. Plan guides?
Unfortunately I can't play with hints or the like, beacause I can't modify the views.
Yes, Plan Guides is what I was thinking about. I haven't used them, but I believe that you can assign them to queries, and then those queries use that plan guide. Not sure if it would even work in this case.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply