Index Question

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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.

  • 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) "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".

  • 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).

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • 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:

  • 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.

  • 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.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • 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