May 11, 2012 at 8:17 am
primary key = id
recorddate ascending = non unique, non clustered
That's your problem.
You must *cluster* by recorddate. Add a non-clus index on id, if you need it.
Lowell's change is vital also, but by itself, it won't gain you nearly as much as properly clustering the table.
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!
May 11, 2012 at 1:33 pm
is it ok to have a clustered index on recorddate? I have two rows per device per day for the recorddate.
May 11, 2012 at 2:06 pm
You're still fine. SQL will add an integer counter itself so it can tell the rows apart. It's some slight overhead but it's not a real issue.
If you decide to try the clustered index, follow these steps:
1) Script out existing indexes.
2) Drop existing indexes.
3) Create the new clustered index -- be sure to specify the filegroup to create it on, if that is relevant for your db.
4) Re-create any nonclustered indexes you want to retain; again, specify filegroup if needed.
When you add a clus index, all non clus indexes must be rebuilt anyway, and typically you're better off doing the rebuilds yourself.
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 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply