September 6, 2005 at 2:47 pm
1st question, if you put a clustered index on a table say in Enterpirse manager and click save/ok/close, is this the point it actually reorganizes the table on the hard drive based on the clustered index. does this happen immediately or do I have to do something?
Am I on the right track here. My logic is to put and index on a column where the search arguments in the where clause is based on that column (assuming this is a common query and SARg hitting the db)
Then I go into query analyzes and run a query on on the table with the search argument on the index column and I should see and imporivement (less proecess or mememory and i/o cost)?
AM I on the right track?
thanks
September 6, 2005 at 2:49 pm
If you made the right choice for that query yes (assuming there's enough data for the index to be usefull). But you have to also consider all other queries hitting that table, which can take a lot of time to test.
September 6, 2005 at 3:13 pm
Watch out for the possibility that INSERT statements can add a row that has its clustered index value 'between' existing entries - possibly forcing a major rewrite of data on disk.
Clustered indexes are particularly good where queries such as
...
WHERE ID between x and y
are being executed frequently, for obvious reasons.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 7, 2005 at 7:07 am
thanks, Also when does it actually index, after I set the index in enterprise manager?
September 7, 2005 at 7:09 am
When you hit save in EM... or when you run the create index script in QA.
September 7, 2005 at 11:17 am
Be wary of these 'auto-magic' features in EM. On large tables you may be looking at an hour glass for quite some time. I've always found it best to script out changes and execute them either in QA or the Task Scheduler.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply