Just a few Index 101 type points of advice.
When creating an index on an existing table, that has populated data, keep in mind that it could take a long time. If it takes too long and is initiated via the SQL Management Studio the result is very likely to be a timeout. Ideally when creating an index via the SQL Management Studio it is good to create the index before data population.
Otherwise the best way to create an index is either via T-SQL directly or create the index and maintenance of the index via a SQL Server Job.
To setup an index via SQL Management Studio follow these quick steps.
First right click and select Indexes/Keys… and click. A dialog will pop up.
In the dialog, first click add (#1), then name the new index appropriately (#2), and then select the actual column or columns that you want the index placed on (#3). The results should be similar to the image below. Once everything is set click on close.
Next click on save (circled below) and the index will be created. Remember, if there is anything in the table there is a good change at a time out.
To do it the truly über easy way, execute the following T-SQL command.
CREATE NONCLUSTERED INDEX IX_analysis_time ON dbo.profile_stats (analysis_time)
As mentioned, the UI times out trying to build this index for me because of the rows in the table. This table had a mere 283204 rows and took 4:40. So you can imagine with more data how much longer it might take. A small 10-30 second time out for the UI is good considering it locks the interface up while it is trying to create the index. For more information on the command check out the BOL entry on MSDN for CREATE INDEX. Also it is good to check out the general index design guidelines. Also check out my more exhaustive best practices entry for indexes.
Keep in mind when creating a index rebuild or index reorganization job that a period of time should be taken to observe the baseline behavior of a table. Then setup a maintenance task to run on a scheduled basis of when you observe fragmentation and other things starting to occur. For more information on reorganizing and the specific steps and commands for fragmentation and such, read the MSDN article.
To setup a job follow these steps.
Open up SQL Server Management Studio.
Expand the SQL Server Agent. Select the Jobs Folder and right click, on the menu select New Job….
The New Job Dialog pops up. Fill out each of the General Section fields with pertinent information.
Next select the Steps Section.
Another sub dialog appears, add the pertinent data to this field and add whatever the maintenance command is that you wish to execute on a regular basis.
After that, click on OK and then move on to Schedules. Again click on the New… Button to bring up another sub dialog.
A New Job Schedule sub dialog appears. Again, you know what to do. Add pertinent information.
Next add any Alerts… same process.
You can even setup a specific response for the alerts.
Next setup any operators or new operators that you want to receive the alert.
Select the method that will receive the error alert.
Click ok until you’re back out to the main screen. At this point you should see the job item within the job folder. It will run at the scheduled time or just right click on it and select the run option that way.
…and that’s it. Build, run, and maintain those indexes!
