June 20, 2011 at 1:02 pm
Hi,
I'm having some issues with a query that performs really poorly on TOP 1, but not on TOP n (where n > 1).
It's exactly the same query for both, with just the TOP number being different. It's a mixed query with both full text search and relational predicates.
What I noticed was that the estimate vs the actual rows on the TOP 1 TVF for [FullTextMatch] varies by quite a bit (estimate is 2.8k+, actual is 210k+, 1 rebind, 722 rewinds). Also, the same nested loop for TOP 1 does not seem to be using correlated parameters (WHERE vs OUTER REFERENCES).
Table has 120K rows. Statistics are up to date. Both table and full text search indexes were rebuilt and show no signs of fragmentation.
The TOP 2 TVF for [FullTextMatch] has an estimated and actual rows of 1 and 2 respectively (874 rebinds and 0 rewinds).
What would cause TOP 1 to not use correlated parameters?
Any advice on what else may be causing the poor performance for TOP 1?
Here's the SHOWPLAN_ALL for TOP 1:
SELECT TOP 1 s.ItemId, s.Date, s.Clicked, s.Emailed, s.Commented, s.Rated, s.TotalRating, s.Rating
FROM [dbo].[Search] s (nolock) WHERE [Deleted] IS NULL AND .[SiteId] IN ('33333333-3333-3333-3333-333333333333','11111111-1111-1111-1111-111111111111') AND s.TemplateId IN ('22222222-2222-2222-2222-222222222222') AND (s.Date >= '1753-01-01T00:00:00') AND CONTAINS(s.SearchText, '"44444444444444444444444444444444" AND "55555555555555555555555555555555"') ORDER BY s.Date DESC
|--Top(TOP EXPRESSION: ((1)))
|--Nested Loops(Left Semi Join, WHERE: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid]=FulltextMatch.[docid]))
|--Nested Loops(Inner Join, OUTER REFERENCES: (.[ItemId], [Expr1007]) WITH ORDERED PREFETCH)
| |--Compute Scalar(DEFINE: (.[Rating]=[SearchDB].[dbo].[Search].[Rating] as .[Rating]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES: (.[ItemId], [Expr1006]) WITH ORDERED PREFETCH)
| | |--Index Seek(OBJECT: ([SearchDB].[dbo].[Search].[IX_SearchSiteIdDateDeleted] AS ), SEEK: (.[Deleted]=NULL AND .[Date] >= '1753-01-01 00:00:00.000'), WHERE: ([SearchDB].[dbo].[Search].[TemplateId] as .[TemplateId]={guid'22222222-2222-2222-2222-222222222222'} AND ([SearchDB].[dbo].[Search].[SiteId] as .[SiteId]={guid'11111111-1111-1111-1111-111111111111'} OR [SearchDB].[dbo].[Search].[SiteId] as .[SiteId]={guid'33333333-3333-3333-3333-333333333333'})) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([SearchDB].[dbo].[Search].[PK_Search] AS ), SEEK: (.[ItemId]=[SearchDB].[dbo].[Search].[ItemId] as .[ItemId]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[i2]), SEEK: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[ftkey]=[SearchDB].[dbo].[Search].[ItemId] as .[ItemId]) ORDERED FORWARD)
|--Table-valued function
SHOWPLAN_ALL for TOP 2:
SELECT TOP 2 s.ItemId, s.Date, s.Clicked, s.Emailed, s.Commented, s.Rated, s.TotalRating, s.Rating
FROM [dbo].[Search] s (nolock) WHERE [Deleted] IS NULL AND .[SiteId] IN ('33333333-3333-3333-3333-333333333333','11111111-1111-1111-1111-111111111111') AND s.TemplateId IN ('22222222-2222-2222-2222-222222222222') AND (s.Date >= '1753-01-01T00:00:00') AND CONTAINS(s.SearchText, '"44444444444444444444444444444444" AND "55555555555555555555555555555555"') ORDER BY s.Date DESC
|--Top(TOP EXPRESSION: ((2)))
|--Nested Loops(Left Semi Join, OUTER REFERENCES: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid]))
|--Nested Loops(Inner Join, OUTER REFERENCES: (.[ItemId], [Expr1007]) WITH ORDERED PREFETCH)
| |--Compute Scalar(DEFINE: (.[Rating]=[SearchDB].[dbo].[Search].[Rating] as .[Rating]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES: (.[ItemId], [Expr1006]) WITH ORDERED PREFETCH)
| | |--Index Seek(OBJECT: ([SearchDB].[dbo].[Search].[IX_SearchSiteIdDateDeleted] AS ), SEEK: (.[Deleted]=NULL AND .[Date] >= '1753-01-01 00:00:00.000'), WHERE: ([SearchDB].[dbo].[Search].[TemplateId] as .[TemplateId]={guid'22222222-2222-2222-2222-222222222222'} AND ([SearchDB].[dbo].[Search].[SiteId] as .[SiteId]={guid'11111111-1111-1111-1111-111111111111'} OR [SearchDB].[dbo].[Search].[SiteId] as .[SiteId]={guid'33333333-3333-3333-3333-333333333333'})) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT: ([SearchDB].[dbo].[Search].[PK_Search] AS ), SEEK: (.[ItemId]=[SearchDB].[dbo].[Search].[ItemId] as .[ItemId]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[i2]), SEEK: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[ftkey]=[SearchDB].[dbo].[Search].[ItemId] as .[ItemId]) ORDERED FORWARD)
|--Table-valued function
Any help appreciated. Thanks!
June 20, 2011 at 2:08 pm
Doing a text compare these are the only diffs:
|--Top(TOP EXPRESSION: ((1)))
|--Nested Loops(Left Semi Join, WHERE: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid]=FulltextMatch.[docid]))
|--Top(TOP EXPRESSION: ((2)))
|--Nested Loops(Left Semi Join, OUTER REFERENCES: ([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid]))
Could you please save the actual execution plans in graphical format and attach them to this thread?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 2:35 pm
I would also be curious about how the TVF is created. Is it an inline TVF or a multi-statement TVF?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2011 at 3:20 pm
@opc.three:
Yep, that's the only difference in the actual execution plan. I mentioned the correlated parameters being different in my original post.
I've attached the images: Top1 and Top2. I hope this helps clarify the exec plan 🙂
@Jeffrey Williams:
The TVF I mentioned is the full text search match. The actual query with the mixed predicates is pretty simple (as seen on the top of the SHOWPLAN_ALL execution plan).
June 20, 2011 at 3:22 pm
Thanks, but a picture is not going to help much. Can you please save off the plans as .sqlplan files and attach those?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 3:57 pm
My apologies. I misunderstood what you meant by graphical format.
I've attached the *.sqlplan for both top 1 and top 2.
June 20, 2011 at 4:56 pm
The initial difference I notice is that the TVF in the TOP 1 query is delivering 213,364 rows and the TVF in the TOP 2 query is only delivering 2 rows. Unfortunately exec plans do not provide exec plans for multi-statement TVFs.
Are both queries run on the same server/database, and was the database state pretty much the same at both time? If yes, did you by chance run the TOP 1 query before running the TOP 2 query? I am wondering if caching is affecting anything. What happens when you run the TOP 1 query again immediately after running the TOP 2 query?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 5:09 pm
Could you post two more things at this time? The code for the TVF and the code calling the TVF.
June 20, 2011 at 5:26 pm
Our observations on the TVF pretty much match (on my first post).
Tests were executed on the same server, data, connection.
There's no difference in the execution plan from running TOP 1 a few times by itself.
Running TOP 2 immediately followed by TOP 1 does not change the execution plan (also tried this a few times).
June 20, 2011 at 5:33 pm
Would still like to see the code for the TVF and the code that is calling it.
June 20, 2011 at 6:20 pm
Looks like we might have missed each other's reply.
If this wasn't the case, then I'd like to clarify: I did not create the TVF. It is executed by the FTS engine.
If there is a way for me to actually find out what it is executing inside, please let me know.
Lynn Pettis (6/20/2011)
Would still like to see the code for the TVF and the code that is calling it.
b.lam (6/20/2011)
@Lynn PettisThe TVF is for the FullTextMatch.
Is it possible to actually retrieve the code SQLServer executes for these?
If it is, I would appreciate a short how-to.
The call is from the CONTAINs clause on the query I mentioned on my first post.
June 20, 2011 at 6:23 pm
I think the problem is happening with the FTS CONTAINS function.
Here is the parameter list for the TOP 2 query and you can see its using a "map":
Scalar Operator((1)), Scalar Operator((1)), Scalar Operator((2)), Scalar Operator((15)), Scalar Operator([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid]), Scalar Operator([SearchDB].[sys].[fulltext_index_docidmap_132195521].[docid])
For the TOP 1 query the parameter list looks like this...no map:
Scalar Operator((1)), Scalar Operator((1)), Scalar Operator((2)), Scalar Operator((0)), Scalar Operator(NULL), Scalar Operator(NULL)
The TOP 1 query is returning so much more data than the TOP 2 query and I wonder if they're related.
What happens when you run the query as it is rewritten below? Can you attach the plans for this query when running with TOP 1 and TOP 2?
WITH cte
AS (
SELECT s.ItemId,
s.Date,
s.Clicked,
s.Emailed,
s.Commented,
s.Rated,
s.TotalRating,
s.Rating,
s.SearchText
FROM [dbo].[Search] s ( NOLOCK )
WHERE [Deleted] IS NULL
AND .[SiteId] IN ( '33333333-3333-3333-3333-333333333333', '11111111-1111-1111-1111-111111111111' )
AND s.TemplateId IN ( '22222222-2222-2222-2222-222222222222' )
AND ( s.Date >= '1753-01-01T00:00:00' )
)
SELECT TOP 1
ItemId,
Date,
Clicked,
Emailed,
Commented,
Rated,
TotalRating,
Rating
FROM cte
WHERE CONTAINS ( SearchText, '"44444444444444444444444444444444" AND "55555555555555555555555555555555"' )
ORDER BY Date DESC ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 20, 2011 at 7:22 pm
@opc.three:
Thanks for the suggestion.
The CTE will actually have to be flipped to include the CONTAINs predicate.
The reason being that the CONTAINs predicate cannot be applied to a table that is not full-text indexed (e.g. the CTE).
e.g.
WITH cte AS (
SELECT s.ItemId,
s.Date,
s.Deleted,
s.SiteId,
s.TemplateId,
s.Clicked,
s.Emailed,
s.Commented,
s.Rated,
s.TotalRating,
s.Rating,
s.SearchText
FROM [dbo].[Search] s ( NOLOCK )
WHERE CONTAINS ( SearchText, '"44444444444444444444444444444444" AND "55555555555555555555555555555555"' )
)
SELECT TOP 1
ItemId,
Date,
Clicked,
Emailed,
Commented,
Rated,
TotalRating,
Rating
FROM cte
WHERE [Deleted] IS NULL
AND [SiteId] IN ('33333333-3333-3333-3333-333333333333','11111111-1111-1111-1111-111111111111')
AND TemplateId IN ('22222222-2222-2222-2222-222222222222')
AND ( Date >= '1753-01-01T00:00:00' )
ORDER BY Date DESC;
The above query is similar to one of the things I tried before.
The resulting execution plan for this will be the same as the original query, where the TVF estimated vs actual still having too large a gap on TOP 1 but not on TOP n.
June 20, 2011 at 7:55 pm
I came up with a similar query:
with FTSearch ( -- Find all Items Id's where contains return true
ItemId
) as (
select
s.ItemId
from
dbo.Search s
where
contains(s.SearchText, '"44444444444444444444444444444444" AND "55555555555555555555555555555555"')
)
SELECT TOP 1
s.ItemId,
s.Date,
s.Clicked,
s.Emailed,
s.Commented,
s.Rated,
s.TotalRating,
s.Rating
FROM
dbo.Search s
inner join FTSearch fts
on (s.ItemId = fts.ItemId)
WHERE
s.Deleted IS NULL
AND s.SiteId IN ('33333333-3333-3333-3333-333333333333','11111111-1111-1111-1111-111111111111')
AND s.TemplateId IN ('22222222-2222-2222-2222-222222222222')
AND s.Date >= '1753-01-01T00:00:00'
ORDER BY
s.Date DESC;
I am curious, have you run the code in the cte to see what the results of just the contains predicate looks like?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply