Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Question Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 9:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 AM
Points: 26, Visits: 28
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.
Post #1408491
Posted Thursday, January 17, 2013 9:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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 2008, MVP
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

Post #1408501
Posted Thursday, January 17, 2013 9:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 49, Visits: 581
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?
Post #1408502
Posted Thursday, January 17, 2013 9:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 AM
Points: 26, Visits: 28
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.
Post #1408520
Posted Thursday, January 17, 2013 1:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,030, Visits: 3,027
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1408581
Posted Thursday, January 17, 2013 7:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
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
Post #1408691
Posted Friday, January 18, 2013 4:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1408817
Posted Friday, January 18, 2013 11:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:48 AM
Points: 26, Visits: 28


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.
Post #1409042
Posted Friday, January 18, 2013 5:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:57 PM
Points: 369, Visits: 1,211
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
Post #1409137
Posted Friday, January 18, 2013 7:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:54 PM
Points: 23,227, Visits: 31,921
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1409152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse