index and key: nonclustered

Showing posts with label nonclustered. Show all posts

Monday, March 26, 2012

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column ?
Thanks.
The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column ?
> Thanks.
|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on
>
>.
>
|||Yes bad practise - no reason for it and like Aaron said, there's the disk space too. Also, when you modify the data you have to change the data in the table and the two indexes. You should get your developers to script their indexes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on[vbcol=seagreen]
>
>.
>|||Yes bad practise - no reason for it and like Aaron said, there's the disk sp
ace too. Also, when you modify the data you have to change the data in the t
able and the two indexes. You should get your developers to script their ind
exes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Friday, March 23, 2012

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........
>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
>> Does the performance increase if the same index created
on
>> the table twice ?. Say I have a 2 nonclustered index on
>> the same column. Does it increase the performance or the
>> performance will be the same if there is only 1 index on
>> that column '
>> Thanks.
>
>.
>sql

Monday, March 12, 2012

Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For example,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For exampl
e,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M

Index hell again.

Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.As always, it depends. I would certainly put a fill factor < 100 - and this
depends on how frequent your inserts and defrags are. This index would
cover a query such as:
select
email
from
Customer
where
first_name = 'John'
and last_name = 'Smith'
If you're using SQL 2005, you could make email an included column and just
key on first_name, last_name.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:%23rWVJ1wYGHA.5012@.TK2MSFTNGP04.phx.gbl...
Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.

Index hell again.

Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.As always, it depends. I would certainly put a fill factor < 100 - and this
depends on how frequent your inserts and defrags are. This index would
cover a query such as:
select
email
from
Customer
where
first_name = 'John'
and last_name = 'Smith'
If you're using SQL 2005, you could make email an included column and just
key on first_name, last_name.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"_Stephen" <srussell@.electracash.com> wrote in message
news:%23rWVJ1wYGHA.5012@.TK2MSFTNGP04.phx.gbl...
Would you allow an index on Customer
CREATE NONCLUSTERED INDEX [IX_Customer] ON [dbo].[Customer]
(
[first_name] ASC,
[last_name] ASC,
[email] ASC
) ON [PRIMARY]
So the defrag on this is terrible and insertion on the batches is slowed
down.

Friday, February 24, 2012

Index creation datetime

Hi,
How I find out the creation datetime of index (clustered
and nonclustered)?
Regards.
--
Farhan SoomroNo, SQL Server does not keep any track of index creation date., However, if
you have primary key constraint (which will have an index
clustered/non-clustered) then you can refer to crdate column of sysobjects
table to check the creation date of this constraint which will be same as
creation date of index on primary key.
--
-Vishal
"Farhan Soomro" <fsoomro@.chartlinks.com> wrote in message
news:0bcc01c35b62$5157d860$a501280a@.phx.gbl...
> Hi,
> How I find out the creation datetime of index (clustered
> and nonclustered)?
> Regards.
> --
> Farhan Soomro

Index creation

Dears,
I have question regarding to some nonclustered indexes that were created on
the database. The company that is that has created this database is saying
that these indexes were created by our own.
My question is that, can indexes be created some how without command CREATE
INDEX or through Enterprise Manger on table.
I'm doing also DBCC DBREINDEX on table, but this can't create new index as
far as I know?
Can you please give me a feedback based on your experience, how this exactly
happenned? And can I discover let's say when this index was created and
additional information bisides sysindexes, much descriptive information.
Thank you,
BaniSQL.Hi
Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
unique index to enforce uniqueness
Also ,when you create a PRIMARY KEY on the table SQL Server creates a
clustered index to enforce uniqueness
> I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> far as I know?
Its rebuild the existing indexes not creating
> Can you please give me a feedback based on your experience, how this
> exactly
> happenned? And can I discover let's say when this index was created and
> additional information bisides sysindexes, much descriptive information.
sp_helpindex 'tablename'
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
> Dears,
> I have question regarding to some nonclustered indexes that were created
> on
> the database. The company that is that has created this database is saying
> that these indexes were created by our own.
> My question is that, can indexes be created some how without command
> CREATE
> INDEX or through Enterprise Manger on table.
> I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> far as I know?
> Can you please give me a feedback based on your experience, how this
> exactly
> happenned? And can I discover let's say when this index was created and
> additional information bisides sysindexes, much descriptive information.
> Thank you,
> BaniSQL.|||Thanks for your reply,
with PK contraint (clustered index) is fine, my question is does FK fields
can create indexes? Becuase I have only FK constraints when I ran sp_help
against the table.
And for information regarding to the for the particular index index I'm
interested to find: date of creation, user etc. from MDF not LDF.
Best,
ProBani.
"Uri Dimant" wrote:
> Hi
> Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
> unique index to enforce uniqueness
> Also ,when you create a PRIMARY KEY on the table SQL Server creates a
> clustered index to enforce uniqueness
> > I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> > far as I know?
> Its rebuild the existing indexes not creating
> > Can you please give me a feedback based on your experience, how this
> > exactly
> > happenned? And can I discover let's say when this index was created and
> > additional information bisides sysindexes, much descriptive information.
> sp_helpindex 'tablename'
>
> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
> > Dears,
> > I have question regarding to some nonclustered indexes that were created
> > on
> > the database. The company that is that has created this database is saying
> > that these indexes were created by our own.
> > My question is that, can indexes be created some how without command
> > CREATE
> > INDEX or through Enterprise Manger on table.
> > I'm doing also DBCC DBREINDEX on table, but this can't create new index as
> > far as I know?
> > Can you please give me a feedback based on your experience, how this
> > exactly
> > happenned? And can I discover let's say when this index was created and
> > additional information bisides sysindexes, much descriptive information.
> >
> > Thank you,
> > BaniSQL.
>
>|||Hi
> with PK contraint (clustered index) is fine, my question is does FK
> fields
> can create indexes? Becuase I have only FK constraints when
No , it does not.
select * from master..sysdatabases
You need crdate column
"BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
news:EF5F8DB2-40BE-47E6-A21B-92C73C547DC1@.microsoft.com...
> Thanks for your reply,
> with PK contraint (clustered index) is fine, my question is does FK
> fields
> can create indexes? Becuase I have only FK constraints when I ran sp_help
> against the table.
> And for information regarding to the for the particular index index I'm
> interested to find: date of creation, user etc. from MDF not LDF.
> Best,
> ProBani.
> "Uri Dimant" wrote:
>> Hi
>> Yes, when you create a UNIQUE CONSTRAINT sql server creates non-clustered
>> unique index to enforce uniqueness
>> Also ,when you create a PRIMARY KEY on the table SQL Server creates a
>> clustered index to enforce uniqueness
>> > I'm doing also DBCC DBREINDEX on table, but this can't create new index
>> > as
>> > far as I know?
>> Its rebuild the existing indexes not creating
>> > Can you please give me a feedback based on your experience, how this
>> > exactly
>> > happenned? And can I discover let's say when this index was created and
>> > additional information bisides sysindexes, much descriptive
>> > information.
>> sp_helpindex 'tablename'
>>
>> "BaniSQL" <BaniSQL@.discussions.microsoft.com> wrote in message
>> news:62D2D737-3906-40D5-9AF1-10437FE7C23E@.microsoft.com...
>> > Dears,
>> > I have question regarding to some nonclustered indexes that were
>> > created
>> > on
>> > the database. The company that is that has created this database is
>> > saying
>> > that these indexes were created by our own.
>> > My question is that, can indexes be created some how without command
>> > CREATE
>> > INDEX or through Enterprise Manger on table.
>> > I'm doing also DBCC DBREINDEX on table, but this can't create new index
>> > as
>> > far as I know?
>> > Can you please give me a feedback based on your experience, how this
>> > exactly
>> > happenned? And can I discover let's say when this index was created and
>> > additional information bisides sysindexes, much descriptive
>> > information.
>> >
>> > Thank you,
>> > BaniSQL.
>>

Monday, March 26, 2012

Index question

Index question

Friday, March 23, 2012

Index question

Monday, March 12, 2012

Index Locking Operation Questiong

Index Locking Operation Questiong

Index hell again.

Index hell again.

Friday, February 24, 2012

Index creation datetime

Index creation

Index creation

Index creation

index and key

Blog Archive

About Me