How to create index on tables 1

[Zargo]

Hi all,

I have a performance issue and want to create some indexes on the column of a huge table. I never insert or update or delete in this table. Only what i'm doing is SELECT.

What shall i do:
- Create 1 index on all columns or
- Create for each column a index

And what kind of property must i give to those indexes. Is it good to define indexes on all columns, why not?

A golden star for the wise advice!!!!

 

[vongrunt]

Create indexes only on columns you are frequently using, primarily via WHERE clause in SELECT. One index per column, except for relatively rare cases when sort and/or "index covering" matter.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Since this is read only
set yout transaction level to read uncomitted (or with (no lock) in your select statements)
you can probably put the whole DB in read only mode(no logging will occur)
you will have no problems creating more indexes than usual since you won't be deleting, inserting or updating anyway

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Zargo]

What i want to do is to reduce the duration (i'm checking this in the SQL server profiler). How to set the transaction level to read uncommited? Does this increase the duration? I couldnt set the databse to read only because i must insert/delete/update in other tables, only this table i'm selecting some columns.

What to do with extra options like:
- CLustered index
- do not recompute statistics
- file group
- pad index
- fill factor

TIA

 

[vongrunt]

> How to set the transaction level to read uncommited?

Check SET TRANSACTION_ISOLATION_LEVEL and NOLOCK/READUNCOMMITTED hints in Books Online.

> Does this increase the duration?

On the contrary, this will decrease duration a little bit in your case - without harmful side-effects.

> What to do with extra options like:
- CLustered index

That index dictates physical sort of data in table... kind of. Only one such index per table is possible to have.

> - do not recompute statistics

Irrelevant in your case, since table is read-only. Just create full statistics once and voila.

- file group

Allows you to put database objects (indexes?) into another file group, which may be located on another physical drive. Makes sense for large databases.

- pad index
- fill factor


Smaller padding and larger fill factor lead to (initially) smaller indexes - for a price of heavier INSERTs/UPDATEs. If you don't expect data changes in near future... you got the picture.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Zargo]

Thanks for your replies.

Other replies are also welcome, any idea's in my case?