Performance on large table -Index Creation

  • Hi SQL Experts looking for some suggestion.

    We have a table with 4 million records. The table will be getting new records for every 15 min & they use this table to generate some reports. The reports (Select) mostly compare on the date time.

    The data types of the table are Unique identifier, date time, nvarchar(250), float, nvarchar(50).

    The user is receiving exceptions/performance issues recently.

    The table has non unique, non-clustered index on the column nvarchar(250). No included columns in the index.

    How to improve the performance with out delete records. Is it recommended to create index on date time column?

    The Index usage stats are below

    Heap: user seeks (0), user scans (20000), user_lookups (15000), Total reads (35000), Writes (66000990), Fill Factor (0)

    NonClustered: User seeks (15000), user scans (2), user_lookups (0), Total reads (14000), writes (66000990), Fill factor (0).

     

    • This topic was modified 4 days, 1 hour ago by  ramana3327.
  • Drop the existing index.

    Assuming that the column is datetime and not just time, create a clustered index on the datetime column.

    It would be better to use datetime2 type to reduce duplicate keys, but you're probably past that point now.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply