Query Would Run For Ever - No Clue

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?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.

e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns

Query work on developement sql server 2005 but not on the production server

This is the most rediculous situation.

I have a sql server 2005 running on my computer.

When I go to the clients site they have set up a Sql server 2005 over there.

The query works on my computer but not on the clients machine.

I had them install the latest Service packs and hotfixes but no luck.

You don't have to read the entire query at the button here is what makes it stop working.

Or at least slow down enough to time out.

Its there where clause.

Without it it work great, with it it fails.

What funny is that when I have it set to just one check it works

WHERE Tbl_MainAgents.AgentCommissionCode ='3'

when I check for multiple values it fails

WHERE Tbl_MainAgents.AgentCommissionCode ='3'

or Tbl_MainAgents.AgentCommissionCode ='4'

or Tbl_MainAgents.AgentCommissionCode ='5'

I have trying doing the check using HAVING and I've tried it in the join, I've tried several different was of check for a 3, 4, or 5 but no luck

I even took a backup of the database on the clients machine, restored i on my machine and then it works.

I'm trying to avoid asking the clients to reinstall thier sql server. I already blamed there server by making them install the hotfixes.

here is the query :

SELECTMin([Qry_DetailCommType3&4&5_A].transaction_id)AS FirstOftransaction_id,

[Qry_DetailCommType3&4&5_A].store_no,

[Qry_DetailCommType3&4&5_A].store_name,

[Qry_DetailCommType3&4&5_A].transaction_date,

[Qry_DetailCommType3&4&5_A].PayableSubAgent,

Tbl_SubAgents.SubAgentDesc,

[Qry_DetailCommType3&4&5_A].PayableAgent,

Tbl_MainAgents.AgentName,

Tbl_MainAgents.TypeAS AgentType,

Tbl_MainAgents.AgentCommissionCode,

Tbl_AgentCommCode.AgentCommDesc,

Sum([Qry_DetailCommType3&4&5_A].NoOfCoupon)AS NoOfCoupon,

Sum([Qry_DetailCommType3&4&5_A].Food)AS Food,

Sum([Qry_DetailCommType3&4&5_A].NonHiloHattie)AS NonHiloHattie,

Sum([Qry_DetailCommType3&4&5_A].PrivateLabel)AS PrivateLabel,

Sum([Qry_DetailCommType3&4&5_A].Media)AS Media,

Sum([Qry_DetailCommType3&4&5_A].Others)AS Others,

Sum([Qry_DetailCommType3&4&5_A].SaleItems)AS SaleItems,

Sum([Qry_DetailCommType3&4&5_A].NonCommissionable)AS NonCommissionable,

Sum([Qry_DetailCommType3&4&5_A].InvalidClass)AS InvalidClass,

Tbl_SubAgents.C_PrivateLabel,

Tbl_SubAgents.C_Others,

Tbl_SubAgents.C_NonHiloHattie,

Tbl_SubAgents.C_Media,

Tbl_SubAgents.C_Food,

Tbl_SubAgents.C_SaleItems,

Sum([Qry_DetailCommType3&4&5_A].GwpMugs)AS GwpMugs,

Sum([Qry_DetailCommType3&4&5_A].GwpTshirt)AS GwpTshirt,

Sum([Qry_DetailCommType3&4&5_A].GwpCandies)AS GwpCandies,

Sum([Qry_DetailCommType3&4&5_A].GwpOthers)AS GwpOthers,

Sum([Qry_DetailCommType3&4&5_A].GwpSpecialty)AS GwpSpecialty,

Sum([Qry_DetailCommType3&4&5_A].GwpTowels)AS GwpTowels,

Tbl_SubAgents.MugsDeduction,

Tbl_SubAgents.CandiesDeduction,

Tbl_SubAgents.TShirtsDeduction,

Tbl_SubAgents.OthersDeduction,

Tbl_SubAgents.TowelsDeduction,

Tbl_SubAgents.SpecialtyDeductions,

Sum([Qry_DetailCommType3&4&5_A].CountOfgc)AS CountOfgc,

Sum([Qry_DetailCommType3&4&5_A].gross_line_amount)AS gc_amount,

Sum([Qry_DetailCommType3&4&5_A].[GCDeduc$])AS [GCDeduc$],

'Coupons'AS CommType,

[Qry_DetailCommType3&4&5_A].period,

[Qry_DetailCommType3&4&5_A].Month_Year

FROM(Tbl_MainAgents InnerJOIN Tbl_AgentCommCode ON Tbl_AgentCommCode.AgentCommCode = Tbl_MainAgents.AgentCommissionCode )

InnerJOIN([Qry_DetailCommType3&4&5_A] INNERJOIN Tbl_SubAgents ON [Qry_DetailCommType3&4&5_A].PayableSubAgent = Tbl_SubAgents.SubAgent)ON Tbl_MainAgents.MainAgent = Tbl_SubAgents.MainAgent

WHERE Tbl_MainAgents.AgentCommissionCode ='3'or Tbl_MainAgents.AgentCommissionCode ='4'or Tbl_MainAgents.AgentCommissionCode ='5'

GROUPBY [Qry_DetailCommType3&4&5_A].store_no,

[Qry_DetailCommType3&4&5_A].store_name,

[Qry_DetailCommType3&4&5_A].transaction_date,

[Qry_DetailCommType3&4&5_A].PayableSubAgent,

Tbl_SubAgents.SubAgentDesc,

[Qry_DetailCommType3&4&5_A].PayableAgent,

Tbl_MainAgents.AgentName,

Tbl_MainAgents.Type,

Tbl_MainAgents.AgentCommissionCode,

Tbl_AgentCommCode.AgentCommDesc,

Tbl_SubAgents.C_PrivateLabel,

Tbl_SubAgents.C_Others,

Tbl_SubAgents.C_NonHiloHattie,

Tbl_SubAgents.C_Media,

Tbl_SubAgents.C_Food,

Tbl_SubAgents.C_SaleItems,

Tbl_SubAgents.MugsDeduction,

Tbl_SubAgents.CandiesDeduction,

Tbl_SubAgents.TShirtsDeduction,

Tbl_SubAgents.OthersDeduction,

Tbl_SubAgents.TowelsDeduction,

Tbl_SubAgents.SpecialtyDeductions,

[Qry_DetailCommType3&4&5_A].period, [Qry_DetailCommType3&4&5_A].Month_Year

What's the error? Can you post it?

Adamus

|||

I'm not at the client site now but I think eventualy a timeout message came.

Sometime I get a timeout, sometimes it just keeps going. I let the timer go for more than 16 minuets.

|||when I don't have the "where " in the query it only takes 9 seconds|||

How many records are we talking about?

Is this a production server?

Is the table being locked somehow?

Is the field indexed?

If you add one check to the where clause does it take 9 seconds, then 2 checks = 18 secs?...

Just a few raw thoughts.

Adamus

|||

Is there an index on Tbl_MainAgents.AgentCommissionCode ?

What does the query plan show?

Are their statistics current?

Have you run it through profiler?

|||

It returns 20,000 records.
The server than can not handle this sql is in production.
On my my personal computer (which is not 1/2 as good as the production server) it works fine. Even with the where clause.

I don't know if the table is being locked. I assume not because other querries work.
And when I don't check for agentcommcode more than once it works.

Agentcommcode is a varchar(2). It is a primary key. I don't know why they made it a varchar(2) instead of an int. but I don't want to change it, might break some code somewhere.

with One check

where agencCommCode = '3' it takes 9 seconds

with 2 checks

where agencCommCode = '3' or agencCommCode = '4' take forever

I am very new to sql server 2005, I havn't tried statistics, or lookin at the query plan.

But I took a back up of the database from the production server.
Restored in on my own little PC. Won't all the indexes and query plan come over with that.
When I run the exact database that I backed up from production to my pc it works great.

|||

I'd be interested in see the query plans generated for the differing WHERE clauses.

They haven't tried to do any manual "tuning" on the server have they? Like using fibers or restricting CPU selection, etc?

What else runs on this same machine?

Is there another DB used by another app that is intensive?

|||

I can't answer some of these questions becuase I am not on the clients machine.

I am on my own computer, where everything works fine.

I guess I'll just tell them to reinstall thier sql server.

After that I'll go over there and if the problem still exists, I'll try looking at statistic, and query plan and all that.

If that doesn't work I might tell them to use sql server 2000. I've never had any problem with that. And I do miss enterprise manager.

|||

Just out of mere curiousity, if you change the = '1' to LIKE '%1%' Does it effect the query time even with 3 checks?

(I know it'll return undesired results but I'm curious about the datatype)

Adamus

|||

Again I am currently on my own computer, where everything works fine

changing to like didn't make any difference,

I'll try it on the clients machine and let you know. That is if we still have the problem after reinstalling sql serve 2005,

|||

ksy

Do they have an anti-virus running on that production box?

If so, have they excluded file extensions .mdf, .ndf, and .ldf from the AV processing?

If AV is on the box and they haven't excluded the SS files, that can be an issue.

|||

No there was not anitvirus on that machine.

I delete the table Tbl_AgentCommCode and its relationshipt with the table Tbl_mainAgent and then recreate this table.

This fixed it. I have no idea why. Whats funny is that before I recreate this table I tried my query without this table and it still didn't work.

Anyway now it works. Thanks everybody who tried to help.

|||

Ok, but you don't want to do that every time a query is slow, do you?

It was likely one of a few things.

* Index Stats

* Index fragmentation

* Poor stored query plan (though if you altered it for the test that was probably not it.)

Updating stats would have probably done it, or rebuilding indexes. You really need to look at the plan of a query using Management Studio or Query Analyzer to see what isn't happening on one server and not the other. It will save you time in the future to try these things first.

sql

Query work on developement sql server 2005 but not on the production server

This is the most rediculous situation.

I have a sql server 2005 running on my computer.

When I go to the clients site they have set up a Sql server 2005 over there.

The query works on my computer but not on the clients machine.

I had them install the latest Service packs and hotfixes but no luck.

You don't have to read the entire query at the button here is what makes it stop working.

Or at least slow down enough to time out.

Its there where clause.

Without it it work great, with it it fails.

What funny is that when I have it set to just one check it works

WHERE Tbl_MainAgents.AgentCommissionCode = '3'

when I check for multiple values it fails

WHERE Tbl_MainAgents.AgentCommissionCode = '3'

or Tbl_MainAgents.AgentCommissionCode = '4'

or Tbl_MainAgents.AgentCommissionCode = '5'

I have trying doing the check using HAVING and I've tried it in the join, I've tried several different was of check for a 3, 4, or 5 but no luck

I even took a backup of the database on the clients machine, restored i on my machine and then it works.

I'm trying to avoid asking the clients to reinstall thier sql server. I already blamed there server by making them install the hotfixes.

here is the query :

SELECT Min([Qry_DetailCommType3&4&5_A].transaction_id) AS FirstOftransaction_id,

[Qry_DetailCommType3&4&5_A].store_no,

[Qry_DetailCommType3&4&5_A].store_name,

[Qry_DetailCommType3&4&5_A].transaction_date,

[Qry_DetailCommType3&4&5_A].PayableSubAgent,

Tbl_SubAgents.SubAgentDesc,

[Qry_DetailCommType3&4&5_A].PayableAgent,

Tbl_MainAgents.AgentName,

Tbl_MainAgents.Type AS AgentType,

Tbl_MainAgents.AgentCommissionCode,

Tbl_AgentCommCode.AgentCommDesc,

Sum([Qry_DetailCommType3&4&5_A].NoOfCoupon) AS NoOfCoupon,

Sum([Qry_DetailCommType3&4&5_A].Food) AS Food,

Sum([Qry_DetailCommType3&4&5_A].NonHiloHattie) AS NonHiloHattie,

Sum([Qry_DetailCommType3&4&5_A].PrivateLabel) AS PrivateLabel,

Sum([Qry_DetailCommType3&4&5_A].Media) AS Media,

Sum([Qry_DetailCommType3&4&5_A].Others) AS Others,

Sum([Qry_DetailCommType3&4&5_A].SaleItems) AS SaleItems,

Sum([Qry_DetailCommType3&4&5_A].NonCommissionable) AS NonCommissionable,

Sum([Qry_DetailCommType3&4&5_A].InvalidClass) AS InvalidClass,

Tbl_SubAgents.C_PrivateLabel,

Tbl_SubAgents.C_Others,

Tbl_SubAgents.C_NonHiloHattie,

Tbl_SubAgents.C_Media,

Tbl_SubAgents.C_Food,

Tbl_SubAgents.C_SaleItems,

Sum([Qry_DetailCommType3&4&5_A].GwpMugs) AS GwpMugs,

Sum([Qry_DetailCommType3&4&5_A].GwpTshirt) AS GwpTshirt,

Sum([Qry_DetailCommType3&4&5_A].GwpCandies) AS GwpCandies,

Sum([Qry_DetailCommType3&4&5_A].GwpOthers) AS GwpOthers,

Sum([Qry_DetailCommType3&4&5_A].GwpSpecialty) AS GwpSpecialty,

Sum([Qry_DetailCommType3&4&5_A].GwpTowels) AS GwpTowels,

Tbl_SubAgents.MugsDeduction,

Tbl_SubAgents.CandiesDeduction,

Tbl_SubAgents.TShirtsDeduction,

Tbl_SubAgents.OthersDeduction,

Tbl_SubAgents.TowelsDeduction,

Tbl_SubAgents.SpecialtyDeductions,

Sum([Qry_DetailCommType3&4&5_A].CountOfgc) AS CountOfgc,

Sum([Qry_DetailCommType3&4&5_A].gross_line_amount) AS gc_amount,

Sum([Qry_DetailCommType3&4&5_A].[GCDeduc$]) AS [GCDeduc$],

'Coupons' AS CommType,

[Qry_DetailCommType3&4&5_A].period,

[Qry_DetailCommType3&4&5_A].Month_Year

FROM (Tbl_MainAgents Inner JOIN Tbl_AgentCommCode ON Tbl_AgentCommCode.AgentCommCode = Tbl_MainAgents.AgentCommissionCode )

Inner JOIN ([Qry_DetailCommType3&4&5_A] INNER JOIN Tbl_SubAgents ON [Qry_DetailCommType3&4&5_A].PayableSubAgent = Tbl_SubAgents.SubAgent) ON Tbl_MainAgents.MainAgent = Tbl_SubAgents.MainAgent

WHERE Tbl_MainAgents.AgentCommissionCode = '3' or Tbl_MainAgents.AgentCommissionCode = '4' or Tbl_MainAgents.AgentCommissionCode = '5'

GROUP BY [Qry_DetailCommType3&4&5_A].store_no,

[Qry_DetailCommType3&4&5_A].store_name,

[Qry_DetailCommType3&4&5_A].transaction_date,

[Qry_DetailCommType3&4&5_A].PayableSubAgent,

Tbl_SubAgents.SubAgentDesc,

[Qry_DetailCommType3&4&5_A].PayableAgent,

Tbl_MainAgents.AgentName,

Tbl_MainAgents.Type,

Tbl_MainAgents.AgentCommissionCode,

Tbl_AgentCommCode.AgentCommDesc,

Tbl_SubAgents.C_PrivateLabel,

Tbl_SubAgents.C_Others,

Tbl_SubAgents.C_NonHiloHattie,

Tbl_SubAgents.C_Media,

Tbl_SubAgents.C_Food,

Tbl_SubAgents.C_SaleItems,

Tbl_SubAgents.MugsDeduction,

Tbl_SubAgents.CandiesDeduction,

Tbl_SubAgents.TShirtsDeduction,

Tbl_SubAgents.OthersDeduction,

Tbl_SubAgents.TowelsDeduction,

Tbl_SubAgents.SpecialtyDeductions,

[Qry_DetailCommType3&4&5_A].period, [Qry_DetailCommType3&4&5_A].Month_Year

What's the error? Can you post it?

Adamus

|||

I'm not at the client site now but I think eventualy a timeout message came.

Sometime I get a timeout, sometimes it just keeps going. I let the timer go for more than 16 minuets.

|||when I don't have the "where " in the query it only takes 9 seconds|||

How many records are we talking about?

Is this a production server?

Is the table being locked somehow?

Is the field indexed?

If you add one check to the where clause does it take 9 seconds, then 2 checks = 18 secs?...

Just a few raw thoughts.

Adamus

|||

Is there an index on Tbl_MainAgents.AgentCommissionCode ?

What does the query plan show?

Are their statistics current?

Have you run it through profiler?

|||

It returns 20,000 records.
The server than can not handle this sql is in production.
On my my personal computer (which is not 1/2 as good as the production server) it works fine. Even with the where clause.

I don't know if the table is being locked. I assume not because other querries work.
And when I don't check for agentcommcode more than once it works.

Agentcommcode is a varchar(2). It is a primary key. I don't know why they made it a varchar(2) instead of an int. but I don't want to change it, might break some code somewhere.

with One check

where agencCommCode = '3' it takes 9 seconds

with 2 checks

where agencCommCode = '3' or agencCommCode = '4' take forever

I am very new to sql server 2005, I havn't tried statistics, or lookin at the query plan.

But I took a back up of the database from the production server.
Restored in on my own little PC. Won't all the indexes and query plan come over with that.
When I run the exact database that I backed up from production to my pc it works great.

|||

I'd be interested in see the query plans generated for the differing WHERE clauses.

They haven't tried to do any manual "tuning" on the server have they? Like using fibers or restricting CPU selection, etc?

What else runs on this same machine?

Is there another DB used by another app that is intensive?

|||

I can't answer some of these questions becuase I am not on the clients machine.

I am on my own computer, where everything works fine.

I guess I'll just tell them to reinstall thier sql server.

After that I'll go over there and if the problem still exists, I'll try looking at statistic, and query plan and all that.

If that doesn't work I might tell them to use sql server 2000. I've never had any problem with that. And I do miss enterprise manager.

|||

Just out of mere curiousity, if you change the = '1' to LIKE '%1%' Does it effect the query time even with 3 checks?

(I know it'll return undesired results but I'm curious about the datatype)

Adamus

|||

Again I am currently on my own computer, where everything works fine

changing to like didn't make any difference,

I'll try it on the clients machine and let you know. That is if we still have the problem after reinstalling sql serve 2005,

|||

ksy

Do they have an anti-virus running on that production box?

If so, have they excluded file extensions .mdf, .ndf, and .ldf from the AV processing?

If AV is on the box and they haven't excluded the SS files, that can be an issue.

|||

No there was not anitvirus on that machine.

I delete the table Tbl_AgentCommCode and its relationshipt with the table Tbl_mainAgent and then recreate this table.

This fixed it. I have no idea why. Whats funny is that before I recreate this table I tried my query without this table and it still didn't work.

Anyway now it works. Thanks everybody who tried to help.

|||

Ok, but you don't want to do that every time a query is slow, do you?

It was likely one of a few things.

* Index Stats

* Index fragmentation

* Poor stored query plan (though if you altered it for the test that was probably not it.)

Updating stats would have probably done it, or rebuilding indexes. You really need to look at the plan of a query using Management Studio or Query Analyzer to see what isn't happening on one server and not the other. It will save you time in the future to try these things first.

query synonyms

I'm using a full text index to search organization names. My language word
breaker is French (France).
My situation is that if I search for example for a organization with the word
"meilleur" in the name ("meilleur" means "greatest" in english), the answer
that the search will give me (using freetext or formsof(inflectional))is all
the organizations with "meilleur", "bon" and "bonne" in there name. But the
thing is that my client don't want the organizations with "bon" and "bonne"
(who means "good" in english) for there answer.
I made a little search and the answer that I found is that these are synonyms
and they should be listed in my tsfra.xml file. This file looks like this :
- <XML ID="Tahoe Thesaurus">
- <!--
Commented out
<thesaurus xmlns="x-schema:tsSchema.xml">
<expansion>
<sub weight="0.8">Internet Explorer</sub>
<sub weight="0.2">IE</sub>
<sub weight="0.9">IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub weight="1.0">Windows 2000</sub>
</replacement>
<expansion>
<sub weight="0.5">run**</sub>
<sub weight="0.5">jog**</sub>
</expansion>
</thesaurus>
-->
</XML>
there's nothing that indicate that meilleur is synonyms of good.
Know I wonder is there another files somewhere who can have these synonyms?
Could it be in the Microsoft search engine?
Thanks in advance,
Nik
P.S. Sorry for my english
Message posted via http://www.droptable.com
This is hard coded in the stemmer. Unfortunately there is nothing you can do
other than to write your own stemmer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nick via droptable.com" <forum@.droptable.com> wrote in message
news:52A31264BED2E@.droptable.com...
> I'm using a full text index to search organization names. My language
> word
> breaker is French (France).
> My situation is that if I search for example for a organization with the
> word
> "meilleur" in the name ("meilleur" means "greatest" in english), the
> answer
> that the search will give me (using freetext or formsof(inflectional))is
> all
> the organizations with "meilleur", "bon" and "bonne" in there name. But
> the
> thing is that my client don't want the organizations with "bon" and
> "bonne"
> (who means "good" in english) for there answer.
> I made a little search and the answer that I found is that these are
> synonyms
> and they should be listed in my tsfra.xml file. This file looks like this
> :
> - <XML ID="Tahoe Thesaurus">
> - <!--
> Commented out
> <thesaurus xmlns="x-schema:tsSchema.xml">
> <expansion>
> <sub weight="0.8">Internet Explorer</sub>
> <sub weight="0.2">IE</sub>
> <sub weight="0.9">IE5</sub>
> </expansion>
> <replacement>
> <pat>NT5</pat>
> <pat>W2K</pat>
> <sub weight="1.0">Windows 2000</sub>
> </replacement>
> <expansion>
> <sub weight="0.5">run**</sub>
> <sub weight="0.5">jog**</sub>
> </expansion>
> </thesaurus>
>
> -->
> </XML>
>
> there's nothing that indicate that meilleur is synonyms of good.
> Know I wonder is there another files somewhere who can have these
> synonyms?
> Could it be in the Microsoft search engine?
> Thanks in advance,
> Nik
> P.S. Sorry for my english
>
> --
> Message posted via http://www.droptable.com