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

Question

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

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

icata Hall of Fame Points: 3176 More actions · Answer 1

can you check in the execution plan of the prod server for hash joins and tables scan

Bruce-268684 Grasshopper Points: 11 More actions · Answer 2

Just went through this moving a database from SQL 7 on an old machine to SQL 2000 on a new machine with much more memory. Queries that were instantaneous on old machine took 10+ seconds on new machine. It's a packaged app, could not rewrite query(s). Comparing the execution plans between 7 & 2000, it was apparent that SQL 2000 didn't have a clue.

Workaround - dropped memory in new server from 4 Gb to 1 Gb, runs like a champ while vendor fixes their stuff. If you are dealing with your own database/queries, you can adjust indexing and possibly rewrite the query to give SQL 2000 a fighting chance at generating a proper execution plan.

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 3

Derek here again. Having read the good input from all so far, I would now tend to take an old fashioned approach. You say select count(*) uses 100% cpu. Well to me SQL is taking all the CPU all the time. So I would look very closely at the server configs and sql configs as discussed. Apart from that, try running different types of extremely simple queries like select * from table (NOLOCK) where x = y. In other words is every SQL query using up 100% CPU for everything. My understanding is that SQL uses tempdb when it is has not enough memory to handle it's requests. I would try and break the problem down by gathering more and more info from various senarios. When no query is running how much cpu have you, how big is tempdb etc etc... I hope this is of help. That's my lot, rgds Derek

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 4

Try to use MAXDOP(1) queryhint. Maybe you're experiencing multiprocessors problems

from BOL:

MAXDOP number

Overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option. All semantic rules used with max degree of parallelism configuration option are applicable when using the MAXDOP query hint. For more information, see max degree of parallelism Option.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

krishmeena SSC Enthusiast Points: 156 More actions · Answer 5

MAXDOP doesnt work...

LIST OF INDEXES ON BOTH DATABASES FOR THE BIGGEST TABLE - INVOICE_FACT:

1. Clustered - day_sqn, sales_org_sqn

2. sales_org_sqn

3. inv_nbr, doc_typ_sqn, inv_lin_nbr

4. ord_sqn

5. sold_to_cus_sqn

6. pro_sqn

7. sal_typ_sqn

8. sal_typ

9. trans_typ_sqn

I checked the estimated execution plan on both the databases...

I found a major difference - the one that works uses clustered index

and the one that breaks doesnt use it...

I dont know how to publish the whole execution plan here - its huge.

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 6

can you affort a dbcc dbreindex ?

Indexdefrag does not optimize as well as dbreindex.

Check the execution plan against this 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

and a11.kit_flg in ( 'C','S', 'N')

and a11.sal_typ_sqn = 2

and a11.doc_typ_sqn in (4, 6, 84, 12009, 76, 12019, 90, 103, 104, 105)

and a11.sales_org_sqn = 1

and a12.fis_mth_sqn in (197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186)

join dbo.product a13

on a11.pro_sqn = a13.pro_sqn and

a11.sales_org_sqn = a13.sales_org_sqn

and a13.pro_typ_sqn in (6, 8)

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

group by a14.cbl_sqn,

a17.CG_des,

a13.pro_typ_sqn

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

krishmeena SSC Enthusiast Points: 156 More actions · Answer 7

DBREINDEX was also run on the table (as DBREINDEX updates statistics and DEFRAG doesnt).

krishmeena SSC Enthusiast Points: 156 More actions · Answer 8

RESOLVED:

This is how I resolved it...

No new indexes - no change in anything.

I copied over only the tables involved in the query

to production database - renamed it and ran the query in

production. It works. I dropped the old tables and am using

the new copied over tables now.

Thanks everyone for their time