Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 QUERY NEVER COMPLETES - NO CLUE WHAT TO DO NEXT

Author  Topic 

krishmeena
Starting Member

17 Posts
Posted - 2005-10-25 : 16:16:52
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).

I tried the following:

UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG

Nothing helps... what should I do next?

rob3rto
Starting Member

8 Posts
Posted - 2005-10-25 : 16:28:48
Perhaps you have an uncommited transaction hanging around?

Select @@trancount
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-25 : 16:46:26
None...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-25 : 17:03:08
Could you post the code and DML for the tables involved plus any relevant indexes?

Tara
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-25 : 17:23:07
Sure... The 25 million table is Invoice Fact. Clustered index
is on sales_org_sqn and day_sqn on this table.

Again, to note: The same query against exactly same tables,
# of rows, size, indexes etc on a different server executes
this piece of SQL in 1 minute

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

Thanks
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2005-10-26 : 01:12:26
do they have the same database setup?

run the command and do not restrict the log file growth.

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-10-26 : 01:19:16
I appreciate that it runs OK on the other server, so i reckon there's one of three things [that I can think of!], and of these I would go with (3) because for 1 & 2 we are only talking slowdown, not "never finish"

1) The hardware is slower (or has more work to do) on the slow machine

2) There are some indexes missing - perhaps script the whole DB on both machines and text-compare or use some comparison tool (belt&braces test only!).

3) Something is locking it

"into #ZZT090005WKPO000" might be an issue - could TEMPDB be giving grief? Is TEMPDB getting "schema-locked"? - better to pre-create this table, and then insert into it rather than create on-the-fly (plus you could then put a PK on it which IME makes a dramatic difference to performance of Temp Tables - even if they only have a few rows.

Kristen
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-10-26 : 09:21:15
Here's a suggestion:

Run the query as a COUNT() to see how many records you're going to insert on the production box. Maybe for some reason the data is causing some sort of cartesian product?

Also depending on your indexes, I might change this: 
and a12.fis_mth_sqn in (197, 196, 195, 194, 193, 192, 191, 190, 189, 188, 187, 186)


to this:
and a12.fis_mth_sqn between 186 and 197


Granted that's not going to fix your problem but it makes it easier to read and depending on your indexes may improve the performance by a very small amount.

As Kristen said. If you have a lot of select * into [x] then you could be getting locking. Always create the table first and then use insert into [x] instead.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-26 : 12:36:37
Does sp_who, sp_who2 show anything interesting about blocking?

Tara
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 13:01:54
Thanks for the replies... Even a mere count(*) takes 100% CPU and runs for ever.

I have contacted MSFT support - will post the finding...
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 13:02:58
And there is no other activity on the system - no processes, locks, jobs - CPU is at 2%
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-26 : 13:07:53
What service pack level are you running on each?

Tara
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 13:22:56
SP4 on both
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-10-26 : 13:27:29
SP4 has a performance problem when joining to a numeric column when the other join column is varchar. I believe that's the bug in SP4. Can you confirm if this is the case in your query? It wouldn't explain why it works on the other system though.

Tara
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-10-26 : 13:35:16
Another comment:

Why have "OR" here:

(a11.kit_flg = 'C'
or a11.kit_flg = 'S'
or a11.kit_flg = 'N')


Why not:
a11.kit_flg in ('C', 'S', 'N')


Changing this will most likley help some too.


Try running each of these separately:


SELECT sum(a11.inv_qty) WJXBFS1
FROM dbo.invoice_FACT a11 (NOLOCK)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	and a11.doc_typ_sqn in (4, 6, 84, 12009, 76, 12019, 90, 103, 104, 105)
	and a11.sales_org_sqn = 1)
GO

SELECT top 50 a13.pro_typ_sqn pro_typ_sqn,
	sum(a11.inv_qty) WJXBFS1
FROM dbo.invoice_FACT a11 (NOLOCK)
	INNER JOIN dbo.day a12 (NOLOCK) on (a11.day_sqn = a12.day_sqn)
	INNER JOIN dbo.product a13 (NOLOCK) on (a11.pro_sqn = a13.pro_sqn and a11.sales_org_sqn = a13.sales_org_sqn)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	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 = 1)
group by a13.pro_typ_sqn 
GO

SELECT top 50 a14.cbl_sqn cbl_sqn,
	a17.CG_des CG_des,
	a13.pro_typ_sqn pro_typ_sqn,
	0 XKYCGT,
	sum(a11.inv_qty) WJXBFS1
FROM dbo.invoice_FACT a11 (NOLOCK)
	INNER JOIN dbo.day a12 (NOLOCK) on (a11.day_sqn = a12.day_sqn)
	INNER JOIN dbo.product a13 (NOLOCK) on (a11.pro_sqn = a13.pro_sqn and a11.sales_org_sqn = a13.sales_org_sqn)
	INNER JOIN dbo.CBL_Title a14 (NOLOCK) on (a12.fis_yr_sqn = a14.fis_yr_sqn and a13.tit_sqn = a14.tit_sqn)
	INNER JOIN dbo.customer_level_6 a15 (NOLOCK) on (a11.sales_org_sqn = a15.sales_org_sqn and a11.sold_to_cus_sqn = a15.cus_sqn)
	INNER JOIN dbo.CG_customers_relation a16 (NOLOCK) on (a15.cus_lev4_sqn = a16.cus_lev4_sqn)
	INNER JOIN dbo.CG_keycusFY05 a17 (NOLOCK) on (a16.CG_sqn = a17.CG_sqn)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	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 = 1)
group by a14.cbl_sqn, a17.CG_des, a13.pro_typ_sqn 
GO

SELECT count(*)
FROM dbo.invoice_FACT a11 (NOLOCK)
	INNER JOIN dbo.day a12 (NOLOCK) on (a11.day_sqn = a12.day_sqn)
	INNER JOIN dbo.product a13 (NOLOCK) on (a11.pro_sqn = a13.pro_sqn and a11.sales_org_sqn = a13.sales_org_sqn)
	INNER JOIN dbo.CBL_Title a14 (NOLOCK) on (a12.fis_yr_sqn = a14.fis_yr_sqn and a13.tit_sqn = a14.tit_sqn)
	INNER JOIN dbo.customer_level_6 a15 (NOLOCK) on (a11.sales_org_sqn = a15.sales_org_sqn and a11.sold_to_cus_sqn = a15.cus_sqn)
	INNER JOIN dbo.CG_customers_relation a16 (NOLOCK) on (a15.cus_lev4_sqn = a16.cus_lev4_sqn)
	INNER JOIN dbo.CG_keycusFY05 a17 (NOLOCK) on (a16.CG_sqn = a17.CG_sqn)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	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 = 1)
group by a14.cbl_sqn, a17.CG_des, a13.pro_typ_sqn 
GO

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 (NOLOCK)
	INNER JOIN dbo.day a12 (NOLOCK) on (a11.day_sqn = a12.day_sqn)
	INNER JOIN dbo.product a13 (NOLOCK) on (a11.pro_sqn = a13.pro_sqn and a11.sales_org_sqn = a13.sales_org_sqn)
	INNER JOIN dbo.CBL_Title a14 (NOLOCK) on (a12.fis_yr_sqn = a14.fis_yr_sqn and a13.tit_sqn = a14.tit_sqn)
	INNER JOIN dbo.customer_level_6 a15 (NOLOCK) on (a11.sales_org_sqn = a15.sales_org_sqn and a11.sold_to_cus_sqn = a15.cus_sqn)
	INNER JOIN dbo.CG_customers_relation a16 (NOLOCK) on (a15.cus_lev4_sqn = a16.cus_lev4_sqn)
	INNER JOIN dbo.CG_keycusFY05 a17 (NOLOCK) on (a16.CG_sqn = a17.CG_sqn)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	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 = 1)
group by a14.cbl_sqn, a17.CG_des, a13.pro_typ_sqn 
GO





Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 13:39:24
I checxed yesterday that BETWEEN works.. But the query is generated by a front end app. and I cannot influence it. User can construct any adhoc report (query)...
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-10-26 : 13:52:15
quote:
Originally posted by krishmeena

I checxed yesterday that BETWEEN works.. But the query is generated by a front end app. and I cannot influence it. User can construct any adhoc report (query)...



AH!!! The plot thickens. So you CAN run the query from SQL QA but it fails to load in the front end app? or You CANNOT run the query from SQL QA?

Here's part of the problem in a high transaction db you need to ensure that the tables have the "(NOLOCK)" hint. This will help the query complete under locking problems. If the tables are constantly locked and your query needs some type of shared lock to return the data then it will just sit until it gets a lock or in this case forever... Read about NOLOCK in BOL.

Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 14:07:42
Let me clarify....

The SQL from front end app with a IN clause fails...
Same is the case with SQL Server QA (IN clause)
But, if I try BETWEEN clause in SQL Server QA it works
The other server/database - both IN and BETWEEN works on QA

As the query is basically a report executed by an user
and the user runs a "report" - I have no control over the
SQL generated by the app... I should make the IN work.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-10-26 : 14:16:48
quote:
Originally posted by krishmeena

Let me clarify....

The SQL from front end app with a IN clause fails...
Same is the case with SQL Server QA (IN clause)
But, if I try BETWEEN clause in SQL Server QA it works
The other server/database - both IN and BETWEEN works on QA

As the query is basically a report executed by an user
and the user runs a "report" - I have no control over the
SQL generated by the app... I should make the IN work.


What's generating this? Brio?
Ok. Now we have got somewhere. The query does not work on production if the statement reads:
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


But will work fine if it reads:
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 (NOLOCK)
	INNER JOIN dbo.day a12 (NOLOCK) on (a11.day_sqn = a12.day_sqn)
	INNER JOIN dbo.product a13 (NOLOCK) on (a11.pro_sqn = a13.pro_sqn and a11.sales_org_sqn = a13.sales_org_sqn)
	INNER JOIN dbo.CBL_Title a14 (NOLOCK) on (a12.fis_yr_sqn = a14.fis_yr_sqn and a13.tit_sqn = a14.tit_sqn)
	INNER JOIN dbo.customer_level_6 a15 (NOLOCK) on (a11.sales_org_sqn = a15.sales_org_sqn and a11.sold_to_cus_sqn = a15.cus_sqn)
	INNER JOIN dbo.CG_customers_relation a16 (NOLOCK) on (a15.cus_lev4_sqn = a16.cus_lev4_sqn)
	INNER JOIN dbo.CG_keycusFY05 a17 (NOLOCK) on (a16.CG_sqn = a17.CG_sqn)
WHERE ((a11.kit_flg in ('C', 'S', 'N')
	and a11.sal_typ_sqn in (2)
	and a12.fis_mth_sqn between 186 and 197
	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 = 1)
group by a14.cbl_sqn, a17.CG_des, a13.pro_typ_sqn


And you need the query to work as it is because the application writes poor SQL.

Is that accurate?




Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page

krishmeena
Starting Member

17 Posts
Posted - 2005-10-26 : 16:40:34
Thats correct... except that the piece of SQL that works is:

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 between 186 and 197
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
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2005-10-26 : 16:47:25
quote:
Originally posted by krishmeena

Thats correct... except that the piece of SQL that works is:

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 between 186 and 197
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




Please post a list of the indexes on both production and dev for table A12.

Then also please post the execution plan from production for the broken query and the execution plan from dev for both the broken and working queries.


Daniel, MCP, A+
SQL Server DBA
www.dallasteam.com
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site