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 with OR never completes

This runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
and this runs 'Instantly':
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND
schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
So why does this check ok but never complete when I run it:
SELECT J.JobID, J.JobName, J.CustName
FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
'11/4/2005')
OR J2.Jobid IN (SELECT jobid
FROM jobs WHERE jobname IN
(SELECT parent FROM Jobs WHERE
parent IS NOT NULL AND schedtdate
BETWEEN '1/1/2005' AND '11/4/2005')
Same exact where clauses OR'd
JobId is Identity and Primary
Bob Confused and StupidLook at the estimated execution plan. That should tell you what you need
to know.
one alternate way to do it, if the OR won't optimize is to UNION the two
queries together.
rvgrahamsevatenein@.sbcglobal.net wrote:

>This runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>and this runs 'Instantly':
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
>So why does this check ok but never complete when I run it:
>SELECT J.JobID, J.JobName, J.CustName
>FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
>'11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
>Same exact where clauses OR'd
>JobId is Identity and Primary
>Bob Confused and Stupid
>
>|||SQL gets compiled into an execution plan (how the processor navigates
through tables and indexes), before it is run, and even seemingly
insignificant changes in the SQL can result in an entirely different plan.
Using the Show Execution Plan feature of Query Analyzer, see how the plan is
changed when you instroduce the OR condition. If table scans are being
performed, then you may need to implement a new index.
Graphically Displaying the Execution Plan Using SQL Query Analyzer
http://msdn.microsoft.com/library/d... />
1_5pde.asp
Tips on Optimizing SQL Server Indexes
http://www.sql-server-performance.c...ing_indexes.asp
<rvgrahamsevatenein@.sbcglobal.net> wrote in message
news:1131126018.213438.13380@.g43g2000cwa.googlegroups.com...
> This runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> and this runs 'Instantly':
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND
> schedtdate BETWEEN '1/1/2005' AND '11/4/2005')
> So why does this check ok but never complete when I run it:
> SELECT J.JobID, J.JobName, J.CustName
> FROM JOBS J Join Jobs J2 on J.Jobid = J2.JobID
> WHERE (J.Parent is null and J.SchedTDate between '1/1/2005' AND
> '11/4/2005')
> OR J2.Jobid IN (SELECT jobid
> FROM jobs WHERE jobname IN
> (SELECT parent FROM Jobs WHERE
> parent IS NOT NULL AND schedtdate
> BETWEEN '1/1/2005' AND '11/4/2005')
> Same exact where clauses OR'd
> JobId is Identity and Primary
> Bob Confused and Stupid
>|||I changed a couple of things and execution came down from 55 seconds (I
thought it was never completing, but it was) to about 5 seconds.
Changing the "Between" on the dates to ">=...and <+" seemed to result
in a completely different execution plan. Strange since in the
"Between" version Sql was using ">=...and <+" anyway!
Bob Graham|||My co-worker here had the same problem yesterday in ORACLE.
She had a query with a few ORs and NOT INs.
After running for about 7 minutes she cancelled the query and called me
over.
I suggested changing the NOT INs to NOT EXISTs but still the same problem.
The next suggestion was separate the query in 3 and UNION them.
It ran in under 2 seconds.
Go figure, Microsoft and Oracle agree on something...
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:uOy8QhW4FHA.2888@.tk2msftngp13.phx.gbl...
> Look at the estimated execution plan. That should tell you what you need
> to know.
> one alternate way to do it, if the OR won't optimize is to UNION the two
> queries together.
> rvgrahamsevatenein@.sbcglobal.net wrote:
>|||Hmmm, Union worked well. Wish I had time to learn more about what was
wrong with the OR version, but must press on... Thank You!|||I've seen a lot of things like that. Looks like the optimizer likes to
go for a table scan when it encounters an OR.
Yet it seems to be more willing to find a good plan for every branch of
a UNION...

Query to find who runs jobs

I would like to create a query to find what user owns the job. It probably is in the master db, but I wouldn't know where to begin other than that. Telling me how to either change the job owner or create a job through t-sql would also help. Thanks
-Kyle

Not quite. You should check msdb for all things SQL Agent/job related.

Rather than querying the tables directly though, i'd recommend using the system sprocs:

Check out sp_update_job, sp_add_job, sp_help_job (plus associated sprocs) in Books Online.

HTH!

Query to find databases in full mode

I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.

I really need something along the lines of select db from sys? where mode = 'Full'

Any help appreciated.

MPM

select * from sys.databases

where recovery_model_desc = 'FULL'

Query time massively different between App and QA

We have a query (a few actually) which runs for about 30 secs via Siebel 6 and the same query takes on 1 or 2 secs in Query Analyser, consistently. Naturally this performance problem is causing issues. We're wondering if the App isn't using the same execution plan, or using the indexes or...
It might be worth noting that the query returns 1 or no rows.
(I can add the query and more detail if anyone really, really wants :rolleyes: )

The devlopers have created a VB app which mimmics to app running the query and we've put it through proflier, results..
Duration Reads CPU
Siebel Query 28300 3661280 23984

It seems a high number of reads there, and the result from QA is SO much faster.
Any ideas welcome, thanks.Mmmmmmm, looks to me like your application is likely to be the root cause. How does Siebel access the DB? Named Pipes, TCPIP?|||I'm told Siebel uses Names Pipes.|||Mmmmm, seen named pipes cause an issue before, I think the app ends up scanning through several ports before finding the correct one?

Will do some head scratching|||There are a number of tools that use an older TDS library (some even using DB-Library) that get lousy performance from queries that run well in Query Analyzer. It seems to me that Siebel can be fixed by simply upgrading the client machine's MDAC (http://msdn.microsoft.com/data/mdac/default.aspx), but I don't regularly run Siebel so I'm not certain of that.

-PatP|||Now that is a smart idea. JamesB, you may want to check which version of MDAC the client machines have. I think the current version of MDAC is 2,7. 2.8 has apparently just been released but judging from all the woes on the forum I doubt that would be the one to use at the moment.

Ideally, the client and server MDACs should be the same version.