Index REORGANIZE question

Question

Post reply

Index REORGANIZE question

Digs

SSCertifiable

Points: 7333
More actions
June 7, 2011 at 1:16 pm

#238848

Hi,

I have two large tables that get updated daily or (added to).

I want to run this TSQL daily.

ALTER INDEX ALL ON DIM_Data REORGANIZE

and

DBCC SHRINKDATABASE (MyDB,10);

Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.

Questions:

1) Should I goto to single mode before either of the above?

2) (if no is to 1), when code runs and used run a select over it, is there any risk.

NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.

But anything good be going on during the SHRINK code.

How do I handle the above code with many users, using the database??

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

If you're intent on shrinking, don't waste your time defragmenting the indexes beforehand, as the shrink will result in them being far more fragmented than ever.

Shrink is something that should be done only if some unusual operation has grown the data or log file far beyond normal, or you've archived data and don't expect to reuse the space soon. Otherwise all you're doing is forcing SQL to expend time and resources growing the data file again.

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

Digs (6/7/2011)

Hi,

I have two large tables that get updated daily or (added to).

I want to run this TSQL daily.

ALTER INDEX ALL ON DIM_Data REORGANIZE

and

DBCC SHRINKDATABASE (MyDB,10);

Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.

Questions:

1) Should I goto to single mode before either of the above?

2) (if no is to 1), when code runs and used run a select over it, is there any risk.

NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.

But anything good be going on during the SHRINK code.

How do I handle the above code with many users, using the database??

I hope you don't get flamed on the shrink operation, as it is one of the top 5 most worst practices ever !

You can find a little intro about it at http://qa.sqlservercentral.com/articles/SHRINKFILE/71414/

With very good refs to the hot stuff.

A reorganize not do the most optimal reorganization and your shrink database will destroy any optimization anyway !

If you really, really, really need the shrink and cannot do without, first perform your shrink and then do full rebuilds ( not reorganize ! )

Check books online and the article I referred to for more info on the options !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Digs SSCertifiable Points: 7333 More actions · Answer 3

Digs

SSCertifiable

Points: 7333

June 7, 2011 at 1:54 pm

#1335295

phew...lucky I asked:w00t:

so I should

DBCC SHRINKFILE (MyDB,10);

ALTER INDEX ALL ON DIM_Data REBUILD

****ONLY on DOWNTIME, not automatically via job

QUESTION : MyDB bloats up from 500Mb, to 1000MB quickly after a full days operations, so i shouldnt be worried about being bloated??

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

Digs (6/7/2011)

phew...lucky I asked:w00t:

so I should

DBCC SHRINKFILE (MyDB,10);

?

Why Shrink??????

Have you done some archiving operation that has massively reduced the size of the database, size you won't be reusing? If not, all you're doing with that shrink is forcing the DB to immediately grow.

Do Not Shrink on a regular basis. Only after unusual events (archiving, bad maintenance or similar)

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

GreyBeard Mr or Mrs. 500 Points: 535 More actions · Answer 5

ROFL. No, don't shrink and, IMHO, don't reorganize unless you know the condition of the indexes. I have DBs that get 1M+ rows inserted / updated daily so I run a job nightly that queries the DMV sys.dm_db_index_physical_stats using the fragmentation values returned to determine whether to reorganize or rebuild the indexes individually. You can find a similar script in the Scripts Library.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GreyBeard (6/8/2011)

ROFL. No, don't shrink and, IMHO, don't reorganize unless you know the condition of the indexes. I have DBs that get 1M+ rows inserted / updated daily so I run a job nightly that queries the DMV sys.dm_db_index_physical_stats using the fragmentation values returned to determine whether to reorganize or rebuild the indexes individually. You can find a similar script in the Scripts Library.

http://sqlfool.com/2010/04/index-defrag-script-v4-0/

P.S. If 1 GB of data is an issue for you then you need to buy a bigger drive.

SQL_Athmi SSC Enthusiast Points: 141 More actions · Answer 7

Digs (6/7/2011)

Hi,

I have two large tables that get updated daily or (added to).

I want to run this TSQL daily.

ALTER INDEX ALL ON DIM_Data REORGANIZE

and

DBCC SHRINKDATABASE (MyDB,10);

Say I have 100 uses, and the tables in the REORGANIZE code are used frequently.

Questions:

1) Should I goto to single mode before either of the above?

2) (if no is to 1), when code runs and used run a select over it, is there any risk.

NOTE: NO chance of INSERTS, DELETEs, or UPDATES while REORGANIZE runs.

But anything good be going on during the SHRINK code.

How do I handle the above code with many users, using the database??

First SHRINK and then REORGANIZE.

SINGLE MODE is good.

You are using the same database daily for loading, then why do you SHRINK it?

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

SQL_Athmi (6/9/2011)

First SHRINK and then REORGANIZE.

SINGLE MODE is good.

Rather don't shrink, just reorganise (or rebuild). No need for single user mode, both can be done without any risk with the DB in normal operation (though the tables being reorganised may be unavailable, depending what you're doing and how)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

GilaMonster (6/9/2011)

SQL_Athmi (6/9/2011)

First SHRINK and then REORGANIZE.

SINGLE MODE is good.

Rather don't shrink, just reorganise (or rebuild). No need for single user mode, both can be done without any risk with the DB in normal operation (though the tables being reorganised may be unavailable, depending what you're doing and how)

As a side note I have an app (Ms Dynamics Navision) throw not very elegant errors when doing a rebuild during prod hours (now keep it mind that I was also changing the fill factor that time). Normally I'm not getting any errors of that kind.