August 3, 2014 at 8:02 am
Hello,
I have a fact table which has six field now and I have created non clustered index on all fields.
It will also have more fields later with millions of records .
Please suggest me my indexing will help on this table or should I create index in some other way to optimise performance .
Thanks
August 3, 2014 at 8:13 am
after a semi-internal discussion the answer we agreed on is:
Yes.
If you need a more detailed answer please provide a more detailed question.
August 3, 2014 at 8:25 am
LutzM (8/3/2014)
after a semi-internal discussion the answer we agreed on is:Yes.
If you need a more detailed answer please provide a more detailed question.
Seriously? I thought that this time it was 43
😎
August 3, 2014 at 8:38 am
To be a little more serious:
To add an index depends if there are queries that'll benefit from it.
Adding an index with all columns just because of adding the index doesn't make much sense.
Without knowing much more details it's hard to tell if the index is approriate.
My wild guess would be: No. But that might be wrong...
August 3, 2014 at 9:35 am
raj.prabhu001 (8/3/2014)
Hello,I have a fact table which has six field now and I have created non clustered index on all fields.
It will also have more fields later with millions of records .
Please suggest me my indexing will help on this table or should I create index in some other way to optimise performance .
Thanks
Can you provide execution plan, query statistics and sample queries?
These are the kinds of things that are required to make tuning suggestions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2014 at 10:06 am
raj.prabhu001 (8/3/2014)
I have a fact table which has six field now and I have created non clustered index on all fields.It will also have more fields later with millions of records .
Pretty small table, in other words.
Please suggest me my indexing will help on this table or should I create index in some other way to optimise performance .
Yes, indexes will probably help, you'd design indexes same way you would for other smallish tables or large tables: based on the queries which run against them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2014 at 5:04 pm
LutzM (8/3/2014)
after a semi-internal discussion the answer we agreed on is:Yes.
If you need a more detailed answer please provide a more detailed question.
That's a badlypossibly flawed answer. The correct A better answer is "Probably". Another better answerpossibly correct answer is "Perhaps". It is probablepossible that the best answer is "It depends" or something similar.
Tom
August 3, 2014 at 5:47 pm
@tom:
My answer (Yes) is based on the "question":
...my indexing will help on this table or should I create index in some other way to optimise performance...
which is close to "it depends" from my point of view.
But at a second glance I realize that the option "no indexes at all" would have been ruled out.
Therefore, I accept your answer as being more appropriate.
August 4, 2014 at 3:19 am
Are u really need index for all columns?. if u not needed for columns ,remove that columns then get improvement .
if u needed all columns then go for covered index .
August 4, 2014 at 4:58 am
raj.prabhu001 (8/3/2014)
Hello,I have a fact table which has six field now and I have created non clustered index on all fields.
It will also have more fields later with millions of records .
Please suggest me my indexing will help on this table or should I create index in some other way to optimise performance .
Thanks
Determining which indexes are going to be of use is primarily done by understanding the queries that are going to be run against the data. You can't do one without the other. But, the most important decision you have to make is where to place the clustered index. Take your time to understand both your storage needs and your query needs. Generally, the best place to put the clustered index is on the most common access path to the data. This can be the primary key, but it won't necessarily be so.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2014 at 7:06 am
Creating a non-clustered index containing all columns makes no sense. If you create a clustered index on a table, then technically you already have an index that covers all columns. If someone requests that you index every column in a table, then just nod your head, smile, and tell them it's covered. 😉
When I create a table, I typically create a clustered index on a single column containing a unique sequentially incremented identifier (transaction_time or an identity). Then I create a non-clustered index on some combination of columns that make up the natural primary key (ex: customer_id + phone_number). This typically facilitates 90% of all the join operations.
It's a waste of resources to creating special indexes upfront on columns like unit_price, employee_gender, or street_address based on the assumption that the application just might need them one day.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply