Optimization job

Question

Optimization job

Krasavita

SSC-Insane

Points: 23138
More actions
July 14, 2009 at 11:24 am

#208126

we have very critical databases running n 24/7 production environment. I want
to set optimization job (Reorganize data/index pages AND Remove unused space
from database files)
Whats the impact on the system if I set these optimization jobs? During
these jobs, will application slow or performance down?

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

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

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

Don't run the 'remove unused space' task. If you run that after rebuilding indexes, it'll result in worse fragmentation than before the rebuild. No reason to shrink a database.

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

This is what I have for all my db accept system:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 05C26610-E745-4EF0-9788-D7CCB8A02939 -Rpt "E:\MSSQL\LOG\SAVVIS Full Recovery Model DB Maintenance Plan0.txt" -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

This is for system:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID AE8C432A-46CF-4BEF-8873-A483AFADE908 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

What do I need to change it?

Thank you

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

Don't use the remove unused space option. Go into the maintenance plan designer and remove that task.

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

Do I need to remove for both system and all databases? What happend in future if I remove this option and why do I need to remove it? Thank you

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

Why No reason to shrink a database? what happens web online when this happens?

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

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx

http://www.straightpathsql.com/blog/2009/1/6/dont-touch-that-shrink-button.html

http://www.straightpathsql.com/blog/2009/6/18/a-picture-that-says-dont-shrink-and-other-things.html

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

Would something like ‘Torn Pages’ be detected or handled by the optimization job?

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

No. That would be the data integrity task.

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 9

How, do you know that? I want to know which db changes to 0 status, which to 16 and maybe change options. Thank you

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

Krasavita (7/15/2009)
How, do you know that?

How do I know what?

The database integrity task is a DBCC CheckDB, so it will detect any torn pages, among other corruptions if present.

I want to know which db changes to 0 status, which to 16 and maybe change options.

None of the maintenance plans are going to change the database options. Only an explicit ALTER DATABASE statement issued by one of the admins is going to do that.

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 11

Will any torn pages on, can cause locking when running integrity job?

Krasavita SSC-Insane Points: 23138 More actions · Answer 12

Also,Integrity job swowing in lSQLogs that ran as DBCC CheckDB,but optimization ran in job history I see and not showing in logs, why is that?

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

CheckDB doesn't take locks regardless of what database options are on or off. The point of torn pages, which you would know if you'd read the link I provided for you in another thread, is all about detecting partial writes if there's a problem with the IO subsystem. If it's not on, you risk not being able to detect (and fix) corruption if it occurs.

As for your second question, because CheckDB writes results into the error log no matter how it's run. Index rebuilds don't write results into the error log regardless of how they're run. It's got nothing to do with the maint plans themselves.

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

Krasavita SSC-Insane Points: 23138 More actions · Answer 14

Krasavita

SSC-Insane

Points: 23138

July 15, 2009 at 8:50 am

#1025117

Thank you