DBCC SHOWCONTIG IndexID (0,255)

What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
Mike
Hi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike
|||Richard's explanation below is the correct one of the two posted.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
>
sql

DBCC SHOWCONTIG IndexID (0,255)

What do the output from DBCC SHOWCONTIG, IndexID values of
0 and 255 correspond to?
Thanks
MikeHi ,
0 = Data pages
1 = Clustered index
>1 and <=249 = Nonclustered
255 = Entry for tables that have text or image data
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
will have indid between 2 and 250. 251-254 is reserved. and 255 is for text
and image only.
richard
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> What do the output from DBCC SHOWCONTIG, IndexID values of
> 0 and 255 correspond to?
> Thanks
> Mike|||Richard's explanation below is the correct one of the two posted.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:##LAbjdZEHA.384@.TK2MSFTNGP10.phx.gbl...
> Indid = 0 means a heap. indid = 1 is a clustered index. nonclustered index
> will have indid between 2 and 250. 251-254 is reserved. and 255 is for
text
> and image only.
>
> richard
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2947101c465c4$ea118a70$a601280a@.phx.gbl...
> >
> > What do the output from DBCC SHOWCONTIG, IndexID values of
> > 0 and 255 correspond to?
> >
> > Thanks
> >
> > Mike
>

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 inputbuffer and long commands

We have some commands here that go over 2k characters, and I've seen
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
Josh
On Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default...b;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.
|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com

dbcc inputbuffer and long commands

We have some commands here that go over 2k characters, and I've seen
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
JoshOn Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com