Is there a reliable white paper for clustering sql2k on win2k3. Ive already
got the OS clustered.
SQL2K SP3
TIA, ChrisRChrisR wrote:
> Is there a reliable white paper for clustering sql2k on win2k3. Ive
> already got the OS clustered.
Some of these might help:
http://www.microsoft.com/resources/...r />
1261.mspx
http://support.microsoft.com/defaul...blurb101802.asp
http://msdn.microsoft.com/library/d...
ering_2icn.asp
David Gugick
Imceda Software
www.imceda.com|||This is great information. Does anyone have any updated information on
setting up SQL Server 2000 in a Windows 2003 environment?
Just wondering if 2003 brings anything more to the table over Windows Server
2000.
Thanks!
Tony
"David Gugick" wrote:
> ChrisR wrote:
> Some of these might help:
> http://www.microsoft.com/resources/.../>
/c1261.mspx
> http://support.microsoft.com/defaul...blurb101802.asp
> http://msdn.microsoft.com/library/d...tering_2icn.asp
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Showing posts with label clustered. Show all posts
Showing posts with label clustered. Show all posts
Thursday, March 29, 2012
cluster SSAS and SQL
Hi,
We have a default clusterd SQL server 2005 with sp2 installed in one cluster
group and then I have installed a default SSAS clustered instance in another
cluster group and it works fine (can failover and user can connect). However,
when I applying the sp2 to SSAS instance (with SSAS checked only) and it
failed; it not noly just failed but also take down my default clustered sql
server! We have to call MS to get our server up. It seems that, somehow,
registry for default sql server got messed up by SSAS sp2. My question are:
1.Is it possible or supported to install a default SQL server and a default
Analysis server on a cluster (each one in its own cluster group) with sp2;
2.why applying sp2 on a default AD instance in one cluster group affect a
default sql instance in another cluster group.
thanks much in advance.
Zack.
The answer is that SQL services share some common components. Plus, the
installer has some generic setup logic. Finally, are you using the same
service account for both services? If so, then permissions can get hosed
during a broken update.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"dp" <dp@.discussions.microsoft.com> wrote in message
news:90BAC728-916F-4497-B293-55920962EDE8@.microsoft.com...
> Hi,
> We have a default clusterd SQL server 2005 with sp2 installed in one
> cluster
> group and then I have installed a default SSAS clustered instance in
> another
> cluster group and it works fine (can failover and user can connect).
> However,
> when I applying the sp2 to SSAS instance (with SSAS checked only) and it
> failed; it not noly just failed but also take down my default clustered
> sql
> server! We have to call MS to get our server up. It seems that, somehow,
> registry for default sql server got messed up by SSAS sp2. My question
> are:
> 1. Is it possible or supported to install a default SQL server and a
> default
> Analysis server on a cluster (each one in its own cluster group) with sp2;
> 2. why applying sp2 on a default AD instance in one cluster group affect a
> default sql instance in another cluster group.
> thanks much in advance.
> Zack.
>
|||Zack, were you ever able to upgrade your SSAS in the cluster?
We are having the same or similar issues. We upgraded our cluster from SP1
to SP2. The SQL Server components upgraded to SP2, but the SSAS failed and
is still at SP1. After the upgrade one of our clustered instances did not
come back online. Error logs read something like, "...Cluster IP/Network
resource already in use..."
Well, I called MS because our production SQL 2005 cluster was hosed. The
issue, reg key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microso ft SQL
Server\MSSQL.1\Cluster\ClusterName" had the incorrect instance name for the
MSSQL.1 instance (in our case). We had to run some cluster commands to
remove the MSCS checkpoint, changed the key value to the correct instance
name, then reapply MSCS checkpoint.
This got our SQL Server instance back online. But we still can't update
SSAS to SP2. Maybe the two issues are not related, but I have a gut feeling
they are. The incorrect 'ClusterName' value happened to be the instance name
for our SSAS.
I still have the MS support ticket open trying to determine if the cluster
registry keys are in the correct state on both nodes.
Anyone else having issues with failover cluster anaylsis server upgrade to
SP2?
"dp" wrote:
> Hi,
> We have a default clusterd SQL server 2005 with sp2 installed in one cluster
> group and then I have installed a default SSAS clustered instance in another
> cluster group and it works fine (can failover and user can connect). However,
> when I applying the sp2 to SSAS instance (with SSAS checked only) and it
> failed; it not noly just failed but also take down my default clustered sql
> server! We have to call MS to get our server up. It seems that, somehow,
> registry for default sql server got messed up by SSAS sp2. My question are:
> 1.Is it possible or supported to install a default SQL server and a default
> Analysis server on a cluster (each one in its own cluster group) with sp2;
> 2.why applying sp2 on a default AD instance in one cluster group affect a
> default sql instance in another cluster group.
> thanks much in advance.
> Zack.
>
We have a default clusterd SQL server 2005 with sp2 installed in one cluster
group and then I have installed a default SSAS clustered instance in another
cluster group and it works fine (can failover and user can connect). However,
when I applying the sp2 to SSAS instance (with SSAS checked only) and it
failed; it not noly just failed but also take down my default clustered sql
server! We have to call MS to get our server up. It seems that, somehow,
registry for default sql server got messed up by SSAS sp2. My question are:
1.Is it possible or supported to install a default SQL server and a default
Analysis server on a cluster (each one in its own cluster group) with sp2;
2.why applying sp2 on a default AD instance in one cluster group affect a
default sql instance in another cluster group.
thanks much in advance.
Zack.
The answer is that SQL services share some common components. Plus, the
installer has some generic setup logic. Finally, are you using the same
service account for both services? If so, then permissions can get hosed
during a broken update.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"dp" <dp@.discussions.microsoft.com> wrote in message
news:90BAC728-916F-4497-B293-55920962EDE8@.microsoft.com...
> Hi,
> We have a default clusterd SQL server 2005 with sp2 installed in one
> cluster
> group and then I have installed a default SSAS clustered instance in
> another
> cluster group and it works fine (can failover and user can connect).
> However,
> when I applying the sp2 to SSAS instance (with SSAS checked only) and it
> failed; it not noly just failed but also take down my default clustered
> sql
> server! We have to call MS to get our server up. It seems that, somehow,
> registry for default sql server got messed up by SSAS sp2. My question
> are:
> 1. Is it possible or supported to install a default SQL server and a
> default
> Analysis server on a cluster (each one in its own cluster group) with sp2;
> 2. why applying sp2 on a default AD instance in one cluster group affect a
> default sql instance in another cluster group.
> thanks much in advance.
> Zack.
>
|||Zack, were you ever able to upgrade your SSAS in the cluster?
We are having the same or similar issues. We upgraded our cluster from SP1
to SP2. The SQL Server components upgraded to SP2, but the SSAS failed and
is still at SP1. After the upgrade one of our clustered instances did not
come back online. Error logs read something like, "...Cluster IP/Network
resource already in use..."
Well, I called MS because our production SQL 2005 cluster was hosed. The
issue, reg key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microso ft SQL
Server\MSSQL.1\Cluster\ClusterName" had the incorrect instance name for the
MSSQL.1 instance (in our case). We had to run some cluster commands to
remove the MSCS checkpoint, changed the key value to the correct instance
name, then reapply MSCS checkpoint.
This got our SQL Server instance back online. But we still can't update
SSAS to SP2. Maybe the two issues are not related, but I have a gut feeling
they are. The incorrect 'ClusterName' value happened to be the instance name
for our SSAS.
I still have the MS support ticket open trying to determine if the cluster
registry keys are in the correct state on both nodes.
Anyone else having issues with failover cluster anaylsis server upgrade to
SP2?
"dp" wrote:
> Hi,
> We have a default clusterd SQL server 2005 with sp2 installed in one cluster
> group and then I have installed a default SSAS clustered instance in another
> cluster group and it works fine (can failover and user can connect). However,
> when I applying the sp2 to SSAS instance (with SSAS checked only) and it
> failed; it not noly just failed but also take down my default clustered sql
> server! We have to call MS to get our server up. It seems that, somehow,
> registry for default sql server got messed up by SSAS sp2. My question are:
> 1.Is it possible or supported to install a default SQL server and a default
> Analysis server on a cluster (each one in its own cluster group) with sp2;
> 2.why applying sp2 on a default AD instance in one cluster group affect a
> default sql instance in another cluster group.
> thanks much in advance.
> Zack.
>
Cluster SQL 7.0 in W2KAdv environment
Hi!
This is the scenario:
* Windows 2000 Advanced Server clustered with 2 nodes
After the installation of SQLServer in the first node, does not appear
the 'SQL Server Failover Cluster' or 'Failover Cluster Wizard' in the
program files menu, and the option 'MSDTC Administrative Control' is
in the menu but the file 'dac.exe' isn't installed.
I don't know which is the problem, because the MSDTC resource in the
cluster is on-line and managed by the firt node, after execute the
command comclust. I see the articles in Microsoft KB (290624, 243204,
), but I don't found a solutions. There are a special SQL 7.0 version
for clustering
Thanks!!
Are you sure you installed the Enterprise Edition?
Look at the SQL Server errorlog and verify that you installed the
Enterprise Edition. BAsed on what you are saying about the Failover Wizard
not being there I am thinking that you did not.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I fix this problem by installing the Enterprise Edition (really stupid
), and now the Failover Wizard it's ok, but the 'MSDTC
Administrative Console' still not work. I don't know the reason why
don't install dac.exe. I'll try to install mdac2.8
Thanks for your post.
Regards,
Ren
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<CxzmcDlJEHA.4016@.cpmsftngxa10.phx.gbl>...
> Are you sure you installed the Enterprise Edition?
> Look at the SQL Server errorlog and verify that you installed the
> Enterprise Edition. BAsed on what you are saying about the Failover Wizard
> not being there I am thinking that you did not.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
This is the scenario:
* Windows 2000 Advanced Server clustered with 2 nodes
After the installation of SQLServer in the first node, does not appear
the 'SQL Server Failover Cluster' or 'Failover Cluster Wizard' in the
program files menu, and the option 'MSDTC Administrative Control' is
in the menu but the file 'dac.exe' isn't installed.
I don't know which is the problem, because the MSDTC resource in the
cluster is on-line and managed by the firt node, after execute the
command comclust. I see the articles in Microsoft KB (290624, 243204,
), but I don't found a solutions. There are a special SQL 7.0 version
for clustering
Thanks!!
Are you sure you installed the Enterprise Edition?
Look at the SQL Server errorlog and verify that you installed the
Enterprise Edition. BAsed on what you are saying about the Failover Wizard
not being there I am thinking that you did not.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I fix this problem by installing the Enterprise Edition (really stupid
), and now the Failover Wizard it's ok, but the 'MSDTCAdministrative Console' still not work. I don't know the reason why
don't install dac.exe. I'll try to install mdac2.8
Thanks for your post.
Regards,
Ren
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<CxzmcDlJEHA.4016@.cpmsftngxa10.phx.gbl>...
> Are you sure you installed the Enterprise Edition?
> Look at the SQL Server errorlog and verify that you installed the
> Enterprise Edition. BAsed on what you are saying about the Failover Wizard
> not being there I am thinking that you did not.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
Tuesday, March 27, 2012
Cluster ratio? Cluster factor?
MS SQL Server 2000
let's say there is a table ORDERS with a clustered index on
(order_date, some other column). Also there is a non-clustered index on
shipment_date. Since most orders are shipped within 3 business days,
the data is stored almost ordered by shipment_date.
Most rows for the same shipment date are stored on adjacent data pages.
There is another index on zipcode, which does not correlate with order
date at all. Is there anything I can read from system views to tell the
difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
TIAHi
You probably need DBCC SHOWCONTIG, you can check out the documentation in
Books online. If you want more information on this sort of thing you may als
o
want to read "Inside SQL Server 2000" by Kalen Delaney ISBN 0-7356-0998-5 an
d
Ken Henderson's "The Guru's guide to SQL Server Architecture and
Internals" ISBN 0-201-7004706
John
"ford_desperado@.yahoo.com" wrote:
> MS SQL Server 2000
> let's say there is a table ORDERS with a clustered index on
> (order_date, some other column). Also there is a non-clustered index on
> shipment_date. Since most orders are shipped within 3 business days,
> the data is stored almost ordered by shipment_date.
> Most rows for the same shipment date are stored on adjacent data pages.
> There is another index on zipcode, which does not correlate with order
> date at all. Is there anything I can read from system views to tell the
> difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
> TIA
>
let's say there is a table ORDERS with a clustered index on
(order_date, some other column). Also there is a non-clustered index on
shipment_date. Since most orders are shipped within 3 business days,
the data is stored almost ordered by shipment_date.
Most rows for the same shipment date are stored on adjacent data pages.
There is another index on zipcode, which does not correlate with order
date at all. Is there anything I can read from system views to tell the
difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
TIAHi
You probably need DBCC SHOWCONTIG, you can check out the documentation in
Books online. If you want more information on this sort of thing you may als
o
want to read "Inside SQL Server 2000" by Kalen Delaney ISBN 0-7356-0998-5 an
d
Ken Henderson's "The Guru's guide to SQL Server Architecture and
Internals" ISBN 0-201-7004706
John
"ford_desperado@.yahoo.com" wrote:
> MS SQL Server 2000
> let's say there is a table ORDERS with a clustered index on
> (order_date, some other column). Also there is a non-clustered index on
> shipment_date. Since most orders are shipped within 3 business days,
> the data is stored almost ordered by shipment_date.
> Most rows for the same shipment date are stored on adjacent data pages.
> There is another index on zipcode, which does not correlate with order
> date at all. Is there anything I can read from system views to tell the
> difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
> TIA
>
Sunday, March 25, 2012
Cluster not visible to external IP
Hello
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
ThanksVPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
ThanksVPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>
Cluster not visible to external IP
Hello
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
ThanksVPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>> Hello
>> We have SQL 2000 with two servers clustered (one is a failover) to have
>> one named SQL server. This is fine internally, but external VPN
>> connections can't reach this server (all other machines are reachable).
>> Permissions not an issue.
>> Anyone have any ideas on where to start/look?
>> Thanks
>|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>> VPN connections will have to be enabled to the underlying node IPs, not
>> the virtual server IPs.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "PB" <hoopitup@.gmail.com> wrote in message
>> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>> Hello
>> We have SQL 2000 with two servers clustered (one is a failover) to have
>> one named SQL server. This is fine internally, but external VPN
>> connections can't reach this server (all other machines are reachable).
>> Permissions not an issue.
>> Anyone have any ideas on where to start/look?
>> Thanks
>>
>sqlsql
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
ThanksVPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>> Hello
>> We have SQL 2000 with two servers clustered (one is a failover) to have
>> one named SQL server. This is fine internally, but external VPN
>> connections can't reach this server (all other machines are reachable).
>> Permissions not an issue.
>> Anyone have any ideas on where to start/look?
>> Thanks
>|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>> VPN connections will have to be enabled to the underlying node IPs, not
>> the virtual server IPs.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "PB" <hoopitup@.gmail.com> wrote in message
>> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>> Hello
>> We have SQL 2000 with two servers clustered (one is a failover) to have
>> one named SQL server. This is fine internally, but external VPN
>> connections can't reach this server (all other machines are reachable).
>> Permissions not an issue.
>> Anyone have any ideas on where to start/look?
>> Thanks
>>
>sqlsql
Cluster not visible to external IP
Hello
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
Thanks
VPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>
|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>
|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>
We have SQL 2000 with two servers clustered (one is a failover) to have one
named SQL server. This is fine internally, but external VPN connections
can't reach this server (all other machines are reachable). Permissions not
an issue.
Anyone have any ideas on where to start/look?
Thanks
VPN connections will have to be enabled to the underlying node IPs, not the
virtual server IPs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"PB" <hoopitup@.gmail.com> wrote in message
news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
> Hello
> We have SQL 2000 with two servers clustered (one is a failover) to have
> one named SQL server. This is fine internally, but external VPN
> connections can't reach this server (all other machines are reachable).
> Permissions not an issue.
> Anyone have any ideas on where to start/look?
> Thanks
>
|||We are using the cluster name rather than IP. Will try the actual node name
now, but the only thing then is there is no failover protection.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
> VPN connections will have to be enabled to the underlying node IPs, not
> the virtual server IPs.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "PB" <hoopitup@.gmail.com> wrote in message
> news:OdM$Zy47FHA.1148@.tk2msftngp13.phx.gbl...
>
|||When a client (in this case the firewall) receives an IP packet from a
virtual instance, the sender IP address is the node's physical address, not
the virtual address. You can still connect via virtual IP for name and ARP
resolution, but you will have to enable VPN access via the physical node
addresses.
GNH
"PB" <hoopitup@.gmail.com> wrote in message
news:eUtFO347FHA.1248@.TK2MSFTNGP14.phx.gbl...
> We are using the cluster name rather than IP. Will try the actual node
> name now, but the only thing then is there is no failover protection.
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OkktM047FHA.3224@.TK2MSFTNGP09.phx.gbl...
>
Thursday, March 22, 2012
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
--It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
> > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> >
> > "Avoid creating clustered indexes on identity columns.
> > Clustered indexes perform better on range queries,
> > such as a date. When you have a clustered index on
> > an identity column, you risk your data receiving hot
> > spots, which are caused by many people updating the
> > same data page."
> >
> > I would like to know what is the consenus in this forum on this.
> >
> > --
> >
> >
> >
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> > I prefer the exact opposite. I think hot spots (up to a point) are a
good
> > thing. Hot spotting data gives the cache manager something to grab hold
> of.
> > Clustering on an Identity column also guarantees inserts are at the end
of
> > the table, thus avoiding the dreaded page split. Hot spotting was very
> bad
> > under older versions of SQL, but SQL 2000 copes with it pretty well.
> > Clustering on a narrow unique key also has benefits, especially if you
> have
> > a lot of non-clustered indexes on a table.
> >
> > Having said that, I do not use the identity column as my Primary Key. I
> > create a PK constraint using a non-clustered index on a natural key.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> > news:2unifgF2c9ng0U1@.uni-berlin.de...
> > > According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> > >
> > > "Avoid creating clustered indexes on identity columns.
> > > Clustered indexes perform better on range queries,
> > > such as a date. When you have a clustered index on
> > > an identity column, you risk your data receiving hot
> > > spots, which are caused by many people updating the
> > > same data page."
> > >
> > > I would like to know what is the consenus in this forum on this.
> > >
> > > --
> > >
> > >
> > >
> >
> >
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of[vbcol=seagreen]
> bad
> have
>|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
cluster index and identity
According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
"Avoid creating clustered indexes on identity columns.
Clustered indexes perform better on range queries,
such as a date. When you have a clustered index on
an identity column, you risk your data receiving hot
spots, which are caused by many people updating the
same data page."
I would like to know what is the consenus in this forum on this.
It really depends on your usage; there is no single silver bullet answer.
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||I prefer the exact opposite. I think hot spots (up to a point) are a good
thing. Hot spotting data gives the cache manager something to grab hold of.
Clustering on an Identity column also guarantees inserts are at the end of
the table, thus avoiding the dreaded page split. Hot spotting was very bad
under older versions of SQL, but SQL 2000 copes with it pretty well.
Clustering on a narrow unique key also has benefits, especially if you have
a lot of non-clustered indexes on a table.
Having said that, I do not use the identity column as my Primary Key. I
create a PK constraint using a non-clustered index on a natural key.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:2unifgF2c9ng0U1@.uni-berlin.de...
> According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
> "Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
> I would like to know what is the consenus in this forum on this.
> --
>
>
|||And I fall exactly opposite of Geof... Here are my reasons... When the
table performance is targeted for quick queries, as opposed to fast inserts
( which is most of the time in my experiecnce)... Then don't clustere on the
identity column, cluster on a Where clause key becuase..
clustered indexes are useful when
you are returning many rows in your queries
there are many duplicate clustered keys ( since they are stored together
they will be returned together)
you are doing many range searches
you are doing tons or order by on the clustered key...
Are any of these true when you use an identity column? How many times will
you do select * from emp where empid > 50 ,,, or empid between 100 and 500
(rarely if ever)... You might be ordering by empid... But for the most part,
if you cluster on the identity column you will NOT be able to benefit from
the nice things that clustering can bring to a select.. So most of the time
I cluster on a WHERE clause column which gives the biggest bang ( deciding
which one is sometimes tough tho.)
Here is the exception:
IF the primary performance item for this table is INSERT SPEED, then the
best way to get that to be fast is to cluster on the identity column. This
cuts page allocation in half, because of a special algorythm used in the
Engine... THe inserts will be fast, but the cost will be select speed...
Reasonable people differ here... You should choose you method ( in my
opinion) based on the performance priorities for the table..
hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
> I prefer the exact opposite. I think hot spots (up to a point) are a good
> thing. Hot spotting data gives the cache manager something to grab hold
of.
> Clustering on an Identity column also guarantees inserts are at the end of
> the table, thus avoiding the dreaded page split. Hot spotting was very
bad
> under older versions of SQL, but SQL 2000 copes with it pretty well.
> Clustering on a narrow unique key also has benefits, especially if you
have
> a lot of non-clustered indexes on a table.
> Having said that, I do not use the identity column as my Primary Key. I
> create a PK constraint using a non-clustered index on a natural key.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "rkusenet" <rkusenet@.sympatico.ca> wrote in message
> news:2unifgF2c9ng0U1@.uni-berlin.de...
>
|||Good point. Range queries on the clustered index are going to be faster if
you cluster on a natural key. I have one severely de-normalized
transactional table that I had to cluster on the most used foreign key to
get any decent performance. On a properly normalized schema, I find that
more complex queries run faster clustering on an Identity column. It allows
very fast index comparisons so you can leverage multiple indexes to create a
'virtual' covered index. It also increases index density so you get more
effective use of cache memory.
As always, your application will determine your specific needs. Test, test,
and test again so you know what your application is doing to the system. Be
ready to make changes and measure again so you can make real comparisons and
recommendations.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23FdcEtNwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> And I fall exactly opposite of Geof... Here are my reasons... When the
> table performance is targeted for quick queries, as opposed to fast
inserts
> ( which is most of the time in my experiecnce)... Then don't clustere on
the
> identity column, cluster on a Where clause key becuase..
> clustered indexes are useful when
> you are returning many rows in your queries
> there are many duplicate clustered keys ( since they are stored together
> they will be returned together)
> you are doing many range searches
> you are doing tons or order by on the clustered key...
> Are any of these true when you use an identity column? How many times will
> you do select * from emp where empid > 50 ,,, or empid between 100 and 500
> (rarely if ever)... You might be ordering by empid... But for the most
part,
> if you cluster on the identity column you will NOT be able to benefit from
> the nice things that clustering can bring to a select.. So most of the
time
> I cluster on a WHERE clause column which gives the biggest bang (
deciding[vbcol=seagreen]
> which one is sometimes tough tho.)
> Here is the exception:
> IF the primary performance item for this table is INSERT SPEED, then the
> best way to get that to be fast is to cluster on the identity column. This
> cuts page allocation in half, because of a special algorythm used in the
> Engine... THe inserts will be fast, but the cost will be select speed...
> Reasonable people differ here... You should choose you method ( in my
> opinion) based on the performance priorities for the table..
> hope this helps..
>
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:e5NK88EwEHA.2804@.TK2MSFTNGP14.phx.gbl...
good[vbcol=seagreen]
> of.
of
> bad
> have
>
|||Geoff N. Hiten wrote:
> Good point. Range queries on the clustered index are going to be
> faster if you cluster on a natural key. I have one severely
> de-normalized transactional table that I had to cluster on the most
> used foreign key to get any decent performance. On a properly
> normalized schema, I find that more complex queries run faster
> clustering on an Identity column. It allows very fast index
> comparisons so you can leverage multiple indexes to create a
> 'virtual' covered index. It also increases index density so you get
> more effective use of cache memory.
> As always, your application will determine your specific needs.
> Test, test, and test again so you know what your application is doing
> to the system. Be ready to make changes and measure again so you can
> make real comparisons and recommendations.
>
Just to add my 2 cents. Clustering on any column that changes causes
sever fragmentation of the table. So if the OP chooses to cluster on a
column(s) used to filter queries, make sure that those columns are not
likely to change over time.
The OP should also realize that the clustered key is incorporated into
all non-clustered indexes, so it should be as short as possible.
David Gugick
Imceda Software
www.imceda.com
|||In message <2unifgF2c9ng0U1@.uni-berlin.de>, rkusenet
<rkusenet@.sympatico.ca> writes
>According to Brian Knight in "SQL Server 2000 for Experienced DBAs",
>"Avoid creating clustered indexes on identity columns.
> Clustered indexes perform better on range queries,
> such as a date. When you have a clustered index on
> an identity column, you risk your data receiving hot
> spots, which are caused by many people updating the
> same data page."
>I would like to know what is the consenus in this forum on this.
>
It really depends on your requirements and usage.
In an OLAP environment I would agree with his comment however in an OLTP
environment this would cause performance issues on large tables. What is
worse is you increase the risk of index fragmentation and therefore
require more frequent rebuilds.
If the table is used for linking purposes in an OLTP environment with
few inserts then again his statement would hold true.
It all depends ...
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Tuesday, March 20, 2012
Cluster confusion, and instances?
Hey,
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.
Cluster confusion, and instances?
Hey,
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.sqlsql
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) with
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers each
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.sqlsql
Cluster confusion, and instances?
Hey,
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.> I am trying to setup a clustered SQL 2000 environement (active-active) with">
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers ea
ch
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) wit
h
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers ea
ch
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.
I am trying to setup a clustered SQL 2000 environement (active-active) with
3 instances UAT, DEV and PROD.
Should i be using 3 named instances ? Or do I install 3 virtual servers each
being a default instance? I know this is a newbie question but please some
help would be appreciated.
At the moment I would expect 3 virtual servers of SQL 2000 each with their
own IP and NETBIOS name? ie.
10.10.10.1
10.10.10.2
10.10.10.3
Or do i just create 3 named insstances?ie.
10.10.10.1\UAT
10.10.10.1\DEV
10.10.10.1\PROD
Any help is appreciated.> I am trying to setup a clustered SQL 2000 environement (active-active) with">
> 3 instances UAT, DEV and PROD.
Having PROD share a cluster with QA and DEV defeats the whole purpose of
having a cluster which is to provide high availability.
> Should i be using 3 named instances ? Or do I install 3 virtual servers ea
ch
> being a default instance?
You can use three named instances. You can't have three default instances in
the same cluster; you can have only one default instance in a cluster.
Linchi
"Daniel" wrote:
> Hey,
> I am trying to setup a clustered SQL 2000 environement (active-active) wit
h
> 3 instances UAT, DEV and PROD.
> Should i be using 3 named instances ? Or do I install 3 virtual servers ea
ch
> being a default instance? I know this is a newbie question but please some
> help would be appreciated.
> At the moment I would expect 3 virtual servers of SQL 2000 each with their
> own IP and NETBIOS name? ie.
> 10.10.10.1
> 10.10.10.2
> 10.10.10.3
> Or do i just create 3 named insstances?ie.
> 10.10.10.1\UAT
> 10.10.10.1\DEV
> 10.10.10.1\PROD
> Any help is appreciated.
Cluster and User Defined Functions
Has anyone had difficulty with UDF's in a clustered environment? We have
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Monday, March 19, 2012
CLUSTER
I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
Rod
Rod,
You are probably better off calling Microsoft PSS to troubleshoot this
issue.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rod wrote:
> I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
Rod
Rod,
You are probably better off calling Microsoft PSS to troubleshoot this
issue.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rod wrote:
> I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
CLUSTER
I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
RodRod,
You are probably better off calling Microsoft PSS to troubleshoot this
issue.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rod wrote:
> I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
suspect and the only way to get it back online is to reboot the server and
that gets it back to normal. This happens every week.
Any ideas?
Also can you tell me how to change the location where Microsoft Clustered
Server changes the Temporary file location. Currently on my server it is
c:\winnt which we want to change
Thanks
RodRod,
You are probably better off calling Microsoft PSS to troubleshoot this
issue.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Rod wrote:
> I have a SQL 7.0 Clustered Server with SP3. Sometimes a DB (16GB) becomes
> suspect and the only way to get it back online is to reboot the server and
> that gets it back to normal. This happens every week.
> Any ideas?
> Also can you tell me how to change the location where Microsoft Clustered
> Server changes the Temporary file location. Currently on my server it is
> c:\winnt which we want to change
> Thanks
> Rod
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> > Hi,
> >
> > I read in one sql document tht--If the table have a clustered index, the
> > bookmarks of all non-clustered indexes will have clustering keys for each
> > row, and physically moving a row on disk would of course not have any
> > effect
> > on these.
> >
> > My question is wht is getting stored in clustered index keys which is
> > independent of data row location(as it can be inferred from above tht it
> > doesnt affect non clus. index bookmarks).
> >
> > Thanks in advance.
> > Manu Jaidka
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>> Manu,
>> What is stored in the non-clustered key is the value of the associated
>> clustered key.
>> Therefore, searching a non-clustered index results in the clustered index
>> key, after which the clustered index is searched to find the row.
>> One side effect of this is that the size of the non-clustered index is
>> affected by the size of the clustered index, so keeping the cluster as
>> small
>> as is reasonable is a good idea.
>> RLF
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>> > Hi,
>> >
>> > I read in one sql document tht--If the table have a clustered index,
>> > the
>> > bookmarks of all non-clustered indexes will have clustering keys for
>> > each
>> > row, and physically moving a row on disk would of course not have any
>> > effect
>> > on these.
>> >
>> > My question is wht is getting stored in clustered index keys which is
>> > independent of data row location(as it can be inferred from above tht
>> > it
>> > doesnt affect non clus. index bookmarks).
>> >
>> > Thanks in advance.
>> > Manu Jaidka
>>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu JaidkaManu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as sma
ll
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
>|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Clus. Index keys
Hi,
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
I read in one sql document tht--If the table have a clustered index, the
bookmarks of all non-clustered indexes will have clustering keys for each
row, and physically moving a row on disk would of course not have any effect
on these.
My question is wht is getting stored in clustered index keys which is
independent of data row location(as it can be inferred from above tht it
doesnt affect non clus. index bookmarks).
Thanks in advance.
Manu Jaidka
Manu,
What is stored in the non-clustered key is the value of the associated
clustered key.
Therefore, searching a non-clustered index results in the clustered index
key, after which the clustered index is searched to find the row.
One side effect of this is that the size of the non-clustered index is
affected by the size of the clustered index, so keeping the cluster as small
as is reasonable is a good idea.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Hi Russell,
It was written there in tht doc tht if SQL Server moves data row of a table
on which a clus index is already thr then non clustered index bookmarks
needn't be updated as they points to clus index keys. Why is it so? How come
clus index keys independent of data row location when they themselves
uniquely identifies each row?
Thanks for ur prompt response.
Manu
"Russell Fields" wrote:
> Manu,
> What is stored in the non-clustered key is the value of the associated
> clustered key.
> Therefore, searching a non-clustered index results in the clustered index
> key, after which the clustered index is searched to find the row.
> One side effect of this is that the size of the non-clustered index is
> affected by the size of the clustered index, so keeping the cluster as small
> as is reasonable is a good idea.
> RLF
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
>
>
|||> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
Clustered and Non-Clustered indexes have the same B-Tree index structure.
The difference is that CI has on leaf level the actual data as opposed
to NCI that has a pointer to the data. So if you have (as you said) CI and
NCI indexes on the table and the optimizer uses NCI to rertieve the data ,
then when it riched the leaf level of the NCI it points to clustered index
key (which is actual data) to retrieve the data.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:6348A28D-9103-4A19-882A-18FB2A28B8F1@.microsoft.com...
> Hi,
> I read in one sql document tht--If the table have a clustered index, the
> bookmarks of all non-clustered indexes will have clustering keys for each
> row, and physically moving a row on disk would of course not have any
> effect
> on these.
> My question is wht is getting stored in clustered index keys which is
> independent of data row location(as it can be inferred from above tht it
> doesnt affect non clus. index bookmarks).
> Thanks in advance.
> Manu Jaidka
|||Manu,
Uri also commented, mentioning what is at the leaf level of NCI and CI.
From this you can see that each index has an internal structure that does
know how to find something on disk.
So, the NCI can find its leaf node which has the clustered index key.
Then the CI can find clustered key leaf node which is the data row.
The thing with this approach is that as the data (at the leaf of the
cluster) is moved about due to inserts, deletes, rebuilds of the index, and
so forth, each NCI does _not_ need to be updated with the new position.
Only the CI needs to know where the leaf page is.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
> Thanks for ur prompt response.
> Manu
>
> "Russell Fields" wrote:
|||"manu" <manu@.discussions.microsoft.com> wrote in message
news:36F1FCC4-B8DD-4952-9AE8-3D7F3E9CC4BB@.microsoft.com...
> Hi Russell,
> It was written there in tht doc tht if SQL Server moves data row of a
> table
> on which a clus index is already thr then non clustered index bookmarks
> needn't be updated as they points to clus index keys. Why is it so? How
> come
> clus index keys independent of data row location when they themselves
> uniquely identifies each row?
>
Because a clustered index key identify the row "logically" and a RowID
identifies it "physically".
If you know the clustered index key, you still have to traverse the
clustered index to find the row.
David
Subscribe to:
Posts (Atom)