Performance on large table -Index Creation

Question

Post reply

Performance on large table -Index Creation

ramana3327

SSCoach

Points: 19722
More actions
August 5, 2022 at 3:18 am

#4072741
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 2 years, 4 months ago by ramana3327.
September 6, 2022 at 6:48 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

ScottPletcher SSC Guru Points: 101121 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

MMartin1 One Orange Chip Points: 27799 More actions · Answer 2

Yes a clustered index on the datetime column would help. Currently you have heap (no clustered index) so deleting records will not actually make the size of the table smaller because order is not a consideration for heaps.

Plus + The uniqueIdentifier as one of your columns has me believing this is either a user id or just a row Id. A non clustered index on this column may be usefull if you seek/filter by a user or row ID .

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

If you do build an index (non-clustered or clustered, doesn't matter) on the Unique Identifier column, build the index with a 71% Fill Factor and setup a job to REBUILD it when it hits only 1% fragmentation (it's ready to "avalanche" page splits at that level). With 4 million rows in the table, it should be able to go for at least 2 months before it needs a rebuild even if you insert 10 thousand rows per day. It will also help prevent page splits if you have reasonable sized "ExpAnsive" updates, as well.

Unless you love rampant page splits and fragmentation, NEVER use reorganize on that index.

This reply was modified 2 years, 3 months ago by Jeff Moden. Reason: Forgot to specify the column

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)