Query Timeout

I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com