December 15, 2010 at 2:02 pm
SQL 2005 SP3. An application query is not completing successfully- it runs for over 10 minutes at which time the application times out.
I can see from the activity monitor that it is issuing this query:
(@P1 int,@P2 datetime,@P3 datetime)
select d3.name as 'Contract Name',
l.valstr as 'Customer Name',
l2.valstr as Region,
l3.valstr as Zone from dtree d1,
dtree d2, dtree d3,
llattrdata l, llattrdata l2,
llattrdata l3,
llattrdata l4,
llattrdata l5
where d1.name='Final Contract' and d1.subtype=31108 and
d1.childcount=0 and d2.dataid=d1.parentid and
d3.dataid=d2.parentid and d3.dataid in
(select a.dataid from dtreeancestors a where a.ancestorid=@P1)
and d3.dataid=l.id and
l.defid=21316 and l.attrid=15
and d3.dataid=l2.id and l2.defid=21316
and l2.attrid=67 and d3.dataid=l3.id
and l3.defid=21316 and l3.attrid=66 and d3.dataid=l4.id and l4.defid=239157 and
l4.attrid=72 and l4.valdate>=@P2 and d3.dataid=l5.id and l5.defid=239157 and l5.attrid=72 and l5.valdate<=@P3
I issue the same query in mgmt studio- slightly modified to declare paramters and pass them as listed below:
declare @p1 int
declare @p2 datetime
declare @p3 datetime
--(@P1 int,@P2 datetime,@P3 datetime)
set @p1='796487'
set @p2='01/01/2010'
set @p3='02/01/2010'
select d3.name as 'Contract Name',
l.valstr as 'Customer Name',
l2.valstr as Region,
l3.valstr as Zone from dtree d1,
dtree d2, dtree d3,
llattrdata l, llattrdata l2,
llattrdata l3,
llattrdata l4,
llattrdata l5
where d1.name='Final Contract' and d1.subtype=31108 and
d1.childcount=0 and d2.dataid=d1.parentid and
d3.dataid=d2.parentid and d3.dataid in
(select a.dataid from dtreeancestors a where a.ancestorid=@P1)
and d3.dataid=l.id and
l.defid=21316 and l.attrid=15
and d3.dataid=l2.id and l2.defid=21316
and l2.attrid=67 and d3.dataid=l3.id
and l3.defid=21316 and l3.attrid=66 and d3.dataid=l4.id and l4.defid=239157 and
l4.attrid=72 and l4.valdate>=@P2 and d3.dataid=l5.id and l5.defid=239157 and l5.attrid=72 and l5.valdate<=@P3
From the application, it issues the query in parrellel, something like 47 threads and it never finishes- all CXPACKET waits. From managment studio, it completes in a few seconds and runs in a single thread.
I know I could ask the application group to give a query hint like MAXDOP=1 or something, but I want to understand what is causing this difference... for the record, I am sure that both queries are using the same parameters. I can post the ddl for all the tables involved if neccesary, but in this case, I'm not sure that's really relevant.
let me know what you think- any and all input would be greatly appreciated.
December 15, 2010 at 2:18 pm
Any chance of getting this query into a stored procedure rather than being called from the application directly? With the way the query is written I'm sure the optimizer is having a field day with it. Better T-SQL programmers than me will jump in on this, I'm sure but at minimum I'd be changing the query thusly:
declare @p1 int
declare @p2 datetime
declare @p3 datetime
--(@P1 int,@P2 datetime,@P3 datetime)
set @p1 = '796487'
set @p2 = '01/01/2010'
set @p3 = '02/01/2010'
select d3.name as 'Contract Name'
, l.valstr as 'Customer Name'
, l2.valstr as Region
, l3.valstr as Zone
from dtree d1
INNER JOIN dtree d2
ON d2.dataid = d1.parentid
INNER JOIN dtree d3
ON d3.dataid = d2.parentid
INNER JOIN llattrdata l
ON d3.dataid = l.id
INNER JOIN llattrdata l2
ON d3.dataid = l2.id
INNER JOIN llattrdata l3
ON d3.dataid = l3.id
INNER JOIN llattrdata l4
ON d3.dataid = l4.id
INNER JOIN llattrdata l5
ON d3.dataid = l5.id
where d1.name = 'Final Contract'
and d1.subtype = 31108
and d1.childcount = 0
and d3.dataid in ( select a.dataid
from dtreeancestors a
where a.ancestorid = @P1 )
and l.defid = 21316
and l.attrid = 15
and l2.defid = 21316
and l2.attrid = 67
and l3.defid = 21316
and l3.attrid = 66
and l4.defid = 239157
and l4.attrid = 72
and l4.valdate >= @P2
and l5.defid = 239157
and l5.attrid = 72
and l5.valdate <= @P3
December 15, 2010 at 2:42 pm
no doubt there is room to improve the app code, but I can't go back to them and recommend that route as the fix for this problem since the query completes in an acceptable amount of time as written... really my question is why does the optimizer appear to be choosing a different plan when the application issues it as opposed to when I issue it through MGMT studio...
If I were able to recreate the problem in managment studio, then I could see justificatoin for changing the query, but I can't make the problem happen in mgmt studio... I can see it happen in activity monitor and in SQL Profiler, but I cant determine why it's executing differently.
December 15, 2010 at 2:49 pm
If you need an argument, Non-ANSI join syntax is deprecated, so it will need to be removed/changed in the near future. Also, their non-optimized code creates great risk of locking / blocking.
As to why the difference, without access to the system I'd say it's something related to how they are connecting / the connection properties.
Have you looked at the query plan by any chance?
December 15, 2010 at 3:13 pm
My thought was the same, but I mirrored the connection properties from the app as show in Profiler, and applied those in mgmt studio and didnt get the same result.
Didnt capture the query plan during my first trace, but will get it this evening or tomorrow and see if that gives me some clues.
December 15, 2010 at 3:28 pm
Before I started the second trace I issued DBCC FREEPROCCACHE... and subsequent runs from the application completed quickly.
It's generating the same plan as I got from mgmt studio and completing quickly... I should have run the trace first to see what the bad plan looked like, but I didnt think about it until after...
December 15, 2010 at 5:48 pm
bad plan got cached.
December 16, 2010 at 5:59 am
That is my opinion also, but how to explain the difference in query plan from the application as opposed to Managment Studio?
December 16, 2010 at 6:58 am
figured out my own question: since the query I was using was slightly different, the optimizer generated a new plan.
OK- so then is there any way to determine why the bad plan was originally generated? My first thought is atypical parameters passed on the first execution, but I guess at this point there is no way to be sure....
December 16, 2010 at 7:40 am
Because of how the application is handling this query - not to mention the fact that the query itself is very poorly written* - it's bound to wind up with oddball results and bad query plans. You're likely to run into this again.
Also, make sure you're staying on top of your indexing and index / statistics maintenance.
*I noticed another spot for easy improvement in the query. There's no need whatsoever to join to the same table twice to get the date range. Just use BETWEEN or AND.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply