January 17, 2013 at 9:15 am
Hello All,
I have a primary key on a column that is a unique identifier (width 32 char) and it is nonclustered. There are a lot of inserts, updates and selects that run on this table which is actually taking long times to complete. Sometimes the inserts and selects take almost a minute and sometimes less than 15seconds. I was wondering if i create a new identity column and make it a clustered primary key would reduce the time take it takes to complete the inserts, updates and deletes. Please let me know if this is the right approach.
Thanks a bunch.
January 17, 2013 at 9:35 am
Maybe. Maybe not.
Try it out in a test environment and see. Also, check why the operations are taking so long, what the wait type is that they get.
http://www.sqlservercentral.com/articles/Indexing/68563/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2013 at 9:38 am
pk - non-clustered?
ya go-ahead and create INT IDENTITY column and see?
by the way .. what you see when select queries runs? any wait-type? table scan?
January 17, 2013 at 9:58 am
Wait type for inserts in PAGEIOLATCH_EX
For the Selects I see Clustered Key Lookups and Clustered Index Seeks. Actually these are all EDMX queries.
January 17, 2013 at 1:04 pm
DBA24 (1/17/2013)
Hello All,I have a primary key on a column that is a unique identifier (width 32 char) and it is nonclustered. There are a lot of inserts, updates and selects that run on this table which is actually taking long times to complete. Sometimes the inserts and selects take almost a minute and sometimes less than 15seconds. I was wondering if i create a new identity column and make it a clustered primary key would reduce the time take it takes to complete the inserts, updates and deletes. Please let me know if this is the right approach.
Thanks a bunch.
An identity will definitely insert faster than a uniqueidentifier. As a general rule, if an identity can do what you need, choose it over a uniqueidentifier, esp. for indexing.
For the SELECTs and UPDATEs, it depends on the WHERE clauses. In general, you need to make sure you've selected the best clustering column. There's far too little info here to make that determination.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 17, 2013 at 7:05 pm
If your clustering key contains uniqueidentifier and you fill it's value with newid() it will heavily fragment that CL index and make all operations several times slower (especially inserts) than they should be.
You can use NEWSEQUENTIALID() function instead of NEWID() to get less fragmentation.
int identity would be even better.
Other indexes (NC) can also slow down changes. You can deal with them:
a) consolidate them (drop indexes which columns you have previously merged into other indexes, especially ones with the same leading key column)
b) decrease fill factor to e.g. 90% on indexes that survived consolidation phase. Measure fragmentation before and after and how quickly they get fragmented.
Does the table has many NC indexes? Does it have triggers or is merge replicated ?
You could optimize files also (create several data files per that table, set decent FILEGROWTH in MB not percentage, turn on instant-file initialization, optimize transaction log initial size and growth in MB not percent).
January 18, 2013 at 4:22 am
carlecot88ssn (1/18/2013)
I think you wrote this by mistake. And if this is the truth, why didn't you delete your post ? We should be more conscious while posting a threat or reply.
It's a spam.. Reported ..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
January 18, 2013 at 11:34 am
Does the table has many NC indexes? Does it have triggers or is merge replicated ?
You could optimize files also (create several data files per that table, set decent FILEGROWTH in MB not percentage, turn on instant-file initialization, optimize transaction log initial size and growth in MB not percent).
There are 2 NC indexes on this table. There are no triggers and is not merge replicated.
January 18, 2013 at 5:19 pm
If table does not have a clustered index (it's a heap table) and fill factor is default (0 which is the same as 100%) updates that make rows wider will cause forwarding records out of the row, and that slows all operations on that table.
Use sys.dm_db_index_physical_stats to find out how many of those are there (column "forwarded_record_count"), and what is fragmentation percentage of each index (column avg_fragmentation_in_percent. Column avg_page_space_used_in_percent is also very useful info).
For example, if after inserting and/or updating 10% of the current number of rows you get 10% fragmentation, then you have to solve it as I have described before.
Post table creation and index creation script if you want more precise answer.
January 18, 2013 at 7:23 pm
Also, since this appears to be a heap table, if there are a lot of inserts and deletes SQL Server will not reuse the space in the table created by the deletes. This means your table will continue to grow even if the amount of data remains relatively static.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply