Dynamic creating indexes

Question

Dynamic creating indexes

Sababa

SSC Enthusiast

Points: 150
More actions
September 11, 2018 at 9:00 am

#415607

We have more than 100 indexes exist in our DB. Sometimes due to some processes a part or all the indexes disappear. I can write the SP which will apply to each index and ask, "If Exists" and if not, will run the "Create Index" statement. But I'd like to do it in a dynamic way. How can I do it in a more effective way? Thanks

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If you have to keep recreating the indexes I would look for the process(es) that are dropping the indexes and fix that so the indexes don't keep disappearing.

Sababa SSC Enthusiast Points: 150 More actions · Answer 2

You're completely right, but it's not under our control.
Unfortunately, we have a common DB with our ERP contractor and they may impact on that. when running any processes there.

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

Lynn Pettis - Tuesday, September 11, 2018 9:05 AM
If you have to keep recreating the indexes I would look for the process(es) that are dropping the indexes and fix that so the indexes don't keep disappearing.

+1 to this.
Fixing this might be as simple as having the process re-create the index when it completes whatever work is being done, but it's better to have it handled as part of the process that made the change to begin with. Also, this shouldn't be an ambiguous cleanup/repair task that you expect to maintain - schema changes, especially automated ones should be known and documented - my 2Â¢.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

irit.schwarts - Tuesday, September 11, 2018 9:26 AM
You're completely right, but it's not under our control.
Unfortunately, we have a common DB with our ERP contractor and they may impact on that. when running any processes there.

Then they should be the ones working on the issue. If they are dropping indexes that you need, they need to fix their processes instead of you having to put a band-aid on the problem.

Sababa SSC Enthusiast Points: 150 More actions · Answer 5

Yeh, but for now my mission is to create this dynamic procedure, which will be checking the index existing and if doesn't exists, to create that.
The problem is that every index has it's own table and I don't know how to automate this creating.
Here is my code for example for 2 indexes:

ALTER PROCEDURE [dbo].[CheckCreate_Index]

IF NOT EXISTS (SELECT * FROM [sys].[indexes] I WHERE I.Name = 'Norm_LAST_STAGE' and I.object_id = OBJECT_ID ('[dbo].[FirstTable]'))
CREATE NONCLUSTERED INDEX [Norm_LAST_STAGE] ON [dbo].[FirstTable] ([LAST_STAGE]) INCLUDE ([DIMENSION],[LOCATION],[LAST_MOD]);

IF NOT EXISTS (SELECT * FROM [sys].[indexes] I WHERE I.Name = 'Norm_line_status' and I.object_id = OBJECT_ID ('[dbo].[SecondTable]'))
CREATE NONCLUSTERED INDEX [Norm_line_status] ON [dbo].[SecondTable] ([line_status]) INCLUDE ([dimension],[location],[fkey])

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Script each index while they exist in the database. Put each in their own stored procedure. Use a master procedure to call each individual procedure if the index is missing.
You can schedule a job to run as often as necessary to ensure the indexes are in place.

Sababa SSC Enthusiast Points: 150 More actions · Answer 7

I have more than 100 indexes.
I can create just one procedure which is checking if the index exists, and run this one procedure every hour.
My problem is to detail every index in this procedure, it's going to be a huge procedure with the same logic :check if exists and if no, create.
My question is: is there a place to generate the script? To do it dynamic using variables?
I was lost when trying to automate it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Can we assume that the clustered index is not dropped? How about indexes supporting constraints like primary or unique indexes?

Sababa SSC Enthusiast Points: 150 More actions · Answer 9

We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Sababa - Tuesday, September 11, 2018 12:36 PM
We don't have there any clustered indexes and there are no primary or unique. Only nonlustered

Just an fyi, that could be an issue as well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Are the indexes create in non-standard location(s) such as different file groups from the tables?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Do we need to worry about filestream data in the indexes?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Lynn Pettis

SSC Guru

Points: 442467

September 11, 2018 at 12:47 pm

#2005309

Or filtered indexes?

Sababa SSC Enthusiast Points: 150 More actions · Answer 14

No, they are all in the same format and logic, were issued once and if they disappear, I just run this script again