Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Friday, March 30, 2012
Information Schema Query Question
Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql
Wednesday, March 21, 2012
Indexing?
Anyone got a great reference that explains what fields to index.. etc..
Would you index your foreign keys, or since the key of the referenced table is indexed should I not?
In almost all cases, Foriegn Keys should be indexed.Indexing/Unique Key
This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=890048Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=892551|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=898374
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=890048Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=892551|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=898374
Indexing/Unique Key
This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql
Indexing Question
Hi Gurus,
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.
Now, the question is should I create an index for this Status column? Will it improve the performance?
Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.
- CB|||Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.
- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.
- CB
Originally posted by shekarnarayanan
Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.
Thanks for all the suggestions.|||SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.
blindman|||Originally posted by Brett Kaiser
SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?
Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.
I have a table called Companies with int identity column as primary key and other fields. Also there is a Status column which can hold either 0 or 1. I use this status column in a join from some child table like where a.status = 1 along with other conditions.
Now, the question is should I create an index for this Status column? Will it improve the performance?
Thanks.I would say NO. One of the criteria for creating a good index is selectivity. So your index on a booleon column would not help the performance. In addition, it is just an overhead on the inserts.
- CB|||Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||I agree with Brett. In the situation that he described (covered indexes), it could be helpful to tag that column at the end of the composite index to avoid another trip to get the data.
- CB
Originally posted by Brett Kaiser
Post the query...because the answr is it depends...
If yo had SELECT a.Col1, a.Status, a.Col2 FROM myTable1
INNER JOIN myTable2 b ON a.col1 = b.col and a.col2 = b.col2
I'd add it to the index...not for look up, but to prevent it from having to go to the data pages..|||Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||In this situation, adding status to the index will not help, unless Brett thinks otherwise.
- CB
Originally posted by shekarnarayanan
Ok, here is a sample:
SELECT A.*, B.NAME
FROM Orders A,
Companies B
Where B.CompanyId = A.CompanyId
and B.Status = 1
ORDER BY B.NAME
Hope this helps.|||Quick question,.. why don't you try it and see what happens? Worse comes to worst you can just delete it afterwards...|||Agreed, just try it. Set up a test/dev environment. Run query before index added, look at query execution plan, apply index and look once again at query execution plan. It will help.|||Well, I tried as suggested and the execution plan does not seem to use the new index at all! It just uses the clustered PK index. So I guess the answer is NO to the new index.
Thanks for all the suggestions.|||SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?|||You say your column only holds ones and zeros. If it is a bit field it cannot be indexed. Even if it is not a bit field, if the distribution of values for one and zero are about 50%, the optimizer might not get much out of using the index. In a binary tree it would only save 1 search ply.
blindman|||Originally posted by Brett Kaiser
SELECT *...
No, No, No...
Do you really need all of the columns?
If so, list them out...
Only use SELECT * for testing, analysis...
What's the DDL for the 2 tables?
And the optimizer is making the right call in your case
How many rows of data are we talking about?
Hi Brett,
Thank you for your concern. Yes I do list all the fields and never use the * from my programs. Number of records in the comp. table is around 500 and the orders table may be few thousands. I also filter by company.|||On such a small number of records, you will not see much of an improvement. Anytime you have so a limited distribution like yes/no, male/female ... the optimizer will normally chose a table scan over an index (so normally the recommendation is No Way). Unless your distribution is very high for 1 value and very low for the other value, an index will only help for the low value anyway. If the distribution of these values are remotely close to each the optimizer will probably perform a table scan anyway. Since these tables are small, sql will probably chose a table scan over an index even if your distribution is ripe for an index.
Monday, March 19, 2012
Indexing non-unique data
I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks
Welcome to the forum
Not a guru but some musings:
Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.
The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?
All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.
The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).
Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks
Welcome to the forum
Not a guru but some musings:
Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.
The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?
All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.
Indexing in SQL Server Star Scheme Data Warehouse
Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.
The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.
We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.
We would like to create a time dimension using the following:
1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)
Let me know how the above approach sounds to you guys.
V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.
The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.
We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.
We would like to create a time dimension using the following:
1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)
Let me know how the above approach sounds to you guys.
V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.
Monday, March 12, 2012
Indexing Columns
If you have a table with 3 columns,
ID (Primary Key)
Col1
Col2
And you have to perform the following query frequently
Code Snippet
Select ID where Col1='SomeValue' and Col2='SomeOtherValue'Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"
I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea
If you are using SQL 2005, and this is a frequent or common query, you may wish to explore using the new 'INCLUDE' option.
You could create an INDEX on Col1, Col2, and include [ID].
Something like this:
CREATE NONCLUSTERED INDEX ix_MyTable_Col1Col2
ON MySchema.MyTable( Col1, Col2 )
INCLUDE ( [ID] );
This is a 'covered' index. The entire query is satisfied by the index.
|||Thanks Arnie.... I have to support both SQL 2005 and SQL 2000 for this application at the moment.|||
For SQL 2000, if you use this query frequently, index all three columns.
|||you can not define ID as non clustered since it is a PK.
ID can be clustered index and check the unique checkbox.
you could define col1 and col2 as non clustered and ID as included column but beware of space used by index.
|||
You should create clustered primary indexes based off the 80/20 rule. If you are accessing this table 80% of the time by col1 and col2, then create a clustered primary index over col1, col2, ID. Creating an index over just the ID column will almost always cause bookup lookups. Create primary key indexes based off of usage, not how fast can I load data.
|||Chances are that your ID column is part of the automatically created clustered index since it's the primary key.If that's the case, remember that all columns in the clustered index are appended to all non-clustered indexes for that table.
So there is no reason to add ID to your non-clustered index since it will be there already.
I think that SQL Server is smart enough to just ignore the ID column in the index definition since it knows that it's part of the clustered index, but I'm not sure on that one.
Having all three columns part of an index (ID in the clustered, and Col1 and Col2 in the non-clustered) creates, as somebody else mentioned, a "covering" index.
Basically, a covering index is an index which includes all references columns in your query (from the SELECT, JOIN, and WHERE clauses) so that no bookmark loops are necessary to return all the data. This data can come entirely from indexes... which is much faster than having to go read additional data pages to snag the original row from the table.
|||<P align=left><FONT face=Arial size=2>Hi,</FONT></P>
<P align=left> </P>
<P align=left> We could arrive at a decision of using the index on columns based on the recommendation of the SQL Profiler utility. The input for the profiler would be a database trace file. This trace file will capture the usage of the table by the users and using this profiler will decide whether to use index. Also in this scenario, the table has only 3 columns and all the three columns are accessed by the user frequently. So the choice would be going for the covering index where all the three columns will be covered under index.</P>
<P align=left> </P>
<P align=left>Thanks.</P>
Indexing And Physical Storage Of Data
1> How is the data stored physically when there is now primary key as well as any index defined in the table.....?
2> How is the data stored physically when there is just a primary key defined in one of the column of the table? No INDEX defined.
Thanks,
Rahul JhaHi Rahul
As per Tom's answer in the last thread again you need to read up on this subject. You can't get an understanding of indexes by asking questions like this on a forum - or at least not without asking hundreds of questions.
Read up on this and you'll learn that indexes <> primary keys but SQL Server (and most RDMSs) enforce primary keys through unique, non-null indexes. As such, senario 2 is impossible. Also - a primary key does not determine the physical characteristics of a table. The nature of (or lack of) the indexes does.
2> How is the data stored physically when there is just a primary key defined in one of the column of the table? No INDEX defined.
Thanks,
Rahul JhaHi Rahul
As per Tom's answer in the last thread again you need to read up on this subject. You can't get an understanding of indexes by asking questions like this on a forum - or at least not without asking hundreds of questions.
Read up on this and you'll learn that indexes <> primary keys but SQL Server (and most RDMSs) enforce primary keys through unique, non-null indexes. As such, senario 2 is impossible. Also - a primary key does not determine the physical characteristics of a table. The nature of (or lack of) the indexes does.
Friday, March 9, 2012
indexes.
Hi.
I have 2 queries that are similiar
They involve 2 tables,
1) CDR which has a primary key of cdrid and has half a million rows
2) MODULE which has a primary key of moduleid and has only 240000 rows
Neither of the two tables have indexes in it aside from the clustered index
produced by the primary key constraint
the two queries are as follows
Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdrid
Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
Query #1 takes over 40 minutes to run
Query #2 takes 8 seconds to run.
Currently, I am making an indexed view across the module and cdr tables
hoping that it will speed up the performance of query #2, but in the
meantime, I don't understand why Query #2 is so significantly different in
performance than query #1. Can anyone please explain this to me?
Thanks,
-- Jasonlooks like query #1 is almost a cross join:
a true cross join:
select cdr.cdrid from cdr, module
would return 500K*250K rows, that's a lot. the criteria cdr.cdrid <>
module.cdrid
does not filter out many riows, right?|||The queries take a long time because you are using a cross join, or cartesia
n
product of the two tables.
1. select cdr.cdrid from cdr, module
this produces a result set that is 500,000 x 240,000 = 120,000,000 rows,
which is then reduced by your WHERE clause. Instead, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid NOT IN (SELECT cdrid FROM module)
or
SELECT a.cdrid FROM cdr a
WHERE NOT EXISTS (SELECT b.cdrid FROM module b WHERE a.cdrid=b.cdrid)
Similarly, for #2, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid IN (SELECT cdrid FROM module)
"Jason" wrote:
> Hi.
> I have 2 queries that are similiar
> They involve 2 tables,
> 1) CDR which has a primary key of cdrid and has half a million rows
> 2) MODULE which has a primary key of moduleid and has only 240000 rows
> Neither of the two tables have indexes in it aside from the clustered inde
x
> produced by the primary key constraint
> the two queries are as follows
> Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdri
d
> Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
> Query #1 takes over 40 minutes to run
> Query #2 takes 8 seconds to run.
> Currently, I am making an indexed view across the module and cdr tables
> hoping that it will speed up the performance of query #2, but in the
> meantime, I don't understand why Query #2 is so significantly different i
n
> performance than query #1. Can anyone please explain this to me?
> Thanks,
> -- Jason
>
>|||Alex, you are correct.
What I am really going for is something more like this:
select cdrid from cdr where cdrid not in (select cdrid from module)
In order to put this into an indexed view, I need to get rid of the subquery
though.
In a case like this, I think I need to write it as a left outer join (i
think).
so it would be like this:
select cdr.cdrid from cdr
left outer join module on cdr.cdrid <> module.cdrid
I think that that is still incorrect though because it returns far too many
results. Can someone please point out my error?
Thanks,
-- Jason|||Since you only care about matching records shouldn't you be using an INNER
join?
select cdr.cdrid from cdr
inner join module on cdr.cdrid <> module.cdrid
John
"Jason" <jason@.acd.net> wrote in message
news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
> Alex, you are correct.
> What I am really going for is something more like this:
> select cdrid from cdr where cdrid not in (select cdrid from module)
> In order to put this into an indexed view, I need to get rid of the
> subquery though.
> In a case like this, I think I need to write it as a left outer join (i
> think).
> so it would be like this:
> select cdr.cdrid from cdr
> left outer join module on cdr.cdrid <> module.cdrid
> I think that that is still incorrect though because it returns far too
> many results. Can someone please point out my error?
> Thanks,
> -- Jason
>|||I think I figured it out...
On the contrary, I only care about not-matching records... In this case, I
matched up the CDR records to the MODULE records using a left outer join...
and then used the where clause for filtering out the ones that had actual
matches... as in the following:
select cdr.cdrid, module.cdrid from cdr
left outer join module on cdr.cdrid = module.cdrid
where module.cdrid is null
seems to work, but because it has an outer join, I cant put it into an
indexed view. *sigh*
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:eQaorzYBGHA.3396@.tk2msftngp13.phx.gbl...
> Since you only care about matching records shouldn't you be using an INNER
> join?
> select cdr.cdrid from cdr
> inner join module on cdr.cdrid <> module.cdrid
> John
> "Jason" <jason@.acd.net> wrote in message
> news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
>|||On Tue, 20 Dec 2005 16:06:36 -0500, Jason wrote:
>I think I figured it out...
>On the contrary, I only care about not-matching records... In this case, I
>matched up the CDR records to the MODULE records using a left outer join...
>and then used the where clause for filtering out the ones that had actual
>matches... as in the following:
>select cdr.cdrid, module.cdrid from cdr
> left outer join module on cdr.cdrid = module.cdrid
>where module.cdrid is null
>seems to work, but because it has an outer join, I cant put it into an
>indexed view. *sigh*
Hi Jason,
Any specific reason why you want this in an indexed view?
From the preceeding post, I gather that this is only in an attempt to
increase performance. Why not try the easier options first? Such as
creating a nonclustered index on module.cdrid?
CREATE INDEX NameGoesHere ON module (cdrid)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have 2 queries that are similiar
They involve 2 tables,
1) CDR which has a primary key of cdrid and has half a million rows
2) MODULE which has a primary key of moduleid and has only 240000 rows
Neither of the two tables have indexes in it aside from the clustered index
produced by the primary key constraint
the two queries are as follows
Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdrid
Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
Query #1 takes over 40 minutes to run
Query #2 takes 8 seconds to run.
Currently, I am making an indexed view across the module and cdr tables
hoping that it will speed up the performance of query #2, but in the
meantime, I don't understand why Query #2 is so significantly different in
performance than query #1. Can anyone please explain this to me?
Thanks,
-- Jasonlooks like query #1 is almost a cross join:
a true cross join:
select cdr.cdrid from cdr, module
would return 500K*250K rows, that's a lot. the criteria cdr.cdrid <>
module.cdrid
does not filter out many riows, right?|||The queries take a long time because you are using a cross join, or cartesia
n
product of the two tables.
1. select cdr.cdrid from cdr, module
this produces a result set that is 500,000 x 240,000 = 120,000,000 rows,
which is then reduced by your WHERE clause. Instead, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid NOT IN (SELECT cdrid FROM module)
or
SELECT a.cdrid FROM cdr a
WHERE NOT EXISTS (SELECT b.cdrid FROM module b WHERE a.cdrid=b.cdrid)
Similarly, for #2, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid IN (SELECT cdrid FROM module)
"Jason" wrote:
> Hi.
> I have 2 queries that are similiar
> They involve 2 tables,
> 1) CDR which has a primary key of cdrid and has half a million rows
> 2) MODULE which has a primary key of moduleid and has only 240000 rows
> Neither of the two tables have indexes in it aside from the clustered inde
x
> produced by the primary key constraint
> the two queries are as follows
> Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdri
d
> Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
> Query #1 takes over 40 minutes to run
> Query #2 takes 8 seconds to run.
> Currently, I am making an indexed view across the module and cdr tables
> hoping that it will speed up the performance of query #2, but in the
> meantime, I don't understand why Query #2 is so significantly different i
n
> performance than query #1. Can anyone please explain this to me?
> Thanks,
> -- Jason
>
>|||Alex, you are correct.
What I am really going for is something more like this:
select cdrid from cdr where cdrid not in (select cdrid from module)
In order to put this into an indexed view, I need to get rid of the subquery
though.
In a case like this, I think I need to write it as a left outer join (i
think).
so it would be like this:
select cdr.cdrid from cdr
left outer join module on cdr.cdrid <> module.cdrid
I think that that is still incorrect though because it returns far too many
results. Can someone please point out my error?
Thanks,
-- Jason|||Since you only care about matching records shouldn't you be using an INNER
join?
select cdr.cdrid from cdr
inner join module on cdr.cdrid <> module.cdrid
John
"Jason" <jason@.acd.net> wrote in message
news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
> Alex, you are correct.
> What I am really going for is something more like this:
> select cdrid from cdr where cdrid not in (select cdrid from module)
> In order to put this into an indexed view, I need to get rid of the
> subquery though.
> In a case like this, I think I need to write it as a left outer join (i
> think).
> so it would be like this:
> select cdr.cdrid from cdr
> left outer join module on cdr.cdrid <> module.cdrid
> I think that that is still incorrect though because it returns far too
> many results. Can someone please point out my error?
> Thanks,
> -- Jason
>|||I think I figured it out...
On the contrary, I only care about not-matching records... In this case, I
matched up the CDR records to the MODULE records using a left outer join...
and then used the where clause for filtering out the ones that had actual
matches... as in the following:
select cdr.cdrid, module.cdrid from cdr
left outer join module on cdr.cdrid = module.cdrid
where module.cdrid is null
seems to work, but because it has an outer join, I cant put it into an
indexed view. *sigh*
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:eQaorzYBGHA.3396@.tk2msftngp13.phx.gbl...
> Since you only care about matching records shouldn't you be using an INNER
> join?
> select cdr.cdrid from cdr
> inner join module on cdr.cdrid <> module.cdrid
> John
> "Jason" <jason@.acd.net> wrote in message
> news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
>|||On Tue, 20 Dec 2005 16:06:36 -0500, Jason wrote:
>I think I figured it out...
>On the contrary, I only care about not-matching records... In this case, I
>matched up the CDR records to the MODULE records using a left outer join...
>and then used the where clause for filtering out the ones that had actual
>matches... as in the following:
>select cdr.cdrid, module.cdrid from cdr
> left outer join module on cdr.cdrid = module.cdrid
>where module.cdrid is null
>seems to work, but because it has an outer join, I cant put it into an
>indexed view. *sigh*
Hi Jason,
Any specific reason why you want this in an indexed view?
From the preceeding post, I gather that this is only in an attempt to
increase performance. Why not try the easier options first? Such as
creating a nonclustered index on module.cdrid?
CREATE INDEX NameGoesHere ON module (cdrid)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Wednesday, March 7, 2012
indexes on foreign key columns?
I read recently in an ADO.NET book that I should create indexes on foreign
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel Lyons
Hi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when you
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constraint
> to a column. What's the deal?
> Joel Lyons
>
>
|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel Lyons
Hi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when you
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constraint
> to a column. What's the deal?
> Joel Lyons
>
>
|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
indexes on foreign key columns?
I read recently in an ADO.NET book that I should create indexes on foreign
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel LyonsHi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when yo
u
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constrain
t
> to a column. What's the deal?
> Joel Lyons
>
>|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel LyonsHi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when yo
u
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constrain
t
> to a column. What's the deal?
> Joel Lyons
>
>|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
indexes on foreign key columns?
I read recently in an ADO.NET book that I should create indexes on foreign
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel LyonsHi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when you
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constraint
> to a column. What's the deal?
> Joel Lyons
>
>|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
key constraints if they will commonly be used when joining. I can't
remember where, but I could swear that I had read somewhere else that
indexes were automatically created when you assign a foreign key constraint
to a column. What's the deal?
Joel LyonsHi Joel
You should create (yourself) indexes on referencing columns in a foreign key
constraint as it will improve things like the checking of the column when you
change a referenced value (for instance when you delete a row in the
referenced table). The referenced table will already have a primary key or a
unique index on the column being referenced.
John
"Joel Lyons" wrote:
> I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key constraint
> to a column. What's the deal?
> Joel Lyons
>
>|||Hi Joel
Indexes are NOT automatically created on foreign keys, and it is usually a
good idea to create them. Even if you don't specifically write joins between
foreign and primary keys, if you ever allow deletes or updates to the
primary keys, SQL Server does an internal join to find matching FK values,
and an index can help the performance of the data modification.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Joel Lyons" <joell_REMOVE_@.novarad.net> wrote in message
news:eTWbqWyIIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I read recently in an ADO.NET book that I should create indexes on foreign
> key constraints if they will commonly be used when joining. I can't
> remember where, but I could swear that I had read somewhere else that
> indexes were automatically created when you assign a foreign key
> constraint
> to a column. What's the deal?
> Joel Lyons
>
Indexes getting fragmented very quickly
> What will cause the indexes to get fragmented so quickly?
Some possibilities:
Lots of inserts over a key that is no monotonically increasing.
Lost of updates (where value in index key changes).
Shrinking of database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jayme" <jayloub@.comcast.net> wrote in message
news:1150568837.067145.140050@.c74g2000cwc.googlegroups.com...
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>We have been running SQL Server 2000 on a production machine for years.
Within the last 6 months, we have noticed a performance problem. The
system will suddenly slow to a crawl. We run DBCC Reindex for all of
the tables in the our database, and things go back to normal - running
very quickly. We are currently having to do this multiple times a day.
We have verified the indexes are getting fragmented by using the DBCC
showcontig. What will cause the indexes to get fragmented so quickly?
How do we prevent this from happening?
Any help would greatly be appreciated.
Thank you!
Jayme|||> What will cause the indexes to get fragmented so quickly?
Some possibilities:
Lots of inserts over a key that is no monotonically increasing.
Lost of updates (where value in index key changes).
Shrinking of database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jayme" <jayloub@.comcast.net> wrote in message
news:1150568837.067145.140050@.c74g2000cwc.googlegroups.com...
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>|||Jayme wrote:
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>
Are you sure you're seeing index fragmentation, and not disk
fragmentation? Post the output of your DBCC command..|||Jayme wrote:
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>
Are you sure you're seeing index fragmentation, and not disk
fragmentation? Post the output of your DBCC command..|||Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Check out for disk fragmentation also.
If Index fragmentation , then BOL has very good sample, in DBCC
Showconting topic.
It will check index fragmentation and defrag index which are fragmented
below threshold value. Run it regularly.
Regards
Amish Shah|||Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Check out for disk fragmentation also.
If Index fragmentation , then BOL has very good sample, in DBCC
Showconting topic.
It will check index fragmentation and defrag index which are fragmented
below threshold value. Run it regularly.
Regards
Amish Shah|||This is the dbcc output before the reindex is done:
DBCC SHOWCONTIG scanning 'QCSKUTable' table...
Table: 'QCSKUTable' (32771224); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 533
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 9.01%
- Extent Scan Fragmentation ...............: 72.06%
- Avg. Bytes Free per Page................: 789.2
- Avg. Page Density (full)................: 90.25%
DBCC SHOWCONTIG scanning 'Map' table...
Table: 'Map' (101575400); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6089.0
- Avg. Page Density (full)................: 24.77%
DBCC SHOWCONTIG scanning 'Zone' table...
Table: 'Zone' (133575514); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 70
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 837.4
- Avg. Page Density (full)................: 89.65%
DBCC SHOWCONTIG scanning 'QCConfiguration' table...
Table: 'QCConfiguration' (192771794); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6976.0
- Avg. Page Density (full)................: 13.81%
DBCC SHOWCONTIG scanning 'TestQCSKUTable' table...
Table: 'TestQCSKUTable' (230395990); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 535
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 67.65%
- Avg. Bytes Free per Page................: 786.5
- Avg. Page Density (full)................: 90.28%
DBCC SHOWCONTIG scanning 'Bay' table...
Table: 'Bay' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 20.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2084.0
- Avg. Page Density (full)................: 74.25%
DBCC SHOWCONTIG scanning 'ZoneMap' table...
Table: 'ZoneMap' (357576312); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 309
- Extents Scanned.......................: 39
- Extent Switches.......................: 38
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [39:39]
- Logical Scan Fragmentation ..............: 0.32%
- Extent Scan Fragmentation ...............: 69.23%
- Avg. Bytes Free per Page................: 799.9
- Avg. Page Density (full)................: 90.12%
DBCC SHOWCONTIG scanning 'Device' table...
Table: 'Device' (421576540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 293
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.34%
- Extent Scan Fragmentation ...............: 81.08%
- Avg. Bytes Free per Page................: 779.5
- Avg. Page Density (full)................: 90.37%
DBCC SHOWCONTIG scanning 'Location' table...
Table: 'Location' (501576825); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 285
- Extents Scanned.......................: 36
- Extent Switches.......................: 35
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [36:36]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 5.56%
- Avg. Bytes Free per Page................: 786.2
- Avg. Page Density (full)................: 90.29%
DBCC SHOWCONTIG scanning 'Carrier' table...
Table: 'Carrier' (580913141); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 232
- Extents Scanned.......................: 34
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 85.29% [29:34]
- Extent Scan Fragmentation ...............: 94.12%
- Avg. Bytes Free per Page................: 1359.3
- Avg. Page Density (full)................: 83.21%
DBCC SHOWCONTIG scanning 'NetController' table...
Table: 'NetController' (581577110); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 1356.0
- Avg. Page Density (full)................: 83.25%
DBCC SHOWCONTIG scanning 'QCContentDetail' table...
Table: 'QCContentDetail' (596249229); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 6
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3306.5
- Avg. Page Density (full)................: 59.15%
DBCC SHOWCONTIG scanning 'OrderToCarrier' table...
Table: 'OrderToCarrier' (628913312); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 139
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 58.06% [18:31]
- Extent Scan Fragmentation ...............: 96.77%
- Avg. Bytes Free per Page................: 1491.1
- Avg. Page Density (full)................: 81.58%
DBCC SHOWCONTIG scanning 'WaveGroup' table...
Table: 'WaveGroup' (645577338); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7988.0
- Avg. Page Density (full)................: 1.31%
DBCC SHOWCONTIG scanning 'WorkLoadCriteriaDetail' table...
Table: 'WorkLoadCriteriaDetail' (654625375); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 9
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 970.7
- Avg. Page Density (full)................: 88.01%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (660913426); index ID: 1, database ID: 6
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................: 8056.0
- Avg. Page Density (full)................: 0.47%
DBCC SHOWCONTIG scanning 'CloseToteData' table...
Table: 'CloseToteData' (691025743); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 17307
- Extents Scanned.......................: 2167
- Extent Switches.......................: 2166
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.86% [2164:2167]
- Extent Scan Fragmentation ...............: 18.00%
- Avg. Bytes Free per Page................: 356.5
- Avg. Page Density (full)................: 95.60%
DBCC SHOWCONTIG scanning 'OrderState' table...
Table: 'OrderState' (692913540); index ID: 1, database ID: 6
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................: 7561.0
- Avg. Page Density (full)................: 6.59%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (709577566); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4796.0
- Avg. Page Density (full)................: 40.75%
DBCC SHOWCONTIG scanning 'LNG' table...
Table: 'LNG' (715149593); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 6.3
- Scan Density [Best Count:Actual Count]......: 100.00% [4:4]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 916.6
- Avg. Page Density (full)................: 88.68%
DBCC SHOWCONTIG scanning 'WaveState' table...
Table: 'WaveState' (724913654); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7659.0
- Avg. Page Density (full)................: 5.37%
DBCC SHOWCONTIG scanning 'PCS_Object' table...
Table: 'PCS_Object' (747149707); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 905.0
- Avg. Page Density (full)................: 88.82%
DBCC SHOWCONTIG scanning 'WaveByProduct' table...
Table: 'WaveByProduct' (756913768); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00% [1:5]
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 7734.6
- Avg. Page Density (full)................: 4.44%
DBCC SHOWCONTIG scanning 'LineItem' table...
Table: 'LineItem' (762902235); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5894
- Extents Scanned.......................: 745
- Extent Switches.......................: 744
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.93% [737:745]
- Extent Scan Fragmentation ...............: 89.13%
- Avg. Bytes Free per Page................: 1303.0
- Avg. Page Density (full)................: 83.90%
DBCC SHOWCONTIG scanning 'PCS_User' table...
Table: 'PCS_User' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.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................: 1251.7
- Avg. Page Density (full)................: 84.54%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (788913882); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.1
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Extent Scan Fragmentation ...............: 57.14%
- Avg. Bytes Free per Page................: 7723.8
- Avg. Page Density (full)................: 4.57%
DBCC SHOWCONTIG scanning 'PDSynchronize' table...
Table: 'PDSynchronize' (794902349); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 66.67%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3770.7
- Avg. Page Density (full)................: 53.41%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroup' table...
Table: 'PCS_PermissionGroup' (811149935); index ID: 1, database ID: 6
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................: 7319.0
- Avg. Page Density (full)................: 9.57%
DBCC SHOWCONTIG scanning 'Order' table...
Table: 'Order' (836914053); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 364
- Extents Scanned.......................: 55
- Extent Switches.......................: 54
- Avg. Pages per Extent..................: 6.6
- Scan Density [Best Count:Actual Count]......: 83.64% [46:55]
- Extent Scan Fragmentation ...............: 98.18%
- Avg. Bytes Free per Page................: 1263.0
- Avg. Page Density (full)................: 84.40%
DBCC SHOWCONTIG scanning 'Task' table...
Table: 'Task' (837578022); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 296
- Extents Scanned.......................: 42
- Extent Switches.......................: 43
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 84.09% [37:44]
- Logical Scan Fragmentation ..............: 0.68%
- Extent Scan Fragmentation ...............: 64.29%
- Avg. Bytes Free per Page................: 843.2
- Avg. Page Density (full)................: 89.58%
DBCC SHOWCONTIG scanning 'PCS_Component' table...
Table: 'PCS_Component' (843150049); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5189.0
- Avg. Page Density (full)................: 35.89%
DBCC SHOWCONTIG scanning 'QCEventLog' table...
Table: 'QCEventLog' (843918128); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3603
- Extents Scanned.......................: 465
- Extent Switches.......................: 464
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.99% [451:465]
- Extent Scan Fragmentation ...............: 36.99%
- Avg. Bytes Free per Page................: 575.1
- Avg. Page Density (full)................: 92.89%
DBCC SHOWCONTIG scanning 'OrderLine' table...
Table: 'OrderLine' (884914224); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6298
- Extents Scanned.......................: 798
- Extent Switches.......................: 797
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.75% [788:798]
- Extent Scan Fragmentation ...............: 98.37%
- Avg. Bytes Free per Page................: 255.3
- Avg. Page Density (full)................: 96.85%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroupHasPL1' table...
Table: 'PCS_PermissionGroupHasPL1' (891150220); index ID: 1, database
ID: 6
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................: 2068.0
- Avg. Page Density (full)................: 74.45%
DBCC SHOWCONTIG scanning 'QCErrors' table...
Table: 'QCErrors' (923918413); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 848.3
- Avg. Page Density (full)................: 89.52%
DBCC SHOWCONTIG scanning 'StateTransitionTimes' table...
Table: 'StateTransitionTimes' (932914395); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 590
- Extents Scanned.......................: 100
- Extent Switches.......................: 99
- Avg. Pages per Extent..................: 5.9
- Scan Density [Best Count:Actual Count]......: 74.00% [74:100]
- Extent Scan Fragmentation ...............: 91.00%
- Avg. Bytes Free per Page................: 599.8
- Avg. Page Density (full)................: 92.59%
DBCC SHOWCONTIG scanning 'PCS_UserInPermissionGroup' table...
Table: 'PCS_UserInPermissionGroup' (955150448); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 869.0
- Avg. Page Density (full)................: 89.26%
DBCC SHOWCONTIG scanning 'PickMission' table...
Table: 'PickMission' (964914509); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3950
- Extents Scanned.......................: 499
- Extent Switches.......................: 498
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.00% [494:499]
- Extent Scan Fragmentation ...............: 99.20%
- Avg. Bytes Free per Page................: 279.3
- Avg. Page Density (full)................: 96.55%
DBCC SHOWCONTIG scanning 'Mission' table...
Table: 'Mission' (965578478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 346
- Extents Scanned.......................: 52
- Extent Switches.......................: 53
- Avg. Pages per Extent..................: 6.7
- Scan Density [Best Count:Actual Count]......: 81.48% [44:54]
- Logical Scan Fragmentation ..............: 1.16%
- Extent Scan Fragmentation ...............: 82.69%
- Avg. Bytes Free per Page................: 876.8
- Avg. Page Density (full)................: 89.17%
DBCC SHOWCONTIG scanning 'QCMasterList' table...
Table: 'QCMasterList' (1003918698); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 128
- Extent Switches.......................: 133
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 91.79% [123:134]
- Logical Scan Fragmentation ..............: 1.12%
- Extent Scan Fragmentation ...............: 64.06%
- Avg. Bytes Free per Page................: 753.2
- Avg. Page Density (full)................: 90.69%
DBCC SHOWCONTIG scanning 'PCS_Client' table...
Table: 'PCS_Client' (1019150676); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7427.0
- Avg. Page Density (full)................: 8.24%
DBCC SHOWCONTIG scanning 'PCS_ClientRestriction' table...
Table: 'PCS_ClientRestriction' (1051150790); index ID: 1, database ID:
6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7780.0
- Avg. Page Density (full)................: 3.88%
DBCC SHOWCONTIG scanning 'PickMissionComplete' table...
Table: 'PickMissionComplete' (1060914851); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1661
- Extents Scanned.......................: 461
- Extent Switches.......................: 460
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 45.12% [208:461]
- Extent Scan Fragmentation ...............: 66.81%
- Avg. Bytes Free per Page................: 2926.2
- Avg. Page Density (full)................: 63.85%
DBCC SHOWCONTIG scanning 'UISCFG_Band' table...
Table: 'UISCFG_Band' (1083150904); index ID: 1, database ID: 6
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................: 6640.0
- Avg. Page Density (full)................: 17.96%
DBCC SHOWCONTIG scanning 'CurrentActivity' table...
Table: 'CurrentActivity' (1093578934); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 743.0
- Avg. Page Density (full)................: 90.82%
DBCC SHOWCONTIG scanning 'UISCFG_ComponentEntry' table...
Table: 'UISCFG_ComponentEntry' (1115151018); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 2.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................: 816.0
- Avg. Page Density (full)................: 89.92%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1156915193); index ID: 1, database ID: 6
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................: 7690.0
- Avg. Page Density (full)................: 4.99%
DBCC SHOWCONTIG scanning 'UISCFG_CE_on_Band' table...
Table: 'UISCFG_CE_on_Band' (1163151189); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4881.0
- Avg. Page Density (full)................: 39.70%
DBCC SHOWCONTIG scanning 'UISCFG_Treeview' table...
Table: 'UISCFG_Treeview' (1227151417); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3117.0
- Avg. Page Density (full)................: 61.49%
DBCC SHOWCONTIG scanning 'UISCFG_Events' table...
Table: 'UISCFG_Events' (1307151702); index ID: 0, database ID: 6
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................: 8051.0
- Avg. Page Density (full)................: 0.53%
DBCC SHOWCONTIG scanning 'IAS_AutoInteraction' table...
Table: 'IAS_AutoInteraction' (1323151759); index ID: 1, database ID: 6
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................: 2797.0
- Avg. Page Density (full)................: 65.44%
DBCC SHOWCONTIG scanning 'WorkLoadRollup' table...
Table: 'WorkLoadRollup' (1332199796); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2370
- Extents Scanned.......................: 298
- Extent Switches.......................: 297
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.66% [297:298]
- Logical Scan Fragmentation ..............: 9.66%
- Extent Scan Fragmentation ...............: 61.07%
- Avg. Bytes Free per Page................: 696.5
- Avg. Page Density (full)................: 91.39%
DBCC SHOWCONTIG scanning 'MissionDetail' table...
Table: 'MissionDetail' (1349579846); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1494
- Extents Scanned.......................: 189
- Extent Switches.......................: 855
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 21.85% [187:856]
- Logical Scan Fragmentation ..............: 27.58%
- Extent Scan Fragmentation ...............: 89.42%
- Avg. Bytes Free per Page................: 4327.5
- Avg. Page Density (full)................: 46.54%
DBCC SHOWCONTIG scanning 'UISCFG_SystemParameters' table...
Table: 'UISCFG_SystemParameters' (1355151873); index ID: 1, database
ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7984.0
- Avg. Page Density (full)................: 1.36%
DBCC SHOWCONTIG scanning 'UISCFG_ProductInfo' table...
Table: 'UISCFG_ProductInfo' (1387151987); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8048.0
- Avg. Page Density (full)................: 0.57%
DBCC SHOWCONTIG scanning 'UISCFG_UserPreferences' table...
Table: 'UISCFG_UserPreferences' (1419152101); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 1943.7
- Avg. Page Density (full)................: 75.99%
DBCC SHOWCONTIG scanning 'dtproperties' table...
Table: 'dtproperties' (1451152215); index ID: 1, database ID: 6
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................: 7567.0
- Avg. Page Density (full)................: 6.51%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1573580644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7290.0
- Avg. Page Density (full)................: 9.93%
DBCC SHOWCONTIG scanning 'Instruction' table...
Table: 'Instruction' (1605580758); index ID: 0, database ID: 6
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................: 4248.0
- Avg. Page Density (full)................: 47.52%
DBCC SHOWCONTIG scanning 'Message' table...
Table: 'Message' (1669580986); index ID: 1, database ID: 6
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................: 3633.0
- Avg. Page Density (full)................: 55.11%
DBCC SHOWCONTIG scanning 'SequenceNumbers' table...
Table: 'SequenceNumbers' (1765581328); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6080.0
- Avg. Page Density (full)................: 24.88%
DBCC SHOWCONTIG scanning '_bufferlog' table...
Table: '_bufferlog' (1807306194); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 212
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 87.10% [27:31]
- Extent Scan Fragmentation ...............: 58.06%
- Avg. Bytes Free per Page................: 331.4
- Avg. Page Density (full)................: 95.91%
DBCC SHOWCONTIG scanning 'Technology' table...
Table: 'Technology' (1877581727); index ID: 1, database ID: 6
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................: 7983.0
- Avg. Page Density (full)................: 1.37%
DBCC SHOWCONTIG scanning 'PostImportMap' table...
Table: 'PostImportMap' (1925581898); index ID: 0, database ID: 6
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................: 7818.0
- Avg. Page Density (full)................: 3.41%
DBCC SHOWCONTIG scanning 'ImportErrors' table...
Table: 'ImportErrors' (1941581955); index ID: 0, database ID: 6
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................: 7992.0
- Avg. Page Density (full)................: 1.26%
DBCC SHOWCONTIG scanning 'System' table...
Table: 'System' (2057058364); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8077.0
- Avg. Page Density (full)................: 0.21%
DBCC SHOWCONTIG scanning 'WorkArea' table...
Table: 'WorkArea' (2089058478); index ID: 1, database ID: 6
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................: 7970.0
- Avg. Page Density (full)................: 1.53%
DBCC SHOWCONTIG scanning 'ImportMap' table...
Table: 'ImportMap' (2112726579); index ID: 0, database ID: 6
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................: 7742.0
- Avg. Page Density (full)................: 4.35%
DBCC SHOWCONTIG scanning 'QCCurrentActivity' table...
Table: 'QCCurrentActivity' (2116254644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7940.0
- Avg. Page Density (full)................: 1.90%
DBCC SHOWCONTIG scanning 'Module' table...
Table: 'Module' (2137058649); index ID: 1, database ID: 6
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................: 6848.0
- Avg. Page Density (full)................: 15.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..|||Jayme wrote:
> This is the dbcc output before the reindex is done:
>
Ahh yes, I see lots of high "Extent Scan Fragmentation" values,
indicating external fragmentation, i.e. DISK fragmentation:
http://www.sql-server-performance.c...agmentation.asp
Some things you can do:
1. Never shrink the database. It's going to grow again, and repeated
shrink/growth operations will cause disk fragmentation.
2. Size the database properly to accommodate your needs to several
months, to avoid auto-growth
3. After sizing the DB properly, schedule an outage where you can shut
down SQL Server and run a full disk defrag. Copy the database files
(mdf and ldf) to another volume, use the Windows defragger to defrag the
drive, then copy the data files back, one at a time.
4. Now that the external fragmentation has been resolved, rebuild the
indexes.|||This is the dbcc output before the reindex is done:
DBCC SHOWCONTIG scanning 'QCSKUTable' table...
Table: 'QCSKUTable' (32771224); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 533
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 9.01%
- Extent Scan Fragmentation ...............: 72.06%
- Avg. Bytes Free per Page................: 789.2
- Avg. Page Density (full)................: 90.25%
DBCC SHOWCONTIG scanning 'Map' table...
Table: 'Map' (101575400); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6089.0
- Avg. Page Density (full)................: 24.77%
DBCC SHOWCONTIG scanning 'Zone' table...
Table: 'Zone' (133575514); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 70
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 837.4
- Avg. Page Density (full)................: 89.65%
DBCC SHOWCONTIG scanning 'QCConfiguration' table...
Table: 'QCConfiguration' (192771794); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6976.0
- Avg. Page Density (full)................: 13.81%
DBCC SHOWCONTIG scanning 'TestQCSKUTable' table...
Table: 'TestQCSKUTable' (230395990); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 535
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 67.65%
- Avg. Bytes Free per Page................: 786.5
- Avg. Page Density (full)................: 90.28%
DBCC SHOWCONTIG scanning 'Bay' table...
Table: 'Bay' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 20.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2084.0
- Avg. Page Density (full)................: 74.25%
DBCC SHOWCONTIG scanning 'ZoneMap' table...
Table: 'ZoneMap' (357576312); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 309
- Extents Scanned.......................: 39
- Extent Switches.......................: 38
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [39:39]
- Logical Scan Fragmentation ..............: 0.32%
- Extent Scan Fragmentation ...............: 69.23%
- Avg. Bytes Free per Page................: 799.9
- Avg. Page Density (full)................: 90.12%
DBCC SHOWCONTIG scanning 'Device' table...
Table: 'Device' (421576540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 293
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.34%
- Extent Scan Fragmentation ...............: 81.08%
- Avg. Bytes Free per Page................: 779.5
- Avg. Page Density (full)................: 90.37%
DBCC SHOWCONTIG scanning 'Location' table...
Table: 'Location' (501576825); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 285
- Extents Scanned.......................: 36
- Extent Switches.......................: 35
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [36:36]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 5.56%
- Avg. Bytes Free per Page................: 786.2
- Avg. Page Density (full)................: 90.29%
DBCC SHOWCONTIG scanning 'Carrier' table...
Table: 'Carrier' (580913141); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 232
- Extents Scanned.......................: 34
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 85.29% [29:34]
- Extent Scan Fragmentation ...............: 94.12%
- Avg. Bytes Free per Page................: 1359.3
- Avg. Page Density (full)................: 83.21%
DBCC SHOWCONTIG scanning 'NetController' table...
Table: 'NetController' (581577110); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 1356.0
- Avg. Page Density (full)................: 83.25%
DBCC SHOWCONTIG scanning 'QCContentDetail' table...
Table: 'QCContentDetail' (596249229); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 6
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3306.5
- Avg. Page Density (full)................: 59.15%
DBCC SHOWCONTIG scanning 'OrderToCarrier' table...
Table: 'OrderToCarrier' (628913312); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 139
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 58.06% [18:31]
- Extent Scan Fragmentation ...............: 96.77%
- Avg. Bytes Free per Page................: 1491.1
- Avg. Page Density (full)................: 81.58%
DBCC SHOWCONTIG scanning 'WaveGroup' table...
Table: 'WaveGroup' (645577338); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7988.0
- Avg. Page Density (full)................: 1.31%
DBCC SHOWCONTIG scanning 'WorkLoadCriteriaDetail' table...
Table: 'WorkLoadCriteriaDetail' (654625375); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 9
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 970.7
- Avg. Page Density (full)................: 88.01%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (660913426); index ID: 1, database ID: 6
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................: 8056.0
- Avg. Page Density (full)................: 0.47%
DBCC SHOWCONTIG scanning 'CloseToteData' table...
Table: 'CloseToteData' (691025743); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 17307
- Extents Scanned.......................: 2167
- Extent Switches.......................: 2166
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.86% [2164:2167]
- Extent Scan Fragmentation ...............: 18.00%
- Avg. Bytes Free per Page................: 356.5
- Avg. Page Density (full)................: 95.60%
DBCC SHOWCONTIG scanning 'OrderState' table...
Table: 'OrderState' (692913540); index ID: 1, database ID: 6
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................: 7561.0
- Avg. Page Density (full)................: 6.59%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (709577566); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4796.0
- Avg. Page Density (full)................: 40.75%
DBCC SHOWCONTIG scanning 'LNG' table...
Table: 'LNG' (715149593); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 6.3
- Scan Density [Best Count:Actual Count]......: 100.00% [4:4]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 916.6
- Avg. Page Density (full)................: 88.68%
DBCC SHOWCONTIG scanning 'WaveState' table...
Table: 'WaveState' (724913654); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7659.0
- Avg. Page Density (full)................: 5.37%
DBCC SHOWCONTIG scanning 'PCS_Object' table...
Table: 'PCS_Object' (747149707); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 905.0
- Avg. Page Density (full)................: 88.82%
DBCC SHOWCONTIG scanning 'WaveByProduct' table...
Table: 'WaveByProduct' (756913768); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00% [1:5]
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 7734.6
- Avg. Page Density (full)................: 4.44%
DBCC SHOWCONTIG scanning 'LineItem' table...
Table: 'LineItem' (762902235); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5894
- Extents Scanned.......................: 745
- Extent Switches.......................: 744
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.93% [737:745]
- Extent Scan Fragmentation ...............: 89.13%
- Avg. Bytes Free per Page................: 1303.0
- Avg. Page Density (full)................: 83.90%
DBCC SHOWCONTIG scanning 'PCS_User' table...
Table: 'PCS_User' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.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................: 1251.7
- Avg. Page Density (full)................: 84.54%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (788913882); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.1
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Extent Scan Fragmentation ...............: 57.14%
- Avg. Bytes Free per Page................: 7723.8
- Avg. Page Density (full)................: 4.57%
DBCC SHOWCONTIG scanning 'PDSynchronize' table...
Table: 'PDSynchronize' (794902349); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 66.67%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3770.7
- Avg. Page Density (full)................: 53.41%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroup' table...
Table: 'PCS_PermissionGroup' (811149935); index ID: 1, database ID: 6
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................: 7319.0
- Avg. Page Density (full)................: 9.57%
DBCC SHOWCONTIG scanning 'Order' table...
Table: 'Order' (836914053); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 364
- Extents Scanned.......................: 55
- Extent Switches.......................: 54
- Avg. Pages per Extent..................: 6.6
- Scan Density [Best Count:Actual Count]......: 83.64% [46:55]
- Extent Scan Fragmentation ...............: 98.18%
- Avg. Bytes Free per Page................: 1263.0
- Avg. Page Density (full)................: 84.40%
DBCC SHOWCONTIG scanning 'Task' table...
Table: 'Task' (837578022); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 296
- Extents Scanned.......................: 42
- Extent Switches.......................: 43
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 84.09% [37:44]
- Logical Scan Fragmentation ..............: 0.68%
- Extent Scan Fragmentation ...............: 64.29%
- Avg. Bytes Free per Page................: 843.2
- Avg. Page Density (full)................: 89.58%
DBCC SHOWCONTIG scanning 'PCS_Component' table...
Table: 'PCS_Component' (843150049); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5189.0
- Avg. Page Density (full)................: 35.89%
DBCC SHOWCONTIG scanning 'QCEventLog' table...
Table: 'QCEventLog' (843918128); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3603
- Extents Scanned.......................: 465
- Extent Switches.......................: 464
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.99% [451:465]
- Extent Scan Fragmentation ...............: 36.99%
- Avg. Bytes Free per Page................: 575.1
- Avg. Page Density (full)................: 92.89%
DBCC SHOWCONTIG scanning 'OrderLine' table...
Table: 'OrderLine' (884914224); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6298
- Extents Scanned.......................: 798
- Extent Switches.......................: 797
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.75% [788:798]
- Extent Scan Fragmentation ...............: 98.37%
- Avg. Bytes Free per Page................: 255.3
- Avg. Page Density (full)................: 96.85%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroupHasPL1' table...
Table: 'PCS_PermissionGroupHasPL1' (891150220); index ID: 1, database
ID: 6
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................: 2068.0
- Avg. Page Density (full)................: 74.45%
DBCC SHOWCONTIG scanning 'QCErrors' table...
Table: 'QCErrors' (923918413); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 848.3
- Avg. Page Density (full)................: 89.52%
DBCC SHOWCONTIG scanning 'StateTransitionTimes' table...
Table: 'StateTransitionTimes' (932914395); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 590
- Extents Scanned.......................: 100
- Extent Switches.......................: 99
- Avg. Pages per Extent..................: 5.9
- Scan Density [Best Count:Actual Count]......: 74.00% [74:100]
- Extent Scan Fragmentation ...............: 91.00%
- Avg. Bytes Free per Page................: 599.8
- Avg. Page Density (full)................: 92.59%
DBCC SHOWCONTIG scanning 'PCS_UserInPermissionGroup' table...
Table: 'PCS_UserInPermissionGroup' (955150448); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 869.0
- Avg. Page Density (full)................: 89.26%
DBCC SHOWCONTIG scanning 'PickMission' table...
Table: 'PickMission' (964914509); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3950
- Extents Scanned.......................: 499
- Extent Switches.......................: 498
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.00% [494:499]
- Extent Scan Fragmentation ...............: 99.20%
- Avg. Bytes Free per Page................: 279.3
- Avg. Page Density (full)................: 96.55%
DBCC SHOWCONTIG scanning 'Mission' table...
Table: 'Mission' (965578478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 346
- Extents Scanned.......................: 52
- Extent Switches.......................: 53
- Avg. Pages per Extent..................: 6.7
- Scan Density [Best Count:Actual Count]......: 81.48% [44:54]
- Logical Scan Fragmentation ..............: 1.16%
- Extent Scan Fragmentation ...............: 82.69%
- Avg. Bytes Free per Page................: 876.8
- Avg. Page Density (full)................: 89.17%
DBCC SHOWCONTIG scanning 'QCMasterList' table...
Table: 'QCMasterList' (1003918698); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 128
- Extent Switches.......................: 133
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 91.79% [123:134]
- Logical Scan Fragmentation ..............: 1.12%
- Extent Scan Fragmentation ...............: 64.06%
- Avg. Bytes Free per Page................: 753.2
- Avg. Page Density (full)................: 90.69%
DBCC SHOWCONTIG scanning 'PCS_Client' table...
Table: 'PCS_Client' (1019150676); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7427.0
- Avg. Page Density (full)................: 8.24%
DBCC SHOWCONTIG scanning 'PCS_ClientRestriction' table...
Table: 'PCS_ClientRestriction' (1051150790); index ID: 1, database ID:
6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7780.0
- Avg. Page Density (full)................: 3.88%
DBCC SHOWCONTIG scanning 'PickMissionComplete' table...
Table: 'PickMissionComplete' (1060914851); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1661
- Extents Scanned.......................: 461
- Extent Switches.......................: 460
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 45.12% [208:461]
- Extent Scan Fragmentation ...............: 66.81%
- Avg. Bytes Free per Page................: 2926.2
- Avg. Page Density (full)................: 63.85%
DBCC SHOWCONTIG scanning 'UISCFG_Band' table...
Table: 'UISCFG_Band' (1083150904); index ID: 1, database ID: 6
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................: 6640.0
- Avg. Page Density (full)................: 17.96%
DBCC SHOWCONTIG scanning 'CurrentActivity' table...
Table: 'CurrentActivity' (1093578934); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 743.0
- Avg. Page Density (full)................: 90.82%
DBCC SHOWCONTIG scanning 'UISCFG_ComponentEntry' table...
Table: 'UISCFG_ComponentEntry' (1115151018); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 2.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................: 816.0
- Avg. Page Density (full)................: 89.92%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1156915193); index ID: 1, database ID: 6
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................: 7690.0
- Avg. Page Density (full)................: 4.99%
DBCC SHOWCONTIG scanning 'UISCFG_CE_on_Band' table...
Table: 'UISCFG_CE_on_Band' (1163151189); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4881.0
- Avg. Page Density (full)................: 39.70%
DBCC SHOWCONTIG scanning 'UISCFG_Treeview' table...
Table: 'UISCFG_Treeview' (1227151417); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3117.0
- Avg. Page Density (full)................: 61.49%
DBCC SHOWCONTIG scanning 'UISCFG_Events' table...
Table: 'UISCFG_Events' (1307151702); index ID: 0, database ID: 6
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................: 8051.0
- Avg. Page Density (full)................: 0.53%
DBCC SHOWCONTIG scanning 'IAS_AutoInteraction' table...
Table: 'IAS_AutoInteraction' (1323151759); index ID: 1, database ID: 6
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................: 2797.0
- Avg. Page Density (full)................: 65.44%
DBCC SHOWCONTIG scanning 'WorkLoadRollup' table...
Table: 'WorkLoadRollup' (1332199796); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2370
- Extents Scanned.......................: 298
- Extent Switches.......................: 297
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.66% [297:298]
- Logical Scan Fragmentation ..............: 9.66%
- Extent Scan Fragmentation ...............: 61.07%
- Avg. Bytes Free per Page................: 696.5
- Avg. Page Density (full)................: 91.39%
DBCC SHOWCONTIG scanning 'MissionDetail' table...
Table: 'MissionDetail' (1349579846); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1494
- Extents Scanned.......................: 189
- Extent Switches.......................: 855
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 21.85% [187:856]
- Logical Scan Fragmentation ..............: 27.58%
- Extent Scan Fragmentation ...............: 89.42%
- Avg. Bytes Free per Page................: 4327.5
- Avg. Page Density (full)................: 46.54%
DBCC SHOWCONTIG scanning 'UISCFG_SystemParameters' table...
Table: 'UISCFG_SystemParameters' (1355151873); index ID: 1, database
ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7984.0
- Avg. Page Density (full)................: 1.36%
DBCC SHOWCONTIG scanning 'UISCFG_ProductInfo' table...
Table: 'UISCFG_ProductInfo' (1387151987); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8048.0
- Avg. Page Density (full)................: 0.57%
DBCC SHOWCONTIG scanning 'UISCFG_UserPreferences' table...
Table: 'UISCFG_UserPreferences' (1419152101); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 1943.7
- Avg. Page Density (full)................: 75.99%
DBCC SHOWCONTIG scanning 'dtproperties' table...
Table: 'dtproperties' (1451152215); index ID: 1, database ID: 6
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................: 7567.0
- Avg. Page Density (full)................: 6.51%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1573580644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7290.0
- Avg. Page Density (full)................: 9.93%
DBCC SHOWCONTIG scanning 'Instruction' table...
Table: 'Instruction' (1605580758); index ID: 0, database ID: 6
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................: 4248.0
- Avg. Page Density (full)................: 47.52%
DBCC SHOWCONTIG scanning 'Message' table...
Table: 'Message' (1669580986); index ID: 1, database ID: 6
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................: 3633.0
- Avg. Page Density (full)................: 55.11%
DBCC SHOWCONTIG scanning 'SequenceNumbers' table...
Table: 'SequenceNumbers' (1765581328); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6080.0
- Avg. Page Density (full)................: 24.88%
DBCC SHOWCONTIG scanning '_bufferlog' table...
Table: '_bufferlog' (1807306194); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 212
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 87.10% [27:31]
- Extent Scan Fragmentation ...............: 58.06%
- Avg. Bytes Free per Page................: 331.4
- Avg. Page Density (full)................: 95.91%
DBCC SHOWCONTIG scanning 'Technology' table...
Table: 'Technology' (1877581727); index ID: 1, database ID: 6
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................: 7983.0
- Avg. Page Density (full)................: 1.37%
DBCC SHOWCONTIG scanning 'PostImportMap' table...
Table: 'PostImportMap' (1925581898); index ID: 0, database ID: 6
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................: 7818.0
- Avg. Page Density (full)................: 3.41%
DBCC SHOWCONTIG scanning 'ImportErrors' table...
Table: 'ImportErrors' (1941581955); index ID: 0, database ID: 6
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................: 7992.0
- Avg. Page Density (full)................: 1.26%
DBCC SHOWCONTIG scanning 'System' table...
Table: 'System' (2057058364); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8077.0
- Avg. Page Density (full)................: 0.21%
DBCC SHOWCONTIG scanning 'WorkArea' table...
Table: 'WorkArea' (2089058478); index ID: 1, database ID: 6
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................: 7970.0
- Avg. Page Density (full)................: 1.53%
DBCC SHOWCONTIG scanning 'ImportMap' table...
Table: 'ImportMap' (2112726579); index ID: 0, database ID: 6
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................: 7742.0
- Avg. Page Density (full)................: 4.35%
DBCC SHOWCONTIG scanning 'QCCurrentActivity' table...
Table: 'QCCurrentActivity' (2116254644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7940.0
- Avg. Page Density (full)................: 1.90%
DBCC SHOWCONTIG scanning 'Module' table...
Table: 'Module' (2137058649); index ID: 1, database ID: 6
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................: 6848.0
- Avg. Page Density (full)................: 15.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Some possibilities:
Lots of inserts over a key that is no monotonically increasing.
Lost of updates (where value in index key changes).
Shrinking of database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jayme" <jayloub@.comcast.net> wrote in message
news:1150568837.067145.140050@.c74g2000cwc.googlegroups.com...
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>We have been running SQL Server 2000 on a production machine for years.
Within the last 6 months, we have noticed a performance problem. The
system will suddenly slow to a crawl. We run DBCC Reindex for all of
the tables in the our database, and things go back to normal - running
very quickly. We are currently having to do this multiple times a day.
We have verified the indexes are getting fragmented by using the DBCC
showcontig. What will cause the indexes to get fragmented so quickly?
How do we prevent this from happening?
Any help would greatly be appreciated.
Thank you!
Jayme|||> What will cause the indexes to get fragmented so quickly?
Some possibilities:
Lots of inserts over a key that is no monotonically increasing.
Lost of updates (where value in index key changes).
Shrinking of database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jayme" <jayloub@.comcast.net> wrote in message
news:1150568837.067145.140050@.c74g2000cwc.googlegroups.com...
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>|||Jayme wrote:
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>
Are you sure you're seeing index fragmentation, and not disk
fragmentation? Post the output of your DBCC command..|||Jayme wrote:
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>
Are you sure you're seeing index fragmentation, and not disk
fragmentation? Post the output of your DBCC command..|||Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Check out for disk fragmentation also.
If Index fragmentation , then BOL has very good sample, in DBCC
Showconting topic.
It will check index fragmentation and defrag index which are fragmented
below threshold value. Run it regularly.
Regards
Amish Shah|||Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Check out for disk fragmentation also.
If Index fragmentation , then BOL has very good sample, in DBCC
Showconting topic.
It will check index fragmentation and defrag index which are fragmented
below threshold value. Run it regularly.
Regards
Amish Shah|||This is the dbcc output before the reindex is done:
DBCC SHOWCONTIG scanning 'QCSKUTable' table...
Table: 'QCSKUTable' (32771224); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 533
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 9.01%
- Extent Scan Fragmentation ...............: 72.06%
- Avg. Bytes Free per Page................: 789.2
- Avg. Page Density (full)................: 90.25%
DBCC SHOWCONTIG scanning 'Map' table...
Table: 'Map' (101575400); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6089.0
- Avg. Page Density (full)................: 24.77%
DBCC SHOWCONTIG scanning 'Zone' table...
Table: 'Zone' (133575514); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 70
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 837.4
- Avg. Page Density (full)................: 89.65%
DBCC SHOWCONTIG scanning 'QCConfiguration' table...
Table: 'QCConfiguration' (192771794); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6976.0
- Avg. Page Density (full)................: 13.81%
DBCC SHOWCONTIG scanning 'TestQCSKUTable' table...
Table: 'TestQCSKUTable' (230395990); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 535
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 67.65%
- Avg. Bytes Free per Page................: 786.5
- Avg. Page Density (full)................: 90.28%
DBCC SHOWCONTIG scanning 'Bay' table...
Table: 'Bay' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 20.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2084.0
- Avg. Page Density (full)................: 74.25%
DBCC SHOWCONTIG scanning 'ZoneMap' table...
Table: 'ZoneMap' (357576312); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 309
- Extents Scanned.......................: 39
- Extent Switches.......................: 38
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [39:39]
- Logical Scan Fragmentation ..............: 0.32%
- Extent Scan Fragmentation ...............: 69.23%
- Avg. Bytes Free per Page................: 799.9
- Avg. Page Density (full)................: 90.12%
DBCC SHOWCONTIG scanning 'Device' table...
Table: 'Device' (421576540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 293
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.34%
- Extent Scan Fragmentation ...............: 81.08%
- Avg. Bytes Free per Page................: 779.5
- Avg. Page Density (full)................: 90.37%
DBCC SHOWCONTIG scanning 'Location' table...
Table: 'Location' (501576825); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 285
- Extents Scanned.......................: 36
- Extent Switches.......................: 35
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [36:36]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 5.56%
- Avg. Bytes Free per Page................: 786.2
- Avg. Page Density (full)................: 90.29%
DBCC SHOWCONTIG scanning 'Carrier' table...
Table: 'Carrier' (580913141); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 232
- Extents Scanned.......................: 34
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 85.29% [29:34]
- Extent Scan Fragmentation ...............: 94.12%
- Avg. Bytes Free per Page................: 1359.3
- Avg. Page Density (full)................: 83.21%
DBCC SHOWCONTIG scanning 'NetController' table...
Table: 'NetController' (581577110); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 1356.0
- Avg. Page Density (full)................: 83.25%
DBCC SHOWCONTIG scanning 'QCContentDetail' table...
Table: 'QCContentDetail' (596249229); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 6
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3306.5
- Avg. Page Density (full)................: 59.15%
DBCC SHOWCONTIG scanning 'OrderToCarrier' table...
Table: 'OrderToCarrier' (628913312); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 139
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 58.06% [18:31]
- Extent Scan Fragmentation ...............: 96.77%
- Avg. Bytes Free per Page................: 1491.1
- Avg. Page Density (full)................: 81.58%
DBCC SHOWCONTIG scanning 'WaveGroup' table...
Table: 'WaveGroup' (645577338); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7988.0
- Avg. Page Density (full)................: 1.31%
DBCC SHOWCONTIG scanning 'WorkLoadCriteriaDetail' table...
Table: 'WorkLoadCriteriaDetail' (654625375); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 9
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 970.7
- Avg. Page Density (full)................: 88.01%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (660913426); index ID: 1, database ID: 6
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................: 8056.0
- Avg. Page Density (full)................: 0.47%
DBCC SHOWCONTIG scanning 'CloseToteData' table...
Table: 'CloseToteData' (691025743); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 17307
- Extents Scanned.......................: 2167
- Extent Switches.......................: 2166
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.86% [2164:2167]
- Extent Scan Fragmentation ...............: 18.00%
- Avg. Bytes Free per Page................: 356.5
- Avg. Page Density (full)................: 95.60%
DBCC SHOWCONTIG scanning 'OrderState' table...
Table: 'OrderState' (692913540); index ID: 1, database ID: 6
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................: 7561.0
- Avg. Page Density (full)................: 6.59%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (709577566); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4796.0
- Avg. Page Density (full)................: 40.75%
DBCC SHOWCONTIG scanning 'LNG' table...
Table: 'LNG' (715149593); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 6.3
- Scan Density [Best Count:Actual Count]......: 100.00% [4:4]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 916.6
- Avg. Page Density (full)................: 88.68%
DBCC SHOWCONTIG scanning 'WaveState' table...
Table: 'WaveState' (724913654); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7659.0
- Avg. Page Density (full)................: 5.37%
DBCC SHOWCONTIG scanning 'PCS_Object' table...
Table: 'PCS_Object' (747149707); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 905.0
- Avg. Page Density (full)................: 88.82%
DBCC SHOWCONTIG scanning 'WaveByProduct' table...
Table: 'WaveByProduct' (756913768); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00% [1:5]
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 7734.6
- Avg. Page Density (full)................: 4.44%
DBCC SHOWCONTIG scanning 'LineItem' table...
Table: 'LineItem' (762902235); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5894
- Extents Scanned.......................: 745
- Extent Switches.......................: 744
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.93% [737:745]
- Extent Scan Fragmentation ...............: 89.13%
- Avg. Bytes Free per Page................: 1303.0
- Avg. Page Density (full)................: 83.90%
DBCC SHOWCONTIG scanning 'PCS_User' table...
Table: 'PCS_User' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.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................: 1251.7
- Avg. Page Density (full)................: 84.54%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (788913882); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.1
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Extent Scan Fragmentation ...............: 57.14%
- Avg. Bytes Free per Page................: 7723.8
- Avg. Page Density (full)................: 4.57%
DBCC SHOWCONTIG scanning 'PDSynchronize' table...
Table: 'PDSynchronize' (794902349); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 66.67%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3770.7
- Avg. Page Density (full)................: 53.41%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroup' table...
Table: 'PCS_PermissionGroup' (811149935); index ID: 1, database ID: 6
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................: 7319.0
- Avg. Page Density (full)................: 9.57%
DBCC SHOWCONTIG scanning 'Order' table...
Table: 'Order' (836914053); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 364
- Extents Scanned.......................: 55
- Extent Switches.......................: 54
- Avg. Pages per Extent..................: 6.6
- Scan Density [Best Count:Actual Count]......: 83.64% [46:55]
- Extent Scan Fragmentation ...............: 98.18%
- Avg. Bytes Free per Page................: 1263.0
- Avg. Page Density (full)................: 84.40%
DBCC SHOWCONTIG scanning 'Task' table...
Table: 'Task' (837578022); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 296
- Extents Scanned.......................: 42
- Extent Switches.......................: 43
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 84.09% [37:44]
- Logical Scan Fragmentation ..............: 0.68%
- Extent Scan Fragmentation ...............: 64.29%
- Avg. Bytes Free per Page................: 843.2
- Avg. Page Density (full)................: 89.58%
DBCC SHOWCONTIG scanning 'PCS_Component' table...
Table: 'PCS_Component' (843150049); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5189.0
- Avg. Page Density (full)................: 35.89%
DBCC SHOWCONTIG scanning 'QCEventLog' table...
Table: 'QCEventLog' (843918128); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3603
- Extents Scanned.......................: 465
- Extent Switches.......................: 464
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.99% [451:465]
- Extent Scan Fragmentation ...............: 36.99%
- Avg. Bytes Free per Page................: 575.1
- Avg. Page Density (full)................: 92.89%
DBCC SHOWCONTIG scanning 'OrderLine' table...
Table: 'OrderLine' (884914224); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6298
- Extents Scanned.......................: 798
- Extent Switches.......................: 797
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.75% [788:798]
- Extent Scan Fragmentation ...............: 98.37%
- Avg. Bytes Free per Page................: 255.3
- Avg. Page Density (full)................: 96.85%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroupHasPL1' table...
Table: 'PCS_PermissionGroupHasPL1' (891150220); index ID: 1, database
ID: 6
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................: 2068.0
- Avg. Page Density (full)................: 74.45%
DBCC SHOWCONTIG scanning 'QCErrors' table...
Table: 'QCErrors' (923918413); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 848.3
- Avg. Page Density (full)................: 89.52%
DBCC SHOWCONTIG scanning 'StateTransitionTimes' table...
Table: 'StateTransitionTimes' (932914395); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 590
- Extents Scanned.......................: 100
- Extent Switches.......................: 99
- Avg. Pages per Extent..................: 5.9
- Scan Density [Best Count:Actual Count]......: 74.00% [74:100]
- Extent Scan Fragmentation ...............: 91.00%
- Avg. Bytes Free per Page................: 599.8
- Avg. Page Density (full)................: 92.59%
DBCC SHOWCONTIG scanning 'PCS_UserInPermissionGroup' table...
Table: 'PCS_UserInPermissionGroup' (955150448); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 869.0
- Avg. Page Density (full)................: 89.26%
DBCC SHOWCONTIG scanning 'PickMission' table...
Table: 'PickMission' (964914509); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3950
- Extents Scanned.......................: 499
- Extent Switches.......................: 498
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.00% [494:499]
- Extent Scan Fragmentation ...............: 99.20%
- Avg. Bytes Free per Page................: 279.3
- Avg. Page Density (full)................: 96.55%
DBCC SHOWCONTIG scanning 'Mission' table...
Table: 'Mission' (965578478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 346
- Extents Scanned.......................: 52
- Extent Switches.......................: 53
- Avg. Pages per Extent..................: 6.7
- Scan Density [Best Count:Actual Count]......: 81.48% [44:54]
- Logical Scan Fragmentation ..............: 1.16%
- Extent Scan Fragmentation ...............: 82.69%
- Avg. Bytes Free per Page................: 876.8
- Avg. Page Density (full)................: 89.17%
DBCC SHOWCONTIG scanning 'QCMasterList' table...
Table: 'QCMasterList' (1003918698); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 128
- Extent Switches.......................: 133
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 91.79% [123:134]
- Logical Scan Fragmentation ..............: 1.12%
- Extent Scan Fragmentation ...............: 64.06%
- Avg. Bytes Free per Page................: 753.2
- Avg. Page Density (full)................: 90.69%
DBCC SHOWCONTIG scanning 'PCS_Client' table...
Table: 'PCS_Client' (1019150676); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7427.0
- Avg. Page Density (full)................: 8.24%
DBCC SHOWCONTIG scanning 'PCS_ClientRestriction' table...
Table: 'PCS_ClientRestriction' (1051150790); index ID: 1, database ID:
6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7780.0
- Avg. Page Density (full)................: 3.88%
DBCC SHOWCONTIG scanning 'PickMissionComplete' table...
Table: 'PickMissionComplete' (1060914851); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1661
- Extents Scanned.......................: 461
- Extent Switches.......................: 460
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 45.12% [208:461]
- Extent Scan Fragmentation ...............: 66.81%
- Avg. Bytes Free per Page................: 2926.2
- Avg. Page Density (full)................: 63.85%
DBCC SHOWCONTIG scanning 'UISCFG_Band' table...
Table: 'UISCFG_Band' (1083150904); index ID: 1, database ID: 6
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................: 6640.0
- Avg. Page Density (full)................: 17.96%
DBCC SHOWCONTIG scanning 'CurrentActivity' table...
Table: 'CurrentActivity' (1093578934); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 743.0
- Avg. Page Density (full)................: 90.82%
DBCC SHOWCONTIG scanning 'UISCFG_ComponentEntry' table...
Table: 'UISCFG_ComponentEntry' (1115151018); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 2.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................: 816.0
- Avg. Page Density (full)................: 89.92%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1156915193); index ID: 1, database ID: 6
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................: 7690.0
- Avg. Page Density (full)................: 4.99%
DBCC SHOWCONTIG scanning 'UISCFG_CE_on_Band' table...
Table: 'UISCFG_CE_on_Band' (1163151189); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4881.0
- Avg. Page Density (full)................: 39.70%
DBCC SHOWCONTIG scanning 'UISCFG_Treeview' table...
Table: 'UISCFG_Treeview' (1227151417); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3117.0
- Avg. Page Density (full)................: 61.49%
DBCC SHOWCONTIG scanning 'UISCFG_Events' table...
Table: 'UISCFG_Events' (1307151702); index ID: 0, database ID: 6
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................: 8051.0
- Avg. Page Density (full)................: 0.53%
DBCC SHOWCONTIG scanning 'IAS_AutoInteraction' table...
Table: 'IAS_AutoInteraction' (1323151759); index ID: 1, database ID: 6
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................: 2797.0
- Avg. Page Density (full)................: 65.44%
DBCC SHOWCONTIG scanning 'WorkLoadRollup' table...
Table: 'WorkLoadRollup' (1332199796); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2370
- Extents Scanned.......................: 298
- Extent Switches.......................: 297
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.66% [297:298]
- Logical Scan Fragmentation ..............: 9.66%
- Extent Scan Fragmentation ...............: 61.07%
- Avg. Bytes Free per Page................: 696.5
- Avg. Page Density (full)................: 91.39%
DBCC SHOWCONTIG scanning 'MissionDetail' table...
Table: 'MissionDetail' (1349579846); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1494
- Extents Scanned.......................: 189
- Extent Switches.......................: 855
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 21.85% [187:856]
- Logical Scan Fragmentation ..............: 27.58%
- Extent Scan Fragmentation ...............: 89.42%
- Avg. Bytes Free per Page................: 4327.5
- Avg. Page Density (full)................: 46.54%
DBCC SHOWCONTIG scanning 'UISCFG_SystemParameters' table...
Table: 'UISCFG_SystemParameters' (1355151873); index ID: 1, database
ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7984.0
- Avg. Page Density (full)................: 1.36%
DBCC SHOWCONTIG scanning 'UISCFG_ProductInfo' table...
Table: 'UISCFG_ProductInfo' (1387151987); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8048.0
- Avg. Page Density (full)................: 0.57%
DBCC SHOWCONTIG scanning 'UISCFG_UserPreferences' table...
Table: 'UISCFG_UserPreferences' (1419152101); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 1943.7
- Avg. Page Density (full)................: 75.99%
DBCC SHOWCONTIG scanning 'dtproperties' table...
Table: 'dtproperties' (1451152215); index ID: 1, database ID: 6
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................: 7567.0
- Avg. Page Density (full)................: 6.51%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1573580644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7290.0
- Avg. Page Density (full)................: 9.93%
DBCC SHOWCONTIG scanning 'Instruction' table...
Table: 'Instruction' (1605580758); index ID: 0, database ID: 6
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................: 4248.0
- Avg. Page Density (full)................: 47.52%
DBCC SHOWCONTIG scanning 'Message' table...
Table: 'Message' (1669580986); index ID: 1, database ID: 6
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................: 3633.0
- Avg. Page Density (full)................: 55.11%
DBCC SHOWCONTIG scanning 'SequenceNumbers' table...
Table: 'SequenceNumbers' (1765581328); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6080.0
- Avg. Page Density (full)................: 24.88%
DBCC SHOWCONTIG scanning '_bufferlog' table...
Table: '_bufferlog' (1807306194); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 212
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 87.10% [27:31]
- Extent Scan Fragmentation ...............: 58.06%
- Avg. Bytes Free per Page................: 331.4
- Avg. Page Density (full)................: 95.91%
DBCC SHOWCONTIG scanning 'Technology' table...
Table: 'Technology' (1877581727); index ID: 1, database ID: 6
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................: 7983.0
- Avg. Page Density (full)................: 1.37%
DBCC SHOWCONTIG scanning 'PostImportMap' table...
Table: 'PostImportMap' (1925581898); index ID: 0, database ID: 6
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................: 7818.0
- Avg. Page Density (full)................: 3.41%
DBCC SHOWCONTIG scanning 'ImportErrors' table...
Table: 'ImportErrors' (1941581955); index ID: 0, database ID: 6
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................: 7992.0
- Avg. Page Density (full)................: 1.26%
DBCC SHOWCONTIG scanning 'System' table...
Table: 'System' (2057058364); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8077.0
- Avg. Page Density (full)................: 0.21%
DBCC SHOWCONTIG scanning 'WorkArea' table...
Table: 'WorkArea' (2089058478); index ID: 1, database ID: 6
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................: 7970.0
- Avg. Page Density (full)................: 1.53%
DBCC SHOWCONTIG scanning 'ImportMap' table...
Table: 'ImportMap' (2112726579); index ID: 0, database ID: 6
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................: 7742.0
- Avg. Page Density (full)................: 4.35%
DBCC SHOWCONTIG scanning 'QCCurrentActivity' table...
Table: 'QCCurrentActivity' (2116254644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7940.0
- Avg. Page Density (full)................: 1.90%
DBCC SHOWCONTIG scanning 'Module' table...
Table: 'Module' (2137058649); index ID: 1, database ID: 6
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................: 6848.0
- Avg. Page Density (full)................: 15.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..|||Jayme wrote:
> This is the dbcc output before the reindex is done:
>
Ahh yes, I see lots of high "Extent Scan Fragmentation" values,
indicating external fragmentation, i.e. DISK fragmentation:
http://www.sql-server-performance.c...agmentation.asp
Some things you can do:
1. Never shrink the database. It's going to grow again, and repeated
shrink/growth operations will cause disk fragmentation.
2. Size the database properly to accommodate your needs to several
months, to avoid auto-growth
3. After sizing the DB properly, schedule an outage where you can shut
down SQL Server and run a full disk defrag. Copy the database files
(mdf and ldf) to another volume, use the Windows defragger to defrag the
drive, then copy the data files back, one at a time.
4. Now that the external fragmentation has been resolved, rebuild the
indexes.|||This is the dbcc output before the reindex is done:
DBCC SHOWCONTIG scanning 'QCSKUTable' table...
Table: 'QCSKUTable' (32771224); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 533
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 9.01%
- Extent Scan Fragmentation ...............: 72.06%
- Avg. Bytes Free per Page................: 789.2
- Avg. Page Density (full)................: 90.25%
DBCC SHOWCONTIG scanning 'Map' table...
Table: 'Map' (101575400); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6089.0
- Avg. Page Density (full)................: 24.77%
DBCC SHOWCONTIG scanning 'Zone' table...
Table: 'Zone' (133575514); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 70
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 837.4
- Avg. Page Density (full)................: 89.65%
DBCC SHOWCONTIG scanning 'QCConfiguration' table...
Table: 'QCConfiguration' (192771794); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6976.0
- Avg. Page Density (full)................: 13.81%
DBCC SHOWCONTIG scanning 'TestQCSKUTable' table...
Table: 'TestQCSKUTable' (230395990); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 535
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 67.65%
- Avg. Bytes Free per Page................: 786.5
- Avg. Page Density (full)................: 90.28%
DBCC SHOWCONTIG scanning 'Bay' table...
Table: 'Bay' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 20.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2084.0
- Avg. Page Density (full)................: 74.25%
DBCC SHOWCONTIG scanning 'ZoneMap' table...
Table: 'ZoneMap' (357576312); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 309
- Extents Scanned.......................: 39
- Extent Switches.......................: 38
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [39:39]
- Logical Scan Fragmentation ..............: 0.32%
- Extent Scan Fragmentation ...............: 69.23%
- Avg. Bytes Free per Page................: 799.9
- Avg. Page Density (full)................: 90.12%
DBCC SHOWCONTIG scanning 'Device' table...
Table: 'Device' (421576540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 293
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.34%
- Extent Scan Fragmentation ...............: 81.08%
- Avg. Bytes Free per Page................: 779.5
- Avg. Page Density (full)................: 90.37%
DBCC SHOWCONTIG scanning 'Location' table...
Table: 'Location' (501576825); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 285
- Extents Scanned.......................: 36
- Extent Switches.......................: 35
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [36:36]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 5.56%
- Avg. Bytes Free per Page................: 786.2
- Avg. Page Density (full)................: 90.29%
DBCC SHOWCONTIG scanning 'Carrier' table...
Table: 'Carrier' (580913141); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 232
- Extents Scanned.......................: 34
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 85.29% [29:34]
- Extent Scan Fragmentation ...............: 94.12%
- Avg. Bytes Free per Page................: 1359.3
- Avg. Page Density (full)................: 83.21%
DBCC SHOWCONTIG scanning 'NetController' table...
Table: 'NetController' (581577110); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 1356.0
- Avg. Page Density (full)................: 83.25%
DBCC SHOWCONTIG scanning 'QCContentDetail' table...
Table: 'QCContentDetail' (596249229); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 6
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3306.5
- Avg. Page Density (full)................: 59.15%
DBCC SHOWCONTIG scanning 'OrderToCarrier' table...
Table: 'OrderToCarrier' (628913312); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 139
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 58.06% [18:31]
- Extent Scan Fragmentation ...............: 96.77%
- Avg. Bytes Free per Page................: 1491.1
- Avg. Page Density (full)................: 81.58%
DBCC SHOWCONTIG scanning 'WaveGroup' table...
Table: 'WaveGroup' (645577338); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7988.0
- Avg. Page Density (full)................: 1.31%
DBCC SHOWCONTIG scanning 'WorkLoadCriteriaDetail' table...
Table: 'WorkLoadCriteriaDetail' (654625375); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 9
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 970.7
- Avg. Page Density (full)................: 88.01%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (660913426); index ID: 1, database ID: 6
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................: 8056.0
- Avg. Page Density (full)................: 0.47%
DBCC SHOWCONTIG scanning 'CloseToteData' table...
Table: 'CloseToteData' (691025743); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 17307
- Extents Scanned.......................: 2167
- Extent Switches.......................: 2166
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.86% [2164:2167]
- Extent Scan Fragmentation ...............: 18.00%
- Avg. Bytes Free per Page................: 356.5
- Avg. Page Density (full)................: 95.60%
DBCC SHOWCONTIG scanning 'OrderState' table...
Table: 'OrderState' (692913540); index ID: 1, database ID: 6
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................: 7561.0
- Avg. Page Density (full)................: 6.59%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (709577566); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4796.0
- Avg. Page Density (full)................: 40.75%
DBCC SHOWCONTIG scanning 'LNG' table...
Table: 'LNG' (715149593); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 6.3
- Scan Density [Best Count:Actual Count]......: 100.00% [4:4]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 916.6
- Avg. Page Density (full)................: 88.68%
DBCC SHOWCONTIG scanning 'WaveState' table...
Table: 'WaveState' (724913654); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7659.0
- Avg. Page Density (full)................: 5.37%
DBCC SHOWCONTIG scanning 'PCS_Object' table...
Table: 'PCS_Object' (747149707); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 905.0
- Avg. Page Density (full)................: 88.82%
DBCC SHOWCONTIG scanning 'WaveByProduct' table...
Table: 'WaveByProduct' (756913768); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00% [1:5]
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 7734.6
- Avg. Page Density (full)................: 4.44%
DBCC SHOWCONTIG scanning 'LineItem' table...
Table: 'LineItem' (762902235); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5894
- Extents Scanned.......................: 745
- Extent Switches.......................: 744
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.93% [737:745]
- Extent Scan Fragmentation ...............: 89.13%
- Avg. Bytes Free per Page................: 1303.0
- Avg. Page Density (full)................: 83.90%
DBCC SHOWCONTIG scanning 'PCS_User' table...
Table: 'PCS_User' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.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................: 1251.7
- Avg. Page Density (full)................: 84.54%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (788913882); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.1
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Extent Scan Fragmentation ...............: 57.14%
- Avg. Bytes Free per Page................: 7723.8
- Avg. Page Density (full)................: 4.57%
DBCC SHOWCONTIG scanning 'PDSynchronize' table...
Table: 'PDSynchronize' (794902349); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 66.67%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3770.7
- Avg. Page Density (full)................: 53.41%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroup' table...
Table: 'PCS_PermissionGroup' (811149935); index ID: 1, database ID: 6
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................: 7319.0
- Avg. Page Density (full)................: 9.57%
DBCC SHOWCONTIG scanning 'Order' table...
Table: 'Order' (836914053); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 364
- Extents Scanned.......................: 55
- Extent Switches.......................: 54
- Avg. Pages per Extent..................: 6.6
- Scan Density [Best Count:Actual Count]......: 83.64% [46:55]
- Extent Scan Fragmentation ...............: 98.18%
- Avg. Bytes Free per Page................: 1263.0
- Avg. Page Density (full)................: 84.40%
DBCC SHOWCONTIG scanning 'Task' table...
Table: 'Task' (837578022); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 296
- Extents Scanned.......................: 42
- Extent Switches.......................: 43
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 84.09% [37:44]
- Logical Scan Fragmentation ..............: 0.68%
- Extent Scan Fragmentation ...............: 64.29%
- Avg. Bytes Free per Page................: 843.2
- Avg. Page Density (full)................: 89.58%
DBCC SHOWCONTIG scanning 'PCS_Component' table...
Table: 'PCS_Component' (843150049); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5189.0
- Avg. Page Density (full)................: 35.89%
DBCC SHOWCONTIG scanning 'QCEventLog' table...
Table: 'QCEventLog' (843918128); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3603
- Extents Scanned.......................: 465
- Extent Switches.......................: 464
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.99% [451:465]
- Extent Scan Fragmentation ...............: 36.99%
- Avg. Bytes Free per Page................: 575.1
- Avg. Page Density (full)................: 92.89%
DBCC SHOWCONTIG scanning 'OrderLine' table...
Table: 'OrderLine' (884914224); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6298
- Extents Scanned.......................: 798
- Extent Switches.......................: 797
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.75% [788:798]
- Extent Scan Fragmentation ...............: 98.37%
- Avg. Bytes Free per Page................: 255.3
- Avg. Page Density (full)................: 96.85%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroupHasPL1' table...
Table: 'PCS_PermissionGroupHasPL1' (891150220); index ID: 1, database
ID: 6
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................: 2068.0
- Avg. Page Density (full)................: 74.45%
DBCC SHOWCONTIG scanning 'QCErrors' table...
Table: 'QCErrors' (923918413); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 848.3
- Avg. Page Density (full)................: 89.52%
DBCC SHOWCONTIG scanning 'StateTransitionTimes' table...
Table: 'StateTransitionTimes' (932914395); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 590
- Extents Scanned.......................: 100
- Extent Switches.......................: 99
- Avg. Pages per Extent..................: 5.9
- Scan Density [Best Count:Actual Count]......: 74.00% [74:100]
- Extent Scan Fragmentation ...............: 91.00%
- Avg. Bytes Free per Page................: 599.8
- Avg. Page Density (full)................: 92.59%
DBCC SHOWCONTIG scanning 'PCS_UserInPermissionGroup' table...
Table: 'PCS_UserInPermissionGroup' (955150448); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 869.0
- Avg. Page Density (full)................: 89.26%
DBCC SHOWCONTIG scanning 'PickMission' table...
Table: 'PickMission' (964914509); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3950
- Extents Scanned.......................: 499
- Extent Switches.......................: 498
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.00% [494:499]
- Extent Scan Fragmentation ...............: 99.20%
- Avg. Bytes Free per Page................: 279.3
- Avg. Page Density (full)................: 96.55%
DBCC SHOWCONTIG scanning 'Mission' table...
Table: 'Mission' (965578478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 346
- Extents Scanned.......................: 52
- Extent Switches.......................: 53
- Avg. Pages per Extent..................: 6.7
- Scan Density [Best Count:Actual Count]......: 81.48% [44:54]
- Logical Scan Fragmentation ..............: 1.16%
- Extent Scan Fragmentation ...............: 82.69%
- Avg. Bytes Free per Page................: 876.8
- Avg. Page Density (full)................: 89.17%
DBCC SHOWCONTIG scanning 'QCMasterList' table...
Table: 'QCMasterList' (1003918698); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 128
- Extent Switches.......................: 133
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 91.79% [123:134]
- Logical Scan Fragmentation ..............: 1.12%
- Extent Scan Fragmentation ...............: 64.06%
- Avg. Bytes Free per Page................: 753.2
- Avg. Page Density (full)................: 90.69%
DBCC SHOWCONTIG scanning 'PCS_Client' table...
Table: 'PCS_Client' (1019150676); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7427.0
- Avg. Page Density (full)................: 8.24%
DBCC SHOWCONTIG scanning 'PCS_ClientRestriction' table...
Table: 'PCS_ClientRestriction' (1051150790); index ID: 1, database ID:
6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7780.0
- Avg. Page Density (full)................: 3.88%
DBCC SHOWCONTIG scanning 'PickMissionComplete' table...
Table: 'PickMissionComplete' (1060914851); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1661
- Extents Scanned.......................: 461
- Extent Switches.......................: 460
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 45.12% [208:461]
- Extent Scan Fragmentation ...............: 66.81%
- Avg. Bytes Free per Page................: 2926.2
- Avg. Page Density (full)................: 63.85%
DBCC SHOWCONTIG scanning 'UISCFG_Band' table...
Table: 'UISCFG_Band' (1083150904); index ID: 1, database ID: 6
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................: 6640.0
- Avg. Page Density (full)................: 17.96%
DBCC SHOWCONTIG scanning 'CurrentActivity' table...
Table: 'CurrentActivity' (1093578934); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 743.0
- Avg. Page Density (full)................: 90.82%
DBCC SHOWCONTIG scanning 'UISCFG_ComponentEntry' table...
Table: 'UISCFG_ComponentEntry' (1115151018); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 2.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................: 816.0
- Avg. Page Density (full)................: 89.92%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1156915193); index ID: 1, database ID: 6
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................: 7690.0
- Avg. Page Density (full)................: 4.99%
DBCC SHOWCONTIG scanning 'UISCFG_CE_on_Band' table...
Table: 'UISCFG_CE_on_Band' (1163151189); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4881.0
- Avg. Page Density (full)................: 39.70%
DBCC SHOWCONTIG scanning 'UISCFG_Treeview' table...
Table: 'UISCFG_Treeview' (1227151417); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3117.0
- Avg. Page Density (full)................: 61.49%
DBCC SHOWCONTIG scanning 'UISCFG_Events' table...
Table: 'UISCFG_Events' (1307151702); index ID: 0, database ID: 6
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................: 8051.0
- Avg. Page Density (full)................: 0.53%
DBCC SHOWCONTIG scanning 'IAS_AutoInteraction' table...
Table: 'IAS_AutoInteraction' (1323151759); index ID: 1, database ID: 6
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................: 2797.0
- Avg. Page Density (full)................: 65.44%
DBCC SHOWCONTIG scanning 'WorkLoadRollup' table...
Table: 'WorkLoadRollup' (1332199796); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2370
- Extents Scanned.......................: 298
- Extent Switches.......................: 297
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.66% [297:298]
- Logical Scan Fragmentation ..............: 9.66%
- Extent Scan Fragmentation ...............: 61.07%
- Avg. Bytes Free per Page................: 696.5
- Avg. Page Density (full)................: 91.39%
DBCC SHOWCONTIG scanning 'MissionDetail' table...
Table: 'MissionDetail' (1349579846); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1494
- Extents Scanned.......................: 189
- Extent Switches.......................: 855
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 21.85% [187:856]
- Logical Scan Fragmentation ..............: 27.58%
- Extent Scan Fragmentation ...............: 89.42%
- Avg. Bytes Free per Page................: 4327.5
- Avg. Page Density (full)................: 46.54%
DBCC SHOWCONTIG scanning 'UISCFG_SystemParameters' table...
Table: 'UISCFG_SystemParameters' (1355151873); index ID: 1, database
ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7984.0
- Avg. Page Density (full)................: 1.36%
DBCC SHOWCONTIG scanning 'UISCFG_ProductInfo' table...
Table: 'UISCFG_ProductInfo' (1387151987); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8048.0
- Avg. Page Density (full)................: 0.57%
DBCC SHOWCONTIG scanning 'UISCFG_UserPreferences' table...
Table: 'UISCFG_UserPreferences' (1419152101); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 1943.7
- Avg. Page Density (full)................: 75.99%
DBCC SHOWCONTIG scanning 'dtproperties' table...
Table: 'dtproperties' (1451152215); index ID: 1, database ID: 6
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................: 7567.0
- Avg. Page Density (full)................: 6.51%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1573580644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7290.0
- Avg. Page Density (full)................: 9.93%
DBCC SHOWCONTIG scanning 'Instruction' table...
Table: 'Instruction' (1605580758); index ID: 0, database ID: 6
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................: 4248.0
- Avg. Page Density (full)................: 47.52%
DBCC SHOWCONTIG scanning 'Message' table...
Table: 'Message' (1669580986); index ID: 1, database ID: 6
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................: 3633.0
- Avg. Page Density (full)................: 55.11%
DBCC SHOWCONTIG scanning 'SequenceNumbers' table...
Table: 'SequenceNumbers' (1765581328); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6080.0
- Avg. Page Density (full)................: 24.88%
DBCC SHOWCONTIG scanning '_bufferlog' table...
Table: '_bufferlog' (1807306194); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 212
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 87.10% [27:31]
- Extent Scan Fragmentation ...............: 58.06%
- Avg. Bytes Free per Page................: 331.4
- Avg. Page Density (full)................: 95.91%
DBCC SHOWCONTIG scanning 'Technology' table...
Table: 'Technology' (1877581727); index ID: 1, database ID: 6
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................: 7983.0
- Avg. Page Density (full)................: 1.37%
DBCC SHOWCONTIG scanning 'PostImportMap' table...
Table: 'PostImportMap' (1925581898); index ID: 0, database ID: 6
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................: 7818.0
- Avg. Page Density (full)................: 3.41%
DBCC SHOWCONTIG scanning 'ImportErrors' table...
Table: 'ImportErrors' (1941581955); index ID: 0, database ID: 6
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................: 7992.0
- Avg. Page Density (full)................: 1.26%
DBCC SHOWCONTIG scanning 'System' table...
Table: 'System' (2057058364); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8077.0
- Avg. Page Density (full)................: 0.21%
DBCC SHOWCONTIG scanning 'WorkArea' table...
Table: 'WorkArea' (2089058478); index ID: 1, database ID: 6
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................: 7970.0
- Avg. Page Density (full)................: 1.53%
DBCC SHOWCONTIG scanning 'ImportMap' table...
Table: 'ImportMap' (2112726579); index ID: 0, database ID: 6
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................: 7742.0
- Avg. Page Density (full)................: 4.35%
DBCC SHOWCONTIG scanning 'QCCurrentActivity' table...
Table: 'QCCurrentActivity' (2116254644); index ID: 1, database ID: 6
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 ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7940.0
- Avg. Page Density (full)................: 1.90%
DBCC SHOWCONTIG scanning 'Module' table...
Table: 'Module' (2137058649); index ID: 1, database ID: 6
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................: 6848.0
- Avg. Page Density (full)................: 15.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Tracy McKibben wrote:
> Jayme wrote:
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Subscribe to:
Posts (Atom)