Choosing which index is best

Can sql server ever choose to use 2 indexes for a query?
tia
MG
Hurme,
Yes, certainly, from different tables, but you probably meant 'from the same
table', which is also Yes. In earlier versions, SQL Server would only use 1
index per table, but since SQL Server 7.0 it can use multiple indexes per
table at the determination of the optimizer. (Maybe some limited cases
earlier, but I don't remember any more.)
RLF
"Hurme" <michael.geles@.thomson.com> wrote in message
news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG
|||In addition to Russell's response: SQL Server supports index AND-ing and
index OR-ing.
A typical example is a query like this:
SELECT *
FROM my_table
WHERE lat BETWEEN 4 AND 6
AND long BETWEEN 10 AND 12
If there is an index on my_table(lat) and an index on my_table(long),
then the optimizer might choose to seek and partially scan both indexes
and intersect the results.
However, in my experience this feature is not used much, because in many
potential situations a different approach is faster, especially if there
is a similar compound index. For the example that could be an index on
my_table(lat,long).
For index OR-ing, the 'cheaper' alternative could be a (clustered) index
scan.
Bottom line: you need really selective predicates for index AND-ing or
OR-ing to kick in.
Gert-Jan
Hurme wrote:
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG
|||> (Maybe some limited cases earlier, but I don't remember any more.)
I believe that old architecture could use 2 indexes for OR, like below
WHERE col1 = 23
OR col2 = 34
But above was only case. Index intersection (AND) was added in 7.0 (along possibly with other new
cases).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eofv5zg1HHA.536@.TK2MSFTNGP06.phx.gbl...
> Hurme,
> Yes, certainly, from different tables, but you probably meant 'from the same table', which is also
> Yes. In earlier versions, SQL Server would only use 1 index per table, but since SQL Server 7.0
> it can use multiple indexes per table at the determination of the optimizer. (Maybe some limited
> cases earlier, but I don't remember any more.)
> RLF
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
>

Choosing which index is best

Can sql server ever choose to use 2 indexes for a query?
tia
--
MGHurme,
Yes, certainly, from different tables, but you probably meant 'from the same
table', which is also Yes. In earlier versions, SQL Server would only use 1
index per table, but since SQL Server 7.0 it can use multiple indexes per
table at the determination of the optimizer. (Maybe some limited cases
earlier, but I don't remember any more.)
RLF
"Hurme" <michael.geles@.thomson.com> wrote in message
news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||In addition to Russell's response: SQL Server supports index AND-ing and
index OR-ing.
A typical example is a query like this:
SELECT *
FROM my_table
WHERE lat BETWEEN 4 AND 6
AND long BETWEEN 10 AND 12
If there is an index on my_table(lat) and an index on my_table(long),
then the optimizer might choose to seek and partially scan both indexes
and intersect the results.
However, in my experience this feature is not used much, because in many
potential situations a different approach is faster, especially if there
is a similar compound index. For the example that could be an index on
my_table(lat,long).
For index OR-ing, the 'cheaper' alternative could be a (clustered) index
scan.
Bottom line: you need really selective predicates for index AND-ing or
OR-ing to kick in.
Gert-Jan
Hurme wrote:
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||> (Maybe some limited cases earlier, but I don't remember any more.)
I believe that old architecture could use 2 indexes for OR, like below
WHERE col1 = 23
OR col2 = 34
But above was only case. Index intersection (AND) was added in 7.0 (along po
ssibly with other new
cases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eofv5zg1HHA.536@.TK2MSFTNGP06.phx.gbl...
> Hurme,
> Yes, certainly, from different tables, but you probably meant 'from the sa
me table', which is also
> Yes. In earlier versions, SQL Server would only use 1 index per table, bu
t since SQL Server 7.0
> it can use multiple indexes per table at the determination of the optimize
r. (Maybe some limited
> cases earlier, but I don't remember any more.)
> RLF
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
>

Choosing which index is best

Can sql server ever choose to use 2 indexes for a query?
tia
--
MGHurme,
Yes, certainly, from different tables, but you probably meant 'from the same
table', which is also Yes. In earlier versions, SQL Server would only use 1
index per table, but since SQL Server 7.0 it can use multiple indexes per
table at the determination of the optimizer. (Maybe some limited cases
earlier, but I don't remember any more.)
RLF
"Hurme" <michael.geles@.thomson.com> wrote in message
news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||In addition to Russell's response: SQL Server supports index AND-ing and
index OR-ing.
A typical example is a query like this:
SELECT *
FROM my_table
WHERE lat BETWEEN 4 AND 6
AND long BETWEEN 10 AND 12
If there is an index on my_table(lat) and an index on my_table(long),
then the optimizer might choose to seek and partially scan both indexes
and intersect the results.
However, in my experience this feature is not used much, because in many
potential situations a different approach is faster, especially if there
is a similar compound index. For the example that could be an index on
my_table(lat,long).
For index OR-ing, the 'cheaper' alternative could be a (clustered) index
scan.
Bottom line: you need really selective predicates for index AND-ing or
OR-ing to kick in.
Gert-Jan
Hurme wrote:
> Can sql server ever choose to use 2 indexes for a query?
> tia
> --
> MG|||> (Maybe some limited cases earlier, but I don't remember any more.)
I believe that old architecture could use 2 indexes for OR, like below
WHERE col1 = 23
OR col2 = 34
But above was only case. Index intersection (AND) was added in 7.0 (along possibly with other new
cases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eofv5zg1HHA.536@.TK2MSFTNGP06.phx.gbl...
> Hurme,
> Yes, certainly, from different tables, but you probably meant 'from the same table', which is also
> Yes. In earlier versions, SQL Server would only use 1 index per table, but since SQL Server 7.0
> it can use multiple indexes per table at the determination of the optimizer. (Maybe some limited
> cases earlier, but I don't remember any more.)
> RLF
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:425878FE-2EFA-4EE9-8302-38E719328F11@.microsoft.com...
>> Can sql server ever choose to use 2 indexes for a query?
>> tia
>> --
>> MG
>

Choosing Indexes

After doing some reading, I'd like some expert opinions on choosing indexes
for several scenarios.
1) One book said that you should almost index foreign keys in a table since
lookups are more inefficient without them. I have tables with up to ten FKs
used mostly for lookups from/joins with the PK table (e.g. marital status,
education level, etc.). Each of these PK lookup tables only have 3-10
possible values. However, pulling info from these PKs to display the linked
items is a frequent event and many times a day a massive report effectively
denormalizes everything for any individual customer.
Generally, is it worth the overhead to index foreign keys in one table for
primary key tables that have a handful of values?
2) If I have a web application for numerous companies that each have their
own customers, and when the folks from one company are using the application
they only see their customers one at a time or an alphabetical list (a
pretty common scenario I would think), what is the best way to index
CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
Is it better to have three distinct indexes? Which one should be the optimal
for a clustered index? (I would think CompanyID since it is used for almost
every query except for individual customers which are already indexed by
being a PK as CustomerID)
Or, should all of these be rolled into one composite index? If these are
rolled into a composite index, is there any value at all for still having
distinct indexes for these three columns? Would you still need an index for
the company FK join to the PK Company table, e.g. to display the company
name?
Even though the CompanyID is not as selective as the combination of first
name and last name, I would think a clustered index of CompanyID,
CustLastName, CustFirstName would do the trick without any other indices.
But maybe a separate index for CompanyID (for company information joins)?
Thanks for any tips.1) yes, otherwise you will end up doing table scans. With the indexes in
place you will scan the index which is much more efficient.
2) yes, always go from most restrictive to least restrict. You probably have
more first names, than last names and more last names than companies, hence
the index should look like companyID, LastName, Firstname. I would use one
index but it really depends. If most of your queries are like this
select * from tablename where companyid=1 and lastname =smith and
firstname=don the index should be on companyid, lastname and then first
name, however if you query on lastnames or firstnames you should have an
index on each of these columns as well.
Use a clustered index if you are doing a scan ie select * from tablename
where companyid>100, otherwise if you are only returning a few values use a
non clustered index.
HTH
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
> After doing some reading, I'd like some expert opinions on choosing
> indexes
> for several scenarios.
> 1) One book said that you should almost index foreign keys in a table
> since
> lookups are more inefficient without them. I have tables with up to ten
> FKs
> used mostly for lookups from/joins with the PK table (e.g. marital status,
> education level, etc.). Each of these PK lookup tables only have 3-10
> possible values. However, pulling info from these PKs to display the
> linked
> items is a frequent event and many times a day a massive report
> effectively
> denormalizes everything for any individual customer.
> Generally, is it worth the overhead to index foreign keys in one table for
> primary key tables that have a handful of values?
> 2) If I have a web application for numerous companies that each have their
> own customers, and when the folks from one company are using the
> application
> they only see their customers one at a time or an alphabetical list (a
> pretty common scenario I would think), what is the best way to index
> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
> Is it better to have three distinct indexes? Which one should be the
> optimal
> for a clustered index? (I would think CompanyID since it is used for
> almost
> every query except for individual customers which are already indexed by
> being a PK as CustomerID)
> Or, should all of these be rolled into one composite index? If these are
> rolled into a composite index, is there any value at all for still having
> distinct indexes for these three columns? Would you still need an index
> for
> the company FK join to the PK Company table, e.g. to display the company
> name?
> Even though the CompanyID is not as selective as the combination of first
> name and last name, I would think a clustered index of CompanyID,
> CustLastName, CustFirstName would do the trick without any other indices.
> But maybe a separate index for CompanyID (for company information
> joins)?
> Thanks for any tips.
>|||Thanks for the advice, but I'm not sure if I understand all of your answers.
> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
Even faster than a table scan of only 5-10 rows total?
> 2) yes, always go from most restrictive to least restrict. You probably
have
> more first names, than last names and more last names than companies,
hence
> the index should look like companyID, LastName, Firstname.
So, if you go from most restrictive to least restrictive shouldn't the index
be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)
> I would use one
> index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name,
Most of my queries are probably more like this
select * from tablename where companyID = 1 order by lastname, firstname
> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
a
> non clustered index.
The companyID will always be just one value and not a range. With that said,
maybe the LastName, FirstName should be the clustered choice with a
non-clustered index for companyID?
Thanks again for your help.|||Hi Hilary
Why is a clustered index better for a scan? Aren't all indexes sorted &
therefore equally beneficial for scans? Non-clustered actually indexes more
efficient for scans due to their higher page / row storage density..
The only thing a clustered index can do better than a non-clustered index is
cover the SELECT *, as it contains all columns, which definitely makes your
advice right in this case, but I think your rationale isn't clear.
I'm starting a series of short articles, intended to debunk the many myths
about clustered indexes on my blog, including the classic "clustered indexes
are better for range scans". I started this series a couple of days ago,
covering the bookmark lookup problem but I'll cover this specific issue soon
(it's definitely bogus):
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/10/363.aspx
Regards,
Greg Linwood
SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
> 2) yes, always go from most restrictive to least restrict. You probably
> have more first names, than last names and more last names than companies,
> hence the index should look like companyID, LastName, Firstname. I would
> use one index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name, however if you query on lastnames or firstnames you should have an
> index on each of these columns as well.
> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
> a non clustered index.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
>> After doing some reading, I'd like some expert opinions on choosing
>> indexes
>> for several scenarios.
>> 1) One book said that you should almost index foreign keys in a table
>> since
>> lookups are more inefficient without them. I have tables with up to ten
>> FKs
>> used mostly for lookups from/joins with the PK table (e.g. marital
>> status,
>> education level, etc.). Each of these PK lookup tables only have 3-10
>> possible values. However, pulling info from these PKs to display the
>> linked
>> items is a frequent event and many times a day a massive report
>> effectively
>> denormalizes everything for any individual customer.
>> Generally, is it worth the overhead to index foreign keys in one table
>> for
>> primary key tables that have a handful of values?
>> 2) If I have a web application for numerous companies that each have
>> their
>> own customers, and when the folks from one company are using the
>> application
>> they only see their customers one at a time or an alphabetical list (a
>> pretty common scenario I would think), what is the best way to index
>> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
>> Is it better to have three distinct indexes? Which one should be the
>> optimal
>> for a clustered index? (I would think CompanyID since it is used for
>> almost
>> every query except for individual customers which are already indexed by
>> being a PK as CustomerID)
>> Or, should all of these be rolled into one composite index? If these are
>> rolled into a composite index, is there any value at all for still having
>> distinct indexes for these three columns? Would you still need an index
>> for
>> the company FK join to the PK Company table, e.g. to display the company
>> name?
>> Even though the CompanyID is not as selective as the combination of first
>> name and last name, I would think a clustered index of CompanyID,
>> CustLastName, CustFirstName would do the trick without any other indices.
>> But maybe a separate index for CompanyID (for company information
>> joins)?
>> Thanks for any tips.
>>
>|||On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:
>After doing some reading, I'd like some expert opinions on choosing indexes
>for several scenarios.
Hi Don,
Here are mine. They are the diametrically opposite of Hilary's opinions,
just to confuse you <eg>
>1) One book said that you should almost index foreign keys in a table since
>lookups are more inefficient without them. I have tables with up to ten FKs
>used mostly for lookups from/joins with the PK table (e.g. marital status,
>education level, etc.). Each of these PK lookup tables only have 3-10
>possible values. However, pulling info from these PKs to display the linked
>items is a frequent event and many times a day a massive report effectively
>denormalizes everything for any individual customer.
>Generally, is it worth the overhead to index foreign keys in one table for
>primary key tables that have a handful of values?
In cases like this, with the referred table holding marital status,
education level, etc, indexes on the foreign key columns are often not
needed. Though it depends on the nature of your queries. If you often
query for female university level people or similar, indexes on those
columns *MIGHT* get used. But for most queries, I expect SQL Server to
filter rows based on other (more restrictive) criteria in the SELECT,
then use bookmark lookup or a hash join to find the english name for the
gender code, marital status code and education level code.
Indexes on foreign key columns are mainly used to check the constraint
on changes in the referenced table (which, in the case of marital status
and education level, should be extremely rare - the contents of those
tables are alomst constant) or when a very efficient filter can be
applied to the referenced table (which is often the case between
referencing table that hold non-constant data, such as orders and order
details, or orders and customers).
>2) If I have a web application for numerous companies that each have their
>own customers, and when the folks from one company are using the application
>they only see their customers one at a time or an alphabetical list (a
>pretty common scenario I would think), what is the best way to index
>CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
For queries like that, a clustered index no (CompanyID, CustLastName,
CustFirstName) is best. The engine can use the index structure to find
the first matching row in an instant; after that, it can navigate the
logical order of the data in the clustered index to find all rows for
the same company, and they'll already be in the order required. (It
might help to include CompanyID as the first column in the ORDER BY
clause.)
Note that, contrary to popular belief, more or less restrictive should
NOT be the first consideration for the order of columns in an index. You
should start with hhow the columns are used; restrictiveness comes only
second. If most of your searches are based on last name only, then last
name should be the first column in the index, whether restrictive or
not. If most searches are based on all three columns, then the order
doesn't matter for those searches (though statistics will be slightly
better if the most restrictive column is first). If all kinds of
searches are mixed, then you should have the most restrictive column as
the first in the index - and you might consider addition indexes for
searches that can't use the index.
>Is it better to have three distinct indexes? Which one should be the optimal
>for a clustered index? (I would think CompanyID since it is used for almost
>every query except for individual customers which are already indexed by
>being a PK as CustomerID)
I don't think three seperate indexes is a good idea. That might work if
you often search on first name only, often search on last name only, and
often search on CompanyID only. For searches on two or all three
columns, SQL Server *can* use operations that use all three tables, then
combine the intermediate results from those index searches - but the
overhead of that is quite costly, so it'll very often just use one index
to narrow down the search a bit, then search the rest by accessing all
rows and checking the values.
If you do use three indexes, than I think that CompanyID might indeed be
the best choice, but I'd try different options to make sure. Of course,
this also depends on the nature of other queries - above, you outlines
one typical query, but iff there are others as well, you should include
them in your analysis.
And I agree that searches for individual customers would benefit from an
index on CustomerID; since this is your key, the index shoould already
exist. If you've used default options, it's the clustered index - I
suggest that yoou change it to a nonclustered index, so that you can
have (CompanyID, CustLastName, CustFirstName) as clustered index. The
search for an individual customer results in just a single bookmark
lookup, which is quite inexpensive. It's the searches for a range of
rows where repeated bookmark lookups get expensive.
>Or, should all of these be rolled into one composite index? If these are
>rolled into a composite index, is there any value at all for still having
>distinct indexes for these three columns? Would you still need an index for
>the company FK join to the PK Company table, e.g. to display the company
>name?
Some situations might benefit from additional indexes on the single
columns (especially on the columns that are NOT first in the composite
index).
Finding company name based on CompanyID will in almost all cases require
an index on the CompanyID column in the Companies table - which should
be there already if you didn;t forget the primary key constraint.
>Even though the CompanyID is not as selective as the combination of first
>name and last name, I would think a clustered index of CompanyID,
>CustLastName, CustFirstName would do the trick without any other indices.
>But maybe a separate index for CompanyID (for company information joins)?
With that clustered index, a seperate index on only CompanyID would only
give you some benefit in rare cases where the query is covered by this
index - the benefit will probably not outweigh the cost of maintaining
an additional index.
--
Hugo Kornelis, SQL Server MVP|||1) well probably not. But for larger tables indexes are faster than table
scans.
2) Well when I think most restrictive I think of a value which returns the
fewest results. So as there are fewer company id's than Smiths and even
fewer company id's than Johns, I would argue that it would go some
companyID, LastName and then FirstName.
So consider John Smith where there are 200 john smiths per company id and
there are 20 companies. If you search on FirstName, LastName, CompanyID you
would return 200*20 rows to the filtering condition which filters out
company id's. Then 200 are returned. If you go Company ID, then Names, 200
rows are returned to the filtering condition and then 200 remain in the
filter.
Make sense?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:uXL6NgQ1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Thanks for the advice, but I'm not sure if I understand all of your
> answers.
>> 1) yes, otherwise you will end up doing table scans. With the indexes in
>> place you will scan the index which is much more efficient.
> Even faster than a table scan of only 5-10 rows total?
>> 2) yes, always go from most restrictive to least restrict. You probably
> have
>> more first names, than last names and more last names than companies,
> hence
>> the index should look like companyID, LastName, Firstname.
> So, if you go from most restrictive to least restrictive shouldn't the
> index
> be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)
>> I would use one
>> index but it really depends. If most of your queries are like this
>> select * from tablename where companyid=1 and lastname =smith and
>> firstname=don the index should be on companyid, lastname and then first
>> name,
> Most of my queries are probably more like this
> select * from tablename where companyID = 1 order by lastname, firstname
>> Use a clustered index if you are doing a scan ie select * from tablename
>> where companyid>100, otherwise if you are only returning a few values use
> a
>> non clustered index.
> The companyID will always be just one value and not a range. With that
> said,
> maybe the LastName, FirstName should be the clustered choice with a
> non-clustered index for companyID?
> Thanks again for your help.
>|||Great answer Hugo, better than mine.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.4ax.com...
> On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:
>>After doing some reading, I'd like some expert opinions on choosing
>>indexes
>>for several scenarios.
> Hi Don,
> Here are mine. They are the diametrically opposite of Hilary's opinions,
> just to confuse you <eg>
>>1) One book said that you should almost index foreign keys in a table
>>since
>>lookups are more inefficient without them. I have tables with up to ten
>>FKs
>>used mostly for lookups from/joins with the PK table (e.g. marital status,
>>education level, etc.). Each of these PK lookup tables only have 3-10
>>possible values. However, pulling info from these PKs to display the
>>linked
>>items is a frequent event and many times a day a massive report
>>effectively
>>denormalizes everything for any individual customer.
>>Generally, is it worth the overhead to index foreign keys in one table for
>>primary key tables that have a handful of values?
> In cases like this, with the referred table holding marital status,
> education level, etc, indexes on the foreign key columns are often not
> needed. Though it depends on the nature of your queries. If you often
> query for female university level people or similar, indexes on those
> columns *MIGHT* get used. But for most queries, I expect SQL Server to
> filter rows based on other (more restrictive) criteria in the SELECT,
> then use bookmark lookup or a hash join to find the english name for the
> gender code, marital status code and education level code.
> Indexes on foreign key columns are mainly used to check the constraint
> on changes in the referenced table (which, in the case of marital status
> and education level, should be extremely rare - the contents of those
> tables are alomst constant) or when a very efficient filter can be
> applied to the referenced table (which is often the case between
> referencing table that hold non-constant data, such as orders and order
> details, or orders and customers).
>>2) If I have a web application for numerous companies that each have their
>>own customers, and when the folks from one company are using the
>>application
>>they only see their customers one at a time or an alphabetical list (a
>>pretty common scenario I would think), what is the best way to index
>>CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
> Note that, contrary to popular belief, more or less restrictive should
> NOT be the first consideration for the order of columns in an index. You
> should start with hhow the columns are used; restrictiveness comes only
> second. If most of your searches are based on last name only, then last
> name should be the first column in the index, whether restrictive or
> not. If most searches are based on all three columns, then the order
> doesn't matter for those searches (though statistics will be slightly
> better if the most restrictive column is first). If all kinds of
> searches are mixed, then you should have the most restrictive column as
> the first in the index - and you might consider addition indexes for
> searches that can't use the index.
>>Is it better to have three distinct indexes? Which one should be the
>>optimal
>>for a clustered index? (I would think CompanyID since it is used for
>>almost
>>every query except for individual customers which are already indexed by
>>being a PK as CustomerID)
> I don't think three seperate indexes is a good idea. That might work if
> you often search on first name only, often search on last name only, and
> often search on CompanyID only. For searches on two or all three
> columns, SQL Server *can* use operations that use all three tables, then
> combine the intermediate results from those index searches - but the
> overhead of that is quite costly, so it'll very often just use one index
> to narrow down the search a bit, then search the rest by accessing all
> rows and checking the values.
> If you do use three indexes, than I think that CompanyID might indeed be
> the best choice, but I'd try different options to make sure. Of course,
> this also depends on the nature of other queries - above, you outlines
> one typical query, but iff there are others as well, you should include
> them in your analysis.
> And I agree that searches for individual customers would benefit from an
> index on CustomerID; since this is your key, the index shoould already
> exist. If you've used default options, it's the clustered index - I
> suggest that yoou change it to a nonclustered index, so that you can
> have (CompanyID, CustLastName, CustFirstName) as clustered index. The
> search for an individual customer results in just a single bookmark
> lookup, which is quite inexpensive. It's the searches for a range of
> rows where repeated bookmark lookups get expensive.
>>Or, should all of these be rolled into one composite index? If these are
>>rolled into a composite index, is there any value at all for still having
>>distinct indexes for these three columns? Would you still need an index
>>for
>>the company FK join to the PK Company table, e.g. to display the company
>>name?
> Some situations might benefit from additional indexes on the single
> columns (especially on the columns that are NOT first in the composite
> index).
> Finding company name based on CompanyID will in almost all cases require
> an index on the CompanyID column in the Companies table - which should
> be there already if you didn;t forget the primary key constraint.
>>Even though the CompanyID is not as selective as the combination of first
>>name and last name, I would think a clustered index of CompanyID,
>>CustLastName, CustFirstName would do the trick without any other indices.
>>But maybe a separate index for CompanyID (for company information
>>joins)?
> With that clustered index, a seperate index on only CompanyID would only
> give you some benefit in rare cases where the query is covered by this
> index - the benefit will probably not outweigh the cost of maintaining
> an additional index.
> --
> Hugo Kornelis, SQL Server MVP|||Hi Greg!
I have been putting together a presentation on indexing fundamentals for
some of the code camps. In some tests I ran it seems to me that clustered
indexes were better for scans, however I will have to try to repeat this to
verify it. I look forward to your research.
I'll be posting something in the private group soon which I have discovered
which I think it pretty interesting.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OHZdTlS1GHA.1300@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary
> Why is a clustered index better for a scan? Aren't all indexes sorted &
> therefore equally beneficial for scans? Non-clustered actually indexes
> more efficient for scans due to their higher page / row storage density..
> The only thing a clustered index can do better than a non-clustered index
> is cover the SELECT *, as it contains all columns, which definitely makes
> your advice right in this case, but I think your rationale isn't clear.
> I'm starting a series of short articles, intended to debunk the many myths
> about clustered indexes on my blog, including the classic "clustered
> indexes are better for range scans". I started this series a couple of
> days ago, covering the bookmark lookup problem but I'll cover this
> specific issue soon (it's definitely bogus):
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/10/363.aspx
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> 1) yes, otherwise you will end up doing table scans. With the indexes in
>> place you will scan the index which is much more efficient.
>> 2) yes, always go from most restrictive to least restrict. You probably
>> have more first names, than last names and more last names than
>> companies, hence the index should look like companyID, LastName,
>> Firstname. I would use one index but it really depends. If most of your
>> queries are like this
>> select * from tablename where companyid=1 and lastname =smith and
>> firstname=don the index should be on companyid, lastname and then first
>> name, however if you query on lastnames or firstnames you should have an
>> index on each of these columns as well.
>> Use a clustered index if you are doing a scan ie select * from tablename
>> where companyid>100, otherwise if you are only returning a few values use
>> a non clustered index.
>> HTH
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
>> After doing some reading, I'd like some expert opinions on choosing
>> indexes
>> for several scenarios.
>> 1) One book said that you should almost index foreign keys in a table
>> since
>> lookups are more inefficient without them. I have tables with up to ten
>> FKs
>> used mostly for lookups from/joins with the PK table (e.g. marital
>> status,
>> education level, etc.). Each of these PK lookup tables only have 3-10
>> possible values. However, pulling info from these PKs to display the
>> linked
>> items is a frequent event and many times a day a massive report
>> effectively
>> denormalizes everything for any individual customer.
>> Generally, is it worth the overhead to index foreign keys in one table
>> for
>> primary key tables that have a handful of values?
>> 2) If I have a web application for numerous companies that each have
>> their
>> own customers, and when the folks from one company are using the
>> application
>> they only see their customers one at a time or an alphabetical list (a
>> pretty common scenario I would think), what is the best way to index
>> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
>> Is it better to have three distinct indexes? Which one should be the
>> optimal
>> for a clustered index? (I would think CompanyID since it is used for
>> almost
>> every query except for individual customers which are already indexed by
>> being a PK as CustomerID)
>> Or, should all of these be rolled into one composite index? If these are
>> rolled into a composite index, is there any value at all for still
>> having
>> distinct indexes for these three columns? Would you still need an index
>> for
>> the company FK join to the PK Company table, e.g. to display the company
>> name?
>> Even though the CompanyID is not as selective as the combination of
>> first
>> name and last name, I would think a clustered index of CompanyID,
>> CustLastName, CustFirstName would do the trick without any other
>> indices.
>> But maybe a separate index for CompanyID (for company information
>> joins)?
>> Thanks for any tips.
>>
>>
>|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.4ax.com...
> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
Actually, I tried to simplify things a little. Besides a company ID, there
are multiple regions within that company. I tried a clustered composite
index using companyID, regionID, lastname, firstname
Just looking at execution plans, I found that if I have a query like
select * from companies where companyID = ' and regionID = ' order by
lastname, firstname, or (as you suggested)
select * from companies where companyID = ' order by regionID, lastname,
firstname
there is one clustered index seek, but if I drop the regionID in the where
or order by clause, the execution plan includes a sort step in addition to
the index seek.
Hmmm. Didn't know that including *somewhere* the columns in a clustered
composite index would eliminate execution steps. Thanks for the
parenthetical.

Choosing Indexes

After doing some reading, I'd like some expert opinions on choosing indexes
for several scenarios.
1) One book said that you should almost index foreign keys in a table since
lookups are more inefficient without them. I have tables with up to ten FKs
used mostly for lookups from/joins with the PK table (e.g. marital status,
education level, etc.). Each of these PK lookup tables only have 3-10
possible values. However, pulling info from these PKs to display the linked
items is a frequent event and many times a day a massive report effectively
denormalizes everything for any individual customer.
Generally, is it worth the overhead to index foreign keys in one table for
primary key tables that have a handful of values?
2) If I have a web application for numerous companies that each have their
own customers, and when the folks from one company are using the application
they only see their customers one at a time or an alphabetical list (a
pretty common scenario I would think), what is the best way to index
CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
Is it better to have three distinct indexes? Which one should be the optimal
for a clustered index? (I would think CompanyID since it is used for almost
every query except for individual customers which are already indexed by
being a PK as CustomerID)
Or, should all of these be rolled into one composite index? If these are
rolled into a composite index, is there any value at all for still having
distinct indexes for these three columns? Would you still need an index for
the company FK join to the PK Company table, e.g. to display the company
name?
Even though the CompanyID is not as selective as the combination of first
name and last name, I would think a clustered index of CompanyID,
CustLastName, CustFirstName would do the trick without any other indices.
But maybe a separate index for CompanyID (for company information joins)?
Thanks for any tips.1) yes, otherwise you will end up doing table scans. With the indexes in
place you will scan the index which is much more efficient.
2) yes, always go from most restrictive to least restrict. You probably have
more first names, than last names and more last names than companies, hence
the index should look like companyID, LastName, Firstname. I would use one
index but it really depends. If most of your queries are like this
select * from tablename where companyid=1 and lastname =smith and
firstname=don the index should be on companyid, lastname and then first
name, however if you query on lastnames or firstnames you should have an
index on each of these columns as well.
Use a clustered index if you are doing a scan ie select * from tablename
where companyid>100, otherwise if you are only returning a few values use a
non clustered index.
HTH
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
> After doing some reading, I'd like some expert opinions on choosing
> indexes
> for several scenarios.
> 1) One book said that you should almost index foreign keys in a table
> since
> lookups are more inefficient without them. I have tables with up to ten
> FKs
> used mostly for lookups from/joins with the PK table (e.g. marital status,
> education level, etc.). Each of these PK lookup tables only have 3-10
> possible values. However, pulling info from these PKs to display the
> linked
> items is a frequent event and many times a day a massive report
> effectively
> denormalizes everything for any individual customer.
> Generally, is it worth the overhead to index foreign keys in one table for
> primary key tables that have a handful of values?
> 2) If I have a web application for numerous companies that each have their
> own customers, and when the folks from one company are using the
> application
> they only see their customers one at a time or an alphabetical list (a
> pretty common scenario I would think), what is the best way to index
> CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
> Is it better to have three distinct indexes? Which one should be the
> optimal
> for a clustered index? (I would think CompanyID since it is used for
> almost
> every query except for individual customers which are already indexed by
> being a PK as CustomerID)
> Or, should all of these be rolled into one composite index? If these are
> rolled into a composite index, is there any value at all for still having
> distinct indexes for these three columns? Would you still need an index
> for
> the company FK join to the PK Company table, e.g. to display the company
> name?
> Even though the CompanyID is not as selective as the combination of first
> name and last name, I would think a clustered index of CompanyID,
> CustLastName, CustFirstName would do the trick without any other indices.
> But maybe a separate index for CompanyID (for company information
> joins)?
> Thanks for any tips.
>|||Thanks for the advice, but I'm not sure if I understand all of your answers.

> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
Even faster than a table scan of only 5-10 rows total?

> 2) yes, always go from most restrictive to least restrict. You probably
have
> more first names, than last names and more last names than companies,
hence
> the index should look like companyID, LastName, Firstname.
So, if you go from most restrictive to least restrictive shouldn't the index
be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)

> I would use one
> index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name,
Most of my queries are probably more like this
select * from tablename where companyID = 1 order by lastname, firstname

> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
a
> non clustered index.
The companyID will always be just one value and not a range. With that said,
maybe the LastName, FirstName should be the clustered choice with a
non-clustered index for companyID?
Thanks again for your help.|||Hi Hilary
Why is a clustered index better for a scan? Aren't all indexes sorted &
therefore equally beneficial for scans? Non-clustered actually indexes more
efficient for scans due to their higher page / row storage density..
The only thing a clustered index can do better than a non-clustered index is
cover the SELECT *, as it contains all columns, which definitely makes your
advice right in this case, but I think your rationale isn't clear.
I'm starting a series of short articles, intended to debunk the many myths
about clustered indexes on my blog, including the classic "clustered indexes
are better for range scans". I started this series a couple of days ago,
covering the bookmark lookup problem but I'll cover this specific issue soon
(it's definitely bogus):
http://blogs.sqlserver.org.au/blogs.../09/10/363.aspx
Regards,
Greg Linwood
SQL Server MVP
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
> 1) yes, otherwise you will end up doing table scans. With the indexes in
> place you will scan the index which is much more efficient.
> 2) yes, always go from most restrictive to least restrict. You probably
> have more first names, than last names and more last names than companies,
> hence the index should look like companyID, LastName, Firstname. I would
> use one index but it really depends. If most of your queries are like this
> select * from tablename where companyid=1 and lastname =smith and
> firstname=don the index should be on companyid, lastname and then first
> name, however if you query on lastnames or firstnames you should have an
> index on each of these columns as well.
> Use a clustered index if you are doing a scan ie select * from tablename
> where companyid>100, otherwise if you are only returning a few values use
> a non clustered index.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Obu59EP1GHA.324@.TK2MSFTNGP05.phx.gbl...
>|||On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:

>After doing some reading, I'd like some expert opinions on choosing indexes
>for several scenarios.
Hi Don,
Here are mine. They are the diametrically opposite of Hilary's opinions,
just to confuse you <eg>

>1) One book said that you should almost index foreign keys in a table since
>lookups are more inefficient without them. I have tables with up to ten FKs
>used mostly for lookups from/joins with the PK table (e.g. marital status,
>education level, etc.). Each of these PK lookup tables only have 3-10
>possible values. However, pulling info from these PKs to display the linked
>items is a frequent event and many times a day a massive report effectively
>denormalizes everything for any individual customer.
>Generally, is it worth the overhead to index foreign keys in one table for
>primary key tables that have a handful of values?
In cases like this, with the referred table holding marital status,
education level, etc, indexes on the foreign key columns are often not
needed. Though it depends on the nature of your queries. If you often
query for female university level people or similar, indexes on those
columns *MIGHT* get used. But for most queries, I expect SQL Server to
filter rows based on other (more restrictive) criteria in the SELECT,
then use bookmark lookup or a hash join to find the english name for the
gender code, marital status code and education level code.
Indexes on foreign key columns are mainly used to check the constraint
on changes in the referenced table (which, in the case of marital status
and education level, should be extremely rare - the contents of those
tables are alomst constant) or when a very efficient filter can be
applied to the referenced table (which is often the case between
referencing table that hold non-constant data, such as orders and order
details, or orders and customers).

>2) If I have a web application for numerous companies that each have their
>own customers, and when the folks from one company are using the applicatio
n
>they only see their customers one at a time or an alphabetical list (a
>pretty common scenario I would think), what is the best way to index
>CompanyID (a FK to a Company PK table), CustLastName, CustFirstName?
For queries like that, a clustered index no (CompanyID, CustLastName,
CustFirstName) is best. The engine can use the index structure to find
the first matching row in an instant; after that, it can navigate the
logical order of the data in the clustered index to find all rows for
the same company, and they'll already be in the order required. (It
might help to include CompanyID as the first column in the ORDER BY
clause.)
Note that, contrary to popular belief, more or less restrictive should
NOT be the first consideration for the order of columns in an index. You
should start with hhow the columns are used; restrictiveness comes only
second. If most of your searches are based on last name only, then last
name should be the first column in the index, whether restrictive or
not. If most searches are based on all three columns, then the order
doesn't matter for those searches (though statistics will be slightly
better if the most restrictive column is first). If all kinds of
searches are mixed, then you should have the most restrictive column as
the first in the index - and you might consider addition indexes for
searches that can't use the index.

>Is it better to have three distinct indexes? Which one should be the optima
l
>for a clustered index? (I would think CompanyID since it is used for almost
>every query except for individual customers which are already indexed by
>being a PK as CustomerID)
I don't think three seperate indexes is a good idea. That might work if
you often search on first name only, often search on last name only, and
often search on CompanyID only. For searches on two or all three
columns, SQL Server *can* use operations that use all three tables, then
combine the intermediate results from those index searches - but the
overhead of that is quite costly, so it'll very often just use one index
to narrow down the search a bit, then search the rest by accessing all
rows and checking the values.
If you do use three indexes, than I think that CompanyID might indeed be
the best choice, but I'd try different options to make sure. Of course,
this also depends on the nature of other queries - above, you outlines
one typical query, but iff there are others as well, you should include
them in your analysis.
And I agree that searches for individual customers would benefit from an
index on CustomerID; since this is your key, the index shoould already
exist. If you've used default options, it's the clustered index - I
suggest that yoou change it to a nonclustered index, so that you can
have (CompanyID, CustLastName, CustFirstName) as clustered index. The
search for an individual customer results in just a single bookmark
lookup, which is quite inexpensive. It's the searches for a range of
rows where repeated bookmark lookups get expensive.

>Or, should all of these be rolled into one composite index? If these are
>rolled into a composite index, is there any value at all for still having
>distinct indexes for these three columns? Would you still need an index for
>the company FK join to the PK Company table, e.g. to display the company
>name?
Some situations might benefit from additional indexes on the single
columns (especially on the columns that are NOT first in the composite
index).
Finding company name based on CompanyID will in almost all cases require
an index on the CompanyID column in the Companies table - which should
be there already if you didn;t forget the primary key constraint.

>Even though the CompanyID is not as selective as the combination of first
>name and last name, I would think a clustered index of CompanyID,
>CustLastName, CustFirstName would do the trick without any other indices.
>But maybe a separate index for CompanyID (for company information joins)?
With that clustered index, a seperate index on only CompanyID would only
give you some benefit in rare cases where the query is covered by this
index - the benefit will probably not outweigh the cost of maintaining
an additional index.
Hugo Kornelis, SQL Server MVP|||1) well probably not. But for larger tables indexes are faster than table
scans.
2) Well when I think most restrictive I think of a value which returns the
fewest results. So as there are fewer company id's than Smiths and even
fewer company id's than Johns, I would argue that it would go some
companyID, LastName and then FirstName.
So consider John Smith where there are 200 john smiths per company id and
there are 20 companies. If you search on FirstName, LastName, CompanyID you
would return 200*20 rows to the filtering condition which filters out
company id's. Then 200 are returned. If you go Company ID, then Names, 200
rows are returned to the filtering condition and then 200 remain in the
filter.
Make sense?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:uXL6NgQ1GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Thanks for the advice, but I'm not sure if I understand all of your
> answers.
>
> Even faster than a table scan of only 5-10 rows total?
>
> have
> hence
> So, if you go from most restrictive to least restrictive shouldn't the
> index
> be in the exact opposite order? (e.g. FirstName, LastName, CompanyID)
>
> Most of my queries are probably more like this
> select * from tablename where companyID = 1 order by lastname, firstname
>
> a
> The companyID will always be just one value and not a range. With that
> said,
> maybe the LastName, FirstName should be the clustered choice with a
> non-clustered index for companyID?
> Thanks again for your help.
>|||Great answer Hugo, better than mine.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.
4ax.com...
> On Sun, 10 Sep 2006 10:58:40 -0500, Don Miller wrote:
>
> Hi Don,
> Here are mine. They are the diametrically opposite of Hilary's opinions,
> just to confuse you <eg>
>
> In cases like this, with the referred table holding marital status,
> education level, etc, indexes on the foreign key columns are often not
> needed. Though it depends on the nature of your queries. If you often
> query for female university level people or similar, indexes on those
> columns *MIGHT* get used. But for most queries, I expect SQL Server to
> filter rows based on other (more restrictive) criteria in the SELECT,
> then use bookmark lookup or a hash join to find the english name for the
> gender code, marital status code and education level code.
> Indexes on foreign key columns are mainly used to check the constraint
> on changes in the referenced table (which, in the case of marital status
> and education level, should be extremely rare - the contents of those
> tables are alomst constant) or when a very efficient filter can be
> applied to the referenced table (which is often the case between
> referencing table that hold non-constant data, such as orders and order
> details, or orders and customers).
>
> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
> Note that, contrary to popular belief, more or less restrictive should
> NOT be the first consideration for the order of columns in an index. You
> should start with hhow the columns are used; restrictiveness comes only
> second. If most of your searches are based on last name only, then last
> name should be the first column in the index, whether restrictive or
> not. If most searches are based on all three columns, then the order
> doesn't matter for those searches (though statistics will be slightly
> better if the most restrictive column is first). If all kinds of
> searches are mixed, then you should have the most restrictive column as
> the first in the index - and you might consider addition indexes for
> searches that can't use the index.
>
> I don't think three seperate indexes is a good idea. That might work if
> you often search on first name only, often search on last name only, and
> often search on CompanyID only. For searches on two or all three
> columns, SQL Server *can* use operations that use all three tables, then
> combine the intermediate results from those index searches - but the
> overhead of that is quite costly, so it'll very often just use one index
> to narrow down the search a bit, then search the rest by accessing all
> rows and checking the values.
> If you do use three indexes, than I think that CompanyID might indeed be
> the best choice, but I'd try different options to make sure. Of course,
> this also depends on the nature of other queries - above, you outlines
> one typical query, but iff there are others as well, you should include
> them in your analysis.
> And I agree that searches for individual customers would benefit from an
> index on CustomerID; since this is your key, the index shoould already
> exist. If you've used default options, it's the clustered index - I
> suggest that yoou change it to a nonclustered index, so that you can
> have (CompanyID, CustLastName, CustFirstName) as clustered index. The
> search for an individual customer results in just a single bookmark
> lookup, which is quite inexpensive. It's the searches for a range of
> rows where repeated bookmark lookups get expensive.
>
> Some situations might benefit from additional indexes on the single
> columns (especially on the columns that are NOT first in the composite
> index).
> Finding company name based on CompanyID will in almost all cases require
> an index on the CompanyID column in the Companies table - which should
> be there already if you didn;t forget the primary key constraint.
>
> With that clustered index, a seperate index on only CompanyID would only
> give you some benefit in rare cases where the query is covered by this
> index - the benefit will probably not outweigh the cost of maintaining
> an additional index.
> --
> Hugo Kornelis, SQL Server MVP|||Hi Greg!
I have been putting together a presentation on indexing fundamentals for
some of the code camps. In some tests I ran it seems to me that clustered
indexes were better for scans, however I will have to try to repeat this to
verify it. I look forward to your research.
I'll be posting something in the private group soon which I have discovered
which I think it pretty interesting.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OHZdTlS1GHA.1300@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary
> Why is a clustered index better for a scan? Aren't all indexes sorted &
> therefore equally beneficial for scans? Non-clustered actually indexes
> more efficient for scans due to their higher page / row storage density..
> The only thing a clustered index can do better than a non-clustered index
> is cover the SELECT *, as it contains all columns, which definitely makes
> your advice right in this case, but I think your rationale isn't clear.
> I'm starting a series of short articles, intended to debunk the many myths
> about clustered indexes on my blog, including the classic "clustered
> indexes are better for range scans". I started this series a couple of
> days ago, covering the bookmark lookup problem but I'll cover this
> specific issue soon (it's definitely bogus):
> [url]http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/10/363.aspx[/ur
l]
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OUziSWQ1GHA.1256@.TK2MSFTNGP04.phx.gbl...
>|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:fj39g258fjaagokp4k9ci2r2flbqkpindb@.
4ax.com...

> For queries like that, a clustered index no (CompanyID, CustLastName,
> CustFirstName) is best. The engine can use the index structure to find
> the first matching row in an instant; after that, it can navigate the
> logical order of the data in the clustered index to find all rows for
> the same company, and they'll already be in the order required. (It
> might help to include CompanyID as the first column in the ORDER BY
> clause.)
Actually, I tried to simplify things a little. Besides a company ID, there
are multiple regions within that company. I tried a clustered composite
index using companyID, regionID, lastname, firstname
Just looking at execution plans, I found that if I have a query like
select * from companies where companyID = ' and regionID = ' order by
lastname, firstname, or (as you suggested)
select * from companies where companyID = ' order by regionID, lastname,
firstname
there is one clustered index seek, but if I drop the regionID in the where
or order by clause, the execution plan includes a sort step in addition to
the index seek.
Hmmm. Didn't know that including *somewhere* the columns in a clustered
composite index would eliminate execution steps. Thanks for the
parenthetical.

Checkdb vs Integrity with indexes

Is there a difference running checkdb -DBCC checkdb ('database') and running
the Integrity with Indexes option of a maintenance plan? The checkdb output
shows detail while the integrity output shows almost no detail.
Thanks
Ron
What about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
Do the outputs match then?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> Is there a difference running checkdb -DBCC checkdb ('database') and
running
> the Integrity with Indexes option of a maintenance plan? The checkdb
output
> shows detail while the integrity output shows almost no detail.
> Thanks
> Ron
|||No different results.
Because the Integrity with Indexes via the maintenance plan produces
different output than a DBCC checkdb database, I wanted to know if both are
doing the same thing.
I'm thinking of replacing the integrity with a DBCC checkdb, but would like
to know what's the difference between the two besides the output.
"Paul S Randal [MS]" wrote:

> What about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
> Do the outputs match then?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> running
> output
>
>
|||Can you post the output? As far as I'm aware, the integrity check runs DBCC
CHECKDB.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> No different results.
> Because the Integrity with Indexes via the maintenance plan produces
> different output than a DBCC checkdb database, I wanted to know if both
are
> doing the same thing.
> I'm thinking of replacing the integrity with a DBCC checkdb, but would
like[vbcol=seagreen]
> to know what's the difference between the two besides the output.
> "Paul S Randal [MS]" wrote:
NO_INFOMSGS?[vbcol=seagreen]
rights.[vbcol=seagreen]
|||Here the Integrity output:
1] Database ABCD: Check Data and Index Linkage...
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
Here's the DBCC checkdb output :
Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
04:00:00
DBCC results for 'Cinfo'. [SQLSTATE 01000]
DBCC results for 'sysobjects'. [SQLSTATE 01000]
There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
DBCC results for 'sysindexes'. [SQLSTATE 01000]
There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
DBCC results for 'syscolumns'. [SQLSTATE 01000]
There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
DBCC results for 'systypes'. [SQLSTATE 01000]
There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
DBCC results for 'syscomments'. [SQLSTATE 01000]
There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
DBCC results for 'sysfiles1'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
DBCC results for 'syspermissions'. [SQLSTATE 01000]
There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
DBCC results for 'sysusers'. [SQLSTATE 01000]
There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
DBCC results for 'sysproperties'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
DBCC results for 'sysdepends'. [SQLSTATE 01000]
There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
DBCC results for 'sysreferences'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
[SQLSTATE 01000]
DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
CHECKDB found 0 allocation errors and 0 consistency errors in database
'cinfo'. [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]
"Paul S Randal [MS]" wrote:

> Can you post the output? As far as I'm aware, the integrity check runs DBCC
> CHECKDB.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> are
> like
> NO_INFOMSGS?
> rights.
>
>
|||Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...[vbcol=seagreen]
> Here the Integrity output:
> 1] Database ABCD: Check Data and Index Linkage...
>
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Here's the DBCC checkdb output :
> Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
> 04:00:00
> DBCC results for 'Cinfo'. [SQLSTATE 01000]
> DBCC results for 'sysobjects'. [SQLSTATE 01000]
> There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
> DBCC results for 'sysindexes'. [SQLSTATE 01000]
> There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
> DBCC results for 'syscolumns'. [SQLSTATE 01000]
> There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
> DBCC results for 'systypes'. [SQLSTATE 01000]
> There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> DBCC results for 'syscomments'. [SQLSTATE 01000]
> There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
> DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> DBCC results for 'syspermissions'. [SQLSTATE 01000]
> There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
> DBCC results for 'sysusers'. [SQLSTATE 01000]
> There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> DBCC results for 'sysproperties'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
> DBCC results for 'sysdepends'. [SQLSTATE 01000]
> There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
> DBCC results for 'sysreferences'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
> DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'cinfo'. [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]
>
> "Paul S Randal [MS]" wrote:
|||I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
look like the Integrity output.
I guess my question is simply "Does the Integrity (with indexes) job from
the maintenance plan do exactly what DBCC checkdb does?"
"Tibor Karaszi" wrote:

> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
>
>
|||> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
All the maintenance wizard does is execute SQL commands. I ran a profiler trace to capture the
command executed by the maintenance wizard:
dbcc checkdb WITH NO_INFOMSGS
If you are curious, you can use profiler while the maint wizard is executing to capture the commands
that it executing against SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A051C9CE-671A-46FF-BD75-DAE4A408B081@.microsoft.com...[vbcol=seagreen]
> I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
> look like the Integrity output.
> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
> "Tibor Karaszi" wrote:
|||You can also find the SQL commands that the maintenance plan
runs for whatever options are selected in the following
article:
http://www.sql-server-performance.co...ance_plans.asp
-Sue
On Tue, 15 Feb 2005 06:55:06 -0800, Ron
<Ron@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
>look like the Integrity output.
>I guess my question is simply "Does the Integrity (with indexes) job from
>the maintenance plan do exactly what DBCC checkdb does?"
>"Tibor Karaszi" wrote:
|||And to answer your question, the integrity job uses DBCC CHECKDB so it's
doing exactly the same as a manual DBCC CHECKDB.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qd8411pgobfai7a405kjpisvroftt9a9ap@.4ax.com...
> You can also find the SQL commands that the maintenance plan
> runs for whatever options are selected in the following
> article:
>
http://www.sql-server-performance.co...ance_plans.asp[vbcol=seagreen]
> -Sue
> On Tue, 15 Feb 2005 06:55:06 -0800, Ron
> <Ron@.discussions.microsoft.com> wrote:
doesn't[vbcol=seagreen]
commend...[vbcol=seagreen]
2005-02-13[vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE 01000][vbcol=seagreen]
01000][vbcol=seagreen]
01000][vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
[SQLSTATE[vbcol=seagreen]
01000][vbcol=seagreen]
database[vbcol=seagreen]
your[vbcol=seagreen]
runs DBCC[vbcol=seagreen]
rights.[vbcol=seagreen]
produces[vbcol=seagreen]
both[vbcol=seagreen]
would[vbcol=seagreen]
no[vbcol=seagreen]
('database') and[vbcol=seagreen]
checkdb[vbcol=seagreen]
detail.
>

Checkdb vs Integrity with indexes

Is there a difference running checkdb -DBCC checkdb ('database') and running
the Integrity with Indexes option of a maintenance plan? The checkdb output
shows detail while the integrity output shows almost no detail.
Thanks
RonWhat about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
Do the outputs match then?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> Is there a difference running checkdb -DBCC checkdb ('database') and
running
> the Integrity with Indexes option of a maintenance plan? The checkdb
output
> shows detail while the integrity output shows almost no detail.
> Thanks
> Ron|||No different results.
Because the Integrity with Indexes via the maintenance plan produces
different output than a DBCC checkdb database, I wanted to know if both are
doing the same thing.
I'm thinking of replacing the integrity with a DBCC checkdb, but would like
to know what's the difference between the two besides the output.
"Paul S Randal [MS]" wrote:
> What about if you manually run DBCC CHECKDB ('database') WITH NO_INFOMSGS?
> Do the outputs match then?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > Is there a difference running checkdb -DBCC checkdb ('database') and
> running
> > the Integrity with Indexes option of a maintenance plan? The checkdb
> output
> > shows detail while the integrity output shows almost no detail.
> >
> > Thanks
> >
> > Ron
>
>|||Can you post the output? As far as I'm aware, the integrity check runs DBCC
CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> No different results.
> Because the Integrity with Indexes via the maintenance plan produces
> different output than a DBCC checkdb database, I wanted to know if both
are
> doing the same thing.
> I'm thinking of replacing the integrity with a DBCC checkdb, but would
like
> to know what's the difference between the two besides the output.
> "Paul S Randal [MS]" wrote:
> > What about if you manually run DBCC CHECKDB ('database') WITH
NO_INFOMSGS?
> > Do the outputs match then?
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > > Is there a difference running checkdb -DBCC checkdb ('database') and
> > running
> > > the Integrity with Indexes option of a maintenance plan? The checkdb
> > output
> > > shows detail while the integrity output shows almost no detail.
> > >
> > > Thanks
> > >
> > > Ron
> >
> >
> >|||Here the Integrity output:
1] Database ABCD: Check Data and Index Linkage...
(null)
(null)
** Execution Time: 0 hrs, 0 mins, 1 secs **
Here's the DBCC checkdb output :
Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
04:00:00
DBCC results for 'Cinfo'. [SQLSTATE 01000]
DBCC results for 'sysobjects'. [SQLSTATE 01000]
There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
DBCC results for 'sysindexes'. [SQLSTATE 01000]
There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
DBCC results for 'syscolumns'. [SQLSTATE 01000]
There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
DBCC results for 'systypes'. [SQLSTATE 01000]
There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
DBCC results for 'syscomments'. [SQLSTATE 01000]
There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
DBCC results for 'sysfiles1'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
DBCC results for 'syspermissions'. [SQLSTATE 01000]
There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
DBCC results for 'sysusers'. [SQLSTATE 01000]
There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
DBCC results for 'sysproperties'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
DBCC results for 'sysdepends'. [SQLSTATE 01000]
There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
DBCC results for 'sysreferences'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
[SQLSTATE 01000]
DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
01000]
DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
CHECKDB found 0 allocation errors and 0 consistency errors in database
'cinfo'. [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]
"Paul S Randal [MS]" wrote:
> Can you post the output? As far as I'm aware, the integrity check runs DBCC
> CHECKDB.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> > No different results.
> >
> > Because the Integrity with Indexes via the maintenance plan produces
> > different output than a DBCC checkdb database, I wanted to know if both
> are
> > doing the same thing.
> >
> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
> like
> > to know what's the difference between the two besides the output.
> >
> > "Paul S Randal [MS]" wrote:
> >
> > > What about if you manually run DBCC CHECKDB ('database') WITH
> NO_INFOMSGS?
> > > Do the outputs match then?
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
> > > running
> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
> > > output
> > > > shows detail while the integrity output shows almost no detail.
> > > >
> > > > Thanks
> > > >
> > > > Ron
> > >
> > >
> > >
>
>|||Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> Here the Integrity output:
> 1] Database ABCD: Check Data and Index Linkage...
>
> (null)
> (null)
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Here's the DBCC checkdb output :
> Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
> 04:00:00
> DBCC results for 'Cinfo'. [SQLSTATE 01000]
> DBCC results for 'sysobjects'. [SQLSTATE 01000]
> There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
> DBCC results for 'sysindexes'. [SQLSTATE 01000]
> There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
> DBCC results for 'syscolumns'. [SQLSTATE 01000]
> There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
> DBCC results for 'systypes'. [SQLSTATE 01000]
> There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> DBCC results for 'syscomments'. [SQLSTATE 01000]
> There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
> DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> DBCC results for 'syspermissions'. [SQLSTATE 01000]
> There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
> DBCC results for 'sysusers'. [SQLSTATE 01000]
> There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> DBCC results for 'sysproperties'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
> DBCC results for 'sysdepends'. [SQLSTATE 01000]
> There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
> DBCC results for 'sysreferences'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
> DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
> DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
> DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
> 01000]
> DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'cinfo'. [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]
>
> "Paul S Randal [MS]" wrote:
>> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> CHECKDB.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> > No different results.
>> >
>> > Because the Integrity with Indexes via the maintenance plan produces
>> > different output than a DBCC checkdb database, I wanted to know if both
>> are
>> > doing the same thing.
>> >
>> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> like
>> > to know what's the difference between the two besides the output.
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> NO_INFOMSGS?
>> > > Do the outputs match then?
>> > >
>> > > --
>> > > Paul Randal
>> > > Dev Lead, Microsoft SQL Server Storage Engine
>> > >
>> > > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> > >
>> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> > > running
>> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> > > output
>> > > > shows detail while the integrity output shows almost no detail.
>> > > >
>> > > > Thanks
>> > > >
>> > > > Ron
>> > >
>> > >
>> > >
>>|||I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
look like the Integrity output.
I guess my question is simply "Does the Integrity (with indexes) job from
the maintenance plan do exactly what DBCC checkdb does?"
"Tibor Karaszi" wrote:
> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> > Here the Integrity output:
> > 1] Database ABCD: Check Data and Index Linkage...
> >
> >
> >
> > (null)
> > (null)
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Here's the DBCC checkdb output :
> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
> > 04:00:00
> >
> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
> > DBCC results for 'systypes'. [SQLSTATE 01000]
> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> > [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
> > 01000]
> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
> > 01000]
> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> > CHECKDB found 0 allocation errors and 0 consistency errors in database
> > 'cinfo'. [SQLSTATE 01000]
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator. [SQLSTATE 01000]
> >
> >
> > "Paul S Randal [MS]" wrote:
> >
> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
> >> CHECKDB.
> >>
> >> --
> >> Paul Randal
> >> Dev Lead, Microsoft SQL Server Storage Engine
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> >> > No different results.
> >> >
> >> > Because the Integrity with Indexes via the maintenance plan produces
> >> > different output than a DBCC checkdb database, I wanted to know if both
> >> are
> >> > doing the same thing.
> >> >
> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
> >> like
> >> > to know what's the difference between the two besides the output.
> >> >
> >> > "Paul S Randal [MS]" wrote:
> >> >
> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> >> NO_INFOMSGS?
> >> > > Do the outputs match then?
> >> > >
> >> > > --
> >> > > Paul Randal
> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> >> > >
> >> > > This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> > >
> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
> >> > > running
> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
> >> > > output
> >> > > > shows detail while the integrity output shows almost no detail.
> >> > > >
> >> > > > Thanks
> >> > > >
> >> > > > Ron
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
All the maintenance wizard does is execute SQL commands. I ran a profiler trace to capture the
command executed by the maintenance wizard:
dbcc checkdb WITH NO_INFOMSGS
If you are curious, you can use profiler while the maint wizard is executing to capture the commands
that it executing against SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A051C9CE-671A-46FF-BD75-DAE4A408B081@.microsoft.com...
> I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
> look like the Integrity output.
> I guess my question is simply "Does the Integrity (with indexes) job from
> the maintenance plan do exactly what DBCC checkdb does?"
> "Tibor Karaszi" wrote:
>> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
>> > Here the Integrity output:
>> > 1] Database ABCD: Check Data and Index Linkage...
>> >
>> >
>> >
>> > (null)
>> > (null)
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Here's the DBCC checkdb output :
>> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
>> > 04:00:00
>> >
>> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
>> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
>> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
>> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
>> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
>> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
>> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
>> > DBCC results for 'systypes'. [SQLSTATE 01000]
>> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
>> > DBCC results for 'syscomments'. [SQLSTATE 01000]
>> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
>> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
>> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
>> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
>> > DBCC results for 'sysusers'. [SQLSTATE 01000]
>> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
>> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
>> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
>> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
>> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
>> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
>> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
>> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
>> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
>> > [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
>> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
>> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
>> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'cinfo'. [SQLSTATE 01000]
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator. [SQLSTATE 01000]
>> >
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> >> CHECKDB.
>> >>
>> >> --
>> >> Paul Randal
>> >> Dev Lead, Microsoft SQL Server Storage Engine
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no rights.
>> >>
>> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> >> > No different results.
>> >> >
>> >> > Because the Integrity with Indexes via the maintenance plan produces
>> >> > different output than a DBCC checkdb database, I wanted to know if both
>> >> are
>> >> > doing the same thing.
>> >> >
>> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> >> like
>> >> > to know what's the difference between the two besides the output.
>> >> >
>> >> > "Paul S Randal [MS]" wrote:
>> >> >
>> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> >> NO_INFOMSGS?
>> >> > > Do the outputs match then?
>> >> > >
>> >> > > --
>> >> > > Paul Randal
>> >> > > Dev Lead, Microsoft SQL Server Storage Engine
>> >> > >
>> >> > > This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> > >
>> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> >> > > running
>> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> >> > > output
>> >> > > > shows detail while the integrity output shows almost no detail.
>> >> > > >
>> >> > > > Thanks
>> >> > > >
>> >> > > > Ron
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>|||You can also find the SQL commands that the maintenance plan
runs for whatever options are selected in the following
article:
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
-Sue
On Tue, 15 Feb 2005 06:55:06 -0800, Ron
<Ron@.discussions.microsoft.com> wrote:
>I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it doesn't
>look like the Integrity output.
>I guess my question is simply "Does the Integrity (with indexes) job from
>the maintenance plan do exactly what DBCC checkdb does?"
>"Tibor Karaszi" wrote:
>> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC commend...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
>> > Here the Integrity output:
>> > 1] Database ABCD: Check Data and Index Linkage...
>> >
>> >
>> >
>> > (null)
>> > (null)
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Here's the DBCC checkdb output :
>> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing 2005-02-13
>> > 04:00:00
>> >
>> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
>> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
>> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE 01000]
>> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
>> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000]
>> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
>> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE 01000]
>> > DBCC results for 'systypes'. [SQLSTATE 01000]
>> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
>> > DBCC results for 'syscomments'. [SQLSTATE 01000]
>> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE 01000]
>> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
>> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
>> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE 01000]
>> > DBCC results for 'sysusers'. [SQLSTATE 01000]
>> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
>> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE 01000]
>> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
>> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE 01000]
>> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'. [SQLSTATE 01000]
>> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
>> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE 01000]
>> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
>> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
>> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
>> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
>> > [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
>> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
>> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
>> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE 01000]
>> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE
>> > 01000]
>> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
>> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
>> > CHECKDB found 0 allocation errors and 0 consistency errors in database
>> > 'cinfo'. [SQLSTATE 01000]
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator. [SQLSTATE 01000]
>> >
>> >
>> > "Paul S Randal [MS]" wrote:
>> >
>> >> Can you post the output? As far as I'm aware, the integrity check runs DBCC
>> >> CHECKDB.
>> >>
>> >> --
>> >> Paul Randal
>> >> Dev Lead, Microsoft SQL Server Storage Engine
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no rights.
>> >>
>> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
>> >> > No different results.
>> >> >
>> >> > Because the Integrity with Indexes via the maintenance plan produces
>> >> > different output than a DBCC checkdb database, I wanted to know if both
>> >> are
>> >> > doing the same thing.
>> >> >
>> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but would
>> >> like
>> >> > to know what's the difference between the two besides the output.
>> >> >
>> >> > "Paul S Randal [MS]" wrote:
>> >> >
>> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
>> >> NO_INFOMSGS?
>> >> > > Do the outputs match then?
>> >> > >
>> >> > > --
>> >> > > Paul Randal
>> >> > > Dev Lead, Microsoft SQL Server Storage Engine
>> >> > >
>> >> > > This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> > >
>> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
>> >> > > > Is there a difference running checkdb -DBCC checkdb ('database') and
>> >> > > running
>> >> > > > the Integrity with Indexes option of a maintenance plan? The checkdb
>> >> > > output
>> >> > > > shows detail while the integrity output shows almost no detail.
>> >> > > >
>> >> > > > Thanks
>> >> > > >
>> >> > > > Ron
>> >> > >
>> >> > >
>> >> > >
>> >>
>> >>
>> >>
>>|||And to answer your question, the integrity job uses DBCC CHECKDB so it's
doing exactly the same as a manual DBCC CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qd8411pgobfai7a405kjpisvroftt9a9ap@.4ax.com...
> You can also find the SQL commands that the maintenance plan
> runs for whatever options are selected in the following
> article:
>
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
> -Sue
> On Tue, 15 Feb 2005 06:55:06 -0800, Ron
> <Ron@.discussions.microsoft.com> wrote:
> >I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it
doesn't
> >look like the Integrity output.
> >
> >I guess my question is simply "Does the Integrity (with indexes) job from
> >the maintenance plan do exactly what DBCC checkdb does?"
> >
> >"Tibor Karaszi" wrote:
> >
> >> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC
commend...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> >> > Here the Integrity output:
> >> > 1] Database ABCD: Check Data and Index Linkage...
> >> >
> >> >
> >> >
> >> > (null)
> >> > (null)
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > Here's the DBCC checkdb output :
> >> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing
2005-02-13
> >> > 04:00:00
> >> >
> >> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> >> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> >> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE
01000]
> >> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> >> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE
01000]
> >> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> >> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE
01000]
> >> > DBCC results for 'systypes'. [SQLSTATE 01000]
> >> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> >> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> >> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> >> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> >> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE
01000]
> >> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> >> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> >> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> >> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE
01000]
> >> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> >> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE
01000]
> >> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
[SQLSTATE 01000]
> >> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE
01000]
> >> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> >> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> >> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> >> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> >> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> >> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> >> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> >> > [SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> >> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> >> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> >> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> >> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'.
[SQLSTATE 01000]
> >> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> >> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE
01000]
> >> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'.
[SQLSTATE
> >> > 01000]
> >> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> >> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE
01000]
> >> > CHECKDB found 0 allocation errors and 0 consistency errors in
database
> >> > 'cinfo'. [SQLSTATE 01000]
> >> > DBCC execution completed. If DBCC printed error messages, contact
your
> >> > system administrator. [SQLSTATE 01000]
> >> >
> >> >
> >> > "Paul S Randal [MS]" wrote:
> >> >
> >> >> Can you post the output? As far as I'm aware, the integrity check
runs DBCC
> >> >> CHECKDB.
> >> >>
> >> >> --
> >> >> Paul Randal
> >> >> Dev Lead, Microsoft SQL Server Storage Engine
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
rights.
> >> >>
> >> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> >> >> > No different results.
> >> >> >
> >> >> > Because the Integrity with Indexes via the maintenance plan
produces
> >> >> > different output than a DBCC checkdb database, I wanted to know if
both
> >> >> are
> >> >> > doing the same thing.
> >> >> >
> >> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but
would
> >> >> like
> >> >> > to know what's the difference between the two besides the output.
> >> >> >
> >> >> > "Paul S Randal [MS]" wrote:
> >> >> >
> >> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> >> >> NO_INFOMSGS?
> >> >> > > Do the outputs match then?
> >> >> > >
> >> >> > > --
> >> >> > > Paul Randal
> >> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> >> >> > >
> >> >> > > This posting is provided "AS IS" with no warranties, and confers
no
> >> >> rights.
> >> >> > >
> >> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> >> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> >> >> > > > Is there a difference running checkdb -DBCC checkdb
('database') and
> >> >> > > running
> >> >> > > > the Integrity with Indexes option of a maintenance plan? The
checkdb
> >> >> > > output
> >> >> > > > shows detail while the integrity output shows almost no
detail.
> >> >> > > >
> >> >> > > > Thanks
> >> >> > > >
> >> >> > > > Ron
> >> >> > >
> >> >> > >
> >> >> > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Thanks Everyone!
"Paul S Randal [MS]" wrote:
> And to answer your question, the integrity job uses DBCC CHECKDB so it's
> doing exactly the same as a manual DBCC CHECKDB.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:qd8411pgobfai7a405kjpisvroftt9a9ap@.4ax.com...
> > You can also find the SQL commands that the maintenance plan
> > runs for whatever options are selected in the following
> > article:
> >
> http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp
> >
> > -Sue
> >
> > On Tue, 15 Feb 2005 06:55:06 -0800, Ron
> > <Ron@.discussions.microsoft.com> wrote:
> >
> > >I'm not sure I follow. When dbcc checkdb runs with NO_INFOMSGS it
> doesn't
> > >look like the Integrity output.
> > >
> > >I guess my question is simply "Does the Integrity (with indexes) job from
> > >the maintenance plan do exactly what DBCC checkdb does?"
> > >
> > >"Tibor Karaszi" wrote:
> > >
> > >> Seems like maint wiz doesn't add WITH NO_INFOMSGS to the DBCC
> commend...
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> news:57E3528C-7953-47CE-BB5B-19C35199C094@.microsoft.com...
> > >> > Here the Integrity output:
> > >> > 1] Database ABCD: Check Data and Index Linkage...
> > >> >
> > >> >
> > >> >
> > >> > (null)
> > >> > (null)
> > >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > >> >
> > >> > Here's the DBCC checkdb output :
> > >> > Job 'DBCC checkdb' : Step 1, 'CheckDB Cinfo' : Began Executing
> 2005-02-13
> > >> > 04:00:00
> > >> >
> > >> > DBCC results for 'Cinfo'. [SQLSTATE 01000]
> > >> > DBCC results for 'sysobjects'. [SQLSTATE 01000]
> > >> > There are 71 rows in 1 pages for object 'sysobjects'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysindexes'. [SQLSTATE 01000]
> > >> > There are 125 rows in 5 pages for object 'sysindexes'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'syscolumns'. [SQLSTATE 01000]
> > >> > There are 514 rows in 9 pages for object 'syscolumns'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'systypes'. [SQLSTATE 01000]
> > >> > There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000]
> > >> > DBCC results for 'syscomments'. [SQLSTATE 01000]
> > >> > There are 113 rows in 10 pages for object 'syscomments'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000]
> > >> > DBCC results for 'syspermissions'. [SQLSTATE 01000]
> > >> > There are 37 rows in 1 pages for object 'syspermissions'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysusers'. [SQLSTATE 01000]
> > >> > There are 17 rows in 1 pages for object 'sysusers'. [SQLSTATE 01000]
> > >> > DBCC results for 'sysproperties'. [SQLSTATE 01000]
> > >> > There are 0 rows in 0 pages for object 'sysproperties'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysdepends'. [SQLSTATE 01000]
> > >> > There are 422 rows in 2 pages for object 'sysdepends'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysreferences'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'sysreferences'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> > >> > There are 0 rows in 0 pages for object 'sysfulltextnotify'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'sysfilegroups'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_APS'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'cinfo.CI_APS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_AUDITTRACE'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_AUDITTRACE'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_CLSMACHINES'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_CLSMACHINES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_EVENTS'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_EVENTS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GOVINFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_GOVINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GRPINFO'. [SQLSTATE 01000]
> > >> > There are 7 rows in 2 pages for object 'cinfo.CI_GRPINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_GRPUSERS'. [SQLSTATE 01000]
> > >> > There are 106 rows in 2 pages for object 'cinfo.CI_GRPUSERS'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_HISTORY'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_HISTORY'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_INFOOBJECTS'. [SQLSTATE 01000]
> > >> > There are 589 rows in 22 pages for object 'cinfo.CI_INFOOBJECTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_ISPLUGINS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_ISPLUGINS'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_MACHAVAIL'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHAVAIL'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINECLASS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_MACHINECLASS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINEGROUP'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINEGROUP'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_MACHINV'. [SQLSTATE 01000]
> > >> > There are 2 rows in 1 pages for object 'cinfo.CI_MACHINV'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_NEXTID'. [SQLSTATE 01000]
> > >> > There are 1 rows in 1 pages for object 'cinfo.CI_NEXTID'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_RESTRICTIONS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_RESTRICTIONS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_RIGHTROLES'. [SQLSTATE 01000]
> > >> > There are 145 rows in 1 pages for object 'cinfo.CI_RIGHTROLES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_RUNTIMEIMAGE'. [SQLSTATE 01000]
> > >> > There are 2080 rows in 101 pages for object 'cinfo.CI_RUNTIMEIMAGE'.
> > >> > [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_SCHEDULES'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SCHEDULES'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_SECRIGHTS'. [SQLSTATE 01000]
> > >> > There are 1397 rows in 12 pages for object 'cinfo.CI_SECRIGHTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_SPECIALDATES'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDATES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_SPECIALDAYS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_SPECIALDAYS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATEDATES'. [SQLSTATE 01000]
> > >> > There are 458 rows in 4 pages for object 'cinfo.CI_TEMPLATEDATES'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATEDAYS'. [SQLSTATE 01000]
> > >> > There are 143 rows in 1 pages for object 'cinfo.CI_TEMPLATEDAYS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_TEMPLATES'. [SQLSTATE 01000]
> > >> > There are 9 rows in 1 pages for object 'cinfo.CI_TEMPLATES'.
> [SQLSTATE 01000]
> > >> > DBCC results for 'cinfo.CI_TIMEINFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_TIMEINFO'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_USERS'. [SQLSTATE 01000]
> > >> > There are 51 rows in 5 pages for object 'cinfo.CI_USERS'. [SQLSTATE
> 01000]
> > >> > DBCC results for 'cinfo.CI_CHANNEL_INFO'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_CHANNEL_INFO'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'cinfo.CI_ARCHIVEDOBJECTS'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'cinfo.CI_ARCHIVEDOBJECTS'.
> [SQLSTATE
> > >> > 01000]
> > >> > DBCC results for 'dtproperties'. [SQLSTATE 01000]
> > >> > There are 0 rows in 1 pages for object 'dtproperties'. [SQLSTATE
> 01000]
> > >> > CHECKDB found 0 allocation errors and 0 consistency errors in
> database
> > >> > 'cinfo'. [SQLSTATE 01000]
> > >> > DBCC execution completed. If DBCC printed error messages, contact
> your
> > >> > system administrator. [SQLSTATE 01000]
> > >> >
> > >> >
> > >> > "Paul S Randal [MS]" wrote:
> > >> >
> > >> >> Can you post the output? As far as I'm aware, the integrity check
> runs DBCC
> > >> >> CHECKDB.
> > >> >>
> > >> >> --
> > >> >> Paul Randal
> > >> >> Dev Lead, Microsoft SQL Server Storage Engine
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >> >>
> > >> >> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> >> news:03075079-DA30-47EE-9550-E792A8E4EB1D@.microsoft.com...
> > >> >> > No different results.
> > >> >> >
> > >> >> > Because the Integrity with Indexes via the maintenance plan
> produces
> > >> >> > different output than a DBCC checkdb database, I wanted to know if
> both
> > >> >> are
> > >> >> > doing the same thing.
> > >> >> >
> > >> >> > I'm thinking of replacing the integrity with a DBCC checkdb, but
> would
> > >> >> like
> > >> >> > to know what's the difference between the two besides the output.
> > >> >> >
> > >> >> > "Paul S Randal [MS]" wrote:
> > >> >> >
> > >> >> > > What about if you manually run DBCC CHECKDB ('database') WITH
> > >> >> NO_INFOMSGS?
> > >> >> > > Do the outputs match then?
> > >> >> > >
> > >> >> > > --
> > >> >> > > Paul Randal
> > >> >> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >> >> > >
> > >> >> > > This posting is provided "AS IS" with no warranties, and confers
> no
> > >> >> rights.
> > >> >> > >
> > >> >> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > >> >> > > news:9E360D8A-D225-45C8-A5F7-D8671E46F417@.microsoft.com...
> > >> >> > > > Is there a difference running checkdb -DBCC checkdb
> ('database') and
> > >> >> > > running
> > >> >> > > > the Integrity with Indexes option of a maintenance plan? The
> checkdb
> > >> >> > > output
> > >> >> > > > shows detail while the integrity output shows almost no
> detail.
> > >> >> > > >
> > >> >> > > > Thanks
> > >> >> > > >
> > >> >> > > > Ron
> > >> >> > >
> > >> >> > >
> > >> >> > >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
>
>