<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Index Question
Index Question
Rate Topic
Display Mode
Topic Options
Author
Message
DBA24
DBA24
Posted Thursday, January 17, 2013 9:15 AM
SSC 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
GilaMonster
GilaMonster
Posted Thursday, January 17, 2013 9:35 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 38,091,
Visits: 30,385
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
bangsql
bangsql
Posted Thursday, January 17, 2013 9:38 AM
SSC Rookie
Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 28,
Visits: 405
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
DBA24
DBA24
Posted Thursday, January 17, 2013 9:58 AM
SSC 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
ScottPletcher
ScottPletcher
Posted Thursday, January 17, 2013 1:04 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333,
Visits: 1,803
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)
One man with courage makes a majority. Andrew Jackson
Post #1408581
Vedran Kesegic
Vedran Kesegic
Posted Thursday, January 17, 2013 7:05 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 343,
Visits: 1,089
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).
_____________________________________________________
XDetails Addin
- for SQL Developers and DBA
blog.sqlxdetails.com
- Transaction log myths - debunked!
Post #1408691
demonfox
demonfox
Posted Friday, January 18, 2013 4:22 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 1,092,
Visits: 1,122
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
DBA24
DBA24
Posted Friday, January 18, 2013 11:34 AM
SSC 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
Vedran Kesegic
Vedran Kesegic
Posted Friday, January 18, 2013 5:19 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 343,
Visits: 1,089
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.
_____________________________________________________
XDetails Addin
- for SQL Developers and DBA
blog.sqlxdetails.com
- Transaction log myths - debunked!
Post #1409137
Lynn Pettis
Lynn Pettis
Posted Friday, January 18, 2013 7:23 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832,
Visits: 27,858
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
