July 8, 2009 at 2:08 am
Hi
Whilst trying to tweak one of the indexes on our database I noticed that when the stored procedure that uses the index was run there were some symbols appearing that I hadn't see before, the symbols are circles with two arrows in it, I have attached the execution plan, I was just wanted to know if this symbol is a good or a bad thing.
This is the sql code: -
WITH forumThreads AS (
SELECT TOP (100) PERCENT mykey, title, startedby, isLocked, isSticky, active, postDate, postedBy, counter, traffic,
ROW_NUMBER() OVER (ORDER BY isSticky DESC, postDate DESC) AS Row FROM
(
SELECT TOP (100) PERCENT mykey, title, startedby, isLocked, isSticky, active, traffic,p1.postdate,p1.counter, p2.postedBy
FROM dbo.threads WITH (NOLOCK) cross apply
(SELECT MAX(postDate) AS postDate,COUNT(mykey) AS counter
FROM dbo.posts WITH (NOLOCK)
WHERE (active = 'Y') AND (threadID = dbo.threads.mykey)) AS p1 cross apply
(SELECT TOP (1) userid AS postedBy
FROM dbo.posts AS posts_1 WITH (NOLOCK)
WHERE (active = 'Y') AND (threadID = dbo.threads.mykey)
ORDER BY postDate DESC) as p2
WHERE (topicID = 16) AND (active = 'Y')
) as derived where postdate is not null
)
select mykey, title, startedby, isLocked, isSticky, active, postedBy, counter, postdate, traffic
FROM forumThreads WITH (NOLOCK)
WHERE Row between 0 and 30
Thanks
Matt
July 8, 2009 at 2:35 am
That means that those particular operators are running in parallel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2009 at 3:02 am
GilaMonster (7/8/2009)
That means that those particular operators are running in parallel.
Thanks Gail, is that an issue that I need to worry about or is it a normal situation
July 8, 2009 at 3:44 am
This is because of parallelism .
If you right click on say index seek symbol and select properties , you will find that this query is creating 9 worker threads .
Each thread is working on 1 column .
Even though 98% is index seek , but its oprtator looks high to me .
Try runing this query with option Maxdop 1 and see the duration it takes .
the time with maxdop1 will be more than normal as it will be creating only 1 thread ..
A query will only be able to go for parallelism if the Schedulars are free . if a query can go for parallelism but no CPUs are free , the optimizer will choose the other plan with less or no parallelism.
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 8, 2009 at 8:43 am
Hi,
I am totally in line with Abhay's findings. We should try running this query using MAXDOP 1 hint. More over out of those 9 Thread only 4 of them are working and returning some good number of rows.
First thing : Run with MAXDOP 1
Regards
GURSETHI
July 8, 2009 at 9:18 am
hi_abhay78 (7/8/2009)
This is because of parallelism .If you right click on say index seek symbol and select properties , you will find that this query is creating 9 worker threads .
Where do you see that? The degree of parallelism (shown in the tool tip of the SELECT operator) is 8 for this query, so no operator will have more than 8 threads. There may be more than 8 total, usually multiples of 8, but only 8 will be running at a time.
Each thread is working on 1 column .
If a seek is paralleled, each thread reads a portion of the index. If each thread was to retrieve one column from the index, each one would have to read the same data that every other one has read. That would be very inefficient.
Even though 98% is index seek , but its oprtator looks high to me .
Try runing this query with option Maxdop 1 and see the duration it takes .
I suspect the percentage is high because that seek is running nearly 6000 times. It's the outer of a nested loop join. There could be a more efficient way of writing this query that doesn't need the cross apply. Would need to know a bit more about the table structure and the logic of this query to say for sure.
Matt, any reason for the TOP (100) PERCENT?
Do you want some optimisation help with this, or were you just curious about the operators in the plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2009 at 9:29 am
GilaMonster (7/8/2009)
Matt, any reason for the TOP (100) PERCENT?
No reason, why, could it be causing a problem? I think originally I did the query in as a view and the 'top (100) percent' was put in automatically
Do you want some optimisation help with this, or were you just curious about the operators in the plan?
If you have any suggestions on how to make the query better then I'd be eternally gratefull 🙂
July 8, 2009 at 10:02 am
Matt (7/8/2009)
GilaMonster (7/8/2009)
Matt, any reason for the TOP (100) PERCENT?
No reason, why, could it be causing a problem?
No, but they're completely unnecessary. That construct's usually the sign of someone trying to 'order' a view or subquery.
Do you want some optimisation help with this, or were you just curious about the operators in the plan?
If you have any suggestions on how to make the query better then I'd be eternally gratefull 🙂
See my comments about the cross apply. (right above the bit that you quoted)
Edit: Also, why the nolock? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2009 at 8:55 pm
Gail, the number of therads that are working are 8 but the 9th one will be the one consolidating the records and giving the output .
So 0 through 8 is actually 9.
Matt , continuing with what Gail said ,one way to use Merge join for a large number of table is to make sure that the equality columns used in the joins are sorted and have a unique index on them .
The other way is to use merge join hint but it will be costly if you do it in the current scenarion as those columns might not be sorted .
But since the estimated number of rows for the index seek (taking 98% resources) is 6000+ I am not sure if merge join is going to help .But you should try .
Finally ,you can also try to see if top (100) percent can be avoided and see the difference.
Query tuning is all about trying different options and once you get the best one , then try justifying yourself that why its the best .
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 9, 2009 at 1:56 am
hi_abhay78 (7/8/2009)
Gail, the number of therads that are working are 8 but the 9th one will be the one consolidating the records and giving the output .So 0 through 8 is actually 9.
There will be a lot more than 9 threads. Each operator that parallels is allowed to parallel 8 ways. It's the reason why, in sysprocesses and sys.dm_exec_requests, there's often a lot of threads visible for a paralleled query, far more than maxdop, but only up to a number up to the specified maxdop will be runnable at any one time.
The other way is to use merge join hint but it will be costly if you do it in the current scenarion as those columns might not be sorted .
Join hints are, in my opinion, a last resort to be considered after rewriting the query (if necessary) and tuning indexes. It's way to easy to drop a hint in that improves performance today and ruins it a week later.
The other problem is that a merge join cannot be used for a CROSS APPLY. The only join type that can is nested loop. Add an OPTION (merge join) hint to this query and SQL returns the following error
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
The cost of that index seek is so high because it's running 6000 odd times, once for each row in the inner table. That's how CROSS APPLY works and why I suspect the query will be faster if it's removed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply