October 25, 2005 at 3:49 pm
Hello fellow DBAs
I have a strange situation here. I am executing a SQL
query which runs for ever till it fills up all the
available temp space.
The same query runs within 1 minute in another
database on another server. That database is a
development database but with same records (and data). All objects, indexes, data volume, # of records etc are the same in both the databases
I tried the following:
UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG
Nothing helps... what should I do next?
October 25, 2005 at 4:08 pm
Just a silly though, have you try to reboot it?
mom
October 25, 2005 at 4:21 pm
Couldnt afford being a 24/7 system...
October 25, 2005 at 10:59 pm
Can you provide the query? Even better include screen shots of the query plans from both servers.
Might help...
Joe
October 26, 2005 at 2:10 am
Probably not your issue, but are the indexes across both databases identical. Is your query having to do table scans. Have you looked at the Query Governor. Also have you run sp_lock to ensure no intermittent blocking ( hardly likely ). But I would ensure indexes are the same and also what are the spec's for both servers - ram ? processors ?. Hope this helps. Rgds Derek.
October 26, 2005 at 2:50 am
I supposed locking,but you say that query fills tempdb.
What kind of query? And why tempdb is used?
Subselects? Large UNIONs? Views in query?
Did you compare estimated execution plans on both servers?
Are they similar?
Do you see worktable in estimated execution plan on production server?
October 26, 2005 at 3:22 am
This is the query...
select a14.cbl_sqn cbl_sqn,
a17.CG_des CG_des,
a13.pro_typ_sqn pro_typ_sqn,
0 XKYCGT,
sum(a11.inv_qty) WJXBFS1
into #ZZT090005WKPO000
from dbo.invoice_FACT a11
join dbo.day a12
on (a11.day_sqn = a12.day_sqn)
join dbo.product a13
on (a11.pro_sqn = a13.pro_sqn and
a11.sales_org_sqn = a13.sales_org_sqn)
join dbo.CBL_Title a14
on (a12.fis_yr_sqn = a14.fis_yr_sqn and
a13.tit_sqn = a14.tit_sqn)
join dbo.customer_level_6 a15
on (a11.sales_org_sqn = a15.sales_org_sqn and
a11.sold_to_cus_sqn = a15.cus_sqn)
join dbo.CG_customers_relation a16
on (a15.cus_lev4_sqn = a16.cus_lev4_sqn)
join dbo.CG_keycusFY05 a17
on (a16.CG_sqn = a17.CG_sqn)
where ((a11.kit_flg = 'C'
or a11.kit_flg = 'S'
or a11.kit_flg = 'N')
and a11.sal_typ_sqn in (2)
and a12.fis_mth_sqn in (197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186)
and a13.pro_typ_sqn in (6, 8)
and a11.doc_typ_sqn in (4, 6, 84, 12009, 76, 12019, 90, 103, 104, 105)
and a11.sales_org_sqn in (1))
group by a14.cbl_sqn,
a17.CG_des,
a13.pro_typ_sqn
Indexes are identical on both the databases. The server that executes within 1 minute has lesser CPU, RAM.
October 26, 2005 at 4:54 am
My guess is that you might be seeing something like a table spool, where a temporary object is created in tempdb.
You need to check the estimated plan and see where the differences are. Can you see anything odd there?
Is this running as stored proc? If is you might want to try recompiling it after updating the stats.
You might also try running update stats with full_scan if the tables aren't too large.
October 26, 2005 at 6:20 am
Sometimes when comparing queries on different servers, the physical characteristics make a difference. You mentioned that the number of CPUs in each server is different. If you have more CPUs in your production server, that could be the issue. I know that queries will run differently, based on the number of CPUs. I may be out in rright field on this, but it is worth checking out.
October 26, 2005 at 7:51 am
Don't know if this will make a difference, but instead of using SELECT INTO, FROM, try using INSERT INTO, SELECT FROM.
Example:
INSERT INTO #Temp Table (col1, col2, col3, etc)
SELECT (col1, col2, col3, etc)
FROM table1
JOIN table2, etc. etc.
This method has been known to speed up queries.
Sandi
October 26, 2005 at 8:45 am
Do you have SP4 applied. I read several posts about performance issues after SP4 was applied.
October 26, 2005 at 11:25 am
No patches applied... SELECT COUNT(*) also runs for ever and takes 100% CPU...
This is query works with a BETWEEN clause instead of IN clause on the problem database/server. The other database/server both IN and BETWEEN works.
The SQL is generated by a front end app. so I cannot influence the query generation.
No SP used... Temp db available size is 70GB
October 26, 2005 at 11:49 am
If you could get us the query plans for both servers, that would probably help.
One thing you may need to look at is other activity on the production server - I would assume that your production server is busier than your development server. If the production server is already busy doing other things or there are other processes causing blocking in the database that would explain why your queries never complete. Since you're not getting a response at all from the server, the first thing I would look for would be another process blocking your query - kick of your query and then do a sp_who2 to see what, if anything, is blocking your query.
Joe
October 26, 2005 at 11:54 am
Joe
Since I am testing on production server - I am very cautious to make sure there are no processes running... I run this query only when there are no processes, jobs on the server. CPU is around 2%. But after this query starts to run it hits 100% and stays at that... Estimated query plan is huge - not sure how to post it
Thanks
October 26, 2005 at 11:58 am
Good point, I've seen other folks post print screens of their query plans here - but never tried to do it myself - anybody?
Joe
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply