INFORMATION_SCHEMA permissions

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
An sa ID for the master sees everything however.
Thanks for your help
PachydermitisI believe your user needs to be in the ddl_admin role?
"Pachydermitis" <dedejavu@.hotmail.com> wrote in message
news:4f954dcf.0309190903.6fbdfc77@.posting.google.com...
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis

information_schema permissions

Hi I need to see all the indexes in a database. The ID has dbo rights
to the database, but not to the master. I can't see anything in
INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
An sa ID for the master sees everything however.
Thanks for your help
Pachydermitis[posted and mailed, please reply in news]

Pachydermitis (dedejavu@.hotmail.com) writes:
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.

Don't really see where INFORMATION_SCHEMA comes in. There is no
information about indexes there. Generally, I prefer using the
system tables, because they hold the complete set of information.

To see all indexes in a database (save those on system tables):

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||dedejavu@.hotmail.com (Pachydermitis) wrote in message news:<4f954dcf.0309190902.1d421bf4@.posting.google.com>...
> Hi I need to see all the indexes in a database. The ID has dbo rights
> to the database, but not to the master. I can't see anything in
> INFORMATION_SCHEMA.CHECK_CONSTRAINTS or
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> An sa ID for the master sees everything however.
> Thanks for your help
> Pachydermitis

Hi,

This would give you the info what you are seeking..

select name, object_name(id) from sysindexes

-Manoj|||Manoj Rajshekar (manrajshekar@.yahoo.com) writes:
> This would give you the info what you are seeking..
> select name, object_name(id) from sysindexes

And a lot more he is not interested in. He will also get the names of
heap tables (tables without clustered indexes), hypothetcial indexes,
statistics and location of text data. This SELECT filters this kind
of information:

SELECT "table" = object_name(id), name
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254
AND indexproperty(id, name, 'IsHypothetical') = 0
AND indexproperty(id, name, 'IsStatistics') = 0
AND indexproperty(id, name, 'IsAutoStatistics') = 0
AND objectproperty(id, 'IsMsShipped') = 0
ORDER BY "table", name

and also filters indexes on system tables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
I have been able to get the TableName and IndexNames (along with a few
I don't want _WA_. . .) but I can't seem to get the column names or
get rid of the _WA_ ones.

I was trying to get TableName, IndexName, ColumnName

SELECT obj.[name],ind.[name] FROM sysindexes ind
inner join sysobjects obj on ind.[id]=obj.[id]
ORDER BY obj.[name],ind.indid

Thanks again
Pachydermitis|||Pachydermitis (dedejavu@.hotmail.com) writes:
> I have been able to get the TableName and IndexNames (along with a few
> I don't want _WA_. . .) but I can't seem to get the column names or
> get rid of the _WA_ ones.

Had you used the query I suggested, you would have been relieved from the
_WA "indexes". (Which are statistics and hypothetical indexes.)

> I was trying to get TableName, IndexName, ColumnName

Here is a query that gives this. For multi-column indexes you get one
row per index. If you want all columns for an index on one line, you
will have run some iteration.

SELECT "table" = object_name(i.id), i.name,
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
"column" = col_name(i.id, ik.colid), ik.keyno
FROM sysindexes i
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(i.id, name, 'IsHypothetical') = 0
AND indexproperty(i.id, name, 'IsStatistics') = 0
AND indexproperty(i.id, name, 'IsAutoStatistics') = 0
AND objectproperty(i.id, 'IsMsShipped') = 0
ORDER BY "table", "isclustered" DESC, i.name, ik.keyno

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

indices and replication

Will leaving these indexes there cause any
>problems?
No.
I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
Correct

>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
Dont know.
2 outta 3 aint bad. ;-)

>--Original Message--
>first: forgive me, is it indices or indexes?
>I had previously asked about adding indexes to tables
that are a part of
>merge replication (sql 2000 servers). Paul Ibison
reccomended checking out
>sp_addscriptexec. I honestly have not done that yet as I
was just doing a
>little initial digging on the subject at that point. I
have a couple other
>questions surrounding this issue:
>1) I had someone add some indexes to replicated tables on
the subcriber end
>of a merge replication scheme by using the usual
Enterprise Manager GUI
>method. They did not replicate to the publisher. I assume
that is normal. I
>actually did not think EM would have let the change be
done since it was
>done on a replication article. Will leaving these indexes
there cause any
>problems? I don't actually need them on the publisher end
right now anyway.
>2) I see that by default indexes are included when a new
snapshot is applied
>to a subscriber. So, if re-applying a new snapshot is a
doable solution, I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
>any info is greatly appreciated. Thanks!
>
>.
>
2 out of 3 ain't bad at all... Thanks! ;)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6801c4a19e$1ef9d250$a401280a@.phx.gbl...[vbcol=seagreen]
> Will leaving these indexes there cause any
> No.
> I
> method and just
> subscribers... Correct?
> Correct
>
> a published article
> subscribers. Just like
>
> Dont know.
> 2 outta 3 aint bad. ;-)
>
> that are a part of
> reccomended checking out
> was just doing a
> have a couple other
> the subcriber end
> Enterprise Manager GUI
> that is normal. I
> done since it was
> there cause any
> right now anyway.
> snapshot is applied
> doable solution, I
> method and just
> subscribers... Correct?
> a published article
> subscribers. Just like
|||I agree with Chris and (3) yes you can use sp_addscriptexec to send over the
create index statements - or use EM, or scripts etc.
The reason sp_addscriptexec is particularly useful is if you have a lot of
subscribers or if your subscribers are pull subscriptions - you can be sure
everyone gets the change. Be sure to try the script on the publisher first.
A recent poster found that his distribution agent failed due to an incorrect
script. If this happens, then you'll need to edit the script in the repldata
share.
BTW for (2) This is usually a lot less work than recreating a snapshot and
synchronizing.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uYFfuGaoEHA.2864@.TK2MSFTNGP12.phx.gbl...
> I agree with Chris and (3) yes you can use sp_addscriptexec to send over
the
> create index statements - or use EM, or scripts etc.
> The reason sp_addscriptexec is particularly useful is if you have a lot of
> subscribers or if your subscribers are pull subscriptions - you can be
sure
> everyone gets the change. Be sure to try the script on the publisher
first.
> A recent poster found that his distribution agent failed due to an
incorrect
> script. If this happens, then you'll need to edit the script in the
repldata
> share.
> BTW for (2) This is usually a lot less work than recreating a snapshot and
> synchronizing.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Indext extent fragmentation

Hi,
On the databases I manage I notice high (50%+) extent
fragmentation when I run DBCC SHOWCONTIG against the
tables' indexes. I've run dbcc indexdefrag, dbcc
dbreindex, and create index ...with drop existing on these
indexes and they still show the same fragmentation even
after an UPDATE STATISTICS.
Is there any way to eliminate this beyond completely
dropping the indexes and starting from scratch (not a
realistic option)? The procedure I run has virtually
eliminated the logical fragmentation- are there major
performance issues with leaving them as-is?
-DanHi,
If your select statement returns all the records of a table, such table
fragmentation can cause additional page reads which will reduce the
performance of your query and utilize more resource (Memory/CPU / Disk
reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
table.
One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
recreate index, all the fragmented pages will be cleared and you will have
enough
free space in your database.
Thanks
Hari
US Technology
Drop and re-create a clustered index. "Dan Wunder"
<anonymous@.discussions.microsoft.com> wrote in message
news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> Hi,
> On the databases I manage I notice high (50%+) extent
> fragmentation when I run DBCC SHOWCONTIG against the
> tables' indexes. I've run dbcc indexdefrag, dbcc
> dbreindex, and create index ...with drop existing on these
> indexes and they still show the same fragmentation even
> after an UPDATE STATISTICS.
> Is there any way to eliminate this beyond completely
> dropping the indexes and starting from scratch (not a
> realistic option)? The procedure I run has virtually
> eliminated the logical fragmentation- are there major
> performance issues with leaving them as-is?
> -Dan|||Please read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
This explains everything you need to know.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OAqkB7dmDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If your select statement returns all the records of a table, such table
> fragmentation can cause additional page reads which will reduce the
> performance of your query and utilize more resource (Memory/CPU / Disk
> reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
> table.
> One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
> recreate index, all the fragmented pages will be cleared and you will
have
> enough
> free space in your database.
> Thanks
> Hari
> US Technology
>
> Drop and re-create a clustered index. "Dan Wunder"
> <anonymous@.discussions.microsoft.com> wrote in message
> news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> > Hi,
> >
> > On the databases I manage I notice high (50%+) extent
> > fragmentation when I run DBCC SHOWCONTIG against the
> > tables' indexes. I've run dbcc indexdefrag, dbcc
> > dbreindex, and create index ...with drop existing on these
> > indexes and they still show the same fragmentation even
> > after an UPDATE STATISTICS.
> > Is there any way to eliminate this beyond completely
> > dropping the indexes and starting from scratch (not a
> > realistic option)? The procedure I run has virtually
> > eliminated the logical fragmentation- are there major
> > performance issues with leaving them as-is?
> >
> > -Dan
>

indexing/backup issue with large table - transactional replication

Hello,
I have a table on a database that contains over 6 million rows. There
is one clusted and two non-clustered indexes on the table.
Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
Machine varchar (60) NULL,
LogEntry varchar (1000)NULL,
Active varchar (50) NULL,
SysInfo varchar (255),
Idle varchar (50) NULL,
IP varchar (15) NULL,
KioskDate datetime NULL,
KioskTime varchar (22) NULL,
ServerDate datetime NULL,
ServerTime datetime NULL,
Application varchar (15)NULL,
WebDomain varchar (50)NULL ,
NSCode varchar (10) NULL
pk_1 Clustered on Counter
pk_2 NC on NSCode
pk_3 Machine, KioskDate, KioskTime, NSCode
These indexes work well.
A trans log backup runs nightly apart from sunday morning when a full
backup runs. The table is also part of a transactional replication
subscription (along with two other tables). The problem occurs when
replication fails as the database is being fully backed up at the
weekend. The replication also fails when I try to run DBCC REINDEX on
the table. I have tried to remove the table from the subscription
then run the DBCC command, however this also doesn't work. I have
used DBCC INDEXDEFRAG (successfully) however would my indexes still be
effective as using DBCCREINDEX?
The solution is to ensure the table is backed up, the indexes do not
become ineffective and replication continues to work.
All ideas greatly appreciated!
Thanks
ScottAs far as the INDEXDEFRAG, it should *eventually* be as
effective as a full reindex. Because it essentially works
with smaller pieces of the index, it takes much longer to
run.
What I would highly recommend is using filegroups and
splitting off the non-clustered indexes onto a separate
disk array. That should speed up the performance of a
full reindex. I'd also reindex each index as a separate
step, rather than all of the indexes in one job.
I'd also recommend splitting the full backup into backing
up individual files or filegroups more frequently rather
than a single full backup.
Implementing something like SQL Litespeed can drastically
increase the speed at which a backup finishes.
It sounds like you just don't have a large enough
maintenance window (time). In the end, you have two
options -- decrease the maintenance or increase the time
window. Consider the latter as a possibility.
Hope that helps.
>--Original Message--
>Hello,
>
>I have a table on a database that contains over 6 million
rows. There
>is one clusted and two non-clustered indexes on the table.
>Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
>Machine varchar (60) NULL,
>LogEntry varchar (1000)NULL,
>Active varchar (50) NULL,
>SysInfo varchar (255),
>Idle varchar (50) NULL,
>IP varchar (15) NULL,
>KioskDate datetime NULL,
>KioskTime varchar (22) NULL,
>ServerDate datetime NULL,
>ServerTime datetime NULL,
>Application varchar (15)NULL,
>WebDomain varchar (50)NULL ,
>NSCode varchar (10) NULL
>
>pk_1 Clustered on Counter
>pk_2 NC on NSCode
>pk_3 Machine, KioskDate, KioskTime, NSCode
>These indexes work well.
>A trans log backup runs nightly apart from sunday morning
when a full
>backup runs. The table is also part of a transactional
replication
>subscription (along with two other tables). The problem
occurs when
>replication fails as the database is being fully backed
up at the
>weekend. The replication also fails when I try to run
DBCC REINDEX on
>the table. I have tried to remove the table from the
subscription
>then run the DBCC command, however this also doesn't
work. I have
>used DBCC INDEXDEFRAG (successfully) however would my
indexes still be
>effective as using DBCCREINDEX?
>The solution is to ensure the table is backed up, the
indexes do not
>become ineffective and replication continues to work.
>All ideas greatly appreciated!
>
>Thanks
>
>Scott
>.
>

Indexing questions

Conceptual questions here...In BOL it mentions 'Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.'. I'm wondering how this works as non-clustered indexes are built on top of clustered ones - is there in this case a significant slowdown in the speed of accessing non-clustered indexes? Also, I'm guessing here, but is the reason this might be useful is for when you want to disable and rebuild the nonclustered indexes in order to save space?
Cheers,
Paul Ibison, SQL Server MVP

Conceptually speaking, the leaf row of a non-clustered index needs to identify the data row uniquely. There are two ways to do it. First,you can use RID. The down side is that if the data row moves, the non-clustered index entry needs to be updated. Second, you can use clustered index key, if it is unique. The benefit is that if the data row moves say due to rebuild of a clustered index, nothing needs to be changed in the non-clustered index. Of course, in this case, to access data using non-clustered idnex, you will need to traverse the non-clustered index and then need to traverse the clustered index tree. Assuming that clustered index tree will be frequently accessed, it is lkely to be in the buffer pool. So you will incur few extra logical IOs per row access.

I doubt if the second method can be used to save space as it is likely that the clustered index key is longer than RID. The main benefit of the second method is that data row move does not cause the update of non-clustered index as long as the clustered key column value is not changed.

Let us see what others say.

Thanks

|||

Index rebuild (except for disabled index) does not change the logical structure of an index
(like changing a key, adding a new column, or change the sort order), it only changes the physical layout of an index on the disk. You can think of a physical index restructure, restoring

the original fillfactor and allocating the index pages in a right order removing fragmentation and compacting the index space. Therefore there is no need to rebuild the non-clustered index(es) when rebuilding a clustered index. This functionality is fully implemented in SQL Server 2005, and was already partially implemented in SQL Server 2000. We have added the keyword ALL to make sure customers can rebuild all indexes for a given table using one command (ease of use purpose). Clustered or non-clustered index rebuild may increase performance if the index is fragmented, especially when queries use

index scans.
Disabling a clustered index does not really save space. We still need to keep the old clustered index (marked as disabled) to make sure we can rebuild it. Disabling a non-clustered (NC) index brings some space benefit since we drop the old index and keep only its metadata, so when we rebuild an NC index we use either the base table or a clustered index. However this benefit can be easily reached by using two commands DROP NC index and CREATE NC index.

Thanks,

Mirek

|||Thanks Sunil,
this makes perfect sense. I hadn't seen it in this way before - if rebuilding the clustered index is only done to reorder the data (not to remove fragmentation) then there might be no performance gain from also rebuilding the non-clustered index, as the indexes are unlikely to be ordered in the same way.
Cheers,
Paul|||Thanks Mirek,
this makes sense. So in some cases where the table is large (disk space small) it might be useful to rebuild the clustered index without the ALL option, then subsequently disable and rebuild each non-clustered index.
Cheers,
Paul

Indexing Questions

Sorry its been a while since I was taught about indexes,
Can I place indexes on both FK fields of a Associative table?
and what is the recommended number of rows to place an index on a table for SQL server (if different from other DBMS)?
and also whats a clustered index?Yes
I put clustered indexes on every table though not necessarily for performance reasons. Regarding nonclustered indexes, the number of rows is less significant than the number of pages those rows occupy - this depends on the "width" of the rows and fillfactor.
The last question is really fundamental - I suggest you read the entries in BoL re indexes & their associated structures otherwise you will just find yourself asking piecemeal questions and having to peice it all together at a later date. Better to get some understanding and then ask about anything you haven't quite grasped.|||I guess I would like to know why you ask that question

It's kinda like DBA 101

Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:

quote:

---

>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>

---

|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:

quote:

---

> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table
)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

---

sql

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,
How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:
>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:
> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/defaul...kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
--
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griff
so there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>
|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default...b;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

indexing for replicated tables..

Hi All,
We are having a lot of tables in an online
database 'TRANS'. There are no clustered indexes in the
tables. There are only non-clustered indexes in the
tables. we do a weekly re-org of tables by creating and
dropping non-clustered indexes.
We are planning to configure transactional replication for
this database. For tables having non-clustered indexes
alone - dbcc dbreindex does not improve scan density by
much!
So, the stratergy of creating and dropping clustered
indexes work? If not, what is the best startegy to get the
same done?
My next question is,
is it possible to reindex tables in Merge replicated
databases?If your filegroup has more than one file, scan density is not a good
measure, use logical or extent scan fragmentation...
Almost all tables will benefit from a clustered index... perhaps you might
try adding a clustered to several of the tables and see if that helps...
Regarding Merge replication, you may re-do indexes at the client if you
wish... make sure you always have the unique index on the GUID...
"Bharath" <ramakrishnan.bharadhwaj@.citigroup.com> wrote in message
news:069801c33eda$5b27e640$a101280a@.phx.gbl...
> Hi All,
> We are having a lot of tables in an online
> database 'TRANS'. There are no clustered indexes in the
> tables. There are only non-clustered indexes in the
> tables. we do a weekly re-org of tables by creating and
> dropping non-clustered indexes.
> We are planning to configure transactional replication for
> this database. For tables having non-clustered indexes
> alone - dbcc dbreindex does not improve scan density by
> much!
> So, the stratergy of creating and dropping clustered
> indexes work? If not, what is the best startegy to get the
> same done?
> My next question is,
> is it possible to reindex tables in Merge replicated
> databases?

indexing architechture

OK:

1. after rebuilding indexes, shouldn't sp_updatestats and DBCC UPDATEUSAGE be run for best performance?

2. What exactly are sp_updatestats and update usage doing? It looks like (from BOL) that updating usage would be updating the IAM and the page free space, and updating stats would just update the index/row pointers. Indexes are rebuilt nightly where I am currently working, however, unallocated space is consistently negative.

3. rebuilding or defragging the indexes should defrag the tables, right? As in, re-allocate free space depending on fillfactor...

4. for a reporting database, shouldn't the fillfactor be low? That way, you would have fewer page splits during loading, and as far as querying, by the time you are done with your loading, the engine should have evened out the allocation...

Rebuilding indexes (drop/create or DBCC DBREINDEX) will automatically update statistics.

If you are rebuilding indexes each night, then you probably don't need to worry about the statistics, unless you import bulk data throughout the day, truncate tables or significantly change the data distribution through large amounts of updates before the next index rebuild.

DBCC UPDATEUSAGE simply corrects inaccuracies in the sysindexes table.

sp_updatestats runs UPDATE STATISTICS on all user tables in the database.

If you rebuild a clustered index, it will effectively rebuild the table. There is an optional parameter in the reindex command to specify a new fill factor. If not specified, the original fill factor will be used for the reindex.

DEFRAG defragments the leaf level of an index using the original fill factor.

In summary, if you have the luxury of doing an index rebuild each night, then you're in a good position, and shouldn't really need to worry about defrag or statistics.

|||

THanks...however,

I don't quite understand what UPDATEUSAGE does...we have negative unallocated space on a continual basis. Our database is 156GB, and it shows up with 66GB as negative unallocated. THe only thing that fixes this is UPDATEUSEAGE -

1 - the query analyzer can't find the right query plan with -unallocated, right? I'm thinking that it doesn't have the correct IAM, but I don't know...

2- we do loads every night. Sometimes millions of rows. Unfortunately, the DBAs do the re-indexing BEFORE the load, with default fillfactor of 90. :(

(this was just a whine)

3- can someone tell me exactly what updating stats does that is different from updating useage? Inquiring minds want to know...

4- there are no updates during the day, it's read only for reporting. So, It seems to me that we should set the fillfactor low before the load - however, is it going to negatively impact the reporting? Doesn't SQL server start picking pages and extents with an algorithm that evenly distributes the data on imports and inserts?

5 - In what order should the above items be run?

Indexing

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!
You can add indexes using the ONLINE option (some restrictions, see Books Online, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock on the data while the
index is added. ONLINE is only available on Enterprise Edition.
If you don't use ONLINE, the table will be locked by shared lock if you create non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an impact of those who try
to access the tables while the index is being created.
Apart from the locking and blocking situation, the will be an obvious resource usage while the index
is being created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Huddle" <chuddle@.NOSPAMtimeplus.com> wrote in message
news:OMlTwkAWGHA.5148@.TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/databases tables, can I do
> it during the day when the database is in use? Or is this something that I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the right fields), but I'm new
> at this, if you can't tell. Thanks!
>
|||ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.
if the users don't mind, then neither do I !!!!!!!!!!

Indexing

I have a 25gb database. one of the tables has 45Million
rows. It is located on a datafile all on its own and on a
seperate drive. Now I have 7 indexes one of which is a
cluster. Can some one please tell me why every query run
defaults to the clustered index. Is there a bug or what.
System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
Thanks inadvace.
Query Optimizer selects proper index to every query if you don't use index
hint.
I think you may check the query plans of all queries and the statistics of
non-clustered indexes.
Hanky
"Ryan" <anonymous@.discussions.microsoft.com> wrote in message
news:45f601c4801a$06c252c0$a301280a@.phx.gbl...
> I have a 25gb database. one of the tables has 45Million
> rows. It is located on a datafile all on its own and on a
> seperate drive. Now I have 7 indexes one of which is a
> cluster. Can some one please tell me why every query run
> defaults to the clustered index. Is there a bug or what.
> System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
> enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
> Thanks inadvace.
|||I dont use hints and the stats are updated regularly but
still it uses the cluster, which is not the optimal index.
>--Original Message--
>Query Optimizer selects proper index to every query if
you don't use index
>hint.
>I think you may check the query plans of all queries and
the statistics of
>non-clustered indexes.
>Hanky
>
>"Ryan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:45f601c4801a$06c252c0$a301280a@.phx.gbl...
a
>
>.
>
|||Could you inform me the query plan on option 'set showplan_all on' and the
result of 'exec sp_helpindex TableName'?
<anonymous@.discussions.microsoft.com> wrote in message
news:4a5c01c48047$5290ef40$a501280a@.phx.gbl...[vbcol=seagreen]
> I dont use hints and the stats are updated regularly but
> still it uses the cluster, which is not the optimal index.
> you don't use index
> the statistics of
> message
> a
|||On Wed, 11 Aug 2004 20:11:14 -0700, Ryan wrote:

>I have a 25gb database. one of the tables has 45Million
>rows. It is located on a datafile all on its own and on a
>seperate drive. Now I have 7 indexes one of which is a
>cluster. Can some one please tell me why every query run
>defaults to the clustered index. Is there a bug or what.
>System is WIN2K ADV ED with 8Gb RAM Quad 4.2 Xeon AWE
>enabled. 7GB allocated to SQL. SQL 2000 ENT ED both SP3
>Thanks inadvace.
Hi Ryan,
Are the queries using ONLY the clustered index, or arre they using the
clustered index in addition to one or more of the other indexes? I think
it's the latter.
The clustered index defines how the data in the table is physically
organised. All non-clustered indexes combine the data from the index
columns with the corresponding data in the clustered index. That data is
then used to locate the row. A non-clustered index will never be used on
it's own, but always in combination with the clustered index (unless all
rows requested are in either the index or the clustered index; in that
case there's no need to locate the rest of the data).
If you run the following query with the option to show the execution plan,
you'll see (checking the plan from right to left) that an index seek on
the non-clustered index is used to find the rows matching the where
clause, followed by a bookmark lookup on the clustered index to return the
whole row.
use pubs
go
select * from authors
where au_lname = 'White'
and au_fname = 'Johnson'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks,
Hugo
"Hugo Kornelis" wrote:

> On Wed, 11 Aug 2004 20:11:14 -0700, Ryan wrote:
>
> Hi Ryan,
> Are the queries using ONLY the clustered index, or arre they using the
> clustered index in addition to one or more of the other indexes? I think
> it's the latter.
> The clustered index defines how the data in the table is physically
> organised. All non-clustered indexes combine the data from the index
> columns with the corresponding data in the clustered index. That data is
> then used to locate the row. A non-clustered index will never be used on
> it's own, but always in combination with the clustered index (unless all
> rows requested are in either the index or the clustered index; in that
> case there's no need to locate the rest of the data).
> If you run the following query with the option to show the execution plan,
> you'll see (checking the plan from right to left) that an index seek on
> the non-clustered index is used to find the rows matching the where
> clause, followed by a bookmark lookup on the clustered index to return the
> whole row.
> use pubs
> go
> select * from authors
> where au_lname = 'White'
> and au_fname = 'Johnson'
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

indexing

Hello,

I need some help understanding why my indexes do not seem to be affecting my
searches. I would really appreciate help understanding what indexes I need
to make this query run faster. I realize that I use wildcards when searching
for g1.gene_name, but is there anything I can do to make that less of a
problem? I ran EXPLAIN on the search I wanted to optimize and got the
following:

EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1 WHERE
(c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND (g1.gene_sym = 'hh'
OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE '%hh%')) OR (c1.genbank_acc =
'hh' OR c1.SUID = 'hh') OR (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID =
g1.SFID AND g1.locuslink_id = r1.locuslink_id AND (r1.mRNA_acc = 'hh'));
+---+---+--------+--+---+--+---
+--------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+---+---+--------+--+---+--+---
+--------+
| r1 | index | mRNA_acc,llid,rma,rllid | rma | 25 | NULL | 20093
| Using index |
| g1 | ALL | PRIMARY,llid,ggs,gga,gll | NULL | NULL | NULL | 190475
| |
| c1 | ALL | PRIMARY,cga,cs | NULL | NULL | NULL | 43714
| where used |
| t1 | index | gene_SFID,gS,cS,tg,tc | gS | 4 | NULL | 47238
| where used; Using index |
+---+---+--------+--+---+--+---
+--------+

I have the following indexes (which were all added after the database was
populated):

ALTER TABLE cDNA ADD INDEX cga
(genbank_acc, SFID);

ALTER TABLE cDNA ADD INDEX co
(organism, SFID);

ALTER TABLE cDNA ADD INDEX cs
(SUID, SFID);

ALTER TABLE Gene ADD INDEX ggs
(gene_sym, SFID);

ALTER TABLE Gene ADD INDEX gga
(genbank_acc, SFID);

ALTER TABLE Gene ADD INDEX ggn
(gene_name, SFID);

ALTER TABLE Gene ADD INDEX go
(organism, SFID);

ALTER TABLE Gene ADD INDEX gll
(locuslink_id, SFID);

ALTER TABLE Gene ADD INDEX gui
(unigene_id, SFID);

ALTER TABLE Transcript ADD INDEX tg
(gene_SFID, cDNA_SFID);

ALTER TABLE Transcript ADD INDEX tc
(cDNA_SFID);

ALTER TABLE Refseq ADD INDEX rma
(mRNA_acc, locuslink_id);

ALTER TABLE Refseq ADD INDEX rllid
(locuslink_id);"superfly2" <darius_fatakia@.yahoo.com> wrote in message
news:cb030a$11i$1@.news.Stanford.EDU...
> Hello,
> I need some help understanding why my indexes do not seem to be affecting
my
> searches. I would really appreciate help understanding what indexes I need
> to make this query run faster. I realize that I use wildcards when
searching
> for g1.gene_name, but is there anything I can do to make that less of a
> problem? I ran EXPLAIN on the search I wanted to optimize and got the
> following:
> EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1
WHERE
> (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND (g1.gene_sym = 'hh'
> OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE '%hh%')) OR (c1.genbank_acc
=
> 'hh' OR c1.SUID = 'hh') OR (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID =
> g1.SFID AND g1.locuslink_id = r1.locuslink_id AND (r1.mRNA_acc = 'hh'));
+---+---+--------+--+---+--+---
> +--------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
+---+---+--------+--+---+--+---
> +--------+
> | r1 | index | mRNA_acc,llid,rma,rllid | rma | 25 | NULL |
20093
> | Using index |
> | g1 | ALL | PRIMARY,llid,ggs,gga,gll | NULL | NULL | NULL |
190475
> | |
> | c1 | ALL | PRIMARY,cga,cs | NULL | NULL | NULL |
43714
> | where used |
> | t1 | index | gene_SFID,gS,cS,tg,tc | gS | 4 | NULL |
47238
> | where used; Using index |
+---+---+--------+--+---+--+---
> +--------+
>
> I have the following indexes (which were all added after the database was
> populated):
> ALTER TABLE cDNA ADD INDEX cga
> (genbank_acc, SFID);
> ALTER TABLE cDNA ADD INDEX co
> (organism, SFID);
> ALTER TABLE cDNA ADD INDEX cs
> (SUID, SFID);
>
> ALTER TABLE Gene ADD INDEX ggs
> (gene_sym, SFID);
> ALTER TABLE Gene ADD INDEX gga
> (genbank_acc, SFID);
> ALTER TABLE Gene ADD INDEX ggn
> (gene_name, SFID);
> ALTER TABLE Gene ADD INDEX go
> (organism, SFID);
> ALTER TABLE Gene ADD INDEX gll
> (locuslink_id, SFID);
> ALTER TABLE Gene ADD INDEX gui
> (unigene_id, SFID);
>
> ALTER TABLE Transcript ADD INDEX tg
> (gene_SFID, cDNA_SFID);
> ALTER TABLE Transcript ADD INDEX tc
> (cDNA_SFID);
>
> ALTER TABLE Refseq ADD INDEX rma
> (mRNA_acc, locuslink_id);
> ALTER TABLE Refseq ADD INDEX rllid
> (locuslink_id);

I believe that EXPLAIN is a MySQL statement - this is a Microsoft SQL Server
nesgroup, so I guess you'll get a better answer in a MySQL forum.

Simon|||[posted and mailed, please reply in news]

superfly2 (darius_fatakia@.yahoo.com) writes:
> EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1
> WHERE (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND
> (g1.gene_sym = 'hh' OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE
> '%hh%')) OR (c1.genbank_acc = 'hh' OR c1.SUID = 'hh') OR (c1.SFID =
> t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND g1.locuslink_id =
> r1.locuslink_id AND (r1.mRNA_acc = 'hh'));

Obviously you are in the wrong newsgroup, beause there is no EXPLAIN
command in SQL Server, and you don't use ALTER TABLE to add indexes.

Nevertheless, I might be able to give some input. You have this
condition:

g1.gene_name LIKE '%hh%'

To resolve this condition, the DB engine cannot use the index for a
quick look up. Compare with looking through the index of a book and
try to find all keywords with 'hh' in them. You would have to scan
the entire index.

I don't know about your DBMS, but MS SQL Server could use the index
for a scan, and it would do it, if the index includes all columns in the
table that aer referred to. If I see right, you would have to add
locuslink_id to

ALTER TABLE Gene ADD INDEX ggs
(gene_sym, SFID);

Not that I know if this would help on your engine, but most DBMS's are
fond of using covering indexes.

Then again, I have no idea how your DBMS handle all those OR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Indexing

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!You can add indexes using the ONLINE option (some restrictions, see Books On
line, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock
on the data while the
index is added. ONLINE is only available on Enterprise Edition.
If you don't use ONLINE, the table will be locked by shared lock if you crea
te non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an
impact of those who try
to access the tables while the index is being created.
Apart from the locking and blocking situation, the will be an obvious resour
ce usage while the index
is being created.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Huddle" <chuddle@.NOSPAMtimeplus.com> wrote in message
news:OMlTwkAWGHA.5148@.TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/data
bases tables, can I do
> it during the day when the database is in use? Or is this something that
I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the rig
ht fields), but I'm new
> at this, if you can't tell. Thanks!
>|||ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.
if the users don't mind, then neither do I !!!!!!!!!!