Hi to all,
As a result of MsSQL database administrators’ report, it is said that some queries take so long time and needed to be indexed. In Sailpoint IIQ, as I know from the documentation we can index attributes of objects (Identity, Account, Certification etc.). Is there any other database scripts that we can use to make maintenance of the database and define indexes?
This is a topic that I’m interested as well. We are having issue with entitlement load during an access request.
@gureronder - Can you share any more specific details on what queries they report as being long-running or not performant?
@gabs1 - For your issue, that’s why Sailpoint built the Full-text Index functionality, which stores role and entitlement metadata in a host-local cache to service the UI for access requests and such.
These are the default indexes created for each table you referenced:
create index spt_identity_ent_name_ci on identityiq.spt_identity_entitlement (name);
GO
create index spt_identity_ent_value_ci on identityiq.spt_identity_entitlement (value);
GO
create index spt_identity_ent_nativeid_ci on identityiq.spt_identity_entitlement (native_identity);
GO
create index spt_identity_ent_instance_ci on identityiq.spt_identity_entitlement (instance);
GO
create index spt_identity_ent_ag_state on identityiq.spt_identity_entitlement (aggregation_state);
GO
create index spt_identity_ent_source_ci on identityiq.spt_identity_entitlement (source);
GO
create index spt_identity_ent_assigned on identityiq.spt_identity_entitlement (assigned);
GO
create index spt_identity_ent_allowed on identityiq.spt_identity_entitlement (allowed);
GO
create index spt_identity_ent_role_granted on identityiq.spt_identity_entitlement (granted_by_role);
GO
create index spt_identity_ent_assgnid on identityiq.spt_identity_entitlement (assignment_id);
GO
create index spt_identity_ent_type on identityiq.spt_identity_entitlement (type);
GO
alter table identityiq.spt_identity_entitlement
add constraint FKlnoli5e2k3cofry0kh5lqwvk2
foreign key (owner)
references identityiq.spt_identity;
GO
create index FKlnoli5e2k3cofry0kh5lqwvk2 on identityiq.spt_identity_entitlement (owner);
GO
alter table identityiq.spt_identity_entitlement
add constraint FKqy3hyiptyuoo0ik8nfewymdio
foreign key (application)
references identityiq.spt_application;
GO
create index FKqy3hyiptyuoo0ik8nfewymdio on identityiq.spt_identity_entitlement (application);
GO
alter table identityiq.spt_identity_entitlement
add constraint FKjief1jwgixlilqiqsvkpx0k9e
foreign key (identity_id)
references identityiq.spt_identity;
GO
create index FKjief1jwgixlilqiqsvkpx0k9e on identityiq.spt_identity_entitlement (identity_id);
GO
alter table identityiq.spt_identity_entitlement
add constraint FK9p3id5o2as2stlq47md58fm3b
foreign key (request_item)
references identityiq.spt_identity_request_item;
GO
create index FK9p3id5o2as2stlq47md58fm3b on identityiq.spt_identity_entitlement (request_item);
GO
alter table identityiq.spt_identity_entitlement
add constraint FKcn0l4kl1lpjkg7usf4okua4d8
foreign key (pending_request_item)
references identityiq.spt_identity_request_item;
GO
create index FKcn0l4kl1lpjkg7usf4okua4d8 on identityiq.spt_identity_entitlement (pending_request_item);
GO
alter table identityiq.spt_identity_entitlement
add constraint FKbpm8wgk9stf16g8w9ujx10qw3
foreign key (certification_item)
references identityiq.spt_certification_item;
GO
create index FKbpm8wgk9stf16g8w9ujx10qw3 on identityiq.spt_identity_entitlement (certification_item);
GO
alter table identityiq.spt_identity_entitlement
add constraint FK6cwcsuwgv6ydwqpnm6jto062q
foreign key (pending_certification_item)
references identityiq.spt_certification_item;
GO
create index FK6cwcsuwgv6ydwqpnm6jto062q on identityiq.spt_identity_entitlement (pending_certification_item);
GO
create index spt_identity_id on identityiq.spt_identity_snapshot (identity_id);
GO
create index spt_idsnap_id_name on identityiq.spt_identity_snapshot (identity_name);
GO
alter table identityiq.spt_identity_snapshot
add constraint FKh03yxqsq7ebvwlwcthihtym07
foreign key (owner)
references identityiq.spt_identity;
GO
create index FKh03yxqsq7ebvwlwcthihtym07 on identityiq.spt_identity_snapshot (owner);
GO
alter table identityiq.spt_identity_snapshot
add constraint FKj24y3i8my2r0c3il58dlww3fa
foreign key (assigned_scope)
references identityiq.spt_scope;
GO
create index FKj24y3i8my2r0c3il58dlww3fa on identityiq.spt_identity_snapshot (assigned_scope);
GO
alter table identityiq.spt_identity_trigger
add constraint FKaoupv62m0yus73314iwn0fclq
foreign key (owner)
references identityiq.spt_identity;
GO
create index spt_prvtrans_name on identityiq.spt_provisioning_transaction (name);
GO
create index spt_prvtrans_created on identityiq.spt_provisioning_transaction (created);
GO
create index spt_prvtrans_op on identityiq.spt_provisioning_transaction (operation);
GO
create index spt_prvtrans_src on identityiq.spt_provisioning_transaction (source);
GO
create index spt_prvtrans_app_ci on identityiq.spt_provisioning_transaction (application_name);
GO
create index spt_prvtrans_idn_ci on identityiq.spt_provisioning_transaction (identity_name);
GO
create index spt_prvtrans_iddn_ci on identityiq.spt_provisioning_transaction (identity_display_name);
GO
create index spt_prvtrans_nid_ci on identityiq.spt_provisioning_transaction (native_identity);
GO
create index spt_prvtrans_adn_ci on identityiq.spt_provisioning_transaction (account_display_name);
GO
create index spt_prvtrans_integ_ci on identityiq.spt_provisioning_transaction (integration);
GO
create index spt_prvtrans_forced on identityiq.spt_provisioning_transaction (forced);
GO
create index spt_prvtrans_type on identityiq.spt_provisioning_transaction (type);
GO
create index spt_prvtrans_status on identityiq.spt_provisioning_transaction (status);
GO
create index server_stat_snapshot on identityiq.spt_server_statistic (snapshot_name);
GO
alter table identityiq.spt_server_statistic
add constraint FKo4ubx9xkjeqxgnsil2kq8863b
foreign key (host)
references identityiq.spt_server;
GO
create index FKo4ubx9xkjeqxgnsil2kq8863b on identityiq.spt_server_statistic (host);
GO
alter table identityiq.spt_server_statistic
add constraint FK5q7ultbynm7wn0wki1a0vhse7
foreign key (monitoring_statistic)
references identityiq.spt_monitoring_statistic;
GO
create index FK5q7ultbynm7wn0wki1a0vhse7 on identityiq.spt_server_statistic (monitoring_statistic);
GO
You’ll see most commonly-used fields have indexes, as well as any fields that are used for foreign keys. Most DB engines SHOULD have primary-key fields indexed as well.
Generic Hibernate fields like created and modified often will not have indexes as they will be inherently unique, so the index would contain the same number of elements as the field itself. That all being said, if your DBA team feels that adding some additional indexes based on your usage patterns would help improve DB performance, there’s no harm in trying that out. Just make sure that they also provide a way to roll-back the creation of those indexes (and take a DB snapshot beforehand) just to be safe.
Hi Brian.
I said a similar thing, “you are the dba team so you can make maintenance on indexes, create new indexes etc.” but they say we must make the maintenance on indexes so I wanted to ask here if there is a procedure, job,task, script etc. that we run on Sailpoint side.
Thank you so much.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.