CODEWITHSUNDEEP
sql-server-2005
user294146
user294146

Reputation: 47

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. what do we need in this case?

Please kindly give me the answer.

regards, murli

Upvotes: 0

Views: 100

Answers (3)

gbn
gbn

Reputation: 432220

  • 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

Upvotes: 0

TomTom
TomTom

Reputation: 62093

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 ;)

Upvotes: 0

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 2

Related Questions