I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,
Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Showing posts with label non-cluster. Show all posts
Showing posts with label non-cluster. Show all posts
Thursday, March 22, 2012
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Indexes / Non-Cluster Indexes
I have application that I'm trying to enhance database performance.
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
One of the ways to enhance database performance is make sure that your
cluster indexes and non-cluster indexes are using the correct fields.
This cluster indexes were not set on the primary key for several tables.
What is the best way to test dropping Non-Cluster index (Primary Key) and
dropping Cluster (Non Primary Key)
Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
index.
What is the easiest way to test the performance increase by dropping and
create indexes that were set up on the incorrect fields?
Thank You,Yes, you would need to drop/ recreate. Profiler would be the easiest way to
look at the speed improvements.
On a side note, there may be times when you don't want clustering on the PK.
(Usually on a reporting server.) For example, you may want to have the
clustering on a date field as most reports are off of date ranges.
--
TIA,
ChrisR
"Joe K." wrote:
> I have application that I'm trying to enhance database performance.
> One of the ways to enhance database performance is make sure that your
> cluster indexes and non-cluster indexes are using the correct fields.
> This cluster indexes were not set on the primary key for several tables.
> What is the best way to test dropping Non-Cluster index (Primary Key) and
> dropping Cluster (Non Primary Key)
> Creating the Primary Key Cluster index and Non-Primary Key to Non-Cluster
> index.
> What is the easiest way to test the performance increase by dropping and
> create indexes that were set up on the incorrect fields?
> Thank You,
>
>
>
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.com/clustered_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.sqlsql
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.com/clustered_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.sqlsql
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.c...red_indexes.asp
David Gugick
Quest Software|||that is a great article that david has given you.
Cluster Index Without Primary Key
I have a SQL Server 2000 database with cluster and non-cluster indexes.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
What advantages do you gain from creating a primary key as a cluster index.
If you were a cluster index without primary key what overhead goes along
with this process?
Thanks,
the clustered index creates an object where the physical order of rows
is the same as the indexed order of the rows.
a primary key creates a unique index on that column. generally
speaking a clustered unique index provides much faster query times than
a nonclustered index.
overhead produced by any index will be filesize creating the new page
set for that particular index.
|||Joe K. wrote:
> I have a SQL Server 2000 database with cluster and non-cluster
> indexes.
> What advantages do you gain from creating a primary key as a cluster
> index. If you were a cluster index without primary key what overhead
> goes along with this process?
> Thanks,
Keys are logical concepts. Indexes are physical constructs. That fact that
primary keys and unique constraints use indexes for enforcement should not
confuse the issue. Design you keys as the business requires in your logical
data model.
Add indexes where the addition of the index helps query performance. The
choice of whether a particular index should be clustered (only 1 per table)
or non-clustered should be clearly examined on a table by table basis.
Here's a good start:
http://www.sql-server-performance.co...ed_indexes.asp
David Gugick
Quest Software
|||that is a great article that david has given you.
Tuesday, March 20, 2012
Cluster and Non-Cluster Indexes
How are cluster and non-cluster indexes data moved with SQL Server 2000
Transaction Replication? I have one database that I am using to replication
to another server.
I would like to execute the dbcc reindex job on the subscriber database to
reindex all of the indexes, would this be replicated to the subscriber
database.
Thanks,
No, it isn't - it is logged though and this does affect the log reader
agent's performance.
You can replicate these commands to the subscriber using sp_addscriptexec if
all of your subscribers are deployed via unc.
Hilary Cotter
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
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8F9EC263-95CD-42D3-9C82-D5BB1DE169EC@.microsoft.com...
> How are cluster and non-cluster indexes data moved with SQL Server 2000
> Transaction Replication? I have one database that I am using to
replication
> to another server.
> I would like to execute the dbcc reindex job on the subscriber database to
> reindex all of the indexes, would this be replicated to the subscriber
> database.
> Thanks,
>
>
Transaction Replication? I have one database that I am using to replication
to another server.
I would like to execute the dbcc reindex job on the subscriber database to
reindex all of the indexes, would this be replicated to the subscriber
database.
Thanks,
No, it isn't - it is logged though and this does affect the log reader
agent's performance.
You can replicate these commands to the subscriber using sp_addscriptexec if
all of your subscribers are deployed via unc.
Hilary Cotter
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
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8F9EC263-95CD-42D3-9C82-D5BB1DE169EC@.microsoft.com...
> How are cluster and non-cluster indexes data moved with SQL Server 2000
> Transaction Replication? I have one database that I am using to
replication
> to another server.
> I would like to execute the dbcc reindex job on the subscriber database to
> reindex all of the indexes, would this be replicated to the subscriber
> database.
> Thanks,
>
>
Subscribe to:
Posts (Atom)