Wikis - Page

How to create new indexes on a Sentinel database

0 Likes

In order to create new indexes on the database used by Sentinel there are 3 steps that need to be taken:



A) Set up the database so that new tables / views are created with the index.

B) Apply indexes to existing tables / views.



For ease of use, I'll be explaining the procedure using MSSQL, but the same general steps apply to any database you might be using.



A) Set up the database so that new tables / views are created with the index



1) Go to Microsoft SQL Server Management and open the ESEC database.



2) Go to table EVENTS_P_MAX and open the Index container.



3) Verify what is the last EVENTS_P_MAX_IX index (lets say the last one is 6) - VERY IMPORTANT STEP -



4) We will now create a "template index" so that all new tables are created with this index. Click on the table EVENTS_P_MAX and enter the following script on the SQL command line:



CREATE NONCLUSTERED INDEX [EVENTS_P_MAX_IX7] ON [dbo].[EVENTS_P_MAX]
(
[SENTINEL_PROCESS_TIME] ASC,
[EVT_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [ESENTX]



IMPORTANT NOTE: Please consider that the index in this case is created with the numeral 7 because the last index in our example was 6. This could be different in your environment.



5) We will now create a "template index" for the historical partitions that will be created. Click on the table HIST_EVENTS_P_MAX and enter the following script on the SQL command line:



CREATE NONCLUSTERED INDEX [HIST_EVENTS_P_MAX_IX7] ON [dbo].[HIST_EVENTS_P_MAX]
(
[SENTINEL_PROCESS_TIME] ASC,
[EVT_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [ESENTX]



IMPORTANT NOTE: Please consider that the index in this case is created with the numeral 7 because the last index was 6. In your environment this could differ.




B) Apply indexes to existing tables / views



1) You will now have to create the index for all the tables that already exist. For this you will have to select each table and create the index. The tables nomenclature is EVENTS_P_TimeStamp. Once you select the table execute the following command:



CREATE INDEX EVENTS_P_20100715070000_IX7 ON EVENTS_P_20100715070000_IX7
(
[SENTINEL_PROCESS_TIME] ASC,
[EVT_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [ESENTX]



IMPORTANT NOTE: In this case we are using table EVENTS_P_20100715070000. In your environment you will have to change the name of the table so that it matches your environment. Also consider that this is a per table index, so when you switch to another table you will have to change the create index command so that it match the table name. We are also using the numeric 7 on the name of the index as that is the same one created on the "template index". Please consider this as well.



2) You will now have to create the index for all the historical tables that already exist. For this you will have to select each table and create the index. The tables nomenclature is HIST_EVENTS_P_TimeStamp. Once you select the table execute the following command:



CREATE INDEX HIST_EVENTS_P_20080715070000_IX7 ON HIST_EVENTS_P_20080715070000_IX7
(
[SENTINEL_PROCESS_TIME] ASC,
[EVT_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [ESENTX]



IMPORTANT NOTE: In this case we are using table HIST_EVENTS_P_20080715070000. In your environment you will have to change the name of the table so that it matches your environment. Also consider that this is a per table index, so when you switch to another table you will have to change the create index command so that it match the table name. We are also using the numeric 7 on the name of the index as that is the same one created on the "template index". Please consider this as well.



Once you are done with these steps your shiny new index should be ready to be used. Test it out and enjoy...

Labels:

How To-Best Practice
Comment List
Related
Recommended