October 9, 2012 at 1:31 pm
I have a table with 16M rows, but no PK. There is an identity column, but no PK, and this column is not part of any index. The table gets about 44k inserts/day, no updates, and frequent selects (this is a log of work activity).
I want to set the identity column as the PK, and I have the SQL statements. I am wondering what impact this may have on insert time. Any?
BG: I am tasked with purging older data - moving old records to an archive DB, using select, insert, delete. Having a PK to do this will improve performance of my SP (I validated this on a test system). Just don't want to mess up the users' inserts.
TIA, Mike
October 9, 2012 at 2:01 pm
If there is already a clustered index on the table, then I would make sure that the PK is not clustered. This will help preserve your current performance.
As for performance impact, I would expect very little impact on insert performance with the addition of a single index. If it was a compound index with several columns involved, or you were adding many new indexes, then I would be concerned.
October 9, 2012 at 3:16 pm
Let's see if I get it right:
- 44 K rows inserted every day
- no updates
- frequent selects
- no primary key
- an identity column
I assume there's no clustered index on the table, since there's no primary key and the best candidate clustering key was not used.
In this scenario I would create a CLUSTERED primary key. The identity column is small, unique, unchanging and ever-increasing: the best possible clustering key.
The index will likely have a negligible impact on inserts and will probably end up boosting read performance.
Hope this helps
Gianluca
-- Gianluca Sartori
October 9, 2012 at 3:35 pm
Thank you all. One thing that happened when I added the PK to my test DB, the DB Log filled up. Not being a DBA, how does this get cleaned up?
October 9, 2012 at 4:35 pm
The log file grows because creating the index requires a lot of data modifications, that get recorder into the transaction log. Even under the simple recovery model, the CREATE INDEX operation will be logged as a transaction, thus requiring space in the transaction log.
If your database is in the simple recovery, you can reclaim unused space and give it back to the OS using DBCC SHRINKFILE.
If your database is in the full recovery model, you will first have to back up your transaction log. Since this is a one-off maintenance task, it will likely grow the log file beyond its usual size and you will probably want to shrink it to the size it had before, which can be achieved again using DBCC SHRINKFILE.
Hope this helps
Gianluca
-- Gianluca Sartori
October 9, 2012 at 4:41 pm
You need more space for your logs, you probably added the index to a huge table. If you're not in simple recovery mode, you need to do a log backup. If you're in simple, just shrink it back down when you're done.
You need to have the space to finish the create index operation though or you'll never complete, no matter what mode you're in.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply