DBCC

Question

DBCC

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
October 23, 2008 at 11:45 pm

#195062

Comments posted to this topic are about the item DBCC

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

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

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 1

Great question and I almost selected "reduce the amount of query activity on the system". The reason I didn't is because though this would help the performance of the DBCC command, do I really have a choice on what queries are executed and when by customers accessing the db? Maybe ad-hoc queries by dba's or developers but not necessarily ones from production system or web sites. I figure I need to choose the best time based on available resources.

Guess I over thought the answer and should have stuck with my initial response. :hehe:

David

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

David,

You do have a choice. You can run DBCC Checkdb at the low usage times, like at night or middle of the month or when you determine the lowes user activity is.

What I am curios about if anybody even considered how ANSI NULLS affect performance.

Regards,Yelena Varsha

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 3

You do have a choice. You can run DBCC Checkdb at the low usage times, like at night or middle of the month or when you determine the lowes user activity is.

You are correct that I have a choice to run it during low usage times (as I should). I was referring to the idea that on a production server I most likely don't have a choice on what queries run and when... thus I cannot actively reduce the SQL activity but rather I need to find the time with the least activity. Basically I read too much into the wording of that particular answer.

David

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

Glad you liked the question. Something triggered this in my mind and I thought it was a good one.

Anipaul SSC-Insane Points: 24681 More actions · Answer 5

Anipaul

SSC-Insane

Points: 24681

October 28, 2008 at 10:59 pm

#891037

Nice question ...

jagadishrashmi SSC-Addicted Points: 496 More actions · Answer 6

I looked at http://msdn.microsoft.com/en-us/library/ms175515.aspx too,

i'm not sure if just reducing the activity will help - because of the statment - "Regardless of system activity, running DBCC CHECKDB against large databases (relative to the size of available memory) causes spooling to the tempdb database. Therefore, tempdb database should be placed on a fast disk or disks, such as a RAID device, separate from user databases"

I picked moving tempdb but may be it is a combination of moving tempdb as well as reducing activity.

Any thoughts?

---- [font="Tahoma"]Live and Let Live![/font] ----

arr.nagaraj SSCertifiable Points: 6518 More actions · Answer 7

Ansii Nulls..hmmm..I dont know the tone of your comment ( Sarcasm ? )

but i felt there is a remote possiblity of Ansii Null affecting this one.

If Anscii nulls are off

-> Many queries wont be picking indexes esp..on computed columns/ indexed views..

-> Slower queries meaning likely more usage of memory

-> More memory meaning likely Check DB slowing down..

I understand this one is far fetched..but wud like to know others think abt this..

Regards,
Raj

http://Strictlysql.blogspot.com

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Nice question. Useful reference in the explanation, I hadn't come across that page before.

The fourth option was amusing - particularly as about 1 person in 4 picked it, about 570 people. Why would anyone think that restricting parallelism would improve DBCC CHECKDB performance? :w00t:

Tom