Disable and enabling index frequently

Question

Disable and enabling index frequently

ManoharV

SSCommitted

Points: 1730
More actions
August 2, 2009 at 5:04 am

#208895

Hi all,
I have a table in which records frequently gets inserted. Around 5000 records per minute.
I am selecting data from that table once or twice per minute
Because of huge record set, I added index, but the insertion is getting little slower.
Can i disable index while inserting and enable while selecting.
Will there be any performance problem?
Here is the table structure
UserResponseIDbigint
ScheduleUserIDbigint
QuestionGUIDvarchar(100)
UserResponsexml
Date datetime
TimeSpentnvarchar(20)

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

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

manohar (8/2/2009)
Hi all,
I have a table in which records frequently gets inserted. Around 5000 records per minute.
I am selecting data from that table once or twice per minute
Because of huge record set, I added index, but the insertion is getting little slower.
Can i disable index while inserting and enable while selecting.
Will there be any performance problem?
Here is the table structure
UserResponseIDbigint
ScheduleUserIDbigint
QuestionGUIDvarchar(100)
UserResponsexml
Date datetime
TimeSpentnvarchar(20)

Even if you could, I wouldn't do that. What would happen to the index if you were inserting rows with the index turned off? They would be missing from the index.

What is the CREATE INDEX code for the index you added and what is the clustered index on this table?

Also, why is "TimeSpent" character based? THAT would be a huge problem in my opinion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jagpro SSC Journeyman Points: 81 More actions · Answer 2

Hi,

If the specified table has frequent inserts and updates, the FILL_FACTOR is to be set and PAD_INDEX to be set ON. By default, FILL_FACTOR = 0 (same as 100%) and PAD_INDEX = OFF. You have to ALTER the index.

Pleae refer:

http://www.sql-server-performance.com/tips/oltp_performance_p2.aspx

http://www.dotnetspider.com/forum/37788-what-fillfactor-sqlserver.aspx

- Jagpro

ManoharV SSCommitted Points: 1730 More actions · Answer 3

Thanks for the reply....

I will update on this once it is done...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If you disable an index SQL drops the index tree completely, leaving just the metadata bahind. When you re-enable the index, the index is completely rebuilt from scratch, just as if you'd done an ALTER INDEX .. REBUILD. That takes lots of time on large indexes.

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: 1004504 More actions · Answer 5

Jeff Moden (8/2/2009)
Even if you could, I wouldn't do that. What would happen to the index if you were inserting rows with the index turned off? They would be missing from the index.

Can't happen. Only way an index can be missing rows is due to IO susbsystem glitches or bugs and it's a form of database corruption.

When an index is disabled, SQL drops the b-tree of that index completely, leaving just the index's metadata in the system tables. Hence any inserts don't pay the penalty of updating the index, but it's also not there for selects to use. To enable it, you actually have to issue an index rebuild statement to rebuild the entire b-tree

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

ManoharV SSCommitted Points: 1730 More actions · Answer 6

Gail I have one more query for you...

I have created a non clustered index for these to columns... will there be any impact on the size of the column... because GUID will be atmost 35 to 40 characters... and i have assigned the size to 100(in case of QuestionGUID)....

ScheduleUserID bigint

QuestionGUID varchar(100)

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

What do you mean by 'impact on the size of the column'? A column size isn't affected by creating indexes.

Why on earth are you storing a guid in a varchar column? Use UNIQUEIDENTIFIER and it will only take up 16 bytes.

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

ManoharV SSCommitted Points: 1730 More actions · Answer 8

ManoharV

SSCommitted

Points: 1730

August 6, 2009 at 5:28 am

#1035276

Thanks Gail...

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

1) Gail is right - use uniqueidentifier type for the GUID storage. 20-bytes per record savings there.

2) As another user pointed out, timespent is not only char based but Nchar - requiring double-byte storage. Also if you do any math on this it will have to be converted for every record. Bad stuff there. Make it some appropriate numeric type.

3) Have you ever done a fragmentation check on this table and the index(es)? I am betting not. Probably fragmented to heck and back. Fill factor is important here too, as another user pointed out.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

GilaMonster (8/6/2009)
Jeff Moden (8/2/2009)
Even if you could, I wouldn't do that. What would happen to the index if you were inserting rows with the index turned off? They would be missing from the index.
Can't happen. Only way an index can be missing rows is due to IO susbsystem glitches or bugs and it's a form of database corruption.
When an index is disabled, SQL drops the b-tree of that index completely, leaving just the index's metadata in the system tables. Hence any inserts don't pay the penalty of updating the index, but it's also not there for selects to use. To enable it, you actually have to issue an index rebuild statement to rebuild the entire b-tree

Thanks Gail... I never heard of disabling an index before. I've always dropped them and recreated them when I've need to do such a thing and got lazy on this post... I didn't look it up before letting the pearly whites open up. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

It was a feature added in SQL 2005 for cases where an index needs to be dropped for data loads. It means that the index can be recreated without needing the full creation script. Removes the possibility that the index definition and the data load job are out of sync.

I use it a fair bit when I'm doing index tuning.

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

GilaMonster (8/6/2009)
It was a feature added in SQL 2005 for cases where an index needs to be dropped for data loads. It means that the index can be recreated without needing the full creation script. Removes the possibility that the index definition and the data load job are out of sync.
I use it a fair bit when I'm doing index tuning.

Both uses sound good. Thanks again, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

Jeff Moden (8/6/2009)
GilaMonster (8/6/2009)
It was a feature added in SQL 2005 for cases where an index needs to be dropped for data loads. It means that the index can be recreated without needing the full creation script. Removes the possibility that the index definition and the data load job are out of sync.
I use it a fair bit when I'm doing index tuning.
Both uses sound good. Thanks again, Gail.

And, just for the record - the way you re-enable a disabled index through code is: ALTER INDEX ... REBUILD;

You disable it using ALTER INDEX ... DISABLE; but there is no corresponding ENABLE. In other words, the code is very clear on what you are doing when you 'enable' a disabled index.

One of the nicer options in 2005 is the ability to rebuild all indexes for a table with a single command. So, instead of trying to figure out all of the specific indexes a table has, you just issue the following:

ALTER INDEX ALL ON dbo.MyTable REBUILD;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Thanks... at work, I'm still stuck in the world of 2k. Haven't really had a chance to dig into that stuff, yet. I appreciate the leg up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)