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.

Choosing clustered index

Dear friends:
There is some confusion here about the choice of which index should be
clustered. The choices are generally:
- the surrogate identity column.
- one or more columns that make up the natural key.
My contention has been that the latter is the obvious choice. This is the
order in which the rows are commonly placed on the screen and on reports.
The identity column really places the rows in no particular order at all,
except somewhat "sequentially" with respect to the order they were entered
(assuming an incrementing integer key).
What is the conventional wisdom about this?
Tom EllisonUsually clustered indexes should:
- Be as narrow as possible.
- Be placed on columns that would benefit the most:
-- Columns that have very high cardinality are good candidates.
-- So are columns that are searched for large ranges at a time using
operators like BETWEEN, LIKE 'x%' and >, <, etc.
-- Columns that are searched the most frequently, since they eliminate
bookmark lookups which can occur with narrow nonclustered indexes.
A lot of times the Primary Key will fit a lot of these requirements, but not
always. I usually would only make an IDENTITY column the clustered index on
supporting (lookup-type) tables, to improve JOIN performance. One or more
columns (with high cardinality) that make up the natural key on your main
tables would be good clustered index candidates.
[url]http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/05/08/599.aspx[
/url]
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
> Dear friends:
> There is some confusion here about the choice of which index should be
> clustered. The choices are generally:
> - the surrogate identity column.
> - one or more columns that make up the natural key.
> My contention has been that the latter is the obvious choice. This is the
> order in which the rows are commonly placed on the screen and on reports.
> The identity column really places the rows in no particular order at all,
> except somewhat "sequentially" with respect to the order they were entered
> (assuming an incrementing integer key).
> What is the conventional wisdom about this?
> Tom Ellison
>|||Dear Mike:
Thanks for the opinions. The article was quite helpful.
Now, how do you figure that an IDENTITY column makes a good clustered index?
I ask because:
- in my experience, processing rarely proceeds in identity number order.
- user directed searches don't proceed along these lines
- moving through the data in some sequential order, such as generating data
for the screen or a report will follow a natural key order, not an identity
order
My thought is that the identity column is assigned sequentially over time
(if auto-incremented) but does not generally follow any organization of the
data that is likely to be repeated.
The natural key of which I spoke is unique (thus, highly cardinal) and tends
to be the most commonly used sequence in processing (screens and reports).
It is often the column(s) filtered or JOINed in many of the queries used.
Thanks again,
Tom Ellison
"Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
> Usually clustered indexes should:
> - Be as narrow as possible.
> - Be placed on columns that would benefit the most:
> -- Columns that have very high cardinality are good candidates.
> -- So are columns that are searched for large ranges at a time using
> operators like BETWEEN, LIKE 'x%' and >, <, etc.
> -- Columns that are searched the most frequently, since they eliminate
> bookmark lookups which can occur with narrow nonclustered indexes.
> A lot of times the Primary Key will fit a lot of these requirements, but
> not always. I usually would only make an IDENTITY column the clustered
> index on supporting (lookup-type) tables, to improve JOIN performance.
> One or more columns (with high cardinality) that make up the natural key
> on your main tables would be good clustered index candidates.
> http://blogs.sqlservercentral.com/b...99.asp
x
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:O0nC9gVdGHA.5016@.TK2MSFTNGP04.phx.gbl...
>|||Tom,
are you using non-clustered indexes? If yes, take in account that
bookmark lookups take more time if bookmarks are wider.|||I generally use IDENTITY columns as clustered indexes only when it's in
supporting (lookup/foreign-key) tables. The only reason then is to improve
JOIN performance by potentially eliminating bookmark lookups you might get
with a narrow nonclustered index. Other than that, put your clustered
indexes where they'll do the most good.
"Tom Ellison" wrote:

> Dear Mike:
> Thanks for the opinions. The article was quite helpful.
> Now, how do you figure that an IDENTITY column makes a good clustered inde
x?
> I ask because:
> - in my experience, processing rarely proceeds in identity number order.
> - user directed searches don't proceed along these lines
> - moving through the data in some sequential order, such as generating dat
a
> for the screen or a report will follow a natural key order, not an identit
y
> order
> My thought is that the identity column is assigned sequentially over time
> (if auto-incremented) but does not generally follow any organization of th
e
> data that is likely to be repeated.
> The natural key of which I spoke is unique (thus, highly cardinal) and ten
ds
> to be the most commonly used sequence in processing (screens and reports).
> It is often the column(s) filtered or JOINed in many of the queries used.
> Thanks again,
> Tom Ellison
>
> "Mike C#" <xxx@.yyy.com> wrote in message news:pBQ8g.184$Ut2.60@.fe09.lga...
>
>

choice of column for clustered index

I often founds 2 recommendations that sounds contradicting to me,
regarding what types of columns should be chosen for a clustered index
(in OLTP environment with lots of inserts):
1)avoid identity column, it will cause inserts to be slowed since they
will compete the same disk area at the end of the table (hot spot).
Instead, use a column whose new value can be at any part of the table
2)use sequential column (like identity one), so that new inserts only
happen at the end of the table and don't cause row migrations (when a
row inserted forces the next rows to move to a new page)
Can anyone give me a more sounding judgement of each of these 2
choices? I'm curious to know in which scenario, which choice is better
than the other, and what the cures are. Correct me if I'm wrong, I feel
that "row migration" is more fearful than "hot spot"
thanks,
TamUnless you're writing a million new rows a day, I doubt the hot spot concern
is valid on today's hardware. Obviously there will be a threshold but for
most applications I can envision this should be a minimal concern.
I have witnessed cases where fragmentation and page splitting, on the other
hand, has caused abysmal performance.
I can't really think of a situation where you'd rather jab new data in the
middle of a page than tack it on the end, unless you were just stuffing
every single transaction on a very busy system into an audit table that you
purge regularly and that you're rarely going to query, in which case, who
cares which way you go.
A
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125521936.674430.273880@.g49g2000cwa.googlegroups.com...
>I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Tam Vu" wrote:

> I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||> Unless you're writing a million new rows a day,
Wow, did I really say "day" there? Eep. In my experience, a hot spot comes
at a far greater volume than that. Then again, I have been spoiled with
must faster hardware than I had access to in college. :-)

checksum

It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:

> Elmer Miller wrote:
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...[vbcol=seagreen]
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:

Checking the integrity of the FT catalog/index

What would be an efficient way to check for the integrity of a FT
catalog or index in SQL 2000? Beside monitoring the event logs, I'm
planning to write a script to parse out a string of text from a text
column of a random row from a table has FT indexes, then go back and do
a FT search on that string/words to make sure the catalog is ok.
SQL 2005 has cidump, does SQL 2000 has any equivalent utility?
Thanks,
Hai
Hai,
The MSSearch service does its own internal integrity checking by design, so
little to no addition checking is normally required. However, I would
recommend that you monitor the free space, memory and cpu usage via the
"Microsoft Search" Performance counters. The following blog entry has links
to the more common SQL FTS issues.
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
323739 "INF: SQL Server 2000 Full-Text Search Deployment White Paper" will
have more information about the MSSearch perfmon counters.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<tran.hai@.gmail.com> wrote in message
news:1130170118.779530.104390@.z14g2000cwz.googlegr oups.com...
> What would be an efficient way to check for the integrity of a FT
> catalog or index in SQL 2000? Beside monitoring the event logs, I'm
> planning to write a script to parse out a string of text from a text
> column of a random row from a table has FT indexes, then go back and do
> a FT search on that string/words to make sure the catalog is ok.
> SQL 2005 has cidump, does SQL 2000 has any equivalent utility?
> Thanks,
> Hai
>
|||Thanks John!

checking if a table has index on it in ms sql server7

I am using MS sql 7 as database with ASP as front end. in the front end i want to check if a particular table has an index on it. if yes i want to drop index, truncate the table, insert new values into the table and recreate index. this i am doing to make inserting values faster.
in case the front end is being run for first time ever.index would not be present. so if i do drop index in front end script i would get error.
so in this case drop index should not be done and directly table shd be truncated(step 2)
so how can i check if this particular table has an index already created.. any sql query that can send a reply of presence or absence of index in sql server7? any thing related to sysindexes?indid on sysindexes could be used to get this info -

if 0 then no clustered index, but nonclustered indexes may exist
if 255 then clustered index
if >0 and <255 then it is a nonclustered index

So, logic would be something like -
if table has an indid = 0 and no other rows exist in sysindexes for that table then it has no indexes.|||sorry clustered index indid = 1 (not 255 as I said prev)|||From The BOL (The Holy Book) -
Sysindexes table

id -

ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.

indid -

ID of index:
1 = Clustered index
>1 = Nonclustered
255 = Entry for tables that have text or image data

So you can query the sysindexes table for the desired results

or you can simply use

sp_helpindex your_table_name

Checking for row existence with secondary key

Hi -
I'm no SQL wizard (obviously). I have a table (conceivably very
large (500k+rows)) with a non-unique secondary index. I need an
efficient query to check for row existence using that secondary index.
Any ideas will be appreciated...
Thanks,
BryanIF EXISTS (SELECT * FROM yourTable AS a WHERE a.Col = YourCondition)
-- do your stuff here
Andrew J. Kelly SQL MVP
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan|||Bryan,
DDL would help but try:
SELECT ID
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID)
HTH
Jerry
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan

checking data and index linkages on msdb and master

Hi, I am running a maintenance plan daily, and under default configurations,
it's failing to Check data and index linkages on the "master" and "msdb"
database because it's not in single user mode. Am I not able to do this?
If so, do I need to change it to single user? Will that harm anything else?
_____
DC GIt's failing because you've set the job up to automatically repair minor
errors. You should not do this - always investigate integrity problems
before taking any corrective action (i.e. restoring from your backups)
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"DC Gringo" <glevine@.visiontechnology.net> wrote in message
news:ueA0bX0lDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Hi, I am running a maintenance plan daily, and under default
configurations,
> it's failing to Check data and index linkages on the "master" and "msdb"
> database because it's not in single user mode. Am I not able to do this?
> If so, do I need to change it to single user? Will that harm anything
else?
> _____
> DC G
>

CHECKDB on master ?

If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>Can I get data loss from running CHECKDB on master (or user databases)
if the consistency errors are in indexes?
Here are the results of CHECKDB on master:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 12, index ID 0: Page (1:364) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 2
Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1272 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4884 rows in 85 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2024 rows in 952 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 788 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5282 rows in 28 pages for object 'sysdepends'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sysdepends' (object ID 12).
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 309 rows in 13 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 465 rows in 14 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3831 rows in 165 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 7 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 618 rows in 123 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 2 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over
Paul S Randal [MS] wrote:[vbcol=seagreen]
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have
a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserversto...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...|||Before you undertake the effort of restoring your currently damaged master d
b
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:

> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over
>
> Paul S Randal [MS] wrote:
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over
Paul S Randal [MS] wrote:[vbcol=seagreen]
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have
a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserversto...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...|||Before you undertake the effort of restoring your currently damaged master d
b
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:

> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over
>
> Paul S Randal [MS] wrote:
>|||Paul
Yes I can select * from sysdepends
Are you saying that I can repair the master database or I cannot?
It looks like our backups are corrupt too. I am not sure when the
original errors were introduced. It looks like I might have to use a
procedure similar to what Sassan described above.
Paul S Randal [MS] wrote:[vbcol=seagreen]
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 on
es
> are done but haven't made it out to the wild yet)
> Can you select * from sysdepends in master? You may be lucky and there's
> nothing there, in which case (once you've made sure you know exactly why t
he
> problem occured and have taken steps to make sure it doesn't happen again)
,
> you should be able to get away with running repair rather than going back
to
> your old backups. And, of course, get a much better backup strategy.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...