July 28, 2008 at 10:37 am
Hi,
I am analyzing a server and found that following query (written by some freak) is chocking our server. I have seen the execution plan of this query but i don't know what the execution plan is saying. So can some one plz look at the attached execution plan and can tell whats goin wrong and can suggest some improvements. Or help me understand the execution plan (any reference or article). Thanks
SELECT * FROM
(
SELECT Communication.*, Comm_Link.*, Person.*, Company.* ,Users.*,Escalations.*
FROM Users,Communication
LEFT OUTER JOIN Comm_Link ON Comm_CommunicationId = CmLi_Comm_CommunicationId
LEFT OUTER JOIN Person ON CmLi_Comm_PersonId = Pers_PersonId
LEFT OUTER JOIN Company ON CmLi_Comm_CompanyId = Comp_CompanyId
LEFT OUTER JOIN Escalations ON Comm_CommunicationId = Escl_RecordID and Escl_TableId=4 and Escl_deleted is Null
WHERE Comm_Deleted IS NULL
AND CmLi_Deleted IS NULL
and Cmli_Comm_UserId = User_UserId
and comp_deleted is NULL
and pers_deleted is NULL
and user_deleted is NULL
) A
WHERE A.comm_emailflag = 'Y'
and 858 = A.cmli_comm_userid
AND ((A.Escl_EscalationId is NULL )
OR (A.Escl_WorkflowRuleId <> 10388 )
OR ((A.Escl_WorkFlowRuleId = 10388 )
AND A.Escl_Datetime < '20080728 11:54:34'
AND A.Escl_UserId = 858))
ORDER BY A.Comm_CommunicationId
July 28, 2008 at 12:30 pm
The query plan is read from right to left.
So, it starts with something a bit strange - the Comm_Link table. This is weird because you are LEFT OUTER JOINing to it. It is starting here because you have criteria negating the outer join (A.Escl_UserId = 858). Since this field must have a value, outer joining to the table does not do anything.
It is then doing a key lookup on the Comm_Link table because this filter (A.Escl_UserId = 858) is on an indexed column, but not a covering index or clustered index. This is usually ok, but the key lookup is 34% of the query - this is suspect and suggests a possible fragmented index.
Shortly after that, you are getting a nested loop join to the Communication table. Loops suggest the optimizer thinks there are a lot of records in Comm_Link and a relatively small number of records per Comm_Link record being returned from Communication. The indication in the plan is that you will get 1 record per. But, this is taking 32% of the query time.
I would start with two things. First, make sure your statistics are updated. Second, the query syntax is a mixture of ANSI 89 and ANSI 92 syntax ("FROM Users,Communication" is ANSI 89 and "LEFT OUTER JOIN" is ANSI 92). Remove all ANSI 89 syntax because it is deprecated and eliminate the sub-query syntax. The optimizer will have a better time with it. Once that is done, re-examine the execution plan.
July 28, 2008 at 12:50 pm
Posting the table definitions with indexes would be very helpful also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 28, 2008 at 2:44 pm
There are two resources that I know of for learning to read exec plans.
Firstly there's Grant Fritchey's e-book. Last I saw it was available if you download any redgate product for evaluation. The book is excellent.
Second, there's a half-finished blog series on reading execution plans[/url]. It's not comprehensive, but may give you an idea of there to start.
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 28, 2008 at 10:11 pm
Hi,
First the query what you have specified is itself seems problem.
1. it uses subquery. - Try to avoid subqueries.
2. it uses leftouter join - I don't know the no of records in Table. if it is huge then there is problem.
3. checking NULL condition in WHERE clause is also problem. Try to eliminate NULL condition in WHERE clause and check with "0".
Try to optimize the query and check with query plan.
Regards,
Sivakumar R
July 29, 2008 at 3:43 am
Why do you say that subqueries, outer joins and NULL checks are bad?
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 29, 2008 at 9:18 am
Actually the Inner Query (aliased as A) is not a subquery in my database, its a view, and users call this view with their where clause.... I just copied the code of the view for your understanding...
July 29, 2008 at 2:34 pm
usman.tanveer (7/29/2008)
Actually the Inner Query (aliased as A) is not a subquery in my database, its a view, and users call this view with their where clause.... I just copied the code of the view for your understanding...
Which is exactly how SQL Server executes it, so that makes sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 2:41 pm
usman.tanveer (7/29/2008)
Actually the Inner Query (aliased as A) is not a subquery in my database, its a view, and users call this view with their where clause.... I just copied the code of the view for your understanding...
This kind of view being used for ad hoc user-queries is really a bad idea in the first place, as you've already noted. Since you probably can't break up the view into a better solution, but have to leave it monolithic, what you might want to look into is how it's being used.
You might be able to get away with setting up a reporting copy of the database, where users can query this view, but where it won't interfere with OLTP operations in the primary database. Log shipping can be used for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 10:43 am
@
"You might be able to get away with setting up a reporting copy of the database, where users can query this view, but where it won't interfere with OLTP operations in the primary database. Log shipping can be used for that kind of thing. "
How can i do that?i only thing i know about is replication for doing this, so can u please tell me how it can be done with log shipping?
Thanks,
Usman
July 30, 2008 at 11:26 am
Try the Log Shipping section of Books Online.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply