Question
Incessant Technologies Pvt Ltd.
AU
Last activity: 27 Apr 2016 9:43 EDT
Can the OOTB index index_key_purp on the table pc_index_workparty be updated?
We are seeing some performance issues with queries that are being run on pc_index_workpary and updating the OOTB index index_key_purp helped in improving the performance of these queries.
New Index:
CREATE NONCLUSTERED INDEX [index_key_purp] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC,
[pxIndexPurpose] ASC,
[(pxObjClass)],
[(pxPartyRole)],
[(pyPartyIdentifier)]
)
Would like to know if it is safe to update (drop and recreate) the OOTB index with the same name but new definition or create a new index altogether with the new defintion, can some one assist please?
We did an update (drop and recreate) the existing index as the existing index also would do the similar thing
Existing Index:
CREATE NONCLUSTERED INDEX [index_key_purp] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC,
[pxIndexPurpose] ASC,
[(pxObjClass)],
We are seeing some performance issues with queries that are being run on pc_index_workpary and updating the OOTB index index_key_purp helped in improving the performance of these queries.
New Index:
CREATE NONCLUSTERED INDEX [index_key_purp] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC,
[pxIndexPurpose] ASC,
[(pxObjClass)],
[(pxPartyRole)],
[(pyPartyIdentifier)]
)
Would like to know if it is safe to update (drop and recreate) the OOTB index with the same name but new definition or create a new index altogether with the new defintion, can some one assist please?
We did an update (drop and recreate) the existing index as the existing index also would do the similar thing
Existing Index:
CREATE NONCLUSTERED INDEX [index_key_purp] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC,
[pxIndexPurpose] ASC,
[(pxObjClass)],
[(pxPartyRole)],
[(pyPartyIdentifier)]
)
Message was edited by: Lochan to add Category
Incessant Technologies Pvt Ltd.
AU
Apologies, please ignore the Existing Index given in the original post.
This one is the existing OOTB index - index_key_purp
/****** Object: Index [index_key_purp] Script Date: 11/04/2016 10:49:58 AM ******/
CREATE NONCLUSTERED INDEX [index_key_purp] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC,
[pxIndexPurpose] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Also there is another OOTB index (index_party_key), this looks more similar to above index index_key_purp, when is this index used, will there be any impact if this index is deleted, having additional indexes might impact insert / updates.
/****** Object: Index [index_party_key] Script Date: 11/04/2016 10:49:53 AM ******/
CREATE NONCLUSTERED INDEX [index_party_key] ON [dbo].[pc_index_workparty]
(
[pxInsIndexedKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
We would like to get rid of duplicate indexes and create a new optimized index (or modify the existing one).
Incessant Technologies
IN
Hi Hareen,
It is not good idea to delete or modify the existing index in general. Because whatever is provided OOTB is developed for solving some purpose and if you delete/modify it will impact some functionality.
If needed you should go for creating new index I would suggest.
Thanks
Rajesh