QUERY THAT WILL RUN FOR EVER IN ONE DATABASE - BUT COMPLETES IN 1 MINUTE IN ANOTHER

  • 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?

  • Just a silly though, have you try to reboot it? 

     

     

    mom

  • Couldnt afford being a 24/7 system...

  • Can you provide the query?  Even better include screen shots of the query plans from both servers.

    Might help...

     

    Joe

     

  • 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.

  • 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?

  • 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.

     

  • 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.

     

  • 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.

  • 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

  • Do you have SP4 applied. I read several posts about performance issues after SP4 was applied.

  • 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

  • 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

     

  • 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

  • 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