hello everyone.
i've got a "name" column that i would like to split into "firstname"
and "lastname" columns. the firstname was no problem (the values have
already been LTRIM and RTRIM'd):
UPDATE test
SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
however i'm curious about how to do the last name. you see, there might
be an arbitrary number of ' ' characters in the "name" column values
(in fact the max is seven). however regardless of how many spaces
occur, or what's inbetween those spaces.
now, i can set up a very complicated nested CHARINDEX/SUBSTRING
analysis for each number of ' ' characters that occur, starting with
the seven space condition, and working my way down. but good grief that
would be complicated.
is there any nifty tricks to find the LAST index of a specified
character in a string, by chance? that would save me a world of hurt.
thanks!
jason> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
How about CHARINDEX(' ', REVERSE(TRIM(name)))
However saving you even more of a world of hurt (e.g. not changing the names
of people like Larry da Costa and Filipe de Simone), you should just grab an
off-the-shelf package for data scrubbing. These have much more advanced
rules, and a much higher success ratio, than anything anyone will be able to
write in T-SQL alone.
A|||Try using reverse to flip the string around
UPDATE test
SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
Archer
"jason" wrote:
> hello everyone.
> i've got a "name" column that i would like to split into "firstname"
> and "lastname" columns. the firstname was no problem (the values have
> already been LTRIM and RTRIM'd):
> UPDATE test
> SET firstname = LEFT(name, CHARINDEX(' ', LTRIM(name), 1) - 1)
> however i'm curious about how to do the last name. you see, there might
> be an arbitrary number of ' ' characters in the "name" column values
> (in fact the max is seven). however regardless of how many spaces
> occur, or what's inbetween those spaces.
> now, i can set up a very complicated nested CHARINDEX/SUBSTRING
> analysis for each number of ' ' characters that occur, starting with
> the seven space condition, and working my way down. but good grief that
> would be complicated.
> is there any nifty tricks to find the LAST index of a specified
> character in a string, by chance? that would save me a world of hurt.
> thanks!
> jason
>|||Jason,
Could you use something like this'
declare @.name varchar(50),
@.FirstName varchar(25),
@.lastName varchar(25)
Set @.name = 'Joe Bloggs'
Set @.firstname = ltrim(rtrim(LEFT(@.name, CHARINDEX(' ', LTRIM(@.name),
1) - 1)))
Set @.LastName =ltrim(rtrim(Right(@.Name, (Len(@.Name) -
Len(@.FirstName)))))
select @.name, @.FirstName, @.lastName
HTH
Barry|||Sorry, it probably should be
UPDATE test
SET lastname = LEFT(REVERSE(name), CHARINDEX(' ', LTRIM(REVERSE(name)), 1) -
1)
Archer
"bagman3rd" wrote:
> Try using reverse to flip the string around
> UPDATE test
> SET lastname = LEFT(name, CHARINDEX(' ', LTRIM(REVERSE(name)), 1) - 1)
> Archer
> "jason" wrote:
>|||> How about CHARINDEX(' ', REVERSE(TRIM(name)))
by TRIM( I meant LTRIM(RTRIM(, of course. Not all of us create a function
called TRIM(). :-)|||AHA! *REVERSE* that's exactly what I was hoping existed. Took a little
tweaking like so:
UPDATE test
SET lastname = REVERSE(LEFT(REVERSE(name), CHARINDEX(' ',
REVERSE(name), 1) - 1))
But that worked like a charm! Thanks very much.
As for the value of the data, it's not worth any scrubbing of note, but
I do appreciate the suggestion for cases that might be.
Jason
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Large Views need index
I have couple of large views to data for reporting. That will be wonderful i
f
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.[url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/url
]
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view..
.
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful
if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
f
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.[url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/url
]
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> [url]http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm[/u
rl]
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view..
.
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful
if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
Large Views need index
I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.
http://www.novicksoftware.com/Articl...SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>
|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...
|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>
|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.
http://www.novicksoftware.com/Articl...SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü?meyer" wrote:
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
>
|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articl...SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>
|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...
|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>
|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldnt suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>
|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...[vbcol=seagreen]
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
Large Views need index
I have couple of large views to data for reporting. That will be wonderful if
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü�meyer" wrote:
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
> >I have couple of large views to data for reporting. That will be wonderful
> >if
> > there is a way I can put indexs on a view. I could'n find a way to do so.
> > Any ideas?
> >
> > Thanks a lot.
> >
> >
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online:)
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be wonderful
>>if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldn´t suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online:)
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
>> (But remember that Index Views are only available in SQL2kEE)
>> HTH, Jens Suessmeyer.
>>
>> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be
>>wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>>
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>> I have couple of large views to data for reporting. That will be
>> wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
there is a way I can put indexs on a view. I could'n find a way to do so.
Any ideas?
Thanks a lot.http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
(But remember that Index Views are only available in SQL2kEE)
HTH, Jens Suessmeyer.
"Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Very good, I was thinking about the indexed view, but I was sure the
overheads it brings. We have a one-day old history database for reporting
only, may be indexed view is the best way . Thanks a lot.
"Jens Sü�meyer" wrote:
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
> >I have couple of large views to data for reporting. That will be wonderful
> >if
> > there is a way I can put indexs on a view. I could'n find a way to do so.
> > Any ideas?
> >
> > Thanks a lot.
> >
> >
>
>|||Jens,
FYI, We can create Indexed views on all editions of SQL Server 2000.
Info from books online:)
Note Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used.
Thanks
Hari
SQL Server MVP
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
> (But remember that Index Views are only available in SQL2kEE)
> HTH, Jens Suessmeyer.
>
> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be wonderful
>>if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>|||>I have couple of large views to data for reporting. That will be wonderful
>if
> there is a way I can put indexs on a view.
In most cases, if you correctly index the base tables, you shouldn't need an
index on the view...|||I used union to combine 2 tables in a view, may not be indexed on the view...
"Catelin Wang" wrote:
> I have couple of large views to data for reporting. That will be wonderful if
> there is a way I can put indexs on a view. I could'n find a way to do so.
> Any ideas?
> Thanks a lot.
>|||Thanks, I knew. Since I am not a fan of using Optimizer Hints, this is not
an option for me and I wouldn´t suggest that to anybody.
Jens.
"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:uFnBFpnfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Jens,
> FYI, We can create Indexed views on all editions of SQL Server 2000.
> Info from books online:)
> Note Indexed views can be created in any edition of SQL Server 2000. In
> SQL Server 2000 Enterprise Edition, the query optimizer will automatically
> consider the indexed view. To use an indexed view in all other editions,
> the NOEXPAND hint must be used.
> Thanks
> Hari
> SQL Server MVP
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uMj89WnfFHA.1416@.TK2MSFTNGP09.phx.gbl...
>> http://www.novicksoftware.com/Articles/Indexed-Views-Basics-in-SQL-Server.htm
>> (But remember that Index Views are only available in SQL2kEE)
>> HTH, Jens Suessmeyer.
>>
>> "Catelin Wang" <CatelinWang@.discussions.microsoft.com> schrieb im
>> Newsbeitrag news:6C62F500-8C4C-4BF8-A8D4-A554D71A60C9@.microsoft.com...
>>I have couple of large views to data for reporting. That will be
>>wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
>> Thanks a lot.
>>
>>
>|||"Catelin Wang" <CatelinWang@.discussions.microsoft.com> wrote in message
news:09C9D493-57C5-464C-A198-1D799FFE0B3E@.microsoft.com...
>I used union to combine 2 tables in a view, may not be indexed on the
>view...
> "Catelin Wang" wrote:
>> I have couple of large views to data for reporting. That will be
>> wonderful if
>> there is a way I can put indexs on a view. I could'n find a way to do
>> so.
>> Any ideas?
Perhaps you can create two indexed views and union those. But perhaps there
is a more direct way to improve your performance.
If you post the table DDL, the view text and a description of how many rows
are in each base table, and how many different values each of the important
columns has, and how many rows you will get back from the query, someone may
suggest an optimization either to your base table indexes or your SQL
design.
David
Monday, February 20, 2012
Large Index
Is it normal for Index size to be three times the size of its corresponding
table in SQL 2005
DBCC SHOWCONTIG scanning 'DSECURITY' table...
Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7950.0
- Avg. Page Density (full)................: 1.78%
DBCC SHOWCONTIG scanning 'MHINFO' table...
Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'VERINFO' table...
Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7389.0
- Avg. Page Density (full)................: 8.71%
DBCC SHOWCONTIG scanning 'ID_TABLE' table...
Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7656.0
- Avg. Page Density (full)................: 5.41%
DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7604.0
- Avg. Page Density (full)................: 6.05%
DBCC SHOWCONTIG scanning 'PALETTE' table...
Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 136
- Extents Scanned.......................: 17
- Extent Switches.......................: 16
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7241.5
- Avg. Page Density (full)................: 10.53%
DBCC SHOWCONTIG scanning 'CUSTOM1' table...
Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2385
- Extents Scanned.......................: 299
- Extent Switches.......................: 298
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.33%
- Avg. Bytes Free per Page................: 7265.2
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7574.0
- Avg. Page Density (full)................: 6.42%
DBCC SHOWCONTIG scanning 'CUSTOM2' table...
Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 7745
- Extents Scanned.......................: 969
- Extent Switches.......................: 968
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.31%
- Avg. Bytes Free per Page................: 7259.7
- Avg. Page Density (full)................: 10.31%
DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 27
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.93%
- Extent Scan Fragmentation ...............: 60.00%
- Avg. Bytes Free per Page................: 7291.8
- Avg. Page Density (full)................: 9.91%
DBCC SHOWCONTIG scanning 'CUSTOM3' table...
Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORMS' table...
Table: 'FORMS' (226099846); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7499.5
- Avg. Page Density (full)................: 7.34%
DBCC SHOWCONTIG scanning 'CUSTOM4' table...
Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM5' table...
Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 52
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7247.9
- Avg. Page Density (full)................: 10.45%
DBCC SHOWCONTIG scanning 'CUSTOM6' table...
Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 355
- Extents Scanned.......................: 49
- Extent Switches.......................: 52
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
- Logical Scan Fragmentation ..............: 6.48%
- Extent Scan Fragmentation ...............: 6.12%
- Avg. Bytes Free per Page................: 6637.2
- Avg. Page Density (full)................: 18.00%
DBCC SHOWCONTIG scanning 'CUSTOM7' table...
Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ROLES' table...
Table: 'ROLES' (354100302); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7815.0
- Avg. Page Density (full)................: 3.45%
DBCC SHOWCONTIG scanning 'CUSTOM8' table...
Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USERROLES' table...
Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7646.0
- Avg. Page Density (full)................: 5.53%
DBCC SHOWCONTIG scanning 'CUSTOM9' table...
Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 28967
- Extents Scanned.......................: 3621
- Extent Switches.......................: 3620
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 3.01%
- Avg. Bytes Free per Page................: 7211.5
- Avg. Page Density (full)................: 10.90%
DBCC SHOWCONTIG scanning 'CUSTOM10' table...
Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 46
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.8
- Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
- Logical Scan Fragmentation ..............: 8.70%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 7281.7
- Avg. Page Density (full)................: 10.04%
DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
- Logical Scan Fragmentation ..............: 87.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 7335.6
- Avg. Page Density (full)................: 9.37%
DBCC SHOWCONTIG scanning 'CUSTOM11' table...
Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7641.0
- Avg. Page Density (full)................: 5.60%
DBCC SHOWCONTIG scanning 'CUSTOM12' table...
Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8040.0
- Avg. Page Density (full)................: 0.67%
DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM29' table...
Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM30' table...
Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM31' table...
Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'RESTORETBL' table...
Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'DOCMASTER' table...
Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 454519
- Extents Scanned.......................: 56834
- Extent Switches.......................: 57417
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
- Logical Scan Fragmentation ..............: 1.10%
- Extent Scan Fragmentation ...............: 2.94%
- Avg. Bytes Free per Page................: 6930.2
- Avg. Page Density (full)................: 14.38%
DBCC SHOWCONTIG scanning 'USERACTIONS' table...
Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7941.0
- Avg. Page Density (full)................: 1.89%
DBCC SHOWCONTIG scanning 'USERHISTORY' table...
Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 114
- Extents Scanned.......................: 24
- Extent Switches.......................: 23
- Avg. Pages per Extent..................: 4.8
- Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
- Extent Scan Fragmentation ...............: 91.67%
- Avg. Bytes Free per Page................: 1233.4
- Avg. Page Density (full)................: 84.76%
DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8063.0
- Avg. Page Density (full)................: 0.38%
DBCC SHOWCONTIG scanning 'SID_TABLE' table...
Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7846.0
- Avg. Page Density (full)................: 3.06%
DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
TABLE level scan performed.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
> It is possible to mis-use the index configuration options to force this to
> happen - eg, set fillfactor 33, include all columns in the index & it
> could easily be ~ 3 times the size of the table..
> What's your fill-factor for this index?
> Can you post DBCC SHOWCONTIG output for this index?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>
|||DOCMASTER
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Which table has the index which is 3x the size of the table?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>
table in SQL 2005
DBCC SHOWCONTIG scanning 'DSECURITY' table...
Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7950.0
- Avg. Page Density (full)................: 1.78%
DBCC SHOWCONTIG scanning 'MHINFO' table...
Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'VERINFO' table...
Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7389.0
- Avg. Page Density (full)................: 8.71%
DBCC SHOWCONTIG scanning 'ID_TABLE' table...
Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7656.0
- Avg. Page Density (full)................: 5.41%
DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7604.0
- Avg. Page Density (full)................: 6.05%
DBCC SHOWCONTIG scanning 'PALETTE' table...
Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 136
- Extents Scanned.......................: 17
- Extent Switches.......................: 16
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7241.5
- Avg. Page Density (full)................: 10.53%
DBCC SHOWCONTIG scanning 'CUSTOM1' table...
Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2385
- Extents Scanned.......................: 299
- Extent Switches.......................: 298
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.33%
- Avg. Bytes Free per Page................: 7265.2
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7574.0
- Avg. Page Density (full)................: 6.42%
DBCC SHOWCONTIG scanning 'CUSTOM2' table...
Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 7745
- Extents Scanned.......................: 969
- Extent Switches.......................: 968
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.31%
- Avg. Bytes Free per Page................: 7259.7
- Avg. Page Density (full)................: 10.31%
DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 27
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.93%
- Extent Scan Fragmentation ...............: 60.00%
- Avg. Bytes Free per Page................: 7291.8
- Avg. Page Density (full)................: 9.91%
DBCC SHOWCONTIG scanning 'CUSTOM3' table...
Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORMS' table...
Table: 'FORMS' (226099846); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7499.5
- Avg. Page Density (full)................: 7.34%
DBCC SHOWCONTIG scanning 'CUSTOM4' table...
Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM5' table...
Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 52
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7247.9
- Avg. Page Density (full)................: 10.45%
DBCC SHOWCONTIG scanning 'CUSTOM6' table...
Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 355
- Extents Scanned.......................: 49
- Extent Switches.......................: 52
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
- Logical Scan Fragmentation ..............: 6.48%
- Extent Scan Fragmentation ...............: 6.12%
- Avg. Bytes Free per Page................: 6637.2
- Avg. Page Density (full)................: 18.00%
DBCC SHOWCONTIG scanning 'CUSTOM7' table...
Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ROLES' table...
Table: 'ROLES' (354100302); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7815.0
- Avg. Page Density (full)................: 3.45%
DBCC SHOWCONTIG scanning 'CUSTOM8' table...
Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USERROLES' table...
Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7646.0
- Avg. Page Density (full)................: 5.53%
DBCC SHOWCONTIG scanning 'CUSTOM9' table...
Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 28967
- Extents Scanned.......................: 3621
- Extent Switches.......................: 3620
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 3.01%
- Avg. Bytes Free per Page................: 7211.5
- Avg. Page Density (full)................: 10.90%
DBCC SHOWCONTIG scanning 'CUSTOM10' table...
Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 46
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.8
- Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
- Logical Scan Fragmentation ..............: 8.70%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 7281.7
- Avg. Page Density (full)................: 10.04%
DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
- Logical Scan Fragmentation ..............: 87.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 7335.6
- Avg. Page Density (full)................: 9.37%
DBCC SHOWCONTIG scanning 'CUSTOM11' table...
Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7641.0
- Avg. Page Density (full)................: 5.60%
DBCC SHOWCONTIG scanning 'CUSTOM12' table...
Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8040.0
- Avg. Page Density (full)................: 0.67%
DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM29' table...
Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM30' table...
Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM31' table...
Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'RESTORETBL' table...
Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'DOCMASTER' table...
Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 454519
- Extents Scanned.......................: 56834
- Extent Switches.......................: 57417
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
- Logical Scan Fragmentation ..............: 1.10%
- Extent Scan Fragmentation ...............: 2.94%
- Avg. Bytes Free per Page................: 6930.2
- Avg. Page Density (full)................: 14.38%
DBCC SHOWCONTIG scanning 'USERACTIONS' table...
Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7941.0
- Avg. Page Density (full)................: 1.89%
DBCC SHOWCONTIG scanning 'USERHISTORY' table...
Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 114
- Extents Scanned.......................: 24
- Extent Switches.......................: 23
- Avg. Pages per Extent..................: 4.8
- Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
- Extent Scan Fragmentation ...............: 91.67%
- Avg. Bytes Free per Page................: 1233.4
- Avg. Page Density (full)................: 84.76%
DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8063.0
- Avg. Page Density (full)................: 0.38%
DBCC SHOWCONTIG scanning 'SID_TABLE' table...
Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7846.0
- Avg. Page Density (full)................: 3.06%
DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
TABLE level scan performed.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
> It is possible to mis-use the index configuration options to force this to
> happen - eg, set fillfactor 33, include all columns in the index & it
> could easily be ~ 3 times the size of the table..
> What's your fill-factor for this index?
> Can you post DBCC SHOWCONTIG output for this index?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>
|||DOCMASTER
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Which table has the index which is 3x the size of the table?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>
Large Index
Is it normal for Index size to be three times the size of its corresponding
table in SQL 2005It is possible to mis-use the index configuration options to force this to
happen - eg, set fillfactor 33, include all columns in the index & it could
easily be ~ 3 times the size of the table..
What's your fill-factor for this index?
Can you post DBCC SHOWCONTIG output for this index?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
> Is it normal for Index size to be three times the size of its
> corresponding table in SQL 2005
>|||DBCC SHOWCONTIG scanning 'DSECURITY' table...
Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7950.0
- Avg. Page Density (full)................: 1.78%
DBCC SHOWCONTIG scanning 'MHINFO' table...
Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'VERINFO' table...
Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7389.0
- Avg. Page Density (full)................: 8.71%
DBCC SHOWCONTIG scanning 'ID_TABLE' table...
Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7656.0
- Avg. Page Density (full)................: 5.41%
DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7604.0
- Avg. Page Density (full)................: 6.05%
DBCC SHOWCONTIG scanning 'PALETTE' table...
Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 136
- Extents Scanned.......................: 17
- Extent Switches.......................: 16
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7241.5
- Avg. Page Density (full)................: 10.53%
DBCC SHOWCONTIG scanning 'CUSTOM1' table...
Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2385
- Extents Scanned.......................: 299
- Extent Switches.......................: 298
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.33%
- Avg. Bytes Free per Page................: 7265.2
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7574.0
- Avg. Page Density (full)................: 6.42%
DBCC SHOWCONTIG scanning 'CUSTOM2' table...
Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 7745
- Extents Scanned.......................: 969
- Extent Switches.......................: 968
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.31%
- Avg. Bytes Free per Page................: 7259.7
- Avg. Page Density (full)................: 10.31%
DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 27
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.93%
- Extent Scan Fragmentation ...............: 60.00%
- Avg. Bytes Free per Page................: 7291.8
- Avg. Page Density (full)................: 9.91%
DBCC SHOWCONTIG scanning 'CUSTOM3' table...
Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORMS' table...
Table: 'FORMS' (226099846); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7499.5
- Avg. Page Density (full)................: 7.34%
DBCC SHOWCONTIG scanning 'CUSTOM4' table...
Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM5' table...
Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 52
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7247.9
- Avg. Page Density (full)................: 10.45%
DBCC SHOWCONTIG scanning 'CUSTOM6' table...
Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 355
- Extents Scanned.......................: 49
- Extent Switches.......................: 52
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
- Logical Scan Fragmentation ..............: 6.48%
- Extent Scan Fragmentation ...............: 6.12%
- Avg. Bytes Free per Page................: 6637.2
- Avg. Page Density (full)................: 18.00%
DBCC SHOWCONTIG scanning 'CUSTOM7' table...
Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ROLES' table...
Table: 'ROLES' (354100302); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7815.0
- Avg. Page Density (full)................: 3.45%
DBCC SHOWCONTIG scanning 'CUSTOM8' table...
Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USERROLES' table...
Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7646.0
- Avg. Page Density (full)................: 5.53%
DBCC SHOWCONTIG scanning 'CUSTOM9' table...
Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 28967
- Extents Scanned.......................: 3621
- Extent Switches.......................: 3620
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 3.01%
- Avg. Bytes Free per Page................: 7211.5
- Avg. Page Density (full)................: 10.90%
DBCC SHOWCONTIG scanning 'CUSTOM10' table...
Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 46
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.8
- Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
- Logical Scan Fragmentation ..............: 8.70%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 7281.7
- Avg. Page Density (full)................: 10.04%
DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
- Logical Scan Fragmentation ..............: 87.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 7335.6
- Avg. Page Density (full)................: 9.37%
DBCC SHOWCONTIG scanning 'CUSTOM11' table...
Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7641.0
- Avg. Page Density (full)................: 5.60%
DBCC SHOWCONTIG scanning 'CUSTOM12' table...
Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8040.0
- Avg. Page Density (full)................: 0.67%
DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM29' table...
Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM30' table...
Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM31' table...
Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'RESTORETBL' table...
Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'DOCMASTER' table...
Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 454519
- Extents Scanned.......................: 56834
- Extent Switches.......................: 57417
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
- Logical Scan Fragmentation ..............: 1.10%
- Extent Scan Fragmentation ...............: 2.94%
- Avg. Bytes Free per Page................: 6930.2
- Avg. Page Density (full)................: 14.38%
DBCC SHOWCONTIG scanning 'USERACTIONS' table...
Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7941.0
- Avg. Page Density (full)................: 1.89%
DBCC SHOWCONTIG scanning 'USERHISTORY' table...
Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 114
- Extents Scanned.......................: 24
- Extent Switches.......................: 23
- Avg. Pages per Extent..................: 4.8
- Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
- Extent Scan Fragmentation ...............: 91.67%
- Avg. Bytes Free per Page................: 1233.4
- Avg. Page Density (full)................: 84.76%
DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8063.0
- Avg. Page Density (full)................: 0.38%
DBCC SHOWCONTIG scanning 'SID_TABLE' table...
Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7846.0
- Avg. Page Density (full)................: 3.06%
DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
TABLE level scan performed.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
> It is possible to mis-use the index configuration options to force this to
> happen - eg, set fillfactor 33, include all columns in the index & it
> could easily be ~ 3 times the size of the table..
> What's your fill-factor for this index?
> Can you post DBCC SHOWCONTIG output for this index?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>|||Which table has the index which is 3x the size of the table?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
> DBCC SHOWCONTIG scanning 'DSECURITY' table...
> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7950.0
> - Avg. Page Density (full)................: 1.78%
> DBCC SHOWCONTIG scanning 'MHINFO' table...
> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'VERINFO' table...
> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7389.0
> - Avg. Page Density (full)................: 8.71%
> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7656.0
> - Avg. Page Density (full)................: 5.41%
> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7604.0
> - Avg. Page Density (full)................: 6.05%
> DBCC SHOWCONTIG scanning 'PALETTE' table...
> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 136
> - Extents Scanned.......................: 17
> - Extent Switches.......................: 16
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7241.5
> - Avg. Page Density (full)................: 10.53%
> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2385
> - Extents Scanned.......................: 299
> - Extent Switches.......................: 298
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.33%
> - Avg. Bytes Free per Page................: 7265.2
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7574.0
> - Avg. Page Density (full)................: 6.42%
> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 7745
> - Extents Scanned.......................: 969
> - Extent Switches.......................: 968
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.31%
> - Avg. Bytes Free per Page................: 7259.7
> - Avg. Page Density (full)................: 10.31%
> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 27
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.93%
> - Extent Scan Fragmentation ...............: 60.00%
> - Avg. Bytes Free per Page................: 7291.8
> - Avg. Page Density (full)................: 9.91%
> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORMS' table...
> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7499.5
> - Avg. Page Density (full)................: 7.34%
> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 52
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 7.4
> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7247.9
> - Avg. Page Density (full)................: 10.45%
> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 355
> - Extents Scanned.......................: 49
> - Extent Switches.......................: 52
> - Avg. Pages per Extent..................: 7.2
> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
> - Logical Scan Fragmentation ..............: 6.48%
> - Extent Scan Fragmentation ...............: 6.12%
> - Avg. Bytes Free per Page................: 6637.2
> - Avg. Page Density (full)................: 18.00%
> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ROLES' table...
> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7815.0
> - Avg. Page Density (full)................: 3.45%
> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USERROLES' table...
> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7646.0
> - Avg. Page Density (full)................: 5.53%
> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 28967
> - Extents Scanned.......................: 3621
> - Extent Switches.......................: 3620
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 3.01%
> - Avg. Bytes Free per Page................: 7211.5
> - Avg. Page Density (full)................: 10.90%
> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 46
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 5.8
> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
> - Logical Scan Fragmentation ..............: 8.70%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 7281.7
> - Avg. Page Density (full)................: 10.04%
> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 8
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
> - Logical Scan Fragmentation ..............: 87.50%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 7335.6
> - Avg. Page Density (full)................: 9.37%
> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7641.0
> - Avg. Page Density (full)................: 5.60%
> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8040.0
> - Avg. Page Density (full)................: 0.67%
> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 454519
> - Extents Scanned.......................: 56834
> - Extent Switches.......................: 57417
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
> - Logical Scan Fragmentation ..............: 1.10%
> - Extent Scan Fragmentation ...............: 2.94%
> - Avg. Bytes Free per Page................: 6930.2
> - Avg. Page Density (full)................: 14.38%
> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7941.0
> - Avg. Page Density (full)................: 1.89%
> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 114
> - Extents Scanned.......................: 24
> - Extent Switches.......................: 23
> - Avg. Pages per Extent..................: 4.8
> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
> - Extent Scan Fragmentation ...............: 91.67%
> - Avg. Bytes Free per Page................: 1233.4
> - Avg. Page Density (full)................: 84.76%
> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8063.0
> - Avg. Page Density (full)................: 0.38%
> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7846.0
> - Avg. Page Density (full)................: 3.06%
> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
> TABLE level scan performed.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index & it
>> could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>|||DOCMASTER
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Which table has the index which is 3x the size of the table?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>> DBCC SHOWCONTIG scanning 'DSECURITY' table...
>> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7950.0
>> - Avg. Page Density (full)................: 1.78%
>> DBCC SHOWCONTIG scanning 'MHINFO' table...
>> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'VERINFO' table...
>> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
>> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7389.0
>> - Avg. Page Density (full)................: 8.71%
>> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
>> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
>> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7656.0
>> - Avg. Page Density (full)................: 5.41%
>> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
>> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7604.0
>> - Avg. Page Density (full)................: 6.05%
>> DBCC SHOWCONTIG scanning 'PALETTE' table...
>> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 136
>> - Extents Scanned.......................: 17
>> - Extent Switches.......................: 16
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7241.5
>> - Avg. Page Density (full)................: 10.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
>> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2385
>> - Extents Scanned.......................: 299
>> - Extent Switches.......................: 298
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.33%
>> - Avg. Bytes Free per Page................: 7265.2
>> - Avg. Page Density (full)................: 10.24%
>> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
>> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7574.0
>> - Avg. Page Density (full)................: 6.42%
>> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
>> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 7745
>> - Extents Scanned.......................: 969
>> - Extent Switches.......................: 968
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.31%
>> - Avg. Bytes Free per Page................: 7259.7
>> - Avg. Page Density (full)................: 10.31%
>> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
>> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 27
>> - Extents Scanned.......................: 10
>> - Extent Switches.......................: 9
>> - Avg. Pages per Extent..................: 2.7
>> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
>> - Logical Scan Fragmentation ..............: 25.93%
>> - Extent Scan Fragmentation ...............: 60.00%
>> - Avg. Bytes Free per Page................: 7291.8
>> - Avg. Page Density (full)................: 9.91%
>> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
>> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORMS' table...
>> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7499.5
>> - Avg. Page Density (full)................: 7.34%
>> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
>> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
>> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
>> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 52
>> - Extents Scanned.......................: 7
>> - Extent Switches.......................: 6
>> - Avg. Pages per Extent..................: 7.4
>> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7247.9
>> - Avg. Page Density (full)................: 10.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
>> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
>> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 355
>> - Extents Scanned.......................: 49
>> - Extent Switches.......................: 52
>> - Avg. Pages per Extent..................: 7.2
>> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
>> - Logical Scan Fragmentation ..............: 6.48%
>> - Extent Scan Fragmentation ...............: 6.12%
>> - Avg. Bytes Free per Page................: 6637.2
>> - Avg. Page Density (full)................: 18.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
>> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ROLES' table...
>> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7815.0
>> - Avg. Page Density (full)................: 3.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
>> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USERROLES' table...
>> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7646.0
>> - Avg. Page Density (full)................: 5.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
>> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 28967
>> - Extents Scanned.......................: 3621
>> - Extent Switches.......................: 3620
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 3.01%
>> - Avg. Bytes Free per Page................: 7211.5
>> - Avg. Page Density (full)................: 10.90%
>> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
>> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 46
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 5.8
>> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
>> - Logical Scan Fragmentation ..............: 8.70%
>> - Extent Scan Fragmentation ...............: 37.50%
>> - Avg. Bytes Free per Page................: 7281.7
>> - Avg. Page Density (full)................: 10.04%
>> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
>> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 8
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
>> - Logical Scan Fragmentation ..............: 87.50%
>> - Extent Scan Fragmentation ...............: 75.00%
>> - Avg. Bytes Free per Page................: 7335.6
>> - Avg. Page Density (full)................: 9.37%
>> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
>> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7641.0
>> - Avg. Page Density (full)................: 5.60%
>> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
>> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8040.0
>> - Avg. Page Density (full)................: 0.67%
>> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
>> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
>> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
>> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
>> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
>> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
>> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
>> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
>> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
>> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
>> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 454519
>> - Extents Scanned.......................: 56834
>> - Extent Switches.......................: 57417
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
>> - Logical Scan Fragmentation ..............: 1.10%
>> - Extent Scan Fragmentation ...............: 2.94%
>> - Avg. Bytes Free per Page................: 6930.2
>> - Avg. Page Density (full)................: 14.38%
>> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
>> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7941.0
>> - Avg. Page Density (full)................: 1.89%
>> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
>> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 114
>> - Extents Scanned.......................: 24
>> - Extent Switches.......................: 23
>> - Avg. Pages per Extent..................: 4.8
>> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
>> - Extent Scan Fragmentation ...............: 91.67%
>> - Avg. Bytes Free per Page................: 1233.4
>> - Avg. Page Density (full)................: 84.76%
>> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
>> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8063.0
>> - Avg. Page Density (full)................: 0.38%
>> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
>> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7846.0
>> - Avg. Page Density (full)................: 3.06%
>> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
>> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index & it
>> could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>>
>|||The pages on that table's clustered index are only 14% full, so there's a
lot of wasted space.
Rebuild it with a higher fillfactr (eg 80% or higher) & its size should be
significantly reduced.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23cY8pAbCHHA.1012@.TK2MSFTNGP04.phx.gbl...
> DOCMASTER
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Which table has the index which is 3x the size of the table?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>> DBCC SHOWCONTIG scanning 'DSECURITY' table...
>> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7950.0
>> - Avg. Page Density (full)................: 1.78%
>> DBCC SHOWCONTIG scanning 'MHINFO' table...
>> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'VERINFO' table...
>> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
>> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7389.0
>> - Avg. Page Density (full)................: 8.71%
>> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
>> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
>> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7656.0
>> - Avg. Page Density (full)................: 5.41%
>> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
>> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7604.0
>> - Avg. Page Density (full)................: 6.05%
>> DBCC SHOWCONTIG scanning 'PALETTE' table...
>> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 136
>> - Extents Scanned.......................: 17
>> - Extent Switches.......................: 16
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7241.5
>> - Avg. Page Density (full)................: 10.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
>> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2385
>> - Extents Scanned.......................: 299
>> - Extent Switches.......................: 298
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.33%
>> - Avg. Bytes Free per Page................: 7265.2
>> - Avg. Page Density (full)................: 10.24%
>> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
>> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7574.0
>> - Avg. Page Density (full)................: 6.42%
>> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
>> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 7745
>> - Extents Scanned.......................: 969
>> - Extent Switches.......................: 968
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.31%
>> - Avg. Bytes Free per Page................: 7259.7
>> - Avg. Page Density (full)................: 10.31%
>> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
>> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 27
>> - Extents Scanned.......................: 10
>> - Extent Switches.......................: 9
>> - Avg. Pages per Extent..................: 2.7
>> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
>> - Logical Scan Fragmentation ..............: 25.93%
>> - Extent Scan Fragmentation ...............: 60.00%
>> - Avg. Bytes Free per Page................: 7291.8
>> - Avg. Page Density (full)................: 9.91%
>> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
>> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORMS' table...
>> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7499.5
>> - Avg. Page Density (full)................: 7.34%
>> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
>> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
>> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
>> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 52
>> - Extents Scanned.......................: 7
>> - Extent Switches.......................: 6
>> - Avg. Pages per Extent..................: 7.4
>> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7247.9
>> - Avg. Page Density (full)................: 10.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
>> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
>> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 355
>> - Extents Scanned.......................: 49
>> - Extent Switches.......................: 52
>> - Avg. Pages per Extent..................: 7.2
>> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
>> - Logical Scan Fragmentation ..............: 6.48%
>> - Extent Scan Fragmentation ...............: 6.12%
>> - Avg. Bytes Free per Page................: 6637.2
>> - Avg. Page Density (full)................: 18.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
>> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ROLES' table...
>> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7815.0
>> - Avg. Page Density (full)................: 3.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
>> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USERROLES' table...
>> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7646.0
>> - Avg. Page Density (full)................: 5.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
>> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 28967
>> - Extents Scanned.......................: 3621
>> - Extent Switches.......................: 3620
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 3.01%
>> - Avg. Bytes Free per Page................: 7211.5
>> - Avg. Page Density (full)................: 10.90%
>> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
>> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 46
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 5.8
>> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
>> - Logical Scan Fragmentation ..............: 8.70%
>> - Extent Scan Fragmentation ...............: 37.50%
>> - Avg. Bytes Free per Page................: 7281.7
>> - Avg. Page Density (full)................: 10.04%
>> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
>> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 8
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
>> - Logical Scan Fragmentation ..............: 87.50%
>> - Extent Scan Fragmentation ...............: 75.00%
>> - Avg. Bytes Free per Page................: 7335.6
>> - Avg. Page Density (full)................: 9.37%
>> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
>> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7641.0
>> - Avg. Page Density (full)................: 5.60%
>> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
>> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8040.0
>> - Avg. Page Density (full)................: 0.67%
>> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
>> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
>> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
>> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
>> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
>> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
>> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
>> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
>> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
>> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
>> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 454519
>> - Extents Scanned.......................: 56834
>> - Extent Switches.......................: 57417
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
>> - Logical Scan Fragmentation ..............: 1.10%
>> - Extent Scan Fragmentation ...............: 2.94%
>> - Avg. Bytes Free per Page................: 6930.2
>> - Avg. Page Density (full)................: 14.38%
>> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
>> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7941.0
>> - Avg. Page Density (full)................: 1.89%
>> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
>> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 114
>> - Extents Scanned.......................: 24
>> - Extent Switches.......................: 23
>> - Avg. Pages per Extent..................: 4.8
>> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
>> - Extent Scan Fragmentation ...............: 91.67%
>> - Avg. Bytes Free per Page................: 1233.4
>> - Avg. Page Density (full)................: 84.76%
>> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
>> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8063.0
>> - Avg. Page Density (full)................: 0.38%
>> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
>> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7846.0
>> - Avg. Page Density (full)................: 3.06%
>> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
>> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index &
>> it could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>>
>>
>
table in SQL 2005It is possible to mis-use the index configuration options to force this to
happen - eg, set fillfactor 33, include all columns in the index & it could
easily be ~ 3 times the size of the table..
What's your fill-factor for this index?
Can you post DBCC SHOWCONTIG output for this index?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
> Is it normal for Index size to be three times the size of its
> corresponding table in SQL 2005
>|||DBCC SHOWCONTIG scanning 'DSECURITY' table...
Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7950.0
- Avg. Page Density (full)................: 1.78%
DBCC SHOWCONTIG scanning 'MHINFO' table...
Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'VERINFO' table...
Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7389.0
- Avg. Page Density (full)................: 8.71%
DBCC SHOWCONTIG scanning 'ID_TABLE' table...
Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8079.0
- Avg. Page Density (full)................: 0.19%
DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7656.0
- Avg. Page Density (full)................: 5.41%
DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7604.0
- Avg. Page Density (full)................: 6.05%
DBCC SHOWCONTIG scanning 'PALETTE' table...
Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 136
- Extents Scanned.......................: 17
- Extent Switches.......................: 16
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7241.5
- Avg. Page Density (full)................: 10.53%
DBCC SHOWCONTIG scanning 'CUSTOM1' table...
Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2385
- Extents Scanned.......................: 299
- Extent Switches.......................: 298
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.33%
- Avg. Bytes Free per Page................: 7265.2
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7574.0
- Avg. Page Density (full)................: 6.42%
DBCC SHOWCONTIG scanning 'CUSTOM2' table...
Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 7745
- Extents Scanned.......................: 969
- Extent Switches.......................: 968
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.31%
- Avg. Bytes Free per Page................: 7259.7
- Avg. Page Density (full)................: 10.31%
DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 27
- Extents Scanned.......................: 10
- Extent Switches.......................: 9
- Avg. Pages per Extent..................: 2.7
- Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
- Logical Scan Fragmentation ..............: 25.93%
- Extent Scan Fragmentation ...............: 60.00%
- Avg. Bytes Free per Page................: 7291.8
- Avg. Page Density (full)................: 9.91%
DBCC SHOWCONTIG scanning 'CUSTOM3' table...
Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORMS' table...
Table: 'FORMS' (226099846); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7499.5
- Avg. Page Density (full)................: 7.34%
DBCC SHOWCONTIG scanning 'CUSTOM4' table...
Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM5' table...
Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 52
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 7.4
- Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7247.9
- Avg. Page Density (full)................: 10.45%
DBCC SHOWCONTIG scanning 'CUSTOM6' table...
Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 355
- Extents Scanned.......................: 49
- Extent Switches.......................: 52
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
- Logical Scan Fragmentation ..............: 6.48%
- Extent Scan Fragmentation ...............: 6.12%
- Avg. Bytes Free per Page................: 6637.2
- Avg. Page Density (full)................: 18.00%
DBCC SHOWCONTIG scanning 'CUSTOM7' table...
Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ROLES' table...
Table: 'ROLES' (354100302); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7815.0
- Avg. Page Density (full)................: 3.45%
DBCC SHOWCONTIG scanning 'CUSTOM8' table...
Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USERROLES' table...
Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7646.0
- Avg. Page Density (full)................: 5.53%
DBCC SHOWCONTIG scanning 'CUSTOM9' table...
Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 28967
- Extents Scanned.......................: 3621
- Extent Switches.......................: 3620
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 3.01%
- Avg. Bytes Free per Page................: 7211.5
- Avg. Page Density (full)................: 10.90%
DBCC SHOWCONTIG scanning 'CUSTOM10' table...
Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 46
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.8
- Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
- Logical Scan Fragmentation ..............: 8.70%
- Extent Scan Fragmentation ...............: 37.50%
- Avg. Bytes Free per Page................: 7281.7
- Avg. Page Density (full)................: 10.04%
DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 8
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
- Logical Scan Fragmentation ..............: 87.50%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 7335.6
- Avg. Page Density (full)................: 9.37%
DBCC SHOWCONTIG scanning 'CUSTOM11' table...
Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7641.0
- Avg. Page Density (full)................: 5.60%
DBCC SHOWCONTIG scanning 'CUSTOM12' table...
Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8040.0
- Avg. Page Density (full)................: 0.67%
DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM29' table...
Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM30' table...
Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'CUSTOM31' table...
Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'RESTORETBL' table...
Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 0
- Extents Scanned.......................: 0
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 0.0
- Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 0.0
- Avg. Page Density (full)................: 0.00%
DBCC SHOWCONTIG scanning 'DOCMASTER' table...
Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 454519
- Extents Scanned.......................: 56834
- Extent Switches.......................: 57417
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
- Logical Scan Fragmentation ..............: 1.10%
- Extent Scan Fragmentation ...............: 2.94%
- Avg. Bytes Free per Page................: 6930.2
- Avg. Page Density (full)................: 14.38%
DBCC SHOWCONTIG scanning 'USERACTIONS' table...
Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7941.0
- Avg. Page Density (full)................: 1.89%
DBCC SHOWCONTIG scanning 'USERHISTORY' table...
Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 114
- Extents Scanned.......................: 24
- Extent Switches.......................: 23
- Avg. Pages per Extent..................: 4.8
- Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
- Extent Scan Fragmentation ...............: 91.67%
- Avg. Bytes Free per Page................: 1233.4
- Avg. Page Density (full)................: 84.76%
DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8063.0
- Avg. Page Density (full)................: 0.38%
DBCC SHOWCONTIG scanning 'SID_TABLE' table...
Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7846.0
- Avg. Page Density (full)................: 3.06%
DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
TABLE level scan performed.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
> It is possible to mis-use the index configuration options to force this to
> happen - eg, set fillfactor 33, include all columns in the index & it
> could easily be ~ 3 times the size of the table..
> What's your fill-factor for this index?
> Can you post DBCC SHOWCONTIG output for this index?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>|||Which table has the index which is 3x the size of the table?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
> DBCC SHOWCONTIG scanning 'DSECURITY' table...
> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7950.0
> - Avg. Page Density (full)................: 1.78%
> DBCC SHOWCONTIG scanning 'MHINFO' table...
> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'VERINFO' table...
> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7389.0
> - Avg. Page Density (full)................: 8.71%
> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8079.0
> - Avg. Page Density (full)................: 0.19%
> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7656.0
> - Avg. Page Density (full)................: 5.41%
> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7604.0
> - Avg. Page Density (full)................: 6.05%
> DBCC SHOWCONTIG scanning 'PALETTE' table...
> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 136
> - Extents Scanned.......................: 17
> - Extent Switches.......................: 16
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7241.5
> - Avg. Page Density (full)................: 10.53%
> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2385
> - Extents Scanned.......................: 299
> - Extent Switches.......................: 298
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.33%
> - Avg. Bytes Free per Page................: 7265.2
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7574.0
> - Avg. Page Density (full)................: 6.42%
> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 7745
> - Extents Scanned.......................: 969
> - Extent Switches.......................: 968
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.31%
> - Avg. Bytes Free per Page................: 7259.7
> - Avg. Page Density (full)................: 10.31%
> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 27
> - Extents Scanned.......................: 10
> - Extent Switches.......................: 9
> - Avg. Pages per Extent..................: 2.7
> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
> - Logical Scan Fragmentation ..............: 25.93%
> - Extent Scan Fragmentation ...............: 60.00%
> - Avg. Bytes Free per Page................: 7291.8
> - Avg. Page Density (full)................: 9.91%
> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORMS' table...
> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 2
> - Extents Scanned.......................: 2
> - Extent Switches.......................: 1
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
> - Logical Scan Fragmentation ..............: 50.00%
> - Extent Scan Fragmentation ...............: 50.00%
> - Avg. Bytes Free per Page................: 7499.5
> - Avg. Page Density (full)................: 7.34%
> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 52
> - Extents Scanned.......................: 7
> - Extent Switches.......................: 6
> - Avg. Pages per Extent..................: 7.4
> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7247.9
> - Avg. Page Density (full)................: 10.45%
> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 355
> - Extents Scanned.......................: 49
> - Extent Switches.......................: 52
> - Avg. Pages per Extent..................: 7.2
> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
> - Logical Scan Fragmentation ..............: 6.48%
> - Extent Scan Fragmentation ...............: 6.12%
> - Avg. Bytes Free per Page................: 6637.2
> - Avg. Page Density (full)................: 18.00%
> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ROLES' table...
> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7815.0
> - Avg. Page Density (full)................: 3.45%
> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USERROLES' table...
> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7646.0
> - Avg. Page Density (full)................: 5.53%
> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 28967
> - Extents Scanned.......................: 3621
> - Extent Switches.......................: 3620
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 3.01%
> - Avg. Bytes Free per Page................: 7211.5
> - Avg. Page Density (full)................: 10.90%
> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 46
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 5.8
> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
> - Logical Scan Fragmentation ..............: 8.70%
> - Extent Scan Fragmentation ...............: 37.50%
> - Avg. Bytes Free per Page................: 7281.7
> - Avg. Page Density (full)................: 10.04%
> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 8
> - Extents Scanned.......................: 8
> - Extent Switches.......................: 7
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
> - Logical Scan Fragmentation ..............: 87.50%
> - Extent Scan Fragmentation ...............: 75.00%
> - Avg. Bytes Free per Page................: 7335.6
> - Avg. Page Density (full)................: 9.37%
> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7641.0
> - Avg. Page Density (full)................: 5.60%
> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8040.0
> - Avg. Page Density (full)................: 0.67%
> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 0
> - Extents Scanned.......................: 0
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 0.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 0.0
> - Avg. Page Density (full)................: 0.00%
> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 454519
> - Extents Scanned.......................: 56834
> - Extent Switches.......................: 57417
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
> - Logical Scan Fragmentation ..............: 1.10%
> - Extent Scan Fragmentation ...............: 2.94%
> - Avg. Bytes Free per Page................: 6930.2
> - Avg. Page Density (full)................: 14.38%
> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7941.0
> - Avg. Page Density (full)................: 1.89%
> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 114
> - Extents Scanned.......................: 24
> - Extent Switches.......................: 23
> - Avg. Pages per Extent..................: 4.8
> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
> - Extent Scan Fragmentation ...............: 91.67%
> - Avg. Bytes Free per Page................: 1233.4
> - Avg. Page Density (full)................: 84.76%
> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 8063.0
> - Avg. Page Density (full)................: 0.38%
> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7846.0
> - Avg. Page Density (full)................: 3.06%
> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
> TABLE level scan performed.
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index & it
>> could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>|||DOCMASTER
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
> Which table has the index which is 3x the size of the table?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>> DBCC SHOWCONTIG scanning 'DSECURITY' table...
>> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7950.0
>> - Avg. Page Density (full)................: 1.78%
>> DBCC SHOWCONTIG scanning 'MHINFO' table...
>> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'VERINFO' table...
>> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
>> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7389.0
>> - Avg. Page Density (full)................: 8.71%
>> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
>> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
>> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7656.0
>> - Avg. Page Density (full)................: 5.41%
>> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
>> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7604.0
>> - Avg. Page Density (full)................: 6.05%
>> DBCC SHOWCONTIG scanning 'PALETTE' table...
>> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 136
>> - Extents Scanned.......................: 17
>> - Extent Switches.......................: 16
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7241.5
>> - Avg. Page Density (full)................: 10.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
>> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2385
>> - Extents Scanned.......................: 299
>> - Extent Switches.......................: 298
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.33%
>> - Avg. Bytes Free per Page................: 7265.2
>> - Avg. Page Density (full)................: 10.24%
>> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
>> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7574.0
>> - Avg. Page Density (full)................: 6.42%
>> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
>> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 7745
>> - Extents Scanned.......................: 969
>> - Extent Switches.......................: 968
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.31%
>> - Avg. Bytes Free per Page................: 7259.7
>> - Avg. Page Density (full)................: 10.31%
>> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
>> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 27
>> - Extents Scanned.......................: 10
>> - Extent Switches.......................: 9
>> - Avg. Pages per Extent..................: 2.7
>> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
>> - Logical Scan Fragmentation ..............: 25.93%
>> - Extent Scan Fragmentation ...............: 60.00%
>> - Avg. Bytes Free per Page................: 7291.8
>> - Avg. Page Density (full)................: 9.91%
>> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
>> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORMS' table...
>> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7499.5
>> - Avg. Page Density (full)................: 7.34%
>> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
>> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
>> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
>> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 52
>> - Extents Scanned.......................: 7
>> - Extent Switches.......................: 6
>> - Avg. Pages per Extent..................: 7.4
>> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7247.9
>> - Avg. Page Density (full)................: 10.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
>> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
>> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 355
>> - Extents Scanned.......................: 49
>> - Extent Switches.......................: 52
>> - Avg. Pages per Extent..................: 7.2
>> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
>> - Logical Scan Fragmentation ..............: 6.48%
>> - Extent Scan Fragmentation ...............: 6.12%
>> - Avg. Bytes Free per Page................: 6637.2
>> - Avg. Page Density (full)................: 18.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
>> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ROLES' table...
>> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7815.0
>> - Avg. Page Density (full)................: 3.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
>> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USERROLES' table...
>> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7646.0
>> - Avg. Page Density (full)................: 5.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
>> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 28967
>> - Extents Scanned.......................: 3621
>> - Extent Switches.......................: 3620
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 3.01%
>> - Avg. Bytes Free per Page................: 7211.5
>> - Avg. Page Density (full)................: 10.90%
>> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
>> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 46
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 5.8
>> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
>> - Logical Scan Fragmentation ..............: 8.70%
>> - Extent Scan Fragmentation ...............: 37.50%
>> - Avg. Bytes Free per Page................: 7281.7
>> - Avg. Page Density (full)................: 10.04%
>> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
>> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 8
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
>> - Logical Scan Fragmentation ..............: 87.50%
>> - Extent Scan Fragmentation ...............: 75.00%
>> - Avg. Bytes Free per Page................: 7335.6
>> - Avg. Page Density (full)................: 9.37%
>> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
>> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7641.0
>> - Avg. Page Density (full)................: 5.60%
>> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
>> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8040.0
>> - Avg. Page Density (full)................: 0.67%
>> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
>> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
>> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
>> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
>> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
>> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
>> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
>> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
>> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
>> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
>> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 454519
>> - Extents Scanned.......................: 56834
>> - Extent Switches.......................: 57417
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
>> - Logical Scan Fragmentation ..............: 1.10%
>> - Extent Scan Fragmentation ...............: 2.94%
>> - Avg. Bytes Free per Page................: 6930.2
>> - Avg. Page Density (full)................: 14.38%
>> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
>> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7941.0
>> - Avg. Page Density (full)................: 1.89%
>> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
>> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 114
>> - Extents Scanned.......................: 24
>> - Extent Switches.......................: 23
>> - Avg. Pages per Extent..................: 4.8
>> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
>> - Extent Scan Fragmentation ...............: 91.67%
>> - Avg. Bytes Free per Page................: 1233.4
>> - Avg. Page Density (full)................: 84.76%
>> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
>> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8063.0
>> - Avg. Page Density (full)................: 0.38%
>> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
>> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7846.0
>> - Avg. Page Density (full)................: 3.06%
>> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
>> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index & it
>> could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>>
>|||The pages on that table's clustered index are only 14% full, so there's a
lot of wasted space.
Rebuild it with a higher fillfactr (eg 80% or higher) & its size should be
significantly reduced.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
news:%23cY8pAbCHHA.1012@.TK2MSFTNGP04.phx.gbl...
> DOCMASTER
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OhNsY2aCHHA.5068@.TK2MSFTNGP02.phx.gbl...
>> Which table has the index which is 3x the size of the table?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%2397basaCHHA.204@.TK2MSFTNGP04.phx.gbl...
>> DBCC SHOWCONTIG scanning 'DSECURITY' table...
>> Table: 'DSECURITY' (5575058); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7950.0
>> - Avg. Page Density (full)................: 1.78%
>> DBCC SHOWCONTIG scanning 'MHINFO' table...
>> Table: 'MHINFO' (18099105); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'VERINFO' table...
>> Table: 'VERINFO' (34099162); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'DOCCLASSES' table...
>> Table: 'DOCCLASSES' (37575172); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7389.0
>> - Avg. Page Density (full)................: 8.71%
>> DBCC SHOWCONTIG scanning 'ID_TABLE' table...
>> Table: 'ID_TABLE' (50099219); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8079.0
>> - Avg. Page Density (full)................: 0.19%
>> DBCC SHOWCONTIG scanning 'FORM_TYPES' table...
>> Table: 'FORM_TYPES' (66099276); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7656.0
>> - Avg. Page Density (full)................: 5.41%
>> DBCC SHOWCONTIG scanning 'DOCSUBCLASSES' table...
>> Table: 'DOCSUBCLASSES' (69575286); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7604.0
>> - Avg. Page Density (full)................: 6.05%
>> DBCC SHOWCONTIG scanning 'PALETTE' table...
>> Table: 'PALETTE' (98099390); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 136
>> - Extents Scanned.......................: 17
>> - Extent Switches.......................: 16
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [17:17]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7241.5
>> - Avg. Page Density (full)................: 10.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM1' table...
>> Table: 'CUSTOM1' (117575457); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2385
>> - Extents Scanned.......................: 299
>> - Extent Switches.......................: 298
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [299:299]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.33%
>> - Avg. Bytes Free per Page................: 7265.2
>> - Avg. Page Density (full)................: 10.24%
>> DBCC SHOWCONTIG scanning 'FORM_TEMPLATE' table...
>> Table: 'FORM_TEMPLATE' (146099561); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7574.0
>> - Avg. Page Density (full)................: 6.42%
>> DBCC SHOWCONTIG scanning 'CUSTOM2' table...
>> Table: 'CUSTOM2' (149575571); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 7745
>> - Extents Scanned.......................: 969
>> - Extent Switches.......................: 968
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [969:969]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.31%
>> - Avg. Bytes Free per Page................: 7259.7
>> - Avg. Page Density (full)................: 10.31%
>> DBCC SHOWCONTIG scanning 'TEMPLATE_CONTROLS' table...
>> Table: 'TEMPLATE_CONTROLS' (178099675); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 27
>> - Extents Scanned.......................: 10
>> - Extent Switches.......................: 9
>> - Avg. Pages per Extent..................: 2.7
>> - Scan Density [Best Count:Actual Count]......: 40.00% [4:10]
>> - Logical Scan Fragmentation ..............: 25.93%
>> - Extent Scan Fragmentation ...............: 60.00%
>> - Avg. Bytes Free per Page................: 7291.8
>> - Avg. Page Density (full)................: 9.91%
>> DBCC SHOWCONTIG scanning 'CUSTOM3' table...
>> Table: 'CUSTOM3' (197575742); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORMS' table...
>> Table: 'FORMS' (226099846); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 2
>> - Extents Scanned.......................: 2
>> - Extent Switches.......................: 1
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
>> - Logical Scan Fragmentation ..............: 50.00%
>> - Extent Scan Fragmentation ...............: 50.00%
>> - Avg. Bytes Free per Page................: 7499.5
>> - Avg. Page Density (full)................: 7.34%
>> DBCC SHOWCONTIG scanning 'CUSTOM4' table...
>> Table: 'CUSTOM4' (229575856); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM5' table...
>> Table: 'CUSTOM5' (261575970); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'FORM_CONTROLS' table...
>> Table: 'FORM_CONTROLS' (274100017); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 52
>> - Extents Scanned.......................: 7
>> - Extent Switches.......................: 6
>> - Avg. Pages per Extent..................: 7.4
>> - Scan Density [Best Count:Actual Count]......: 100.00% [7:7]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7247.9
>> - Avg. Page Density (full)................: 10.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM6' table...
>> Table: 'CUSTOM6' (293576084); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'QUICK_RETRIEVE' table...
>> Table: 'QUICK_RETRIEVE' (322100188); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 355
>> - Extents Scanned.......................: 49
>> - Extent Switches.......................: 52
>> - Avg. Pages per Extent..................: 7.2
>> - Scan Density [Best Count:Actual Count]......: 84.91% [45:53]
>> - Logical Scan Fragmentation ..............: 6.48%
>> - Extent Scan Fragmentation ...............: 6.12%
>> - Avg. Bytes Free per Page................: 6637.2
>> - Avg. Page Density (full)................: 18.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM7' table...
>> Table: 'CUSTOM7' (325576198); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ROLES' table...
>> Table: 'ROLES' (354100302); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7815.0
>> - Avg. Page Density (full)................: 3.45%
>> DBCC SHOWCONTIG scanning 'CUSTOM8' table...
>> Table: 'CUSTOM8' (357576312); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USERROLES' table...
>> Table: 'USERROLES' (386100416); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7646.0
>> - Avg. Page Density (full)................: 5.53%
>> DBCC SHOWCONTIG scanning 'CUSTOM9' table...
>> Table: 'CUSTOM9' (389576426); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 28967
>> - Extents Scanned.......................: 3621
>> - Extent Switches.......................: 3620
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [3621:3621]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 3.01%
>> - Avg. Bytes Free per Page................: 7211.5
>> - Avg. Page Density (full)................: 10.90%
>> DBCC SHOWCONTIG scanning 'CUSTOM10' table...
>> Table: 'CUSTOM10' (421576540); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 46
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 5.8
>> - Scan Density [Best Count:Actual Count]......: 75.00% [6:8]
>> - Logical Scan Fragmentation ..............: 8.70%
>> - Extent Scan Fragmentation ...............: 37.50%
>> - Avg. Bytes Free per Page................: 7281.7
>> - Avg. Page Density (full)................: 10.04%
>> DBCC SHOWCONTIG scanning 'ROLE_PROFILES' table...
>> Table: 'ROLE_PROFILES' (450100644); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 8
>> - Extents Scanned.......................: 8
>> - Extent Switches.......................: 7
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 12.50% [1:8]
>> - Logical Scan Fragmentation ..............: 87.50%
>> - Extent Scan Fragmentation ...............: 75.00%
>> - Avg. Bytes Free per Page................: 7335.6
>> - Avg. Page Density (full)................: 9.37%
>> DBCC SHOWCONTIG scanning 'CUSTOM11' table...
>> Table: 'CUSTOM11' (453576654); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7641.0
>> - Avg. Page Density (full)................: 5.60%
>> DBCC SHOWCONTIG scanning 'CUSTOM12' table...
>> Table: 'CUSTOM12' (485576768); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8040.0
>> - Avg. Page Density (full)................: 0.67%
>> DBCC SHOWCONTIG scanning 'SECURITY_TEMPLATE' table...
>> Table: 'SECURITY_TEMPLATE' (498100815); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM29' table...
>> Table: 'CUSTOM29' (517576882); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_ACC_TEMP' table...
>> Table: 'USR_SCTY_ACC_TEMP' (546100986); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM30' table...
>> Table: 'CUSTOM30' (549576996); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'CUSTOM31' table...
>> Table: 'CUSTOM31' (597577167); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'GRP_SCTY_ACC_TEMP' table...
>> Table: 'GRP_SCTY_ACC_TEMP' (610101214); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'ARCHIVETBL' table...
>> Table: 'ARCHIVETBL' (645577338); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'USR_SCTY_TEMP_ASSC' table...
>> Table: 'USR_SCTY_TEMP_ASSC' (674101442); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'RESTORETBL' table...
>> Table: 'RESTORETBL' (677577452); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 0
>> - Extents Scanned.......................: 0
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 0.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [0:0]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 0.0
>> - Avg. Page Density (full)................: 0.00%
>> DBCC SHOWCONTIG scanning 'DOCMASTER' table...
>> Table: 'DOCMASTER' (693577509); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 454519
>> - Extents Scanned.......................: 56834
>> - Extent Switches.......................: 57417
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 98.95% [56815:57418]
>> - Logical Scan Fragmentation ..............: 1.10%
>> - Extent Scan Fragmentation ...............: 2.94%
>> - Avg. Bytes Free per Page................: 6930.2
>> - Avg. Page Density (full)................: 14.38%
>> DBCC SHOWCONTIG scanning 'USERACTIONS' table...
>> Table: 'USERACTIONS' (738101670); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7941.0
>> - Avg. Page Density (full)................: 1.89%
>> DBCC SHOWCONTIG scanning 'USERHISTORY' table...
>> Table: 'USERHISTORY' (770101784); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 114
>> - Extents Scanned.......................: 24
>> - Extent Switches.......................: 23
>> - Avg. Pages per Extent..................: 4.8
>> - Scan Density [Best Count:Actual Count]......: 62.50% [15:24]
>> - Extent Scan Fragmentation ...............: 91.67%
>> - Avg. Bytes Free per Page................: 1233.4
>> - Avg. Page Density (full)................: 84.76%
>> DBCC SHOWCONTIG scanning 'SYSTEM_MANAGEMENT' table...
>> Table: 'SYSTEM_MANAGEMENT' (818101955); index ID: 0, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 8063.0
>> - Avg. Page Density (full)................: 0.38%
>> DBCC SHOWCONTIG scanning 'SID_TABLE' table...
>> Table: 'SID_TABLE' (834102012); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> - Pages Scanned........................: 1
>> - Extents Scanned.......................: 1
>> - Extent Switches.......................: 0
>> - Avg. Pages per Extent..................: 1.0
>> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 7846.0
>> - Avg. Page Density (full)................: 3.06%
>> DBCC SHOWCONTIG scanning 'DOC_NVPS' table...
>> Table: 'DOC_NVPS' (866102126); index ID: 1, database ID: 5
>> TABLE level scan performed.
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:Oj2$RPTCHHA.1824@.TK2MSFTNGP06.phx.gbl...
>> It is possible to mis-use the index configuration options to force this
>> to happen - eg, set fillfactor 33, include all columns in the index &
>> it could easily be ~ 3 times the size of the table..
>> What's your fill-factor for this index?
>> Can you post DBCC SHOWCONTIG output for this index?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Zack" <svoiasgfs@.sdlgweg90dflg.com> wrote in message
>> news:%23au0S5PCHHA.5012@.TK2MSFTNGP06.phx.gbl...
>> Is it normal for Index size to be three times the size of its
>> corresponding table in SQL 2005
>>
>>
>>
>
Subscribe to:
Posts (Atom)