Re: Re : Non clustered indices (ITSM 7.3)
I stumbled across a fresh 7.3 installation and only 13 indices all together and same number at the database level ... so will be discussing this with the DBA over the morning coffee. Thanks --- En date de : Jeu 4.6.09, Lyle Taylor a écrit : De: Lyle Taylor Objet: Re: Re : Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 21h16 ** Is there an appropriate existing index that you could simply add the field to rather than creating a new one for this field? That way, you don’t exceed the limit, and it may be more efficient for the DB as well. Lyle From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Frex Popo Sent: Thursday, June 04, 2009 12:39 PM To: arslist@ARSLIST.ORG Subject: Re : Non clustered indices (ITSM 7.3) ** Reason I am asking is that I think it's better to create the index through the Admin Client so that the server is aware of it rather than having to create the index at the DB level.. But if it comes to having to create it at the DB level I think I would opt for the latter since having an index is better than not having it at all :) --- En date de : Jeu 4.6.09, Frex Popo a écrit : De: Frex Popo Objet: Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 12h26 Hello everyone, Found a number of join forms using a particular column in the join criteria. This column did not look it was indexed on the base form (i.e. HPD:Help Desk), so thought let's create one. When I tried to do so I got a SQL error: No se pudo crear índice no agrupado 'I1035_904003986_1' porque supera el máximo de 249 permitido por tabla o vista. (SQL Server 1910) (ARERR 552). Failure during SQL operation to the database : 904003986 (ARWARN 552). This simply means that the SQL server could not create the nonclustered index as it is beyond the maximum allowed. The form has 13 indexes. As a test I removed an existing non clustered index and create the new one and it works just fine, but of course I want to keep all the existing ones and be able to create the new one. I checked the table definition in the SQL Enterprise client by running sp_helpindex and got a list of some 250 entries. Now, I don't have a non customised version of this form nor do I have another environment to check if this is all created by the remedy server, hence the question, does anyone have all these indices defined on the Help Desk form in their ITSM 7.0 installation? I am using ARS 7.1 patch 4 / ITSM 7.3 / SQL server 2005. Here is part of the 250 entries. index_name,index_description,index_keys I1035_100019_1,nonclustered located on PRIMARY,C100019, C100018 I1035_100063_1,nonclustered located on PRIMARY,C100063, C100064, C100065 I1035_100079_1,nonclustered located on PRIMARY,C100079 I1035_100080_1,nonclustered located on PRIMARY,C100080 I1035_100161_1,nonclustered, unique located on PRIMARY,C100161 I1035_100427_1,nonclustered located on PRIMARY,C100427 I1035_100869_1,nonclustered located on PRIMARY,C100869 I1035_100875_1,nonclustered located on PRIMARY,C100875 I1035_179_1,nonclustered, unique located on PRIMARY,C179 I1035_20003_1,nonclustered located on PRIMARY,C20003, C20004, C20005 I1035_240001002_1,nonclustered located on PRIMARY,C240001002 I1035_4_1,nonclustered located on PRIMARY,C4 I1035_7_1,nonclustered located on PRIMARY,C7 missing_index_100016,nonclustered located on PRIMARY,C100079, C103009, C7 missing_index_100040,nonclustered located on PRIMARY,C100014, C100251, C100296, C100082, C100217 missing_index_100058,nonclustered located on PRIMARY,C100014, C100251, C100079, C100082, C100217 missing_index_100062,nonclustered located on PRIMARY,C100014, C100079, C100217, C100251, C100082 missing_index_100064,nonclustered located on PRIMARY,C100014, C100217, C100251, C100082 missing_index_100089,nonclustered located on PRIMARY,C100079, C7 . . . . missing_index_99846,nonclustered located on PRIMARY,C100099, C100251, C7 missing_index_99878,nonclustered located on PRIMARY,C100014, C100251, C100082 missing_index_99880,nonclustered located on PRIMARY,C100014, C100251, C100082, C100217 missing_index_99882,nonclustered located on PRIMARY,C100014, C100079, C100251, C100296, C100572, C100082, C100217 missing_index_99929,nonclustered located on PRIMARY,C7 PK__T1035__4032018A,clustered, unique, primary key located on PRIMARY,C1 Regards frex _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or dis
Re: Re : Non clustered indices (ITSM 7.3)
Thanks Rick, Yes I am aware of that ... having too many indices on a table requires space and resources to maintain these indeces to start with hence my question, so I can first find out what came initially with the helpdesk form and see what we have added through the Admin tool (none documented here yet) and what the DBAs without telling the rest, how many indices they have added and why without telling the rest. :-) Anyway, I think I will be taking this up with the DBAs. Have a good day frex --- En date de : Jeu 4.6.09, Rick Cook a écrit : De: Rick Cook Objet: Re: Re : Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 20h49 Frex, that limit is there for a reason. Once you exceed about 8 indices, performance increasingly suffers, especially on submits. Rick From: Frex Popo Date: Thu, 4 Jun 2009 18:39:20 + To: Subject: Re : Non clustered indices (ITSM 7.3) ** Reason I am asking is that I think it's better to create the index through the Admin Client so that the server is aware of it rather than having to create the index at the DB level.. But if it comes to having to create it at the DB level I think I would opt for the latter since having an index is better than not having it at all :) --- En date de : Jeu 4.6.09, Frex Popo a écrit : De: Frex Popo Objet: Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 12h26 Hello everyone, Found a number of join forms using a particular column in the join criteria. This column did not look it was indexed on the base form (i.e. HPD:Help Desk), so thought let's create one. When I tried to do so I got a SQL error: No se pudo crear índice no agrupado 'I1035_904003986_1' porque supera el máximo de 249 permitido por tabla o vista. (SQL Server 1910) (ARERR 552). Failure during SQL operation to the database : 904003986 (ARWARN 552). This simply means that the SQL server could not create the nonclustered index as it is beyond the maximum allowed. The form has 13 indexes. As a test I removed an existing non clustered index and create the new one and it works just fine, but of course I want to keep all the existing ones and be able to create the new one. I checked the table definition in the SQL Enterprise client by running sp_helpindex and got a list of some 250 entries. Now, I don't have a non customised version of this form nor do I have another environment to check if this is all created by the remedy server, hence the question, does anyone have all these indices defined on the Help Desk form in their ITSM 7.0 installation? I am using ARS 7.1 patch 4 / ITSM 7.3 / SQL server 2005. Here is part of the 250 entries. index_name,index_description,index_keys I1035_100019_1,nonclustered located on PRIMARY,C100019, C100018 I1035_100063_1,nonclustered located on PRIMARY,C100063, C100064, C100065 I1035_100079_1,nonclustered located on PRIMARY,C100079 I1035_100080_1,nonclustered located on PRIMARY,C100080 I1035_100161_1,nonclustered, unique located on PRIMARY,C100161 I1035_100427_1,nonclustered located on PRIMARY,C100427 I1035_100869_1,nonclustered located on PRIMARY,C100869 I1035_100875_1,nonclustered located on PRIMARY,C100875 I1035_179_1,nonclustered, unique located on PRIMARY,C179 I1035_20003_1,nonclustered located on PRIMARY,C20003, C20004, C20005 I1035_240001002_1,nonclustered located on PRIMARY,C240001002 I1035_4_1,nonclustered located on PRIMARY,C4 I1035_7_1,nonclustered located on PRIMARY,C7 missing_index_100016,nonclustered located on PRIMARY,C100079, C103009, C7 missing_index_100040,nonclustered located on PRIMARY,C100014, C100251, C100296, C100082, C100217 missing_index_100058,nonclustered located on PRIMARY,C100014, C100251, C100079, C100082, C100217 missing_index_100062,nonclustered located on PRIMARY,C100014, C100079, C100217, C100251, C100082 missing_index_100064,nonclustered located on PRIMARY,C100014, C100217, C100251, C100082 missing_index_100089,nonclustered located on PRIMARY,C100079, C7 . . . . missing_index_99846,nonclustered located on PRIMARY,C100099, C100251, C7 missing_index_99878,nonclustered located on PRIMARY,C100014, C100251, C100082 missing_index_99880,nonclustered located on PRIMARY,C100014, C100251, C100082, C100217 missing_index_99882,nonclustered located on PRIMARY,C100014, C100079, C100251, C100296, C100572, C100082, C100217 missing_index_99929,nonclustered located on PRIMARY,C7 PK__T1035__4032018A,clustered, unique, primary key located on PRIMARY,C1 Regards frex _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ ___ UNS
Re: Re : Non clustered indices (ITSM 7.3)
Is there an appropriate existing index that you could simply add the field to rather than creating a new one for this field? That way, you don't exceed the limit, and it may be more efficient for the DB as well. Lyle From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Frex Popo Sent: Thursday, June 04, 2009 12:39 PM To: arslist@ARSLIST.ORG Subject: Re : Non clustered indices (ITSM 7.3) ** Reason I am asking is that I think it's better to create the index through the Admin Client so that the server is aware of it rather than having to create the index at the DB level.. But if it comes to having to create it at the DB level I think I would opt for the latter since having an index is better than not having it at all :) --- En date de : Jeu 4.6.09, Frex Popo a écrit : De: Frex Popo Objet: Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 12h26 Hello everyone, Found a number of join forms using a particular column in the join criteria. This column did not look it was indexed on the base form (i.e. HPD:Help Desk), so thought let's create one. When I tried to do so I got a SQL error: No se pudo crear índice no agrupado 'I1035_904003986_1' porque supera el máximo de 249 permitido por tabla o vista. (SQL Server 1910) (ARERR 552). Failure during SQL operation to the database : 904003986 (ARWARN 552). This simply means that the SQL server could not create the nonclustered index as it is beyond the maximum allowed. The form has 13 indexes. As a test I removed an existing non clustered index and create the new one and it works just fine, but of course I want to keep all the existing ones and be able to create the new one. I checked the table definition in the SQL Enterprise client by running sp_helpindex and got a list of some 250 entries. Now, I don't have a non customised version of this form nor do I have another environment to check if this is all created by the remedy server, hence the question, does anyone have all these indices defined on the Help Desk form in their ITSM 7.0 installation? I am using ARS 7.1 patch 4 / ITSM 7.3 / SQL server 2005. Here is part of the 250 entries. index_name,index_description,index_keys I1035_100019_1,nonclustered located on PRIMARY,C100019, C100018 I1035_100063_1,nonclustered located on PRIMARY,C100063, C100064, C100065 I1035_100079_1,nonclustered located on PRIMARY,C100079 I1035_100080_1,nonclustered located on PRIMARY,C100080 I1035_100161_1,nonclustered, unique located on PRIMARY,C100161 I1035_100427_1,nonclustered located on PRIMARY,C100427 I1035_100869_1,nonclustered located on PRIMARY,C100869 I1035_100875_1,nonclustered located on PRIMARY,C100875 I1035_179_1,nonclustered, unique located on PRIMARY,C179 I1035_20003_1,nonclustered located on PRIMARY,C20003, C20004, C20005 I1035_240001002_1,nonclustered located on PRIMARY,C240001002 I1035_4_1,nonclustered located on PRIMARY,C4 I1035_7_1,nonclustered located on PRIMARY,C7 missing_index_100016,nonclustered located on PRIMARY,C100079, C103009, C7 missing_index_100040,nonclustered located on PRIMARY,C100014, C100251, C100296, C100082, C100217 missing_index_100058,nonclustered located on PRIMARY,C100014, C100251, C100079, C100082, C100217 missing_index_100062,nonclustered located on PRIMARY,C100014, C100079, C100217, C100251, C100082 missing_index_100064,nonclustered located on PRIMARY,C100014, C100217, C100251, C100082 missing_index_100089,nonclustered located on PRIMARY,C100079, C7 . . . . missing_index_99846,nonclustered located on PRIMARY,C100099, C100251, C7 missing_index_99878,nonclustered located on PRIMARY,C100014, C100251, C100082 missing_index_99880,nonclustered located on PRIMARY,C100014, C100251, C100082, C100217 missing_index_99882,nonclustered located on PRIMARY,C100014, C100079, C100251, C100296, C100572, C100082, C100217 missing_index_99929,nonclustered located on PRIMARY,C7 PK__T1035__4032018A,clustered, unique, primary key located on PRIMARY,C1 Regards frex _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
Re: Re : Non clustered indices (ITSM 7.3)
Frex, that limit is there for a reason. Once you exceed about 8 indices, performance increasingly suffers, especially on submits. Rick -Original Message- From: Frex Popo Date: Thu, 4 Jun 2009 18:39:20 To: Subject: Re : Non clustered indices (ITSM 7.3) Reason I am asking is that I think it's better to create the index through the Admin Client so that the server is aware of it rather than having to create the index at the DB level.. But if it comes to having to create it at the DB level I think I would opt for the latter since having an index is better than not having it at all :) --- En date de : Jeu 4.6.09, Frex Popo a écrit : De: Frex Popo Objet: Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 12h26 Hello everyone, Found a number of join forms using a particular column in the join criteria. This column did not look it was indexed on the base form (i.e. HPD:Help Desk), so thought let's create one. When I tried to do so I got a SQL error: No se pudo crear índice no agrupado 'I1035_904003986_1' porque supera el máximo de 249 permitido por tabla o vista. (SQL Server 1910) (ARERR 552). Failure during SQL operation to the database : 904003986 (ARWARN 552). This simply means that the SQL server could not create the nonclustered index as it is beyond the maximum allowed. The form has 13 indexes. As a test I removed an existing non clustered index and create the new one and it works just fine, but of course I want to keep all the existing ones and be able to create the new one. I checked the table definition in the SQL Enterprise client by running sp_helpindex and got a list of some 250 entries. Now, I don't have a non customised version of this form nor do I have another environment to check if this is all created by the remedy server, hence the question, does anyone have all these indices defined on the Help Desk form in their ITSM 7.0 installation? I am using ARS 7.1 patch 4 / ITSM 7.3 / SQL server 2005. Here is part of the 250 entries. index_name,index_description,index_keys I1035_100019_1,nonclustered located on PRIMARY,C100019, C100018 I1035_100063_1,nonclustered located on PRIMARY,C100063, C100064, C100065 I1035_100079_1,nonclustered located on PRIMARY,C100079 I1035_100080_1,nonclustered located on PRIMARY,C100080 I1035_100161_1,nonclustered, unique located on PRIMARY,C100161 I1035_100427_1,nonclustered located on PRIMARY,C100427 I1035_100869_1,nonclustered located on PRIMARY,C100869 I1035_100875_1,nonclustered located on PRIMARY,C100875 I1035_179_1,nonclustered, unique located on PRIMARY,C179 I1035_20003_1,nonclustered located on PRIMARY,C20003, C20004, C20005 I1035_240001002_1,nonclustered located on PRIMARY,C240001002 I1035_4_1,nonclustered located on PRIMARY,C4 I1035_7_1,nonclustered located on PRIMARY,C7 missing_index_100016,nonclustered located on PRIMARY,C100079, C103009, C7 missing_index_100040,nonclustered located on PRIMARY,C100014, C100251, C100296, C100082, C100217 missing_index_100058,nonclustered located on PRIMARY,C100014, C100251, C100079, C100082, C100217 missing_index_100062,nonclustered located on PRIMARY,C100014, C100079, C100217, C100251, C100082 missing_index_100064,nonclustered located on PRIMARY,C100014, C100217, C100251, C100082 missing_index_100089,nonclustered located on PRIMARY,C100079, C7 . . . . missing_index_99846,nonclustered located on PRIMARY,C100099, C100251, C7 missing_index_99878,nonclustered located on PRIMARY,C100014, C100251, C100082 missing_index_99880,nonclustered located on PRIMARY,C100014, C100251, C100082, C100217 missing_index_99882,nonclustered located on PRIMARY,C100014, C100079, C100251, C100296, C100572, C100082, C100217 missing_index_99929,nonclustered located on PRIMARY,C7 PK__T1035__4032018A,clustered, unique, primary key located on PRIMARY,C1 Regards frex ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
Re : Non clustered indices (ITSM 7.3)
Reason I am asking is that I think it's better to create the index through the Admin Client so that the server is aware of it rather than having to create the index at the DB level.. But if it comes to having to create it at the DB level I think I would opt for the latter since having an index is better than not having it at all :) --- En date de : Jeu 4.6.09, Frex Popo a écrit : De: Frex Popo Objet: Non clustered indices (ITSM 7.3) À: arslist@ARSLIST.ORG Date: Jeudi 4 Juin 2009, 12h26 Hello everyone, Found a number of join forms using a particular column in the join criteria. This column did not look it was indexed on the base form (i.e. HPD:Help Desk), so thought let's create one. When I tried to do so I got a SQL error: No se pudo crear índice no agrupado 'I1035_904003986_1' porque supera el máximo de 249 permitido por tabla o vista. (SQL Server 1910) (ARERR 552). Failure during SQL operation to the database : 904003986 (ARWARN 552). This simply means that the SQL server could not create the nonclustered index as it is beyond the maximum allowed. The form has 13 indexes. As a test I removed an existing non clustered index and create the new one and it works just fine, but of course I want to keep all the existing ones and be able to create the new one. I checked the table definition in the SQL Enterprise client by running sp_helpindex and got a list of some 250 entries. Now, I don't have a non customised version of this form nor do I have another environment to check if this is all created by the remedy server, hence the question, does anyone have all these indices defined on the Help Desk form in their ITSM 7.0 installation? I am using ARS 7.1 patch 4 / ITSM 7.3 / SQL server 2005. Here is part of the 250 entries. index_name,index_description,index_keys I1035_100019_1,nonclustered located on PRIMARY,C100019, C100018 I1035_100063_1,nonclustered located on PRIMARY,C100063, C100064, C100065 I1035_100079_1,nonclustered located on PRIMARY,C100079 I1035_100080_1,nonclustered located on PRIMARY,C100080 I1035_100161_1,nonclustered, unique located on PRIMARY,C100161 I1035_100427_1,nonclustered located on PRIMARY,C100427 I1035_100869_1,nonclustered located on PRIMARY,C100869 I1035_100875_1,nonclustered located on PRIMARY,C100875 I1035_179_1,nonclustered, unique located on PRIMARY,C179 I1035_20003_1,nonclustered located on PRIMARY,C20003, C20004, C20005 I1035_240001002_1,nonclustered located on PRIMARY,C240001002 I1035_4_1,nonclustered located on PRIMARY,C4 I1035_7_1,nonclustered located on PRIMARY,C7 missing_index_100016,nonclustered located on PRIMARY,C100079, C103009, C7 missing_index_100040,nonclustered located on PRIMARY,C100014, C100251, C100296, C100082, C100217 missing_index_100058,nonclustered located on PRIMARY,C100014, C100251, C100079, C100082, C100217 missing_index_100062,nonclustered located on PRIMARY,C100014, C100079, C100217, C100251, C100082 missing_index_100064,nonclustered located on PRIMARY,C100014, C100217, C100251, C100082 missing_index_100089,nonclustered located on PRIMARY,C100079, C7 . . . . missing_index_99846,nonclustered located on PRIMARY,C100099, C100251, C7 missing_index_99878,nonclustered located on PRIMARY,C100014, C100251, C100082 missing_index_99880,nonclustered located on PRIMARY,C100014, C100251, C100082, C100217 missing_index_99882,nonclustered located on PRIMARY,C100014, C100079, C100251, C100296, C100572, C100082, C100217 missing_index_99929,nonclustered located on PRIMARY,C7 PK__T1035__4032018A,clustered, unique, primary key located on PRIMARY,C1 Regards frex ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
