Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,
There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,
It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,
|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.

Non-Cluster Index PK

I have a client that has created Primary Keys with Non-Cluster indexes.
These Primary Keys are usually monotically increasing. Who much will this
slow down table joins using Primary Keys that Non-Cluster Indexes.
Please help me with this topic.
Thank You,It really depends. What is clustered? Are foreign keys indexed? Are other
columns used in predicates indexed? Are they having performance problems at
all? Can you run some tests on a development server?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A3D054C0-F6A4-4AAF-A217-F26B83079F0D@.microsoft.com...
> I have a client that has created Primary Keys with Non-Cluster indexes.
> These Primary Keys are usually monotically increasing. Who much will this
> slow down table joins using Primary Keys that Non-Cluster Indexes.
> Please help me with this topic.
> Thank You,|||On Wed, 26 Oct 2005 09:21:05 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have a client that has created Primary Keys with Non-Cluster indexes.
>These Primary Keys are usually monotically increasing. Who much will this
>slow down table joins using Primary Keys that Non-Cluster Indexes.
>Please help me with this topic.
Several answers posted to you yesterday.
J.