Getting Strange Execution Plan w/ DBCC

Hi,
I'm running this:
DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK
And I'm getting this:
Server: Msg 8649, Level 17, State 1, Line 2
The query has been canceled because the estimated cost of this query (13451)
exceeds the configured threshold of 10800. Contact the system administrator.
When I remove the query governor it runs in about 30 seconds not 10800+.
The stats on this database were manually updated with sp_createstats and
sp_updatestats.
Does anyone have any recommendations?
Thanks,
BryanBDB wrote:
> Hi,
> I'm running this:
> DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK
> And I'm getting this:
> Server: Msg 8649, Level 17, State 1, Line 2
> The query has been canceled because the estimated cost of this query
> (13451) exceeds the configured threshold of 10800. Contact the system
> administrator.
> When I remove the query governor it runs in about 30 seconds not
> 10800+. The stats on this database were manually updated with
> sp_createstats and sp_updatestats.
> Does anyone have any recommendations?
> Thanks,
> Bryan
You can remove the query governor setting for the connection using SET
QUERY_GOVERNOR_COST_LIMIT 0. It's possible SQL Server is having a
difficult time determining if the CHECKDB (which could be quite an
intense operation) is going to use more time than the set limit.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||FIX: You receive a "Msg 8649" error message when you execute the DBCC CHECKD
B
statement in SQL Server 2000 Service Pack 3 (SP3)
http://support.microsoft.com/defaul...kb;en-us;840208
AMB
"BDB" wrote:

> Hi,
> I'm running this:
> DBCC CHECKDB ( '<database>', REPAIR_FAST ) WITH ALL_ERRORMSGS, TABLOCK
> And I'm getting this:
> Server: Msg 8649, Level 17, State 1, Line 2
> The query has been canceled because the estimated cost of this query (1345
1)
> exceeds the configured threshold of 10800. Contact the system administrato
r.
> When I remove the query governor it runs in about 30 seconds not 10800+.
> The stats on this database were manually updated with sp_createstats and
> sp_updatestats.
> Does anyone have any recommendations?
> Thanks,
> Bryan
>
>

Getting Plan of previously executed Queries

Hi,
I am newbie to SQL Server. I am using SQL Server 2000.

I know SQL server compiles the SQL stmt or SP and stores the plan for
later use(I know its not always done. But should be done to reduce
execution time) in memory somewere. I want to capute all the execution
plan which are kept in memory. In oracle v$sql_plan can be used to
access the plan in oracle. I want to know how to do that in SQL Server.
I can use profiler, but it contributes some CPU utilization.

Thanks,
Thiru.
WantedToBeDBA
WantedToBeDBA {at} gmail {dot} com"Thiru" <WantedToBeDBA@.gmail.com> wrote in message
news:1117211283.969054.121710@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am newbie to SQL Server. I am using SQL Server 2000.
> I know SQL server compiles the SQL stmt or SP and stores the plan for
> later use(I know its not always done. But should be done to reduce
> execution time) in memory somewere. I want to capute all the execution
> plan which are kept in memory. In oracle v$sql_plan can be used to
> access the plan in oracle. I want to know how to do that in SQL Server.
> I can use profiler, but it contributes some CPU utilization.
> Thanks,
> Thiru.
> WantedToBeDBA
> WantedToBeDBA {at} gmail {dot} com

See syscacheobjects in Books Online. But this only gives you some general
information about what's in the cache; as far as I know, there's no way to
actually view the cached plans.

Profiler is probably the only way to capture this information, and unless
the server is under very heavy load, it shouldn't have much of an impact.
You can use a server-side trace (see sp_trace_create) if you don't want to
leave Profiler running on a client PC.

Simon