Archive for the 'Trace Flag' Category

Faster DBCC CHECK

2011/12/21

It’s common nowaday that taking long time to run DBCC CHECK for TB level database. Now there is a way to consume more IO resource to run DBCC CHECK faster, by using flag 2562 and 2549. This is only availble for SQL 2008 R2 CU11 now.

My 2 cents, pay attention to tempdb usage when enabling these flags as the trade-off is tempdb size and io. And dont set them as startup parameter, just enable them in the session level when you are ready for the trade-off.

http://support.microsoft.com/kb/2634571

Enable/Disable Lock Partitoning

2011/09/24

Lock partitioning is enabled automatically when the server has16 or more logical cpu. If you want to turn it on on <16 cpu box or turn it off on >=16 cpu box, the only way is to use trace flag as start parameter. Trace flag 1228 enables lock partition for 2 or more logical cpu. Trace flag 1229 disable lock partition.