SQL server data base table performance

i was asked by an interviewer regarding sql server. the scenario was we have table with million or records. table has primary key, clustered and non clustered keys as well. still the data is fetching late.开发者_StackOverflow what do we need in this case?

Please kindly give me the answer.

regards, murli

Limited information, but any of these could be attempted.

1. Write more efficient queries.
2. Buy more hardware.
3. Index the columns that are more appropriate for your queries.
4. Place this tables file on a more efficient RAID Controller Type
5. Partition the table.

Pretty much:

As indexing is given already efficiently....

• Better hardware.

That would be either more memory (seen servers with 128gb memory) or a WAY faster disc subsystem. DB servers often do not buy discs for space, but for IO. I have seen a server with 190 attached discs ;)

• No index maintenance
• Poor hardware or not configured correctly
• uniqueidentifier as clustered index
• bad datatypes (too wide, ints in varchar, (max) types etc)
• poor design (EAV?)
• useless index
• statistic disabled
• fill factor of 5%
• ...etc