SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Question


Index Question

Author
Message
DBA24
DBA24
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)SSC Guru (235K reputation)

Group: General Forum Members
Points: 235392 Visits: 46376
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


bangsql
bangsql
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 663
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?
DBA24
DBA24
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20556 Visits: 7433
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1266
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

demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2207 Visits: 1192
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
DBA24
DBA24
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2084 Visits: 1266
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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99139 Visits: 38998
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search