Cluster services overhead

It is possible, although I would not expect the clustering technology to =
have a noticable impact on performance. You talk about instances. Are =
you running multiple instances of SQL Server on the same hardware, or by =
"instance" do you mean Server A (standalone box) and Server B (clustered =
box)? If you are talking about multiple instances on one server are the =
instances configured to use the same amount of resources? =20
This reply will be posted within .server and it will also be copied to =
the .clustering newsgroup. Hopefully the experts that hang out there =
will have additional comments orideas.
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
>=20
> Consider the scenario ...
>=20
> Instance 1 of Sql Server running on Compaq with MSA1000=20
> disks=20
>=20
> Instance 2 of Sql Server running on Compaq with MSA1000=20
> disks BUT in a clustered environment
>=20
> We are finding that Instance 1 offers better performance=20
> than 2 for the same configuration (memory, sql version,=20
> windows etc). Could the use of cluster services for=20
> clustering be causing the overhead?
>=20
> TIA,
> Jack
>
I have never felt the presence of clustering on any of my SQL clusters. The
cluster service has such a small footprint. Could it be that you are not
comparing like DBs?
Cheers,
Rod
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OYr8Ip9TEHA.716@.TK2MSFTNGP11.phx.gbl...
It is possible, although I would not expect the clustering technology to
have a noticable impact on performance. You talk about instances. Are you
running multiple instances of SQL Server on the same hardware, or by
"instance" do you mean Server A (standalone box) and Server B (clustered
box)? If you are talking about multiple instances on one server are the
instances configured to use the same amount of resources?
This reply will be posted within .server and it will also be copied to the
..clustering newsgroup. Hopefully the experts that hang out there will have
additional comments orideas.
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b07301c44fcf$51c472b0$a601280a@.phx.gbl...
> Consider the scenario ...
> Instance 1 of Sql Server running on Compaq with MSA1000
> disks
> Instance 2 of Sql Server running on Compaq with MSA1000
> disks BUT in a clustered environment
> We are finding that Instance 1 offers better performance
> than 2 for the same configuration (memory, sql version,
> windows etc). Could the use of cluster services for
> clustering be causing the overhead?
> TIA,
> Jack
>

Cluster index

If I changed my unique cluster index to non-unique cluster index, what impact
this can cause? please help !
ThanksYou can have non unique values.
Im guessing you meant to ask more than that?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> If I changed my unique cluster index to non-unique cluster index, what
> impact
> this can cause? please help !
> Thanks
It depends...
It will allow you to add duplicate (index column(s)) to your table.
All non-clustered indexes on the table will be rebuilt.
Internally, SQL Server tracks the duplicate cluster key columns.
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry for not specific.
How much it can affect the perfomance?
Thanks.
"ChrisR" wrote:
> You can have non unique values.
> Im guessing you meant to ask more than that?
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
>
>|||If we are running reports off this table, how slow the performance can be?
Thanks for your help.
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> > If I changed my unique cluster index to non-unique cluster index, what
> > impact
> > this can cause? please help !
> >
> > Thanks
> It depends...
> It will allow you to add duplicate (index column(s)) to your table.
> All non-clustered indexes on the table will be rebuilt.
> Internally, SQL Server tracks the duplicate cluster key columns.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Are you talking about this scenario:
You currently have a unique clustered index on column A. You want to change that to a non-unique
clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
you discovered that you suddenly want to allow duplicates in column A?
If you provide more information, we can be more specific.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> Sorry for not specific.
> How much it can affect the perfomance?
> Thanks.
> "ChrisR" wrote:
>> You can have non unique values.
>> Im guessing you meant to ask more than that?
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> > If I changed my unique cluster index to non-unique cluster index, what
>> > impact
>> > this can cause? please help !
>> >
>> > Thanks
>>|||"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> If we are running reports off this table, how slow the performance can be?
> Thanks for your help.
> "Rick Sawtell" wrote:
How many more records are there going to be because of duplicates? That
will be the difference.
Rick|||Thanks for pointing out.
Yes, since we are going to have duplicate values in one of the cluster index
columns. now we have to change the index from unique cluster index to non
unique cluster index. Basicly we are running reports off this table, I would
like to hear your idea on this kind situation, you may have better idea to
keep or speed up performance.
Thanks a lot.
"Tibor Karaszi" wrote:
> Are you talking about this scenario:
> You currently have a unique clustered index on column A. You want to change that to a non-unique
> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that? Have
> you discovered that you suddenly want to allow duplicates in column A?
> If you provide more information, we can be more specific.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> > Sorry for not specific.
> > How much it can affect the perfomance?
> >
> > Thanks.
> >
> > "ChrisR" wrote:
> >
> >> You can have non unique values.
> >>
> >> Im guessing you meant to ask more than that?
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> > impact
> >> > this can cause? please help !
> >> >
> >> > Thanks
> >>
> >>
> >>
>|||I am exactly not sure , thousands may be.
Thanks
"Rick Sawtell" wrote:
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:54022448-21BF-472D-88E0-105EEEC362C9@.microsoft.com...
> > If we are running reports off this table, how slow the performance can be?
> >
> > Thanks for your help.
> >
> > "Rick Sawtell" wrote:
>
> How many more records are there going to be because of duplicates? That
> will be the difference.
>
> Rick
>
>|||Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
added a unique index on one of the joins columns. That speeded up the query very much.
Sometimes, the optimizer also can pick a plan faster from the same fact.
Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
10000 page accesses (which is essentially how non-clustered indexes work).
Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> Thanks for pointing out.
> Yes, since we are going to have duplicate values in one of the cluster index
> columns. now we have to change the index from unique cluster index to non
> unique cluster index. Basicly we are running reports off this table, I would
> like to hear your idea on this kind situation, you may have better idea to
> keep or speed up performance.
> Thanks a lot.
> "Tibor Karaszi" wrote:
>> Are you talking about this scenario:
>> You currently have a unique clustered index on column A. You want to change that to a non-unique
>> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
>> Have
>> you discovered that you suddenly want to allow duplicates in column A?
>> If you provide more information, we can be more specific.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
>> > Sorry for not specific.
>> > How much it can affect the perfomance?
>> >
>> > Thanks.
>> >
>> > "ChrisR" wrote:
>> >
>> >> You can have non unique values.
>> >>
>> >> Im guessing you meant to ask more than that?
>> >>
>> >>
>> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
>> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
>> >> > If I changed my unique cluster index to non-unique cluster index, what
>> >> > impact
>> >> > this can cause? please help !
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thanks Tibor, I just tested and there are almost no difference.
"Tibor Karaszi" wrote:
> Ok, sounds like a strange requirement to me, but you know your business better than I do :-)
> The optimizer can sometimes pick a better plan from the sheer fact that it know that there can be no
> duplicates in a column. I remember a case where the optimizer didn't want to do a merge join until I
> added a unique index on one of the joins columns. That speeded up the query very much.
> Sometimes, the optimizer also can pick a plan faster from the same fact.
> Since it is a clustered index, you don't have to worry as much about the otherwise obvious cases.
> Like returning one row vs. 10000 rows based on a search condition, where 10000 rows would result in
> 10000 page accesses (which is essentially how non-clustered indexes work).
> Bur don't ask us to quantify this. It is impossible. You have the data, the indexes and the queries.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:DEBC853C-181F-42A2-89B1-A4F2C29EF183@.microsoft.com...
> > Thanks for pointing out.
> > Yes, since we are going to have duplicate values in one of the cluster index
> > columns. now we have to change the index from unique cluster index to non
> > unique cluster index. Basicly we are running reports off this table, I would
> > like to hear your idea on this kind situation, you may have better idea to
> > keep or speed up performance.
> >
> > Thanks a lot.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you talking about this scenario:
> >>
> >> You currently have a unique clustered index on column A. You want to change that to a non-unique
> >> clustered index on column A. If so, it is a very odd viewpoint. Why would you want to do that?
> >> Have
> >> you discovered that you suddenly want to allow duplicates in column A?
> >>
> >> If you provide more information, we can be more specific.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> news:2B25D1BD-989A-413D-B9F6-C7290AAB22FB@.microsoft.com...
> >> > Sorry for not specific.
> >> > How much it can affect the perfomance?
> >> >
> >> > Thanks.
> >> >
> >> > "ChrisR" wrote:
> >> >
> >> >> You can have non unique values.
> >> >>
> >> >> Im guessing you meant to ask more than that?
> >> >>
> >> >>
> >> >> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> >> >> news:49E46795-304E-49D2-8343-D5F0789C47E4@.microsoft.com...
> >> >> > If I changed my unique cluster index to non-unique cluster index, what
> >> >> > impact
> >> >> > this can cause? please help !
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Don't get me wrong, but if the rules change, and what was once unique
(possibly a key value) is no longer unique, then you need to review the
rules, and check if the database design still matches the requirements.
If the old unique clustered index represented the key, then you need to
get the new key definition and implement that in the table. From a
logical point of view, this doesn't sound like a quick fix, but a fix
that requires insight in the current model and knowledge of the new
"world view". Worries about performance comes after that, or duing the
phase where you determine the indexes for the changed table. The new key
might still be the (changed) unique clustered index...
Gert-Jan
Matthew Z wrote:
> If I changed my unique cluster index to non-unique cluster index, what impact
> this can cause? please help !
> Thanks