Cluster Index.. Insert update operations Vs Performance in MS SQL 2005

Question

Post reply

Cluster Index.. Insert update operations Vs Performance in MS SQL 2005

ichbinraj

SSCommitted

Points: 1627
More actions
May 29, 2009 at 8:38 am

#155758

Hi Experts, Its little urgent.

I am facing some issues in database performance in MS SQL 2005

I have 1 TB database and having perf problem.

application is using only 1 table and performing Insert update operation on this table and complaining performance problem

I have cluster index setup on this table and records are 160 K . just thinking how cluster index perform insert update operations for new records.. does its take long time to do this operations.

Please let me know your suggestions

Thanks

ichbinraj

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

It depends.

What is the clustered index key? How wide is it?

Where are your inserts in the clustered key, beginning, middle, end?

Are your updates, updating the clustered key?

What is the fill factor of the index?

How many rows are you inserting/updating in a batch?

Performance will be worse on a wide clustered index with inserts anywhere but the end or updates to the clustered index key as the data will have to be re-arranged on disk and page splits will occur.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 2

hi_abhay78

SSCertifiable

Points: 6265

May 29, 2009 at 8:49 am

#1002090

give us the query and the columns on which the indexes have been defined ...

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

ichbinraj SSCommitted Points: 1627 More actions · Answer 3

hi my information about the Table

What is the clustered index key? its created on 5 columns

Where are your inserts in the clustered key, beginning, middle, end? its dynamic process so they can pick up from any where

Are your updates, updating the clustered key? yes

What is the fill factor of the index? 80

Thanks

Ichbinraj

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

Sounds like you may not have the best clustered index key. I'd recommend reading this blog post by Kimberly Tripp as it does a good job defining what makes a good clustered index.

Based on the answers you provided, yup, the clustered index is going to slow down the inserts and updates you are doing. I would guess that you are getting a lot of page splits which will slow down everything as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ichbinraj SSCommitted Points: 1627 More actions · Answer 5

ichbinraj

SSCommitted

Points: 1627

May 29, 2009 at 9:42 am

#1002139

one quick question

IF my application updating existing record that mean

some 123 record does it going to slowdown the performance ?

If I am trying to insert a new records number 11124 what is the process ? if have 150 K record numbers ?

Thanks

Ichbinraj

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Not sure I understand what you're asking.

When a row is updated, the changes have to be made to all of the indexes on the table, likewise when rows are inserted.

If that's not what you're asking, can you please explain your question a bit clearer?

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

If you're having performance problems with a particular query, please post table definition, all index definitions, the query and the execution plan, saved as a .sqlplan file, zipped and attached to your post.

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 8

Hi ,

Here its my query : SELECT * FROM pep.ZSDI38_DATAITEMS WHERE SID = '80168005' and ITEM_CUSTOM1 in (1000180023,1000180024);

and Index information

ZSDI38_DATAITEMS~0 clustered, unique, primary key, stats no recompute located on PRIMARY MANDT, SID, ITEM_CUSTOM1, ITEM_CUSTOM2, ITEMNO

E Plan : I can't find attachment link in this form.

Thanks

ichbinraj

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

And the table structure?

The attachment button is right beneath the text box where you type your post. In the section titled "Post Options"

I can tell you right now that not only is your cluster a poor choice, but it's useless for that query. The leading column of the cluster is Mandt. The query doesn't filter by that at all. Hence the only way that query can run, assuming there are no nonclustered indexes on the table, is by scanning the cluster, ie a full table scan.

You need to do two things to fix it.

1) Remove the SELECT * and explicitly reference just the columns that you need back from that query.

2) Add a nonclustered index on SID, Item_Custom1. Providing the number of rows returned is small, that should be enough. If SQL doesn't use the index, you'll need to make it covering by adding the columns in the select clause as INCLUDE columns, though not a good idea if lots and lots of columns are returned.

One other thing. Why is auto_update stats disabled on that clustered index? Unless you manually update stats, that could be causing you a whol lot of problems.

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 10

ichbinraj

SSCommitted

Points: 1627

May 29, 2009 at 12:21 pm

#1002207

Hi ,

Application fetching all the fields and updating other tables in application.

So I have to select all fields on .

As per your suggestion . I have to create non cluster index on SID and ITEM_custom1 ... right ?

I am attaching execution plan .. remove .txt in the file extension

Thanks

ichbinraj

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

ichbinraj (5/29/2009)

As per your suggestion . I have to create non cluster index on SID and ITEM_custom1 ... right ?

Yup. It should help.

About the auto_update state?

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

ichbinraj SSCommitted Points: 1627 More actions · Answer 12

Hi did you check the execution plan ?

about stats update I am doing it manually.

if i change any thing on cluster index .. means removing cluster index from sid and item _custom1 .. and creating non cluster index on these will help the performance ? or just creating non cluster index on cluster index is fine ?

Can you please modify this following script including creating non cluster index

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [pep].[ZSDI38_DATAITEMS](

[MANDT] [nvarchar](3) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

[SID] [nvarchar](20) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

[ITEM_CUSTOM1] [nvarchar](50) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

[ITEM_CUSTOM2] [nvarchar](50) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

[ITEMNO] [nvarchar](20) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

[ITEMDESC] [nvarchar](80) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL,

CONSTRAINT [ZSDI38_DATAITEMS~0] PRIMARY KEY CLUSTERED

(

[MANDT] ASC,

[SID] ASC,

[ITEM_CUSTOM1] ASC,

[ITEM_CUSTOM2] ASC,

[ITEMNO] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

Thanks

ichbinraj

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

ichbinraj (5/29/2009)

Hi did you check the execution plan ?

Yes, I did

if i change any thing on cluster index .. means removing cluster index from sid and item _custom1 .. and creating non cluster index on these will help the performance ? or just creating non cluster index on cluster index is fine ?

I said add a nonclustered index. I didn't say change the clustered index.

Yes, the cluster is a bad one. Without a lot more info on the queries and the data I can't suggest you a better cluster. Take a look at the blog post that Jack posted and see if you can find a better candidate for the clustered index. It doesn't have to be the primary key.

For now, just add a nonclustered index.

Can you please modify this following script including creating non cluster index

Why don't you try it yourself? I suggest starting with the Books Online entry for CREATE INDEX

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

tosscrosby SSCoach Points: 18045 More actions · Answer 14

ichbinraj (5/29/2009)

application is using only 1 table and performing Insert update operation on this table and complaining performance problem

ichbinraj (5/29/2009)

Hi ,

Application fetching all the fields and updating other tables in application.

So I have to select all fields on .

Thanks

ichbinraj

Am I missing something or is it lost in the translation????

-- You can't be late until you show up.