DBCC PROCCACHE question

How can I change procedure cache values.
Is it possible to change them only for one procedure.
If I have a huge procedure and small one I would like to allocate
values for both of them accordingly."inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0409150533.65df88b3@.posting.google.c om...
> How can I change procedure cache values.
> Is it possible to change them only for one procedure.
> If I have a huge procedure and small one I would like to allocate
> values for both of them accordingly.

You can't - MSSQL creates execution plans and manages the procedure cache
automatically. You can recompile a procedure, which forces MSSQL to create a
new plan (for example, after adding a new index), and you can empty the
entire cache, but that's about it. Check out "Execution Plan Caching and
Reuse" in Books Online for more details on how the cache works.

If you're having a specific problem, perhaps you could give some more
information, and someone may be able to suggest something.

Simon

DBCC DBREINDEX

After we run a dbcc dbreindex for a specific table, we have noticed a huge
drop in, Logical Scan Fragmentation and definite performance improvements.
However, Extent Scan Fragmentation still seems to remain quite high, in most
cases over 50%.
Is there any reason for this?
ThxThis is expected when the index spans multiple files. Also, the number is
not relevant for heaps (tables with no clustered index).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx|||Hi,
Have a look into this article:-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:F92FD054-4D27-4C2C-BADD-443F0946F14F@.microsoft.com...
> After we run a dbcc dbreindex for a specific table, we have noticed a huge
> drop in, Logical Scan Fragmentation and definite performance improvements.
> However, Extent Scan Fragmentation still seems to remain quite high, in
> most
> cases over 50%.
> Is there any reason for this?
> Thx