Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
SQL Server does not support NAS. If you can use a SAN, then you have two
choices. First, you can go with log shipping. Second, you can go with a
cluster solution, as long as the hardware is supported for clustering.
However, you can't just cluster an existing single-server instance. What
you could do is cluster the server that is not being used right now, migrate
the old instance to it and then remove the old instance. At this point, you
have a single-node cluster. Take the old server and add it to the cluster
and you're there.
For a bit more on clustering, check out:
http://www.microsoft.com/technet/technetmag/issues/2007/03/SQLClusters/default.aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
Here is what we are trying to accomplish, and please let me know if we are
going down the right path.
We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
database is on one of the two servers and we want to create a redundant
environment so when one of the SQL servers fails, the other will take over,
or continue to provide the functions for the website.
We are thinking of putting the SQL database on a NAS device and then point
each server to it and if one goes down the other can perform the operations
as needed.
Can this be done by clustering the two SQL servers and then pointing them to
the SQL database on the NAS device?
Do you know of any other way to create a redundant SQL environment?
Hopefully I've provided you with enough information, if not please let me
know if you need more details.
Thanks,
CC
|||There are several techniques for creating a redundnt SQL environment.
Clustering using a NAS box is not one of them. Clustering using a supported
storage unit is one possible technique. Only a very few NAS systems are
supported for SQL and none are supported for clustering.
Another possibility is database mirroring. This technique does not require
a shared storage system, but is more resource intensive and has some other
unique requirements.
Here is the Microsoft SQL 2005 High Availability home page:
http://www.microsoft.com/sql/technologies/highavailability/default.mspx
It contains links to FAQs, white papers, etc. on SQL High Availability.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:D7E35BA4-EDE6-4CEB-B474-652CBD9DD64F@.microsoft.com...
> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take
> over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the
> operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them
> to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||In addition to what the others have said, I just want to add that NAS is
built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
be better with considering iSCSI.
Linchi
"VIT" wrote:
> Here is what we are trying to accomplish, and please let me know if we are
> going down the right path.
> We have 2 Windows 2000 Servers running SQL 2000 for a website. The SQL
> database is on one of the two servers and we want to create a redundant
> environment so when one of the SQL servers fails, the other will take over,
> or continue to provide the functions for the website.
> We are thinking of putting the SQL database on a NAS device and then point
> each server to it and if one goes down the other can perform the operations
> as needed.
> Can this be done by clustering the two SQL servers and then pointing them to
> the SQL database on the NAS device?
> Do you know of any other way to create a redundant SQL environment?
> Hopefully I've provided you with enough information, if not please let me
> know if you need more details.
> Thanks,
> CC
|||So based on what we have what is the best, most cost effective way to achieve
a redundant SQL environment?
Thanks,
CC
"Linchi Shea" wrote:
[vbcol=seagreen]
> In addition to what the others have said, I just want to add that NAS is
> built on top of CIFS, and CIFS is a horrible protocol for block I/Os. You'd
> be better with considering iSCSI.
> Linchi
> "VIT" wrote:
|||What are the requirements of the applications?
Log Shipping is cost-effective, but in the event of an outage, manual
intervention would be required to reconfigure the applications to point to
the failsafe server.
SQL Server 2005 Database Mirroring is also cost-effective but will require
some beefier hardware, an upgraded network to reduce latency, an upgrade to
SQL Server 2005, and a reconfiguration of the applications to the later MDAC
or SQL Native Client protocol. Failover would be automatic.
Finally, Failover Clustering would provide better availability and
automation and is supported under both SQL Server 2000 and 2005; however,
this is not as cost-effective and requires a shared storage array
environment. SQL Server 2005, however, has introduced support for failover
clusters on the Standard Edition, which helps reduce the expense some.
Sincerely,
Anthony Thomas
"VIT" <VIT@.discussions.microsoft.com> wrote in message
news:353890CD-84AA-4E3B-A6FB-8A6580611216@.microsoft.com...
> So based on what we have what is the best, most cost effective way to
achieve[vbcol=seagreen]
> a redundant SQL environment?
> Thanks,
> CC
> "Linchi Shea" wrote:
You'd[vbcol=seagreen]
are[vbcol=seagreen]
SQL[vbcol=seagreen]
redundant[vbcol=seagreen]
over,[vbcol=seagreen]
point[vbcol=seagreen]
operations[vbcol=seagreen]
them to[vbcol=seagreen]
me[vbcol=seagreen]
Showing posts with label redundant. Show all posts
Showing posts with label redundant. Show all posts
Wednesday, March 7, 2012
Redundant SQL server
Hi, good day everyone.
I would like to have a question here,
I want to configure a redundant SQL server. Let's said if server A is down, then server B can take over the workload of server A, and this is transparent to users which means they won't notify server A is down.
Besides the failover clustering method, is there any other solution?
My requirement is needed to run in Microsoft SQL 2000 standard edition and Microsoft Windows 2000 standard edition
Thanks,First, you need to determine what the business is willing to pay for that type of solution. I often find if it costs $100,000 to provide a undetectable failover with no data loss versus paying $5,000 to have a slightly detectable failover with up to 5 minutes of data loss, the business chooses the latter 95% of the time.
Once you've got budget commitment, you decide what approach you use.
In the real world, you are expected to do it for free. However, I always ask the above question the first time. That way, they're prepared the next time.
The answer to the question you are asking is entirely dependent on knowing the resources you have available to do it. Once you know that, let us know the details on what you have committed to do and we'll help you. You should expect hardware to not be an issue because you must have at least the same firepower as your production to ensure you can handle the load for an indefinite time.
Finally, the highest % (by a landslide) of failures that require you to failover are due to user error - for example, a 'DBA' forgetting a WHERE clause. You can handle that with the $5,000 option.|||FWIW: I would think that Windows Server 2003 might be a better option for the OS...|||a redundant server for SQL 2k is known as a standby server
in the absence of a clustered solution, log shipping can be used to copy transaction logs from server a to server b
the logs can be copied at any interval you wish and are restored to the server b database with the standby option.
with standby restores logs to the database places the db in a read only state and allows the subsequent restoration of tlog backups to server b until you change the with standby clause to with recovery.
the switch from SA to SB is much quicker than in prior editions becuase of the addition of stored procedures that will allow the mapping of logins and the changing of server roles.
Books Online {log shipping}|||Scott, that would be nice, except...Log Shipping is avalable only in EE, not standard. You can simulate log shipping behavior by doing it yourself through scheduled tasks, or you can ask someone to make copies of system stored procedures from the enterprise edition and semi-emulate its behavior.
EDITED: wait a minute, does it mean that SQL Server can qualify for "open source product" label?|||you might want to take some of your glycerin before you read this old man.
Scott, that would be nice, except...Log Shipping is avalable only in EE, not standard. You can simulate log shipping behavior by doing it yourself through scheduled tasks, or you can ask someone to make copies of system stored procedures from the enterprise edition and semi-emulate its behavior.
EDITED: wait a minute, does it mean that SQL Server can qualify for "open source product" label?
as you notice i invoked the "with standby" clause of restore and the phrase "standby server". this was to illustrate that it is a process available to all editions
while the sqlmaint process of log shipping and the sp's are exclusive to ent, standby servers are available to all systems.
let' s do lunch next week.|||blah-blah-blah...
OK, lunch sounds great! I'll give you a call.
I would like to have a question here,
I want to configure a redundant SQL server. Let's said if server A is down, then server B can take over the workload of server A, and this is transparent to users which means they won't notify server A is down.
Besides the failover clustering method, is there any other solution?
My requirement is needed to run in Microsoft SQL 2000 standard edition and Microsoft Windows 2000 standard edition
Thanks,First, you need to determine what the business is willing to pay for that type of solution. I often find if it costs $100,000 to provide a undetectable failover with no data loss versus paying $5,000 to have a slightly detectable failover with up to 5 minutes of data loss, the business chooses the latter 95% of the time.
Once you've got budget commitment, you decide what approach you use.
In the real world, you are expected to do it for free. However, I always ask the above question the first time. That way, they're prepared the next time.
The answer to the question you are asking is entirely dependent on knowing the resources you have available to do it. Once you know that, let us know the details on what you have committed to do and we'll help you. You should expect hardware to not be an issue because you must have at least the same firepower as your production to ensure you can handle the load for an indefinite time.
Finally, the highest % (by a landslide) of failures that require you to failover are due to user error - for example, a 'DBA' forgetting a WHERE clause. You can handle that with the $5,000 option.|||FWIW: I would think that Windows Server 2003 might be a better option for the OS...|||a redundant server for SQL 2k is known as a standby server
in the absence of a clustered solution, log shipping can be used to copy transaction logs from server a to server b
the logs can be copied at any interval you wish and are restored to the server b database with the standby option.
with standby restores logs to the database places the db in a read only state and allows the subsequent restoration of tlog backups to server b until you change the with standby clause to with recovery.
the switch from SA to SB is much quicker than in prior editions becuase of the addition of stored procedures that will allow the mapping of logins and the changing of server roles.
Books Online {log shipping}|||Scott, that would be nice, except...Log Shipping is avalable only in EE, not standard. You can simulate log shipping behavior by doing it yourself through scheduled tasks, or you can ask someone to make copies of system stored procedures from the enterprise edition and semi-emulate its behavior.
EDITED: wait a minute, does it mean that SQL Server can qualify for "open source product" label?|||you might want to take some of your glycerin before you read this old man.
Scott, that would be nice, except...Log Shipping is avalable only in EE, not standard. You can simulate log shipping behavior by doing it yourself through scheduled tasks, or you can ask someone to make copies of system stored procedures from the enterprise edition and semi-emulate its behavior.
EDITED: wait a minute, does it mean that SQL Server can qualify for "open source product" label?
as you notice i invoked the "with standby" clause of restore and the phrase "standby server". this was to illustrate that it is a process available to all editions
while the sqlmaint process of log shipping and the sp's are exclusive to ent, standby servers are available to all systems.
let' s do lunch next week.|||blah-blah-blah...
OK, lunch sounds great! I'll give you a call.
Redundant Servers?
I have to setup a redundant server system. I want to
preferably use Windows Server 2003 Standard but I can use
Windows Server 2000 as well. The system will be running
SQL Server 2000. I am writing some custom software to
run on the system and will be storing data to the SQL
database. If the primary server fails then the secondary
server will start handling the functions of the primary
server. What is the best way to accomplish this? I need
the data in each SQL database to be identical, is there a
way to sync this data? I have thought about using Legato
Co-Standby Server AAdvanced, is this a good product?
WIll it do what I need? Any ideas are greatly
appreciated. I am a software engineer not a sys admin so
this is kinda new to me. Thanks!!!Look into Microsoft Cluster Services and Microsoft SQL Clustering.
--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com
"Kris" <grindaz@.hotmail.com> wrote in message
news:022f01c34181$83b10930$a101280a@.phx.gbl...
> I have to setup a redundant server system. I want to
> preferably use Windows Server 2003 Standard but I can use
> Windows Server 2000 as well. The system will be running
> SQL Server 2000. I am writing some custom software to
> run on the system and will be storing data to the SQL
> database. If the primary server fails then the secondary
> server will start handling the functions of the primary
> server. What is the best way to accomplish this? I need
> the data in each SQL database to be identical, is there a
> way to sync this data? I have thought about using Legato
> Co-Standby Server AAdvanced, is this a good product?
> WIll it do what I need? Any ideas are greatly
> appreciated. I am a software engineer not a sys admin so
> this is kinda new to me. Thanks!!!
>|||Kris, the answer depends on what you need exactly. When
you ask for the "best way" to approach, it also depends on
how much $$$ you'd like to spend, and how much down time
your users could deal with in the event of a system
crash. Yes, clustering is a nice option, but it's more
complex until you've done it a while, and you need shared
disk storage between the nodes, Advanced Server and
Enterprise Ed of SQL Server...
Then you could also look at something like shipping your
transaction logs over to the offline server, and if a
crash happens, maybe get that last LOG over, then changing
server names and bringing that server online...
It all depends on the budget and the length of down time
that's bearable... Bruce
>--Original Message--
>I have to setup a redundant server system. I want to
>preferably use Windows Server 2003 Standard but I can use
>Windows Server 2000 as well. The system will be running
>SQL Server 2000. I am writing some custom software to
>run on the system and will be storing data to the SQL
>database. If the primary server fails then the secondary
>server will start handling the functions of the primary
>server. What is the best way to accomplish this? I need
>the data in each SQL database to be identical, is there a
>way to sync this data? I have thought about using Legato
>Co-Standby Server AAdvanced, is this a good product?
>WIll it do what I need? Any ideas are greatly
>appreciated. I am a software engineer not a sys admin so
>this is kinda new to me. Thanks!!!
>.
>|||Thanks for the reply Bruce. I cannot spend lots of money
on this like $11,000 for SQL Ent. ED. But I will be
using Windows Server 20003 Enterprise. I have been
reading about clustering and it sounds like the way to go
except for as you metion the shared storage. How exactly
do you do this? What type of hardware? Also, will
clustering keep data in the SQL database on each machine
synced together or do you have to keep the DB on the
shared storage? I look forward to your comments and
thanks again.
Kris
>--Original Message--
>Kris, the answer depends on what you need exactly. When
>you ask for the "best way" to approach, it also depends
on
>how much $$$ you'd like to spend, and how much down time
>your users could deal with in the event of a system
>crash. Yes, clustering is a nice option, but it's more
>complex until you've done it a while, and you need
shared
>disk storage between the nodes, Advanced Server and
>Enterprise Ed of SQL Server...
>Then you could also look at something like shipping your
>transaction logs over to the offline server, and if a
>crash happens, maybe get that last LOG over, then
changing
>server names and bringing that server online...
>It all depends on the budget and the length of down time
>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
use
>>Windows Server 2000 as well. The system will be
running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
need
>>the data in each SQL database to be identical, is there
a
>>way to sync this data? I have thought about using
Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
so
>>this is kinda new to me. Thanks!!!
>>.
>.
>|||In your situation, you have two servers (nodes), and also
a shared disk array, a separate unit, sitting between and
cbaled to each node. Yes, the database files sit on the
shared drives, there's only one copy of them (well, not
counting whatever you do for disaster planning!)... After
teh 2 node cluster is built, then you install SQL Server
and it's "cluster-aware", so you should generally only
have to install it once, and it actually installs SQL
Server software on BOTH nodes C: drives, but anything
shared goes on the disk array. I'm not a hardware guy, to
talk about disk array brands exactly, and which is
better... But in a cluster you need some type of shared
disk between the nodes. Also, since you're dealing with 2
machines, keep in mind that you have TWO registries, so
anything not cluster-aware, needs to MAYBE be done on each
box... Bruce
>--Original Message--
>Thanks for the reply Bruce. I cannot spend lots of money
>on this like $11,000 for SQL Ent. ED. But I will be
>using Windows Server 20003 Enterprise. I have been
>reading about clustering and it sounds like the way to go
>except for as you metion the shared storage. How exactly
>do you do this? What type of hardware? Also, will
>clustering keep data in the SQL database on each machine
>synced together or do you have to keep the DB on the
>shared storage? I look forward to your comments and
>thanks again.
>Kris
>>--Original Message--
>>Kris, the answer depends on what you need exactly. When
>>you ask for the "best way" to approach, it also depends
>on
>>how much $$$ you'd like to spend, and how much down time
>>your users could deal with in the event of a system
>>crash. Yes, clustering is a nice option, but it's more
>>complex until you've done it a while, and you need
>shared
>>disk storage between the nodes, Advanced Server and
>>Enterprise Ed of SQL Server...
>>Then you could also look at something like shipping your
>>transaction logs over to the offline server, and if a
>>crash happens, maybe get that last LOG over, then
>changing
>>server names and bringing that server online...
>>It all depends on the budget and the length of down time
>>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
>use
>>Windows Server 2000 as well. The system will be
>running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
>secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
>need
>>the data in each SQL database to be identical, is there
>a
>>way to sync this data? I have thought about using
>Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
>so
>>this is kinda new to me. Thanks!!!
>>.
>>.
>.
>|||probably the cheapest solution is log shipping, which can be home grown or
purchased (enterprise edition). Failover is manual, then.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:08f801c34193$8b57c950$a001280a@.phx.gbl...
> In your situation, you have two servers (nodes), and also
> a shared disk array, a separate unit, sitting between and
> cbaled to each node. Yes, the database files sit on the
> shared drives, there's only one copy of them (well, not
> counting whatever you do for disaster planning!)... After
> teh 2 node cluster is built, then you install SQL Server
> and it's "cluster-aware", so you should generally only
> have to install it once, and it actually installs SQL
> Server software on BOTH nodes C: drives, but anything
> shared goes on the disk array. I'm not a hardware guy, to
> talk about disk array brands exactly, and which is
> better... But in a cluster you need some type of shared
> disk between the nodes. Also, since you're dealing with 2
> machines, keep in mind that you have TWO registries, so
> anything not cluster-aware, needs to MAYBE be done on each
> box... Bruce
>
> >--Original Message--
> >Thanks for the reply Bruce. I cannot spend lots of money
> >on this like $11,000 for SQL Ent. ED. But I will be
> >using Windows Server 20003 Enterprise. I have been
> >reading about clustering and it sounds like the way to go
> >except for as you metion the shared storage. How exactly
> >do you do this? What type of hardware? Also, will
> >clustering keep data in the SQL database on each machine
> >synced together or do you have to keep the DB on the
> >shared storage? I look forward to your comments and
> >thanks again.
> >
> >Kris
> >
> >>--Original Message--
> >>Kris, the answer depends on what you need exactly. When
> >>you ask for the "best way" to approach, it also depends
> >on
> >>how much $$$ you'd like to spend, and how much down time
> >>your users could deal with in the event of a system
> >>crash. Yes, clustering is a nice option, but it's more
> >>complex until you've done it a while, and you need
> >shared
> >>disk storage between the nodes, Advanced Server and
> >>Enterprise Ed of SQL Server...
> >>
> >>Then you could also look at something like shipping your
> >>transaction logs over to the offline server, and if a
> >>crash happens, maybe get that last LOG over, then
> >changing
> >>server names and bringing that server online...
> >>
> >>It all depends on the budget and the length of down time
> >>that's bearable... Bruce
> >>
> >>--Original Message--
> >>I have to setup a redundant server system. I want to
> >>preferably use Windows Server 2003 Standard but I can
> >use
> >>Windows Server 2000 as well. The system will be
> >running
> >>SQL Server 2000. I am writing some custom software to
> >>run on the system and will be storing data to the SQL
> >>database. If the primary server fails then the
> >secondary
> >>server will start handling the functions of the primary
> >>server. What is the best way to accomplish this? I
> >need
> >>the data in each SQL database to be identical, is there
> >a
> >>way to sync this data? I have thought about using
> >Legato
> >>Co-Standby Server AAdvanced, is this a good product?
> >>WIll it do what I need? Any ideas are greatly
> >>appreciated. I am a software engineer not a sys admin
> >so
> >>this is kinda new to me. Thanks!!!
> >>
> >>.
> >>
> >>.
> >>
> >.
> >
preferably use Windows Server 2003 Standard but I can use
Windows Server 2000 as well. The system will be running
SQL Server 2000. I am writing some custom software to
run on the system and will be storing data to the SQL
database. If the primary server fails then the secondary
server will start handling the functions of the primary
server. What is the best way to accomplish this? I need
the data in each SQL database to be identical, is there a
way to sync this data? I have thought about using Legato
Co-Standby Server AAdvanced, is this a good product?
WIll it do what I need? Any ideas are greatly
appreciated. I am a software engineer not a sys admin so
this is kinda new to me. Thanks!!!Look into Microsoft Cluster Services and Microsoft SQL Clustering.
--
Geoff N. Hiten
Senior Database Administrator
Careerbuilder.com
"Kris" <grindaz@.hotmail.com> wrote in message
news:022f01c34181$83b10930$a101280a@.phx.gbl...
> I have to setup a redundant server system. I want to
> preferably use Windows Server 2003 Standard but I can use
> Windows Server 2000 as well. The system will be running
> SQL Server 2000. I am writing some custom software to
> run on the system and will be storing data to the SQL
> database. If the primary server fails then the secondary
> server will start handling the functions of the primary
> server. What is the best way to accomplish this? I need
> the data in each SQL database to be identical, is there a
> way to sync this data? I have thought about using Legato
> Co-Standby Server AAdvanced, is this a good product?
> WIll it do what I need? Any ideas are greatly
> appreciated. I am a software engineer not a sys admin so
> this is kinda new to me. Thanks!!!
>|||Kris, the answer depends on what you need exactly. When
you ask for the "best way" to approach, it also depends on
how much $$$ you'd like to spend, and how much down time
your users could deal with in the event of a system
crash. Yes, clustering is a nice option, but it's more
complex until you've done it a while, and you need shared
disk storage between the nodes, Advanced Server and
Enterprise Ed of SQL Server...
Then you could also look at something like shipping your
transaction logs over to the offline server, and if a
crash happens, maybe get that last LOG over, then changing
server names and bringing that server online...
It all depends on the budget and the length of down time
that's bearable... Bruce
>--Original Message--
>I have to setup a redundant server system. I want to
>preferably use Windows Server 2003 Standard but I can use
>Windows Server 2000 as well. The system will be running
>SQL Server 2000. I am writing some custom software to
>run on the system and will be storing data to the SQL
>database. If the primary server fails then the secondary
>server will start handling the functions of the primary
>server. What is the best way to accomplish this? I need
>the data in each SQL database to be identical, is there a
>way to sync this data? I have thought about using Legato
>Co-Standby Server AAdvanced, is this a good product?
>WIll it do what I need? Any ideas are greatly
>appreciated. I am a software engineer not a sys admin so
>this is kinda new to me. Thanks!!!
>.
>|||Thanks for the reply Bruce. I cannot spend lots of money
on this like $11,000 for SQL Ent. ED. But I will be
using Windows Server 20003 Enterprise. I have been
reading about clustering and it sounds like the way to go
except for as you metion the shared storage. How exactly
do you do this? What type of hardware? Also, will
clustering keep data in the SQL database on each machine
synced together or do you have to keep the DB on the
shared storage? I look forward to your comments and
thanks again.
Kris
>--Original Message--
>Kris, the answer depends on what you need exactly. When
>you ask for the "best way" to approach, it also depends
on
>how much $$$ you'd like to spend, and how much down time
>your users could deal with in the event of a system
>crash. Yes, clustering is a nice option, but it's more
>complex until you've done it a while, and you need
shared
>disk storage between the nodes, Advanced Server and
>Enterprise Ed of SQL Server...
>Then you could also look at something like shipping your
>transaction logs over to the offline server, and if a
>crash happens, maybe get that last LOG over, then
changing
>server names and bringing that server online...
>It all depends on the budget and the length of down time
>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
use
>>Windows Server 2000 as well. The system will be
running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
need
>>the data in each SQL database to be identical, is there
a
>>way to sync this data? I have thought about using
Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
so
>>this is kinda new to me. Thanks!!!
>>.
>.
>|||In your situation, you have two servers (nodes), and also
a shared disk array, a separate unit, sitting between and
cbaled to each node. Yes, the database files sit on the
shared drives, there's only one copy of them (well, not
counting whatever you do for disaster planning!)... After
teh 2 node cluster is built, then you install SQL Server
and it's "cluster-aware", so you should generally only
have to install it once, and it actually installs SQL
Server software on BOTH nodes C: drives, but anything
shared goes on the disk array. I'm not a hardware guy, to
talk about disk array brands exactly, and which is
better... But in a cluster you need some type of shared
disk between the nodes. Also, since you're dealing with 2
machines, keep in mind that you have TWO registries, so
anything not cluster-aware, needs to MAYBE be done on each
box... Bruce
>--Original Message--
>Thanks for the reply Bruce. I cannot spend lots of money
>on this like $11,000 for SQL Ent. ED. But I will be
>using Windows Server 20003 Enterprise. I have been
>reading about clustering and it sounds like the way to go
>except for as you metion the shared storage. How exactly
>do you do this? What type of hardware? Also, will
>clustering keep data in the SQL database on each machine
>synced together or do you have to keep the DB on the
>shared storage? I look forward to your comments and
>thanks again.
>Kris
>>--Original Message--
>>Kris, the answer depends on what you need exactly. When
>>you ask for the "best way" to approach, it also depends
>on
>>how much $$$ you'd like to spend, and how much down time
>>your users could deal with in the event of a system
>>crash. Yes, clustering is a nice option, but it's more
>>complex until you've done it a while, and you need
>shared
>>disk storage between the nodes, Advanced Server and
>>Enterprise Ed of SQL Server...
>>Then you could also look at something like shipping your
>>transaction logs over to the offline server, and if a
>>crash happens, maybe get that last LOG over, then
>changing
>>server names and bringing that server online...
>>It all depends on the budget and the length of down time
>>that's bearable... Bruce
>>--Original Message--
>>I have to setup a redundant server system. I want to
>>preferably use Windows Server 2003 Standard but I can
>use
>>Windows Server 2000 as well. The system will be
>running
>>SQL Server 2000. I am writing some custom software to
>>run on the system and will be storing data to the SQL
>>database. If the primary server fails then the
>secondary
>>server will start handling the functions of the primary
>>server. What is the best way to accomplish this? I
>need
>>the data in each SQL database to be identical, is there
>a
>>way to sync this data? I have thought about using
>Legato
>>Co-Standby Server AAdvanced, is this a good product?
>>WIll it do what I need? Any ideas are greatly
>>appreciated. I am a software engineer not a sys admin
>so
>>this is kinda new to me. Thanks!!!
>>.
>>.
>.
>|||probably the cheapest solution is log shipping, which can be home grown or
purchased (enterprise edition). Failover is manual, then.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:08f801c34193$8b57c950$a001280a@.phx.gbl...
> In your situation, you have two servers (nodes), and also
> a shared disk array, a separate unit, sitting between and
> cbaled to each node. Yes, the database files sit on the
> shared drives, there's only one copy of them (well, not
> counting whatever you do for disaster planning!)... After
> teh 2 node cluster is built, then you install SQL Server
> and it's "cluster-aware", so you should generally only
> have to install it once, and it actually installs SQL
> Server software on BOTH nodes C: drives, but anything
> shared goes on the disk array. I'm not a hardware guy, to
> talk about disk array brands exactly, and which is
> better... But in a cluster you need some type of shared
> disk between the nodes. Also, since you're dealing with 2
> machines, keep in mind that you have TWO registries, so
> anything not cluster-aware, needs to MAYBE be done on each
> box... Bruce
>
> >--Original Message--
> >Thanks for the reply Bruce. I cannot spend lots of money
> >on this like $11,000 for SQL Ent. ED. But I will be
> >using Windows Server 20003 Enterprise. I have been
> >reading about clustering and it sounds like the way to go
> >except for as you metion the shared storage. How exactly
> >do you do this? What type of hardware? Also, will
> >clustering keep data in the SQL database on each machine
> >synced together or do you have to keep the DB on the
> >shared storage? I look forward to your comments and
> >thanks again.
> >
> >Kris
> >
> >>--Original Message--
> >>Kris, the answer depends on what you need exactly. When
> >>you ask for the "best way" to approach, it also depends
> >on
> >>how much $$$ you'd like to spend, and how much down time
> >>your users could deal with in the event of a system
> >>crash. Yes, clustering is a nice option, but it's more
> >>complex until you've done it a while, and you need
> >shared
> >>disk storage between the nodes, Advanced Server and
> >>Enterprise Ed of SQL Server...
> >>
> >>Then you could also look at something like shipping your
> >>transaction logs over to the offline server, and if a
> >>crash happens, maybe get that last LOG over, then
> >changing
> >>server names and bringing that server online...
> >>
> >>It all depends on the budget and the length of down time
> >>that's bearable... Bruce
> >>
> >>--Original Message--
> >>I have to setup a redundant server system. I want to
> >>preferably use Windows Server 2003 Standard but I can
> >use
> >>Windows Server 2000 as well. The system will be
> >running
> >>SQL Server 2000. I am writing some custom software to
> >>run on the system and will be storing data to the SQL
> >>database. If the primary server fails then the
> >secondary
> >>server will start handling the functions of the primary
> >>server. What is the best way to accomplish this? I
> >need
> >>the data in each SQL database to be identical, is there
> >a
> >>way to sync this data? I have thought about using
> >Legato
> >>Co-Standby Server AAdvanced, is this a good product?
> >>WIll it do what I need? Any ideas are greatly
> >>appreciated. I am a software engineer not a sys admin
> >so
> >>this is kinda new to me. Thanks!!!
> >>
> >>.
> >>
> >>.
> >>
> >.
> >
Redundant Power Supply
Or perhaps at least an independant generator?!?
WTF?!?
Some fool drove a car through a phone pole last night, about 4 blocks away
from work, and it killed the whole town.
Now I have to mop up all the records from the 6 or 7 batches that ran last
night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
that never happened either. So mopping up the batches is taking bloody ages
because they're now running the stupid backup in the middle of the work day
with all the users online. I think the bosses here are all retards.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldIsn't this what transactions and logs are for? ;)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or perhaps at least an independant generator?!?
> WTF?!?
> Some fool drove a car through a phone pole last night, about 4 blocks away
> from work, and it killed the whole town.
> Now I have to mop up all the records from the 6 or 7 batches that ran last
> night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
> that never happened either. So mopping up the batches is taking bloody
ages
> because they're now running the stupid backup in the middle of the work
day
> with all the users online. I think the bosses here are all retards.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||SQL Humor for this w
?
Very soon you will see experts discussing about the origin, development
accuracy and inaccuracy of gov. systems, power management, generators blah
blah..
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or perhaps at least an independant generator?!?
> WTF?!?
> Some fool drove a car through a phone pole last night, about 4 blocks away
> from work, and it killed the whole town.
> Now I have to mop up all the records from the 6 or 7 batches that ran last
> night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
> that never happened either. So mopping up the batches is taking bloody
> ages because they're now running the stupid backup in the middle of the
> work day with all the users online. I think the bosses here are all
> retards.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> Isn't this what transactions and logs are for? ;)
You would think.
But I'm a developer, not a DBA, and if I started messing with replaying
transaction logs, (I don't even have the authority to do so) I would
probably make the system worse.
This company refuses to hire a DBA (?!?) and the clowns over in MIS can
barely spell "SQL".
So we are currently re-running all the batches. I have like five copies of
the application on my machine running concurrently, and I'm getting really
bored looking at progress bars.
Thank God for code that has "indempotency".
I'm a peace-loving man, but I really hope the idiot that drove through the
power pole got hurt real bad.
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||> SQL Humor for this w?
> Very soon you will see experts discussing about the origin, development
> accuracy and inaccuracy of gov. systems, power management, generators
> blah blah..
ROFLMAO! If you were here for a single day, you would either laugh at
everyone, or beat everyone with a keyboard, and then fax them a very large
invoice.
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uEuc8nMqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> ROFLMAO! If you were here for a single day, you would either laugh at
> everyone, or beat everyone with a keyboard, and then fax them a very large
> invoice.
Sounds good. Where do I sign up? ;)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||>> ROFLMAO! If you were here for a single day, you would either laugh at
> Sounds good. Where do I sign up? ;)
http://www.tns-global.com
Have fun!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||On Wed, 24 Aug 2005 12:35:26 -0400, Mike Labosh wrote:
>I'm a peace-loving man, but I really hope the idiot that drove through the
>power pole got hurt real bad.
Hi Mike,
I think that he has gotten enough punishment already, having has car
wrecked and all.
And you can't hold the poor chap responsible for your company having no
UPS and no proper recovery procedure.
(Maybe you should hope that some managers happen to be walking just in
front of a power pole next time... :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I see no listing for VP of Keyboard Beatdowns :(
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23PtH65MqFHA.616@.TK2MSFTNGP15.phx.gbl...
> http://www.tns-global.com
> Have fun!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||The upside of being a government empoyee is that you are virtually
guaranteed a job. However, the downside is that you have to work with other
people who are also guaranteed a job.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OLl0zcMqFHA.1024@.TK2MSFTNGP09.phx.gbl...
> SQL Humor for this w?
> Very soon you will see experts discussing about the origin, development
> accuracy and inaccuracy of gov. systems, power management, generators
> blah blah..
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
>
WTF?!?
Some fool drove a car through a phone pole last night, about 4 blocks away
from work, and it killed the whole town.
Now I have to mop up all the records from the 6 or 7 batches that ran last
night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
that never happened either. So mopping up the batches is taking bloody ages
because they're now running the stupid backup in the middle of the work day
with all the users online. I think the bosses here are all retards.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldIsn't this what transactions and logs are for? ;)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or perhaps at least an independant generator?!?
> WTF?!?
> Some fool drove a car through a phone pole last night, about 4 blocks away
> from work, and it killed the whole town.
> Now I have to mop up all the records from the 6 or 7 batches that ran last
> night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
> that never happened either. So mopping up the batches is taking bloody
ages
> because they're now running the stupid backup in the middle of the work
day
> with all the users online. I think the bosses here are all retards.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||SQL Humor for this w
?Very soon you will see experts discussing about the origin, development
accuracy and inaccuracy of gov. systems, power management, generators blah
blah..
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Or perhaps at least an independant generator?!?
> WTF?!?
> Some fool drove a car through a phone pole last night, about 4 blocks away
> from work, and it killed the whole town.
> Now I have to mop up all the records from the 6 or 7 batches that ran last
> night -- oh, and since SQL Server is scheduled to back it self up at 3 am,
> that never happened either. So mopping up the batches is taking bloody
> ages because they're now running the stupid backup in the middle of the
> work day with all the users online. I think the bosses here are all
> retards.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> Isn't this what transactions and logs are for? ;)
You would think.
But I'm a developer, not a DBA, and if I started messing with replaying
transaction logs, (I don't even have the authority to do so) I would
probably make the system worse.
This company refuses to hire a DBA (?!?) and the clowns over in MIS can
barely spell "SQL".
So we are currently re-running all the batches. I have like five copies of
the application on my machine running concurrently, and I'm getting really
bored looking at progress bars.
Thank God for code that has "indempotency".
I'm a peace-loving man, but I really hope the idiot that drove through the
power pole got hurt real bad.
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||> SQL Humor for this w
?> Very soon you will see experts discussing about the origin, development
> accuracy and inaccuracy of gov. systems, power management, generators
> blah blah..
ROFLMAO! If you were here for a single day, you would either laugh at
everyone, or beat everyone with a keyboard, and then fax them a very large
invoice.
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uEuc8nMqFHA.2956@.TK2MSFTNGP12.phx.gbl...
> ROFLMAO! If you were here for a single day, you would either laugh at
> everyone, or beat everyone with a keyboard, and then fax them a very large
> invoice.
Sounds good. Where do I sign up? ;)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||>> ROFLMAO! If you were here for a single day, you would either laugh at
> Sounds good. Where do I sign up? ;)
http://www.tns-global.com
Have fun!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||On Wed, 24 Aug 2005 12:35:26 -0400, Mike Labosh wrote:
>I'm a peace-loving man, but I really hope the idiot that drove through the
>power pole got hurt real bad.
Hi Mike,
I think that he has gotten enough punishment already, having has car
wrecked and all.
And you can't hold the poor chap responsible for your company having no
UPS and no proper recovery procedure.
(Maybe you should hope that some managers happen to be walking just in
front of a power pole next time... :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I see no listing for VP of Keyboard Beatdowns :(
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23PtH65MqFHA.616@.TK2MSFTNGP15.phx.gbl...
> http://www.tns-global.com
> Have fun!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||The upside of being a government empoyee is that you are virtually
guaranteed a job. However, the downside is that you have to work with other
people who are also guaranteed a job.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OLl0zcMqFHA.1024@.TK2MSFTNGP09.phx.gbl...
> SQL Humor for this w
?> Very soon you will see experts discussing about the origin, development
> accuracy and inaccuracy of gov. systems, power management, generators
> blah blah..
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:eesr9VMqFHA.2076@.TK2MSFTNGP14.phx.gbl...
>
Redundant Indexes?
Hello,
Ive got a table called 'prd_movcomision' (Ive attached a script with its
structure) which has several indexes as you can see. There are several
indexes which are contained in others. For example:
CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
[itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo])
ON [PRIMARY]
GO
CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
Given the previous indexes, [B] is contained within [A] and [C] is
contained within [B]. The question is if this is really neccessary or once
I have an index A the other indexes (B and C) are redundant. Im having too
many problems with the size of the database, it is growing too fast.
Any advice on this would be very much appreciated.
Jorge Luzarraga
begin 666 prd_movcomision.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M(&1B;RYS>7-O8FIE8W1S('=H97)E
M(&ED(#T@.;V)J96-T7VED*$XG6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ETG*2!A
M;F0@.3T)*14-44%)/4$525%DH:60L($XG27-5<V5R5&%B;&4G*2 ](#$I#0ID
M<F]P('1A8FQE(%MD8F]=+EMP<F1?;6]V8V]M:7-I;VY=#0I'3PT*#0I#4D5!
M5$4@.5$%"3$4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.* T*"5MM;W9C7V-O
M9&EG;UT@.6VYU;65R:6-=*#$X+" P*2!.3U0@.3E5,3" L#0H)6VUO=F-?9F5C
M;6]V72!;9&%T971I;65=($Y/5"!.54Q,("P-"@.E;<&5R<U]R=71C:6%=(%MN
M=6UE<FEC72@.Y+" P*2!.54Q,("P-"@.E;:6YF:5]C;V1I9V]=(%MV87)C:&%R
M72 H-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y5
M3$P@.+ T*"5MD;V-O7VYU;65R;UT@.6VYU;65R:6-=*#$X+" P*2!.54Q,("P-
M"@.E;<&]L:5]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6VET
M<&]?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5ME<W1A7V-O
M9&EG;UT@.6VYU;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;VYE7V-O9&EG;UT@.
M6V-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*"5MP<F]P7V-O9&EG;UT@.6VYU;65R:6-=*#$X+" P
M*2!.54Q,("P-"@.E;:71P<E]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,
M3" L#0H)6V-O;F5?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*
M"5MP<F]P7V9E8V1O8W5M72!;9&%T971I;65=($Y53$P@.+ T*"5MP<F]P7V9E
M8V5M:7-I;VY=(%MD871E=&EM95T@.3E5,3" L#0H)6W!R;W!?9F5C:6YI8VEO
M72!;9&%T971I;65=($Y53$P@.+ T*"5MI='!R7V9E8VEN:6-I;UT@.6V1A=&5T
M:6UE72!.54Q,("P-"@.E;<&]L:5]F96-E;6ES:6]N72!;9&%T971I;65=($Y5
M3$P@.+ T*"5MP;VQI7V9E8VEN:6-I;UT@.6V1A=&5T:6UE72!.54Q,("P-"@.E;
M:71P;U]F96-I;FEC:6]=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO=F-?8V]N
M8V5P=&]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]I;G1E<FU=(%MC:&%R72 H
M,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.
M+ T*"5MM;W9C7W!E<FEO9&]=(%MN=6UE<FEC72@.V+" P*2!.54Q,("P-"@.E;
M;6]V8U]M;W1N;W!A9V]=(%MV87)C:&%R72 H,C4U*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?:6YD8W)I
M=&5R:6]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]=(%MC:&%R72 H,2D@.0T],
M3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM
M;W9C7W5N:61A9&-O;5T@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?9FEJ;UT@.6VYU;65R
M:6-=*#$X+" T*2!.54Q,("P-"@.E;;6]V8U]V87)I86)L95T@.6VYU;65R:6-=
M*#<L(#,I($Y53$P@.+ T*"5MM;W9C7W!R:6UA72!;;G5M97)I8UTH,3@.L(#0I
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6]=(%MN=6UE<FEC72@.Q."P@.-"D@.3D]4
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6-=(%MN=6UE<FEC72@.Q."P@.-"D@.3E5,
M3" L#0H)6VUO=F-?9F5C=F%L;W)=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO
M=F-?9F5C8VEE<G)E72!;9&%T971I;65=($Y53$P@.+ T*"5MM;W9C7V]B<V5R
M=F%C:6]N72!;=F%R8VAA<ET@.*#(U-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E
M;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]R86YG;UT@.6V-H
M87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.
M3E5,3" L#0H)6V-I97)?=&EP;UT@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6V-I97)?8V]R<F5L
M871I=F]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6V-U;W1?8V]D:6=O
M72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5MC=6]T7VYU;65R;UT@.6VYU
M;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;W9C7V9E8VUE=&%=(%MD871E=&EM
M95T@.3E5,3" L#0H)6VUO=F-?<')I;6%N971A72!;=%]D;VU?;6]N=&]=($Y5
M3$P@.+ T*"5MM;W9C7V9E8W!A9V]C;VUI<UT@.6W1?9&]M7V9E8VAA72!.54Q,
M( T**2!/3B!;4%))34%265T-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP
M<F1?;6]V8V]M:7-I;VY=(%=)5$@.@.3D]#2$5#2R!!1$0@.#0H)0T].4U1204E.
M5"!;<&M?;6]V8V]M:7-I;VY=(%!224U!4ED@.2T59("!#3%535$52140@.#0H)
M* T*"0E;;6]V8U]C;V1I9V]=#0H)*2 @.3TX@.6U!224U!4EE=( T*1T\-"@.T*
M($-214%412 @.24Y$15@.@.6T%+7W!R9%]M;W9C;VUI<VEO;ET@.3TX@.6V1B;UTN
M6W!R9%]M;W9C;VUI<VEO;ETH6VUO=F-?=&EP;VEN=&5R;5TL(%MP97)S7W)U
M=&-I85TL(%MM;W9C7V-O;F-E<'1O72P@.6V-O;F5?8V]D:6=O72P@.6W!O;&E?
M8V]D:6=O72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3 D1%6"!;
M06E?<')D7VUO=F-O;6ES:6]N,ET@.3TX@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO
M;ETH6W!E<G-?<G5T8VEA72P@.6W!O;&E?8V]D:6=O72P@.6VET<&]?8V]D:6=O
M72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3D1%6 "!;25A?<')D
M7VUO=F-O;6ES:6]N72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L
M:5]C;V1I9V]=+"!;:71P;U]C;V1I9V]=*2!/3B!;4%))34%265T-"D=/#0H-
M"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXQ72!/3B!;9&)O
M72Y;<')D7VUO=F-O;6ES:6]N72A;<')O<%]C;V1I9V]=*2!/3B!;4%))34%2
M65T-"D=/#0H-"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXR
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L:5]C;V1I9V]=*2!/
M3B!;4%))34%265T-"D=/#0H-"B\J*BHJ*BH@.5&AE(&EN9&5X(&-R96%T960@.
M8GD@.=&AE(&9O;&QO=VEN9R!S=&%T96UE;G0@.:7,@.9F]R(&EN=&5R;F%L('5S
M92!O;FQY+B J*BHJ*BHO#0HO*BHJ*BHJ($ET(&ES(&YO="!A(')E86P@.:6YD
M97@.@.8G5T(&5X:7-T<R!A<R!S=&%T:7-T:6-S(&]N;'DN("HJ*BHJ*B\-"FEF
M("A 0&UI8W)O<V]F='9E<G-I;VX@./B P># W,# P,# P("D-"D5814,@.*"=#
M4D5!5$4@.4U1!5$E35$E#4R!;4W1A=&ES=&EC7VUO=F-?9F5C<&%G;V-O;6ES
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72 H6VUO=F-?9F5C<&%G;V-O
M;6ES72D@.)RD-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP<F1?;6]V8V]M
M:7-I;VY=($%$1" -"@.E#3TY35%)!24Y4(%MF:U]R7V-O;F5?;6]V8V]M72!&
M3U)%24=.($M%62 -"@.DH#0H)"5MC;VYE7V-O9&EG;UT-"@.DI(%)%1D5214Y#
M15,@.6V1B;UTN6W!R9%]C;VUN96=O8VEO72 H#0H)"5MC;VYE7V-O9&EG;UT-
M"@.DI+ T*"4-/3E-44D%)3E0@.6V9K7W)?8W5O=%]M;W9C;VU=($9/4D5)1TX@.
M2T59( T*"2@.-"@.D)6V-U;W1?8V]D:6=O72P-"@.D)6V-U;W1?;G5M97)O70T*
M"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V-U;W1A72 H#0H)"5MC=6]T7V-O
M9&EG;UTL#0H)"5MC=6]T7VYU;65R;UT-"@.DI+ T*"4-/3E-44D%)3E0@.6V9K
M7W)?9&]C8V]M7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)* T*"0E;<&5R<U]R
M=71C:6%=+ T*"0E;:6YF:5]C;V1I9V]=+ T*"0E;9&]C;U]N=6UE<F]=#0H)
M*2!2149%4D5.0T53(%MD8F]=+EMP<F1?9&]C8V]M:7-I;VY=("@.-"@.D)6W!E
M<G-?<G5T8VEA72P-"@.D)6VEN9FE?8V]D:6=O72P-"@.D)6V1O8V]?;G5M97)O
M70T*"2DL#0H)0T].4U1204E.5"!;9FM?<E]E<W1?;6]V8V]M72!&3U)%24=.
M($M%62 -"@.DH#0H)"5ME<W1A7V-O9&EG;UT-"@.DI(%)%1D5214Y#15,@.6V1B
M;UTN6V%D;5]E<W1A9&]=("@.-"@.D)6V5S=&%?8V]D:6=O70T*"2DL#0H)0T].
M4U1204E.5"!;9FM?<E]E<W1C:65R7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)
M* T*"0E;;6]V8U]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL#0H)"5MC:65R
M7V-O<G)E;&%T:79O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V5S=&%D
M;V-I97)R95T@.* T*"0E;8VEE<E]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL
M#0H)"5MC:65R7V-O<G)E;&%T:79O70T*"2DL#0H)0T].4U1204E.5"!;9FM?
M<E]I=&UP<E]M;W9C;VU=($9/4D5)1TX@.2T59( T*"2@.-"@.D)6W!R;W!?8V]D
M:6=O72P-"@.D)6VET<')?8V]D:6=O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;
M=G1A7VET;7!R;W!U97-T85T@.* T*"0E;<')O<%]C;V1I9V]=+ T*"0E;:71P
M<E]C;V1I9V]=#0H)*2P-"@.E#3TY35%)!24Y4(%MF:U]R7VUN9%]M;W9C;VU=
M($9/4D5)1TX@.2T59( T*"2@.-"@.D)6VUO;F5?8V]D:6=O70T*"2D@.4D5&15)%
M3D-%4R!;9&)O72Y;861M7VUO;F5D85T@.* T*"0E;;6]N95]C;V1I9V]=#0H)
M*0T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.
M;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R7V5S=%]M;W9C;VU=#0I'3PT*#0IA
M;'1E<B!T86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O
M;G-T<F%I;G0@.6V9K7W)?97-T8VEE<E]M;W9C;VU=#0I'3PT*#0IA;'1E<B!T
M86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O;G-T<F%I
M;G0@.6V9K7W)?:71M<')?;6]V8V]M70T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B
M;UTN6W!R9%]M;W9C;VUI<VEO;ET@.;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R
47VUN9%]M;W9C;VU=#0I'3PT*#0H`
`
end
Hi
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],
[itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and [C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>
|||Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
************************************************** ********************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
GO
************************************************** ***************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>
|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ************************************************** ********************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
[fk_r_estcier_movcom][vbcol=seagreen]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
> GO
> ************************************************** ***************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY]
>
|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
************************************************** ********************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
GO
************************************************** ***************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>
|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and [C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.
> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Ive got a table called 'prd_movcomision' (Ive attached a script with its
structure) which has several indexes as you can see. There are several
indexes which are contained in others. For example:
CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
[itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo])
ON [PRIMARY]
GO
CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
Given the previous indexes, [B] is contained within [A] and [C] is
contained within [B]. The question is if this is really neccessary or once
I have an index A the other indexes (B and C) are redundant. Im having too
many problems with the size of the database, it is growing too fast.
Any advice on this would be very much appreciated.
Jorge Luzarraga
begin 666 prd_movcomision.sql
M:68@.97AI<W1S("AS96QE8W0@.*B!F<F]M(&1B;RYS>7-O8FIE8W1S('=H97)E
M(&ED(#T@.;V)J96-T7VED*$XG6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ETG*2!A
M;F0@.3T)*14-44%)/4$525%DH:60L($XG27-5<V5R5&%B;&4G*2 ](#$I#0ID
M<F]P('1A8FQE(%MD8F]=+EMP<F1?;6]V8V]M:7-I;VY=#0I'3PT*#0I#4D5!
M5$4@.5$%"3$4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.* T*"5MM;W9C7V-O
M9&EG;UT@.6VYU;65R:6-=*#$X+" P*2!.3U0@.3E5,3" L#0H)6VUO=F-?9F5C
M;6]V72!;9&%T971I;65=($Y/5"!.54Q,("P-"@.E;<&5R<U]R=71C:6%=(%MN
M=6UE<FEC72@.Y+" P*2!.54Q,("P-"@.E;:6YF:5]C;V1I9V]=(%MV87)C:&%R
M72 H-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y5
M3$P@.+ T*"5MD;V-O7VYU;65R;UT@.6VYU;65R:6-=*#$X+" P*2!.54Q,("P-
M"@.E;<&]L:5]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6VET
M<&]?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5ME<W1A7V-O
M9&EG;UT@.6VYU;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;VYE7V-O9&EG;UT@.
M6V-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*"5MP<F]P7V-O9&EG;UT@.6VYU;65R:6-=*#$X+" P
M*2!.54Q,("P-"@.E;:71P<E]C;V1I9V]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,
M3" L#0H)6V-O;F5?8V]D:6=O72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*
M"5MP<F]P7V9E8V1O8W5M72!;9&%T971I;65=($Y53$P@.+ T*"5MP<F]P7V9E
M8V5M:7-I;VY=(%MD871E=&EM95T@.3E5,3" L#0H)6W!R;W!?9F5C:6YI8VEO
M72!;9&%T971I;65=($Y53$P@.+ T*"5MI='!R7V9E8VEN:6-I;UT@.6V1A=&5T
M:6UE72!.54Q,("P-"@.E;<&]L:5]F96-E;6ES:6]N72!;9&%T971I;65=($Y5
M3$P@.+ T*"5MP;VQI7V9E8VEN:6-I;UT@.6V1A=&5T:6UE72!.54Q,("P-"@.E;
M:71P;U]F96-I;FEC:6]=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO=F-?8V]N
M8V5P=&]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]I;G1E<FU=(%MC:&%R72 H
M,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.
M+ T*"5MM;W9C7W!E<FEO9&]=(%MN=6UE<FEC72@.V+" P*2!.54Q,("P-"@.E;
M;6]V8U]M;W1N;W!A9V]=(%MV87)C:&%R72 H,C4U*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?:6YD8W)I
M=&5R:6]=(%MC:&%R72 H,2D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]=(%MC:&%R72 H,2D@.0T],
M3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM
M;W9C7W5N:61A9&-O;5T@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6VUO=F-?9FEJ;UT@.6VYU;65R
M:6-=*#$X+" T*2!.54Q,("P-"@.E;;6]V8U]V87)I86)L95T@.6VYU;65R:6-=
M*#<L(#,I($Y53$P@.+ T*"5MM;W9C7W!R:6UA72!;;G5M97)I8UTH,3@.L(#0I
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6]=(%MN=6UE<FEC72@.Q."P@.-"D@.3D]4
M($Y53$P@.+ T*"5MM;W9C7VUO;G1O;6-=(%MN=6UE<FEC72@.Q."P@.-"D@.3E5,
M3" L#0H)6VUO=F-?9F5C=F%L;W)=(%MD871E=&EM95T@.3E5,3" L#0H)6VUO
M=F-?9F5C8VEE<G)E72!;9&%T971I;65=($Y53$P@.+ T*"5MM;W9C7V]B<V5R
M=F%C:6]N72!;=F%R8VAA<ET@.*#(U-2D@.0T],3$%412!344Q?3&%T:6XQ7T=E
M;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*"5MM;W9C7W1I<&]R86YG;UT@.6V-H
M87)=("@.Q*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.
M3E5,3" L#0H)6V-I97)?=&EP;UT@.6V-H87)=("@.Q*2!#3TQ,051%(%-13%],
M871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H)6V-I97)?8V]R<F5L
M871I=F]=(%MN=6UE<FEC72@.Q."P@.,"D@.3E5,3" L#0H)6V-U;W1?8V]D:6=O
M72!;;G5M97)I8UTH,3@.L(# I($Y53$P@.+ T*"5MC=6]T7VYU;65R;UT@.6VYU
M;65R:6-=*#0L(# I($Y53$P@.+ T*"5MM;W9C7V9E8VUE=&%=(%MD871E=&EM
M95T@.3E5,3" L#0H)6VUO=F-?<')I;6%N971A72!;=%]D;VU?;6]N=&]=($Y5
M3$P@.+ T*"5MM;W9C7V9E8W!A9V]C;VUI<UT@.6W1?9&]M7V9E8VAA72!.54Q,
M( T**2!/3B!;4%))34%265T-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP
M<F1?;6]V8V]M:7-I;VY=(%=)5$@.@.3D]#2$5#2R!!1$0@.#0H)0T].4U1204E.
M5"!;<&M?;6]V8V]M:7-I;VY=(%!224U!4ED@.2T59("!#3%535$52140@.#0H)
M* T*"0E;;6]V8U]C;V1I9V]=#0H)*2 @.3TX@.6U!224U!4EE=( T*1T\-"@.T*
M($-214%412 @.24Y$15@.@.6T%+7W!R9%]M;W9C;VUI<VEO;ET@.3TX@.6V1B;UTN
M6W!R9%]M;W9C;VUI<VEO;ETH6VUO=F-?=&EP;VEN=&5R;5TL(%MP97)S7W)U
M=&-I85TL(%MM;W9C7V-O;F-E<'1O72P@.6V-O;F5?8V]D:6=O72P@.6W!O;&E?
M8V]D:6=O72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3 D1%6"!;
M06E?<')D7VUO=F-O;6ES:6]N,ET@.3TX@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO
M;ETH6W!E<G-?<G5T8VEA72P@.6W!O;&E?8V]D:6=O72P@.6VET<&]?8V]D:6=O
M72D@.3TX@.6U!224U!4EE=#0I'3PT*#0H@.0U)%051%("!)3D1%6 "!;25A?<')D
M7VUO=F-O;6ES:6]N72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L
M:5]C;V1I9V]=+"!;:71P;U]C;V1I9V]=*2!/3B!;4%))34%265T-"D=/#0H-
M"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXQ72!/3B!;9&)O
M72Y;<')D7VUO=F-O;6ES:6]N72A;<')O<%]C;V1I9V]=*2!/3B!;4%))34%2
M65T-"D=/#0H-"B!#4D5!5$4@.($E.1$58(%M)6%]P<F1?;6]V8V]M:7-I;VXR
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72A;<&]L:5]C;V1I9V]=*2!/
M3B!;4%))34%265T-"D=/#0H-"B\J*BHJ*BH@.5&AE(&EN9&5X(&-R96%T960@.
M8GD@.=&AE(&9O;&QO=VEN9R!S=&%T96UE;G0@.:7,@.9F]R(&EN=&5R;F%L('5S
M92!O;FQY+B J*BHJ*BHO#0HO*BHJ*BHJ($ET(&ES(&YO="!A(')E86P@.:6YD
M97@.@.8G5T(&5X:7-T<R!A<R!S=&%T:7-T:6-S(&]N;'DN("HJ*BHJ*B\-"FEF
M("A 0&UI8W)O<V]F='9E<G-I;VX@./B P># W,# P,# P("D-"D5814,@.*"=#
M4D5!5$4@.4U1!5$E35$E#4R!;4W1A=&ES=&EC7VUO=F-?9F5C<&%G;V-O;6ES
M72!/3B!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72 H6VUO=F-?9F5C<&%G;V-O
M;6ES72D@.)RD-"D=/#0H-"D%,5$52(%1!0DQ%(%MD8F]=+EMP<F1?;6]V8V]M
M:7-I;VY=($%$1" -"@.E#3TY35%)!24Y4(%MF:U]R7V-O;F5?;6]V8V]M72!&
M3U)%24=.($M%62 -"@.DH#0H)"5MC;VYE7V-O9&EG;UT-"@.DI(%)%1D5214Y#
M15,@.6V1B;UTN6W!R9%]C;VUN96=O8VEO72 H#0H)"5MC;VYE7V-O9&EG;UT-
M"@.DI+ T*"4-/3E-44D%)3E0@.6V9K7W)?8W5O=%]M;W9C;VU=($9/4D5)1TX@.
M2T59( T*"2@.-"@.D)6V-U;W1?8V]D:6=O72P-"@.D)6V-U;W1?;G5M97)O70T*
M"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V-U;W1A72 H#0H)"5MC=6]T7V-O
M9&EG;UTL#0H)"5MC=6]T7VYU;65R;UT-"@.DI+ T*"4-/3E-44D%)3E0@.6V9K
M7W)?9&]C8V]M7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)* T*"0E;<&5R<U]R
M=71C:6%=+ T*"0E;:6YF:5]C;V1I9V]=+ T*"0E;9&]C;U]N=6UE<F]=#0H)
M*2!2149%4D5.0T53(%MD8F]=+EMP<F1?9&]C8V]M:7-I;VY=("@.-"@.D)6W!E
M<G-?<G5T8VEA72P-"@.D)6VEN9FE?8V]D:6=O72P-"@.D)6V1O8V]?;G5M97)O
M70T*"2DL#0H)0T].4U1204E.5"!;9FM?<E]E<W1?;6]V8V]M72!&3U)%24=.
M($M%62 -"@.DH#0H)"5ME<W1A7V-O9&EG;UT-"@.DI(%)%1D5214Y#15,@.6V1B
M;UTN6V%D;5]E<W1A9&]=("@.-"@.D)6V5S=&%?8V]D:6=O70T*"2DL#0H)0T].
M4U1204E.5"!;9FM?<E]E<W1C:65R7VUO=F-O;5T@.1D]214E'3B!+15D@.#0H)
M* T*"0E;;6]V8U]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL#0H)"5MC:65R
M7V-O<G)E;&%T:79O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;<')D7V5S=&%D
M;V-I97)R95T@.* T*"0E;8VEE<E]P97)I;V1O72P-"@.D)6V-I97)?=&EP;UTL
M#0H)"5MC:65R7V-O<G)E;&%T:79O70T*"2DL#0H)0T].4U1204E.5"!;9FM?
M<E]I=&UP<E]M;W9C;VU=($9/4D5)1TX@.2T59( T*"2@.-"@.D)6W!R;W!?8V]D
M:6=O72P-"@.D)6VET<')?8V]D:6=O70T*"2D@.4D5&15)%3D-%4R!;9&)O72Y;
M=G1A7VET;7!R;W!U97-T85T@.* T*"0E;<')O<%]C;V1I9V]=+ T*"0E;:71P
M<E]C;V1I9V]=#0H)*2P-"@.E#3TY35%)!24Y4(%MF:U]R7VUN9%]M;W9C;VU=
M($9/4D5)1TX@.2T59( T*"2@.-"@.D)6VUO;F5?8V]D:6=O70T*"2D@.4D5&15)%
M3D-%4R!;9&)O72Y;861M7VUO;F5D85T@.* T*"0E;;6]N95]C;V1I9V]=#0H)
M*0T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B;UTN6W!R9%]M;W9C;VUI<VEO;ET@.
M;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R7V5S=%]M;W9C;VU=#0I'3PT*#0IA
M;'1E<B!T86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O
M;G-T<F%I;G0@.6V9K7W)?97-T8VEE<E]M;W9C;VU=#0I'3PT*#0IA;'1E<B!T
M86)L92!;9&)O72Y;<')D7VUO=F-O;6ES:6]N72!N;V-H96-K(&-O;G-T<F%I
M;G0@.6V9K7W)?:71M<')?;6]V8V]M70T*1T\-"@.T*86QT97(@.=&%B;&4@.6V1B
M;UTN6W!R9%]M;W9C;VUI<VEO;ET@.;F]C:&5C:R!C;VYS=')A:6YT(%MF:U]R
47VUN9%]M;W9C;VU=#0I'3PT*#0H`
`
end
Hi
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],
[itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and [C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>
|||Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
************************************************** ********************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
GO
************************************************** ***************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>
|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ************************************************** ********************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
[fk_r_estcier_movcom][vbcol=seagreen]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
> GO
> ************************************************** ***************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY]
>
|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
************************************************** ********************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], [movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [itpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_estcier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itmpr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd_movcom]
GO
************************************************** ***************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>
|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and [C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.
> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Redundant Indexes?
Hi
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],[/vbco
l]
[itpo_codigo])[vbcol=seagreen]
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ****************************************
******************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS[/vb
col]
NULL[vbcol=seagreen]
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia],
[movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [
;itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON &
#91;PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_e
st_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
& #91;fk_r_estcier_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_i
tmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_m
nd_movcom]
> GO
> ****************************************
*************************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY][vbcol=seagreen]
>|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo
],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.
> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I would get rid or A and B. If there is no clustered index on that table,
make C clustered too, assuming it has good selectivity.
Without the data types, it is not possible to tell and be 100% sure (your
attachment gets blocked by Outlook Express)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> Hello,
>
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo],[/vbco
l]
[itpo_codigo])[vbcol=seagreen]
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
once
> I have an index A the other indexes (B and C) are redundant. Im having
too
> many problems with the size of the database, it is growing too fast.
>
> Any advice on this would be very much appreciated.
>
> Jorge Luzarraga
>
>Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Hi
All those columns are nullable, so making a compound index (multiple
columns) is not a good idea. From my experience, the query processor will
think of them as 2nd choice.
Having 1 index on poli_codigo and one on poli_codigo will cover your
queries, and will improve Insert/Update performance as compound index add a
lot to index maintenance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello Mike,
> Are you talking about getting rid of the one which contains the others?
Any
> help is very much appreciated.
> this is the script:
> ****************************************
******************************
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[prd_movcomision]
> GO
> CREATE TABLE [dbo].[prd_movcomision] (
> [movc_codigo] [numeric](18, 0) NOT NULL ,
> [movc_fecmov] [datetime] NOT NULL ,
> [pers_rutcia] [numeric](9, 0) NULL ,
> [infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [doco_numero] [numeric](18, 0) NULL ,
> [poli_codigo] [numeric](18, 0) NULL ,
> [itpo_codigo] [numeric](18, 0) NULL ,
> [esta_codigo] [numeric](4, 0) NULL ,
> [mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [prop_codigo] [numeric](18, 0) NULL ,
> [itpr_codigo] [numeric](18, 0) NULL ,
> [cone_codigo] [numeric](18, 0) NULL ,
> [prop_fecdocum] [datetime] NULL ,
> [prop_fecemision] [datetime] NULL ,
> [prop_fecinicio] [datetime] NULL ,
> [itpr_fecinicio] [datetime] NULL ,
> [poli_fecemision] [datetime] NULL ,
> [poli_fecinicio] [datetime] NULL ,
> [itpo_fecinicio] [datetime] NULL ,
> [movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_periodo] [numeric](6, 0) NULL ,
> [movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS[/vb
col]
NULL[vbcol=seagreen]
> ,
> [movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [movc_fijo] [numeric](18, 4) NULL ,
> [movc_variable] [numeric](7, 3) NULL ,
> [movc_prima] [numeric](18, 4) NULL ,
> [movc_montomo] [numeric](18, 4) NOT NULL ,
> [movc_montomc] [numeric](18, 4) NULL ,
> [movc_fecvalor] [datetime] NULL ,
> [movc_feccierre] [datetime] NULL ,
> [movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL ,
> [movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [cier_correlativo] [numeric](18, 0) NULL ,
> [cuot_codigo] [numeric](18, 0) NULL ,
> [cuot_numero] [numeric](4, 0) NULL ,
> [movc_fecmeta] [datetime] NULL ,
> [movc_primaneta] [t_dom_monto] NULL ,
> [movc_fecpagocomis] [t_dom_fecha] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
> CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
> (
> [movc_codigo]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [AK_prd_movcomision] ON
> [dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia],
[movc_concepto],
> [cone_codigo], [poli_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [Ai_prd_movcomision2] ON
> [dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [
;itpo_codigo]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision] ON
> [dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON &
#91;PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision1] ON
> [dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_prd_movcomision2] ON
> [dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
> GO
> /****** The index created by the following statement is for internal use
> only. ******/
> /****** It is not a real index but exists as statistics only. ******/
> if (@.@.microsoftversion > 0x07000000 )
> EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
> [dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
> GO
> ALTER TABLE [dbo].[prd_movcomision] ADD
> CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
> (
> [cone_codigo]
> ) REFERENCES [dbo].[prd_comnegocio] (
> [cone_codigo]
> ),
> CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
> (
> [cuot_codigo],
> [cuot_numero]
> ) REFERENCES [dbo].[prd_cuota] (
> [cuot_codigo],
> [cuot_numero]
> ),
> CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
> (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ) REFERENCES [dbo].[prd_doccomision] (
> [pers_rutcia],
> [infi_codigo],
> [doco_numero]
> ),
> CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
> (
> [esta_codigo]
> ) REFERENCES [dbo].[adm_estado] (
> [esta_codigo]
> ),
> CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
> (
> [movc_periodo],
> [cier_tipo],
> [cier_correlativo]
> ) REFERENCES [dbo].[prd_estadocierre] (
> [cier_periodo],
> [cier_tipo],
> [cier_correlativo]
> ),
> CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
> (
> [prop_codigo],
> [itpr_codigo]
> ) REFERENCES [dbo].[vta_itmpropuesta] (
> [prop_codigo],
> [itpr_codigo]
> ),
> CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
> (
> [mone_codigo]
> ) REFERENCES [dbo].[adm_moneda] (
> [mone_codigo]
> )
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_e
st_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint
& #91;fk_r_estcier_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_i
tmpr_movcom]
> GO
> alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_m
nd_movcom]
> GO
> ****************************************
*************************
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
> news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
table,[vbcol=seagreen]
(your[vbcol=seagreen]
[PRIMARY][vbcol=seagreen]
>|||It looks like Mike has this handled, but I'd like to interject here.
First, histogram statistics are only stored for the first attribute in any
index, composite or not. That means the QA selectivity evaluation is based
on the leading attribute...alone, regardless if it is singlet or composite.
Second, since SQL Server version 7.0, the Query Optimizer can use index
intersection to make better search conditions...viz., you are not limited to
using a single index in a query expression. Either an index is highly
selective, or it is not. Having multiple single-attribute indexes is more
desirble than fewer composite indexes. And, never should you require
overlapping indexes; SQL Server will only choose one based on selectivity,
the others would only be a waste of space.
That being said, after a set of well chosen, single-attribute indexes have
been created, and every table--and I mean EVERY table--should have a
clustered index defined, then a few--VERY FEW--customized, query targeted,
mission-critical, composite indexes can be built, but only after proving the
multiple single-attribute indexes are not producing sufficient response.
Everything else will only produce storage and processing overhead whenever
you do CRUD or reorg operations.
Sincerely,
Anthony Thomas
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:u0fD$1c6EHA.208@.TK2MSFTNGP12.phx.gbl...
Hello Mike,
Are you talking about getting rid of the one which contains the others? Any
help is very much appreciated.
this is the script:
****************************************
******************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[prd_movcomision]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[prd_movcomision]
GO
CREATE TABLE [dbo].[prd_movcomision] (
[movc_codigo] [numeric](18, 0) NOT NULL ,
[movc_fecmov] [datetime] NOT NULL ,
[pers_rutcia] [numeric](9, 0) NULL ,
[infi_codigo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[doco_numero] [numeric](18, 0) NULL ,
[poli_codigo] [numeric](18, 0) NULL ,
[itpo_codigo] [numeric](18, 0) NULL ,
[esta_codigo] [numeric](4, 0) NULL ,
[mone_codigo] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[prop_codigo] [numeric](18, 0) NULL ,
[itpr_codigo] [numeric](18, 0) NULL ,
[cone_codigo] [numeric](18, 0) NULL ,
[prop_fecdocum] [datetime] NULL ,
[prop_fecemision] [datetime] NULL ,
[prop_fecinicio] [datetime] NULL ,
[itpr_fecinicio] [datetime] NULL ,
[poli_fecemision] [datetime] NULL ,
[poli_fecinicio] [datetime] NULL ,
[itpo_fecinicio] [datetime] NULL ,
[movc_concepto] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[movc_tipointerm] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[movc_periodo] [numeric](6, 0) NULL ,
[movc_motnopago] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[movc_indcriterio] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[movc_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[movc_unidadcom] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[movc_fijo] [numeric](18, 4) NULL ,
[movc_variable] [numeric](7, 3) NULL ,
[movc_prima] [numeric](18, 4) NULL ,
[movc_montomo] [numeric](18, 4) NOT NULL ,
[movc_montomc] [numeric](18, 4) NULL ,
[movc_fecvalor] [datetime] NULL ,
[movc_feccierre] [datetime] NULL ,
[movc_observacion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[movc_tiporango] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[cier_tipo] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cier_correlativo] [numeric](18, 0) NULL ,
[cuot_codigo] [numeric](18, 0) NULL ,
[cuot_numero] [numeric](4, 0) NULL ,
[movc_fecmeta] [datetime] NULL ,
[movc_primaneta] [t_dom_monto] NULL ,
[movc_fecpagocomis] [t_dom_fecha] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prd_movcomision] WITH NOCHECK ADD
CONSTRAINT [pk_movcomision] PRIMARY KEY CLUSTERED
(
[movc_codigo]
) ON [PRIMARY]
GO
CREATE INDEX [AK_prd_movcomision] ON
[dbo].[prd_movcomision]([movc_tipointerm], [pers_rutcia], &#
91;movc_concepto],
[cone_codigo], [poli_codigo]) ON [PRIMARY]
GO
CREATE INDEX [Ai_prd_movcomision2] ON
[dbo].[prd_movcomision]([pers_rutcia], [poli_codigo], [i
tpo_codigo]) ON
[PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision] ON
[dbo].[prd_movcomision]([poli_codigo], [itpo_codigo]) ON
1;PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision1] ON
[dbo].[prd_movcomision]([prop_codigo]) ON [PRIMARY]
GO
CREATE INDEX [IX_prd_movcomision2] ON
[dbo].[prd_movcomision]([poli_codigo]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use
only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@.@.microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_movc_fecpagocomis] ON
[dbo].[prd_movcomision] ([movc_fecpagocomis]) ')
GO
ALTER TABLE [dbo].[prd_movcomision] ADD
CONSTRAINT [fk_r_cone_movcom] FOREIGN KEY
(
[cone_codigo]
) REFERENCES [dbo].[prd_comnegocio] (
[cone_codigo]
),
CONSTRAINT [fk_r_cuot_movcom] FOREIGN KEY
(
[cuot_codigo],
[cuot_numero]
) REFERENCES [dbo].[prd_cuota] (
[cuot_codigo],
[cuot_numero]
),
CONSTRAINT [fk_r_doccom_movcom] FOREIGN KEY
(
[pers_rutcia],
[infi_codigo],
[doco_numero]
) REFERENCES [dbo].[prd_doccomision] (
[pers_rutcia],
[infi_codigo],
[doco_numero]
),
CONSTRAINT [fk_r_est_movcom] FOREIGN KEY
(
[esta_codigo]
) REFERENCES [dbo].[adm_estado] (
[esta_codigo]
),
CONSTRAINT [fk_r_estcier_movcom] FOREIGN KEY
(
[movc_periodo],
[cier_tipo],
[cier_correlativo]
) REFERENCES [dbo].[prd_estadocierre] (
[cier_periodo],
[cier_tipo],
[cier_correlativo]
),
CONSTRAINT [fk_r_itmpr_movcom] FOREIGN KEY
(
[prop_codigo],
[itpr_codigo]
) REFERENCES [dbo].[vta_itmpropuesta] (
[prop_codigo],
[itpr_codigo]
),
CONSTRAINT [fk_r_mnd_movcom] FOREIGN KEY
(
[mone_codigo]
) REFERENCES [dbo].[adm_moneda] (
[mone_codigo]
)
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_est
cier_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_itm
pr_movcom]
GO
alter table [dbo].[prd_movcomision] nocheck constraint [fk_r_mnd
_movcom]
GO
****************************************
*************************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:ODK5Ric6EHA.1392@.tk2msftngp13.phx.gbl...
> Hi
> I would get rid or A and B. If there is no clustered index on that table,
> make C clustered too, assuming it has good selectivity.
> Without the data types, it is not possible to tell and be 100% sure (your
> attachment gets blocked by Outlook Express)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
> news:eM97v2b6EHA.2032@.tk2msftngp13.phx.gbl...
> [itpo_codigo])
> once
> too
>|||Jorge Luzarraga Castro (jluzarraga@.fidens.cl) writes:
> Ive got a table called 'prd_movcomision' (Ive attached a script with its
> structure) which has several indexes as you can see. There are several
> indexes which are contained in others. For example:
> CREATE INDEX [A] ON [dbo].[prd_movcomision]([pers_rutcia]
, [poli_codigo],
> [itpo_codigo]) ON [PRIMARY]
> GO
> CREATE INDEX [B] ON [dbo].[prd_movcomision]([poli_codigo
],
> [itpo_codigo])
> ON [PRIMARY]
> GO
> CREATE INDEX [C] ON [dbo].[prd_movcomision]([prop_codigo
]) ON [PRIMARY]
> GO
>
> Given the previous indexes, [B] is contained within [A] and &#
91;C] is
> contained within [B]. The question is if this is really neccessary or
> once I have an index A the other indexes (B and C) are redundant.
There are no redudant indexes in your table. There may be unused indexes,
that I can't tell, because that depends on your queries.
While the columns index B is included in index A, a query like:
SELECT * FROM tbl WHERE poli_coidgo = @.val1 AND itpo_codigo = @.val2
cannot seek index A to find any rows. It can scan the index, though.
The difference here is that "seek" means that SQL Server finds the
value by walking the fast way through the index tree. "scan" means
that SQL Server searches all leaf pages of the index, which is considerably
slower. In reality, SQL Server will probably scan the entire table instead,
which is even slower. (As a scan. Beside the scanning the index, SQL Server
will also have to access data pages, and with many hits, that can be more
data-pages access than a table scan.)
The same goes for index C, assuming that you mean to have itpo_codigo hear.
So the important issue here, is whether you actually have any queries for
which these indexes are useful.
> Im having too many problems with the size of the database, it is
> growing too fast.
I don't think dropping indexes is a very good help to address that. In
such case it's better to examine whether there is data that is inserted
multiple times, data which is not removed as it should be etc.
Dropping an index can conserve some space - but it can also kill
performance, if there is a query which needs the index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Redundant indexes
When exactly is an index considered redundant? I've read some general
guidelines about composite indexes, but I'm trying to find some
definitive answers or documentation. For example (fixed width font
will work best):
Table 1 existing indexes:
Name Type Columns
PK1 clustered, unique, PK A
IX1 nonclustered B
IX2 nonclustered ACDE
IX3 nonclustered ACBF
Proposed new index:
IX4 nonclustered AE
Is the proposed new index IX4 redundant? The two columns are already
included in IX2, but they're not adjacent in IX2. Does that matter?
Are any of the existing indexes unneeded?
Table 2 existing indexes:
Name Type Columns
PK1 nonclustered, unique, PK A
IX1 clustered B
IX2 nonclustered C
IX3 nonclustered D
IX4 nonclustered DBEFGHIJKLMNOPQR
Proposed new indexes:
IX5 nonclustered CD
IX6 nonclustered BD
For table 2, are the proposed new indexes redundant? The columns are
already indexed individually, so would those be used? There's also an
existing covering index IX4 that includes two columns from the proposed
index IX6, but in a different order. Could IX4 be used in place of IX6?stavros wrote:
> When exactly is an index considered redundant? I've read some general
> guidelines about composite indexes, but I'm trying to find some
> definitive answers or documentation.
An index I is definitive redundant if its columns C1..Cn are the leading
columns of another index Y on the same table that has the same storage
characteristics (i.e. both non clustered). If clustering is different
then it depends on the access patterns to the table which is to be
retained etc.
> For example (fixed width font
> will work best):
> Table 1 existing indexes:
> Name Type Columns
> PK1 clustered, unique, PK A
> IX1 nonclustered B
> IX2 nonclustered ACDE
> IX3 nonclustered ACBF
> Proposed new index:
> IX4 nonclustered AE
> Is the proposed new index IX4 redundant?
Probably not.
> The two columns are already
> included in IX2, but they're not adjacent in IX2. Does that matter?
Yes. I believe newer Oracle versions can do a skip scan when scanning a
range of the index but I doubt that SQL Server can do it. You'd have to
check with the docs for the details.
> Are any of the existing indexes unneeded?
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
Theoretically IX3 is redundant - although it might be used because it
needs less space then IX4.
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
> For table 2, are the proposed new indexes redundant? The columns are
> already indexed individually, so would those be used? There's also an
> existing covering index IX4 that includes two columns from the proposed
> index IX6, but in a different order. Could IX4 be used in place of IX6?
Depends. B has higher selectivity IX6 might have an advantage over IX4
- especially since IX4 is so large.
As you see even redundant indexes can make sense. It all depends...
robert|||On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
wrote:
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
Neither of the new indexes are redundant, but each of them makes an
existing index redundant.
Roy|||Roy Harvey wrote:
> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> wrote:
>
> Neither of the new indexes are redundant, but each of them makes an
> existing index redundant.
> Roy
But since the columns referenced in the new indexes are already indexed
individually, doesn't that make the new indexes redundant?
http://www.sql-server-performance.c...ite_indexes.asp says that
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index", and also
implies that single-column indexes are generally preferred.|||So who are you going to believe? ;-)
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index"
... "just as if" is not true.
SQL Server can join two or more individual indexes together, but there is
more internal work involved to do so, much more than if you already have a
composite index on the columns.
"single-column indexes are generally preferred"
Preferred by whom? Maybe just by the writers of this article. Multi column
indexes have much greater chance to be used as covering indexes, which avoid
table bookmark lookups and are generally preferred.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"stavros" <stavros@.mailinator.com> wrote in message
news:1162249345.592630.63930@.m7g2000cwm.googlegroups.com...
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant?
> http://www.sql-server-performance.c...ite_indexes.asp says that
> "SQL Server has the ability to join two or more individual indexes and
> intersect them, just as if you were using a composite index", and also
> implies that single-column indexes are generally preferred.
>|||Robert Klemme wrote:
> An index I is definitive redundant if its columns C1..Cn are the leading
> columns of another index Y on the same table that has the same storage
> characteristics (i.e. both non clustered). If clustering is different
> then it depends on the access patterns to the table which is to be
> retained etc.
>
Thanks for the clear answer, that helps.|||Another incorrect statement from the same article:
"if you create a composite index, such as "City, State", then a query
such as "WHERE City = 'Springfield'" will use the index, but the query
"WHERE STATE = 'MO'" will not use the index."
;)|||Agreed. Should be:
"but the query "WHERE STATE = 'MO'" will not use the index to search for the
rows that qualifies, it
can be used to scan for the rows."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1162259023.159887.170330@.b28g2000cwb.googlegroups.com...
> Another incorrect statement from the same article:
> "if you create a composite index, such as "City, State", then a query
> such as "WHERE City = 'Springfield'" will use the index, but the query
> "WHERE STATE = 'MO'" will not use the index."
> ;)
>|||On 31.10.2006 00:13, stavros wrote:
> Robert Klemme wrote:
> Thanks for the clear answer, that helps.
You're welcome. But keep in mind that redundant != superfluous as I
tried to point out for the case of the huge index.
Regards
robert|||stavros wrote:
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant? [snip]
That is why "redundant" is not all that useful when you are trying to
maximize query performance. I mean, all but the clustered index are by
definition redundant, because their data is already in the table, and
can be retrieved with a table scan or clustered index scan.
Index IX5 will make IX2 redundant. But if column C is very narrow (let's
say a smallint) and D very wide (let's say char(150)), then finding rows
in index IX5 probably requires more reads then finding them in IX2. It
is up to you to decide which performance is satisfactory, and balance
that against the cost that each additional index adds to
inserts/updates/deletes.
Gert-Jan
guidelines about composite indexes, but I'm trying to find some
definitive answers or documentation. For example (fixed width font
will work best):
Table 1 existing indexes:
Name Type Columns
PK1 clustered, unique, PK A
IX1 nonclustered B
IX2 nonclustered ACDE
IX3 nonclustered ACBF
Proposed new index:
IX4 nonclustered AE
Is the proposed new index IX4 redundant? The two columns are already
included in IX2, but they're not adjacent in IX2. Does that matter?
Are any of the existing indexes unneeded?
Table 2 existing indexes:
Name Type Columns
PK1 nonclustered, unique, PK A
IX1 clustered B
IX2 nonclustered C
IX3 nonclustered D
IX4 nonclustered DBEFGHIJKLMNOPQR
Proposed new indexes:
IX5 nonclustered CD
IX6 nonclustered BD
For table 2, are the proposed new indexes redundant? The columns are
already indexed individually, so would those be used? There's also an
existing covering index IX4 that includes two columns from the proposed
index IX6, but in a different order. Could IX4 be used in place of IX6?stavros wrote:
> When exactly is an index considered redundant? I've read some general
> guidelines about composite indexes, but I'm trying to find some
> definitive answers or documentation.
An index I is definitive redundant if its columns C1..Cn are the leading
columns of another index Y on the same table that has the same storage
characteristics (i.e. both non clustered). If clustering is different
then it depends on the access patterns to the table which is to be
retained etc.
> For example (fixed width font
> will work best):
> Table 1 existing indexes:
> Name Type Columns
> PK1 clustered, unique, PK A
> IX1 nonclustered B
> IX2 nonclustered ACDE
> IX3 nonclustered ACBF
> Proposed new index:
> IX4 nonclustered AE
> Is the proposed new index IX4 redundant?
Probably not.
> The two columns are already
> included in IX2, but they're not adjacent in IX2. Does that matter?
Yes. I believe newer Oracle versions can do a skip scan when scanning a
range of the index but I doubt that SQL Server can do it. You'd have to
check with the docs for the details.
> Are any of the existing indexes unneeded?
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
Theoretically IX3 is redundant - although it might be used because it
needs less space then IX4.
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
> For table 2, are the proposed new indexes redundant? The columns are
> already indexed individually, so would those be used? There's also an
> existing covering index IX4 that includes two columns from the proposed
> index IX6, but in a different order. Could IX4 be used in place of IX6?
Depends. B has higher selectivity IX6 might have an advantage over IX4
- especially since IX4 is so large.
As you see even redundant indexes can make sense. It all depends...
robert|||On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
wrote:
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
Neither of the new indexes are redundant, but each of them makes an
existing index redundant.
Roy|||Roy Harvey wrote:
> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> wrote:
>
> Neither of the new indexes are redundant, but each of them makes an
> existing index redundant.
> Roy
But since the columns referenced in the new indexes are already indexed
individually, doesn't that make the new indexes redundant?
http://www.sql-server-performance.c...ite_indexes.asp says that
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index", and also
implies that single-column indexes are generally preferred.|||So who are you going to believe? ;-)
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index"
... "just as if" is not true.
SQL Server can join two or more individual indexes together, but there is
more internal work involved to do so, much more than if you already have a
composite index on the columns.
"single-column indexes are generally preferred"
Preferred by whom? Maybe just by the writers of this article. Multi column
indexes have much greater chance to be used as covering indexes, which avoid
table bookmark lookups and are generally preferred.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"stavros" <stavros@.mailinator.com> wrote in message
news:1162249345.592630.63930@.m7g2000cwm.googlegroups.com...
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant?
> http://www.sql-server-performance.c...ite_indexes.asp says that
> "SQL Server has the ability to join two or more individual indexes and
> intersect them, just as if you were using a composite index", and also
> implies that single-column indexes are generally preferred.
>|||Robert Klemme wrote:
> An index I is definitive redundant if its columns C1..Cn are the leading
> columns of another index Y on the same table that has the same storage
> characteristics (i.e. both non clustered). If clustering is different
> then it depends on the access patterns to the table which is to be
> retained etc.
>
Thanks for the clear answer, that helps.|||Another incorrect statement from the same article:
"if you create a composite index, such as "City, State", then a query
such as "WHERE City = 'Springfield'" will use the index, but the query
"WHERE STATE = 'MO'" will not use the index."
;)|||Agreed. Should be:
"but the query "WHERE STATE = 'MO'" will not use the index to search for the
rows that qualifies, it
can be used to scan for the rows."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1162259023.159887.170330@.b28g2000cwb.googlegroups.com...
> Another incorrect statement from the same article:
> "if you create a composite index, such as "City, State", then a query
> such as "WHERE City = 'Springfield'" will use the index, but the query
> "WHERE STATE = 'MO'" will not use the index."
> ;)
>|||On 31.10.2006 00:13, stavros wrote:
> Robert Klemme wrote:
> Thanks for the clear answer, that helps.
You're welcome. But keep in mind that redundant != superfluous as I
tried to point out for the case of the huge index.
Regards
robert|||stavros wrote:
> Roy Harvey wrote:
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant? [snip]
That is why "redundant" is not all that useful when you are trying to
maximize query performance. I mean, all but the clustered index are by
definition redundant, because their data is already in the table, and
can be retrieved with a table scan or clustered index scan.
Index IX5 will make IX2 redundant. But if column C is very narrow (let's
say a smallint) and D very wide (let's say char(150)), then finding rows
in index IX5 probably requires more reads then finding them in IX2. It
is up to you to decide which performance is satisfactory, and balance
that against the cost that each additional index adds to
inserts/updates/deletes.
Gert-Jan
Subscribe to:
Posts (Atom)