CogInStr function fails with MSAS 2005

Hi,

Following MDX query works with MSAS 2000 but fails with MSAS 2005:

SELECT
{ FILTER([Product].AllMembers, CogInStr([Product].CurrentMember.Name,"*", 0) > 0) }
ON AXIS(0)
FROM [Sales]

Following error is thrown:

[ 1] Microsoft OLE DB Provider for Analysis S: Query (2, 32) The '[CogInStr]' function does not exist.

Any pointers on what needs to be done in order to support this query in MSAS 2005 would be appreciated.

Thanks and Regards,
Santosh

CogInStr - was not the built in funtion in AS2000, and it is not a built in function in AS2005 either. I suspect in your deployment somebody implemented custom UDF function with such name. You will need to implement it as a stored procedure in AS2005.

|||

Thanks for your response.
Yes, in AS2000 it was implemented as a UDF as shown below:

command = USE LIBRARY "CogUdf.CogRExp"
command = SELECT
{ FILTER([Product].AllMembers, CogInStr([Product].CurrentMember.Name,"*", 0) > 0) }
ON AXIS(0)
FROM [Sales]

Can you please give me some pointers (possible code snippets) on how I can convert this to a stored procedure in AS2005.

Thanks in advance,
Santosh.

|||Also, can't i use it in the same manner as it was in AS2000. i.e, through library function which i can execute using the USE LIBRARY call?|||

Please read BOL on the subject of stored procedures. Once they are deployed to the server, there is no need to do USE LIBRARY command anymore. For the extensive library of custom sprocs, you can check the following CodePlex project: http://www.codeplex.com/ASStoredProcedures

HTH,

Mosha (http://www.mosha.com/msolap)

|||The samples given in codeplex.com have been written in C#. Can stored procedures be implemented using OLE db for olap interfaces?|||You are not asking the right question. Take a look at the implementation of CogInStr - it doesn't use OLEDB for OLAP. You can easily replicate its functionality on C# or VB.NET or any other CLR language.|||

Sorry for the confusion.

I was trying to use the ASSP project with my MSAS 2005. I registered ASSP.dll under server assembly as a COM DLL type of assembly.

Later when I tried to run the following MDX query against the Adventure Works DW sample database, I am getting following error:

Query (1, 30) The '[ASSP].[FindCurrentMemberVerbose]' function does not exist.

Is there a link which demonstrate the use of these stored procedures with MSAS2005 and how to register it under Server/Database assemblies?

Thanks and Regards,
Santosh.

|||

I was able to use the Stored Procedure finally with MSAS 2005.

Thanks,
Santosh.

COALESCE as a Performance Enhancer?!?

I am using a clever product called SQL Expert Pro to optimize
problematic SQL statements. It works by generating syntactically
identical variations of the original SQL, finding all unique execution
plans, and batch testing them.

Anyway, it generally turns out that a very odd change dramatically
improves performance (for a PeopleSoft database on SQL Server 7). Run
time goes from 52 seconds to 8 seconds.

The change is to replace a join condition in a where clause with an
odd equivalent COALESCE construct, e.g. replace WHERE C.PAY_ID =
D.PAY_ID with WHERE C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID).

Has anyone seen this sort of behavior before? Why would it be
advantageous to COALESCE on the same field twice?

The original and COALESCE'd versions are shown below. Does the fact
that this is a nine-table join over large tables have anything to do
with it?

Original Query (52 sec)

SELECT F.ACCT_ID,
I.ENTITY_NAME,
A.TNDR_SOURCE_CD,
C.PAY_EVENT_ID,
C.NON_CIS_NAME,
C.NON_CIS_REF_NBR,
C.NON_CIS_COMMENT,
C.PAY_AMT,
D.PAY_SEG_AMT,
E.ACCOUNTING_DT,
':1',
':2',
':3',
E.FREEZE_OPRID,
E.FREEZE_DTTM,
E.FT_TYPE_FLG
FROM PS_CI_TNDR_CTL A,
PS_CI_PAY_TNDR B,
PS_CI_PAY C,
PS_CI_PAY_SEG D,
PS_CI_SA F,
PS_CI_SA_TYPE G,
PS_CI_ACCT_PER H,
PS_CI_PER_NAME I,
PS_CW_FT E
WHERE A.TNDR_CTL_ID = B.TNDR_CTL_ID
AND A.TNDR_SOURCE_CD LIKE 'STK%'
AND B.PAY_EVENT_ID = C.PAY_EVENT_ID
AND C.PAY_ID = D.PAY_ID
AND D.PAY_SEG_ID = E.SIBLING_ID
AND E.ACCOUNTING_DT BETWEEN '2003-10-01' AND '2003-10-31'
AND E.FT_TYPE_FLG IN ('PS', 'PX')
AND NOT EXISTS (SELECT 'X'
FROM PS_CW_INTERFACE_ID J
WHERE J.PAYOR_ACCT_ID = F.ACCT_ID)
AND E.SA_ID = F.SA_ID
AND F.SA_TYPE_CD = G.SA_TYPE_CD
AND G.DEBT_CL_CD = 'NCIS'
AND F.ACCT_ID = H.ACCT_ID
AND H.PER_ID = I.PER_ID
ORDER BY 2

Optimized Query (8 sec)

SELECT F.ACCT_ID,
I.ENTITY_NAME,
A.TNDR_SOURCE_CD,
C.PAY_EVENT_ID,
C.NON_CIS_NAME,
C.NON_CIS_REF_NBR,
C.NON_CIS_COMMENT,
C.PAY_AMT,
D.PAY_SEG_AMT,
E.ACCOUNTING_DT,
':1',
':2',
':3',
E.FREEZE_OPRID,
E.FREEZE_DTTM,
E.FT_TYPE_FLG
FROM PS_CI_TNDR_CTL A,
PS_CI_PAY_TNDR B,
PS_CI_PAY C,
PS_CI_PAY_SEG D,
PS_CI_SA F,
PS_CI_SA_TYPE G,
PS_CI_ACCT_PER H,
PS_CI_PER_NAME I,
PS_CW_FT E
WHERE A.TNDR_CTL_ID = COALESCE(B.TNDR_CTL_ID,B.TNDR_CTL_ID)
AND A.TNDR_SOURCE_CD LIKE 'STK%'
AND B.PAY_EVENT_ID = COALESCE(C.PAY_EVENT_ID, C.PAY_EVENT_ID)
AND C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID)
AND D.PAY_SEG_ID = COALESCE(E.SIBLING_ID, E.SIBLING_ID)
AND COALESCE(E.ACCOUNTING_DT, E.ACCOUNTING_DT) BETWEEN '2003-10-01'
AND '2003-10-31'
AND COALESCE(E.FT_TYPE_FLG, E.FT_TYPE_FLG) IN ('PS', 'PX')
AND NOT EXISTS (SELECT 'X'
FROM PS_CW_INTERFACE_ID J
WHERE COALESCE(J.PAYOR_ACCT_ID, J.PAYOR_ACCT_ID) =
F.ACCT_ID)
AND E.SA_ID = COALESCE(F.SA_ID,F.SA_ID)
AND F.SA_TYPE_CD = COALESCE(G.SA_TYPE_CD,G.SA_TYPE_CD)
AND G.DEBT_CL_CD = 'NCIS'
AND F.ACCT_ID = COALESCE(H.ACCT_ID ,H.ACCT_ID)
AND H.PER_ID = COALESCE(I.PER_ID ,I.PER_ID)
ORDER BY 2Check out the query plan for both queries, and you are most likely to
see the difference.

I suppose the COALESCE(JoinColumn,JoinColumn) will be interpreted as a
non-optimizable expression. Because of this, the access path analysis
and join strategy will change. My guess is that it will change in favor
of loop joins. Also, the compilation time is most likely to drop
dramatically.

Gert-Jan

Jeff Roughgarden wrote:
> I am using a clever product called SQL Expert Pro to optimize
> problematic SQL statements. It works by generating syntactically
> identical variations of the original SQL, finding all unique execution
> plans, and batch testing them.
> Anyway, it generally turns out that a very odd change dramatically
> improves performance (for a PeopleSoft database on SQL Server 7). Run
> time goes from 52 seconds to 8 seconds.
> The change is to replace a join condition in a where clause with an
> odd equivalent COALESCE construct, e.g. replace WHERE C.PAY_ID =
> D.PAY_ID with WHERE C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID).
> Has anyone seen this sort of behavior before? Why would it be
> advantageous to COALESCE on the same field twice?
> The original and COALESCE'd versions are shown below. Does the fact
> that this is a nine-table join over large tables have anything to do
> with it?
> Original Query (52 sec)
> SELECT F.ACCT_ID,
> I.ENTITY_NAME,
> A.TNDR_SOURCE_CD,
> C.PAY_EVENT_ID,
> C.NON_CIS_NAME,
> C.NON_CIS_REF_NBR,
> C.NON_CIS_COMMENT,
> C.PAY_AMT,
> D.PAY_SEG_AMT,
> E.ACCOUNTING_DT,
> ':1',
> ':2',
> ':3',
> E.FREEZE_OPRID,
> E.FREEZE_DTTM,
> E.FT_TYPE_FLG
> FROM PS_CI_TNDR_CTL A,
> PS_CI_PAY_TNDR B,
> PS_CI_PAY C,
> PS_CI_PAY_SEG D,
> PS_CI_SA F,
> PS_CI_SA_TYPE G,
> PS_CI_ACCT_PER H,
> PS_CI_PER_NAME I,
> PS_CW_FT E
> WHERE A.TNDR_CTL_ID = B.TNDR_CTL_ID
> AND A.TNDR_SOURCE_CD LIKE 'STK%'
> AND B.PAY_EVENT_ID = C.PAY_EVENT_ID
> AND C.PAY_ID = D.PAY_ID
> AND D.PAY_SEG_ID = E.SIBLING_ID
> AND E.ACCOUNTING_DT BETWEEN '2003-10-01' AND '2003-10-31'
> AND E.FT_TYPE_FLG IN ('PS', 'PX')
> AND NOT EXISTS (SELECT 'X'
> FROM PS_CW_INTERFACE_ID J
> WHERE J.PAYOR_ACCT_ID = F.ACCT_ID)
> AND E.SA_ID = F.SA_ID
> AND F.SA_TYPE_CD = G.SA_TYPE_CD
> AND G.DEBT_CL_CD = 'NCIS'
> AND F.ACCT_ID = H.ACCT_ID
> AND H.PER_ID = I.PER_ID
> ORDER BY 2
> Optimized Query (8 sec)
> SELECT F.ACCT_ID,
> I.ENTITY_NAME,
> A.TNDR_SOURCE_CD,
> C.PAY_EVENT_ID,
> C.NON_CIS_NAME,
> C.NON_CIS_REF_NBR,
> C.NON_CIS_COMMENT,
> C.PAY_AMT,
> D.PAY_SEG_AMT,
> E.ACCOUNTING_DT,
> ':1',
> ':2',
> ':3',
> E.FREEZE_OPRID,
> E.FREEZE_DTTM,
> E.FT_TYPE_FLG
> FROM PS_CI_TNDR_CTL A,
> PS_CI_PAY_TNDR B,
> PS_CI_PAY C,
> PS_CI_PAY_SEG D,
> PS_CI_SA F,
> PS_CI_SA_TYPE G,
> PS_CI_ACCT_PER H,
> PS_CI_PER_NAME I,
> PS_CW_FT E
> WHERE A.TNDR_CTL_ID = COALESCE(B.TNDR_CTL_ID,B.TNDR_CTL_ID)
> AND A.TNDR_SOURCE_CD LIKE 'STK%'
> AND B.PAY_EVENT_ID = COALESCE(C.PAY_EVENT_ID, C.PAY_EVENT_ID)
> AND C.PAY_ID = COALESCE(D.PAY_ID, D.PAY_ID)
> AND D.PAY_SEG_ID = COALESCE(E.SIBLING_ID, E.SIBLING_ID)
> AND COALESCE(E.ACCOUNTING_DT, E.ACCOUNTING_DT) BETWEEN '2003-10-01'
> AND '2003-10-31'
> AND COALESCE(E.FT_TYPE_FLG, E.FT_TYPE_FLG) IN ('PS', 'PX')
> AND NOT EXISTS (SELECT 'X'
> FROM PS_CW_INTERFACE_ID J
> WHERE COALESCE(J.PAYOR_ACCT_ID, J.PAYOR_ACCT_ID) =
> F.ACCT_ID)
> AND E.SA_ID = COALESCE(F.SA_ID,F.SA_ID)
> AND F.SA_TYPE_CD = COALESCE(G.SA_TYPE_CD,G.SA_TYPE_CD)
> AND G.DEBT_CL_CD = 'NCIS'
> AND F.ACCT_ID = COALESCE(H.ACCT_ID ,H.ACCT_ID)
> AND H.PER_ID = COALESCE(I.PER_ID ,I.PER_ID)
> ORDER BY 2|||Hi Jeff,

That's pretty bizarre. I can only guess that the old style JOIN
creates a cartesian product with a lot of nulls everywhere, before the
WHERE filters the rows. And that coalesce somehow handles the nulls
better?

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?
It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>
|||I've used it (VMWare) to play with clustering to get a feel for the steps involved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared to increase timeouts
etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>
|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>|||I've used it (VMWare) to play with clustering to get a feel for the steps involved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared to increase timeouts
etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>> Hi,
>> I am using vmware product and i will be creating clusterd on vmware box and
>> cluster sql 2000. does anyone have done that and had any issue?
>>
>|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
--
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>> Hi,
>> I am using vmware product and i will be creating clusterd on vmware box
>> and
>> cluster sql 2000. does anyone have done that and had any issue?
>>
>

clustering SQL 2000

Hi,
I am using vmware product and i will be creating clusterd on vmware box and
cluster sql 2000. does anyone have done that and had any issue?It is close enough to MSCS clustering to make you think it will work. It is
different enough to drive you to drink.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
> Hi,
> I am using vmware product and i will be creating clusterd on vmware box
> and
> cluster sql 2000. does anyone have done that and had any issue?
>|||I've used it (VMWare) to play with clustering to get a feel for the steps in
volved etc. I was handed
an image (two images) with the OS clustered already, but I heard that VMWare
has good info on how to
set up such. For "trying it out" purposes it worked fine for me (be prepared
to increase timeouts
etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
is different enough to
> drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>|||As Geoff says it's close enough but it never works out the same.
In my case, install scenarios for applying SP4 on a SP4 and RTM (after
failure) cluster which work on VMWare don't on real hardware but it's good
enough to learn the steps.
Nik Marshall-Blank MCSD/MCDBA
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:ON0y6R9sFHA.3188@.TK2MSFTNGP14.phx.gbl...
> It is close enough to MSCS clustering to make you think it will work. It
> is different enough to drive you to drink.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Pooja" <Pooja@.discussions.microsoft.com> wrote in message
> news:D5D1EF4F-577F-4D86-B3D3-3BAFD95A1A8D@.microsoft.com...
>

Clustering and Hyperthreading

Hi,
We had a problem with a clustered sql box a month or so ago and contacted MS product support. Anyway, we got a patch which appears to have fixed our problem. However, one thing was curious. I didn't speak to Moft but the person who did said MS said NOT to
turn hyperthreading on SQL clusters on! Now that shocked me. Does anyone know why? We turned it off to see whether it was reponsible for some performance issues and results were inconclusive. So we were going to turn it back on. We were strongly advised
not to do so. So does anyone know why or could point me in the right direction? I've never heard this mentioned before.
Thanks
DaveK
http://www.sqlporn.co.uk
The only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.
Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.
So, how many processors are in your nodes?
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
> Hi,
> We had a problem with a clustered sql box a month or so ago and contacted
MS product support. Anyway, we got a patch which appears to have fixed our
problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||Not exactly.
I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> The only time you turn it off, as far as I know of, is when you have the
max
> number of processors installed already.
> Example: Windows XP support only 2 processors, if you have two already,
turn[vbcol=seagreen]
> off Hyperthreading.
> Example: Windows Enterprise supports 8 processors, if you have 8 already,
> turn off Hyperthreading.
> So, how many processors are in your nodes?
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
contacted
> MS product support. Anyway, we got a patch which appears to have fixed our
> problem. However, one thing was curious. I didn't speak to Moft but the
> person who did said MS said NOT to turn hyperthreading on SQL clusters on!
> Now that shocked me. Does anyone know why? We turned it off to see whether
> it was reponsible for some performance issues and results were
inconclusive.
> So we were going to turn it back on. We were strongly advised not to do
so.
> So does anyone know why or could point me in the right direction? I've
never
> heard this mentioned before.
>
|||HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> Not exactly.
> I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16
virtual
> processors just fine. I believe that Service Pack 2 contained the code to
> fix the virtual/physical processor count issue. Build 816 and 910 (post
SP3
> hotfixes) contain some code to improve performance on Hyperthreaded
systems.[vbcol=seagreen]
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> max
> turn
already,[vbcol=seagreen]
> contacted
our[vbcol=seagreen]
on![vbcol=seagreen]
whether
> inconclusive.
> so.
> never
>
|||I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eQtgTU$REHA.3300@.TK2MSFTNGP09.phx.gbl...
> I agree with the XP recommendation. For a dedicated SQL server, I see a
> significant difference between off and on. (Unisys ES7000 8x2.8GHz)
Average[vbcol=seagreen]
> CPU 26% on, 40% off.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> have
> see
code[vbcol=seagreen]
> to
(post[vbcol=seagreen]
> the
> already,
fixed[vbcol=seagreen]
> the
clusters[vbcol=seagreen]
> do
I've
>
|||Hopefully your not trying to run a cluster on WinXP anyway<g>.
Andrew J. Kelly
SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Dave,
We put 910 into place less than a week ago when we found out why we had to have it: previously - 818 - SQL 2000 allows all logins access to the system before tempdb comes online. Well, we are a web-based company. We are running an active/active SQL cluste
r on a Win2K3 server ent ed cluster with HT enabled. So, I'm concerned to hear your comment about this option. Now, yesterday we see odd behavior on the system. Specifically the following error:
Transaction (Process ID 350) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
Sure, we've had deadlocks before but, not this type "communication buffer".
Another thing we've noticed is that if a process wants to go parallel, we've seen it go to 49 degrees of parallelism. You can imagine all of our CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't make the rest of the processes hap
py.
We only have SQL Server running on these servers. All web servers (over 40) can hit this cluster at any given time.
Would love to have a discussion with you. Microsoft told us that we are only the second company that reported needing the 910 patch. So, I'm expecting you are the other company. Are you web-based? Or, have lots of web server activity against your database
servers?
We've seen some other odd behavior as well. But, this e-mail is getting lengthy.
I see you are in the UK. We are in the USA.
Your reply is appreciated,
Doug
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||910 was built specifically for us after we reported a bug via Unisys.
Technically the build was for Unisys under their Premier agreement, but we
were the end customer.
I have also seen extra-high parallelism. I have limited the degree of
parallelism intentionally since I prefer higher response to the many
transactional queries rather than the few analysis-type queries.
Yes, we are very web-based. About 85-90% of our pages are .Net based. We
are the number one job board in the US (we recently passed Monster).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doug Thomas" <dougt@.ecollege.com> wrote in message
news:uRIk$jTVEHA.3332@.tk2msftngp13.phx.gbl...
> Dave,
> We put 910 into place less than a week ago when we found out why we had to
have it: previously - 818 - SQL 2000 allows all logins access to the system
before tempdb comes online. Well, we are a web-based company. We are running
an active/active SQL cluster on a Win2K3 server ent ed cluster with HT
enabled. So, I'm concerned to hear your comment about this option. Now,
yesterday we see odd behavior on the system. Specifically the following
error:
> Transaction (Process ID 350) was deadlocked on lock | communication
buffer resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..
> Sure, we've had deadlocks before but, not this type "communication
buffer".
> Another thing we've noticed is that if a process wants to go parallel,
we've seen it go to 49 degrees of parallelism. You can imagine all of our
CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't
make the rest of the processes happy.
> We only have SQL Server running on these servers. All web servers (over
40) can hit this cluster at any given time.
> Would love to have a discussion with you. Microsoft told us that we are
only the second company that reported needing the 910 patch. So, I'm
expecting you are the other company. Are you web-based? Or, have lots of web
server activity against your database servers?
> We've seen some other odd behavior as well. But, this e-mail is getting
lengthy.
> I see you are in the UK. We are in the USA.
> Your reply is appreciated,
> Doug
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Clustering and Hyperthreading

Hi,
We had a problem with a clustered sql box a month or so ago and contacted MS product support. Anyway, we got a patch which appears to have fixed our problem. However, one thing was curious. I didn't speak to Moft but the person who did said MS said NOT to
turn hyperthreading on SQL clusters on! Now that shocked me. Does anyone know why? We turned it off to see whether it was reponsible for some performance issues and results were inconclusive. So we were going to turn it back on. We were strongly advised
not to do so. So does anyone know why or could point me in the right direction? I've never heard this mentioned before.
Thanks
DaveK
http://www.sqlporn.co.uk
The only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.
Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.
So, how many processors are in your nodes?
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
> Hi,
> We had a problem with a clustered sql box a month or so ago and contacted
MS product support. Anyway, we got a patch which appears to have fixed our
problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||Not exactly.
I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> The only time you turn it off, as far as I know of, is when you have the
max
> number of processors installed already.
> Example: Windows XP support only 2 processors, if you have two already,
turn[vbcol=seagreen]
> off Hyperthreading.
> Example: Windows Enterprise supports 8 processors, if you have 8 already,
> turn off Hyperthreading.
> So, how many processors are in your nodes?
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
contacted
> MS product support. Anyway, we got a patch which appears to have fixed our
> problem. However, one thing was curious. I didn't speak to Moft but the
> person who did said MS said NOT to turn hyperthreading on SQL clusters on!
> Now that shocked me. Does anyone know why? We turned it off to see whether
> it was reponsible for some performance issues and results were
inconclusive.
> So we were going to turn it back on. We were strongly advised not to do
so.
> So does anyone know why or could point me in the right direction? I've
never
> heard this mentioned before.
>
|||Thanks for the replies. The nodes are Dual Xeon's. So at the moment I'm running it as a dual. This is on Windows 2000 Advanced server. It's SQL 2k sp3 (8.00.919).
Thanks
DaveK
http://www.sqlporn.co.uk
|||HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> Not exactly.
> I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16
virtual
> processors just fine. I believe that Service Pack 2 contained the code to
> fix the virtual/physical processor count issue. Build 816 and 910 (post
SP3
> hotfixes) contain some code to improve performance on Hyperthreaded
systems.[vbcol=seagreen]
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> max
> turn
already,[vbcol=seagreen]
> contacted
our[vbcol=seagreen]
on![vbcol=seagreen]
whether
> inconclusive.
> so.
> never
>
|||I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eQtgTU$REHA.3300@.TK2MSFTNGP09.phx.gbl...
> I agree with the XP recommendation. For a dedicated SQL server, I see a
> significant difference between off and on. (Unisys ES7000 8x2.8GHz)
Average[vbcol=seagreen]
> CPU 26% on, 40% off.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> have
> see
code[vbcol=seagreen]
> to
(post[vbcol=seagreen]
> the
> already,
fixed[vbcol=seagreen]
> the
clusters[vbcol=seagreen]
> do
I've
>
|||Ok thanks. I have penned an email to the guy who spoke to MS to get some more information on why they are recommending this. I will pass on if it proves useful.
Thanks again
DaveK
http://www.sqlporn.co.uk
|||Hopefully your not trying to run a cluster on WinXP anyway<g>.
Andrew J. Kelly
SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>

Clustering and Hyperthreading

Hi,
We had a problem with a clustered sql box a month or so ago and contacted MS
product support. Anyway, we got a patch which appears to have fixed our pro
blem. However, one thing was curious. I didn't speak to Moft but the person
who did said MS said NOT to
turn hyperthreading on SQL clusters on! Now that shocked me. Does anyone kno
w why? We turned it off to see whether it was reponsible for some performanc
e issues and results were inconclusive. So we were going to turn it back on.
We were strongly advised
not to do so. So does anyone know why or could point me in the right directi
on? I've never heard this mentioned before.
Thanks
DaveK
http://www.sqlporn.co.ukThe only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.
Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.
So, how many processors are in your nodes?
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
> Hi,
> We had a problem with a clustered sql box a month or so ago and contacted
MS product support. Anyway, we got a patch which appears to have fixed our
problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.
> Thanks
> DaveK
> http://www.sqlporn.co.uk|||Not exactly.
I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> The only time you turn it off, as far as I know of, is when you have the
max
> number of processors installed already.
> Example: Windows XP support only 2 processors, if you have two already,
turn
> off Hyperthreading.
> Example: Windows Enterprise supports 8 processors, if you have 8 already,
> turn off Hyperthreading.
> So, how many processors are in your nodes?
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
contacted[vbcol=seagreen]
> MS product support. Anyway, we got a patch which appears to have fixed our
> problem. However, one thing was curious. I didn't speak to Moft but the
> person who did said MS said NOT to turn hyperthreading on SQL clusters on!
> Now that shocked me. Does anyone know why? We turned it off to see whether
> it was reponsible for some performance issues and results were
inconclusive.
> So we were going to turn it back on. We were strongly advised not to do
so.
> So does anyone know why or could point me in the right direction? I've
never
> heard this mentioned before.
>|||Thanks for the replies. The nodes are Dual Xeon's. So at the moment I'm runn
ing it as a dual. This is on Windows 2000 Advanced server. It's SQL 2k sp3 (
8.00.919).
Thanks
DaveK
http://www.sqlporn.co.uk|||HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> Not exactly.
> I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16
virtual
> processors just fine. I believe that Service Pack 2 contained the code to
> fix the virtual/physical processor count issue. Build 816 and 910 (post
SP3
> hotfixes) contain some code to improve performance on Hyperthreaded
systems.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> max
> turn
already,[vbcol=seagreen]
> contacted
our[vbcol=seagreen]
on![vbcol=seagreen]
whether[vbcol=seagreen]
> inconclusive.
> so.
> never
>|||I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do[vbcol=seagreen]
>|||Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eQtgTU$REHA.3300@.TK2MSFTNGP09.phx.gbl...
> I agree with the XP recommendation. For a dedicated SQL server, I see a
> significant difference between off and on. (Unisys ES7000 8x2.8GHz)
Average
> CPU 26% on, 40% off.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> have
> see
code[vbcol=seagreen]
> to
(post[vbcol=seagreen]
> the
> already,
fixed[vbcol=seagreen]
> the
clusters[vbcol=seagreen]
> do
I've[vbcol=seagreen]
>|||Ok thanks. I have penned an email to the guy who spoke to MS to get some mor
e information on why they are recommending this. I will pass on if it proves
useful.
Thanks again
DaveK
http://www.sqlporn.co.uk|||Hopefully your not trying to run a cluster on WinXP anyway<g>.
Andrew J. Kelly
SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do[vbcol=seagreen]
>

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nick
The only case I can think of is when the NC covers the query and you have low selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than the CL pages for the
corresponding data-range over the column. However, I suspect that this is the PK and possibly even
some surrogate key, so an NC index on this column will probably not cover that many queries.
I've seen stranger things with commercial software databases... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick
|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick
|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
will pick the NC index over the most narrow column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...[vbcol=seagreen]
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:
|||Tibor Karaszi wrote:
>
> which is a case where the index covers the query, but possibly an important example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan
|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nickThe only case I can think of is when the NC covers the query and you have lo
w selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than t
he CL pages for the
corresponding data-range over the column. However, I suspect that this is th
e PK and possibly even
some surrogate key, so an NC index on this column will probably not cover th
at many queries.
I've seen stranger things with commercial software databases... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important
example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, a
nd probably SQL Server
will pick the NC index over the most narrow column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...[vbcol=seagreen]
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:|||Tibor Karaszi wrote:
>
> which is a case where the index covers the query, but possibly an importan
t example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query,
and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nickThe only case I can think of is when the NC covers the query and you have low selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than the CL pages for the
corresponding data-range over the column. However, I suspect that this is the PK and possibly even
some surrogate key, so an NC index on this column will probably not cover that many queries.
I've seen stranger things with commercial software databases... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
will pick the NC index over the most narrow column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:
>> I have a SS 2000 database that was created from a purchased, commercial
>> software product. I.e. I had no input into the design. This is your typical
>> OLTP and DSS database. I notice on many tables that there is a clustered
>> index and non-clustered index on the same column. I feel like this is a
>> mistake and I should delete every non-clustered index that has a clustered
>> index, assuming the clustered index is the primary key. Is it ever
>> advantageous to have both a CIX and nonCIX on the same field so that the
>> optimizer can use one for range/sorts (CIX) and the other for single record
>> lookup (nonCIX)?
>> TIA - nick|||Tibor Karaszi wrote:
> > In addition to Tibor's reply: such an index can also help if there is no
> > other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> which is a case where the index covers the query, but possibly an important example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
>> > In addition to Tibor's reply: such an index can also help if there is no
>> > other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
>> which is a case where the index covers the query, but possibly an important example worth
>> pointing
>> out explicitly. :-)
>> Also, it can be worth to mention that any NC index will cover above query, and probably SQL
>> Server
>> will pick the NC index over the most narrow column.
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan