DBCC INDEXDEFRAG

Question

DBCC INDEXDEFRAG

Viewing 15 posts - 16 through 30 (of 31 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

sjimmo (8/2/2011)
Good question, but the answer does not match the question. The question does not ask about whether the command is being depricated. The answer is actually in the link posted, and the correct answer is wrong.:(

I'm not sure you're reading it correctly, it seems fine to me.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sjimmo SSChampion Points: 11139 More actions · Answer 2

SQLKiwi: I'm not sure you're reading it correctly, it seems fine to me.
cfradenburg: And the correct answer is actually marked correct unless I'm missing something.

I have to disagree, and maybe I am reading it wrong but:

Unfortunately the answer has been given away, but in the link it states that one of the potential answers is not supported, but it states that the command cannot be used on the other three.

IMO you should not be asking a question about something that hasn't yet happened. At this time the command is a valid command, and the fact that ic can be run against one of those things listed as a potential answer should be allowed as a correct answer.

(I do not care about the point, just the message being conveyed.)

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Paul White SSC Guru Points: 150467 More actions · Answer 3

sjimmo (8/2/2011)
IMO you should not be asking a question about something that hasn't yet happened. At this time the command is a valid command, and the fact that ic can be run against one of those things listed as a potential answer should be allowed as a correct answer.

I think that's a pretty thin argument, to be frank.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Q: "DBCC INDEXDEFRAG can be used for which structures? Select one"

On SQL 2005, If I run:

DBCC INDEXDEFRAG (msdb, 'backupset', backupsetuuid)

and get a result of:

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Doesn't that mean it worked on a system table ?

Just because "DBCC INDEXDEFRAG is not supported for use on system tables" doesn't mean it doesn't work. I got this "wrong" based on my having done this in the past. I want my point !

PS. How do you view the original question ? Now when I go back to QoD, I only see the answer.

sjimmo SSChampion Points: 11139 More actions · Answer 5

Paul
I think that's a pretty thin argument, to be frank

That may be, but in this case the answer doesn't match the question.

In the past, I have almost always defended the writer of the QOD, but I just can't here.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

sknox SSChampion Points: 12388 More actions · Answer 6

SQLkiwi (8/2/2011)
bitbucket-25253 (8/1/2011)
DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)
DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in the AdventureWorks_regular DB
Neither of those are system tables, they are ordinary tables that happen to be used by the system 🙂

Hmm. So why is spt_values categorized by SSMS as a system table...

Looking deeper, my default install of SQL Server 2005 shows the following tables in the master database, all categorized as system tables by SSMS:

spt_fallback_db

spt_fallback_dev

spt_fallback_usg

spt_monitor

spt_values

MSreplication_options

all of which are of type U [USER_TABLE] according to sys.objects. Ditto with SQL 2008R2.

sys.objects shows 51 objects of type S (System Table) but SSMS doesn't show them anywhere. Running DBCC INDEXDEFRAG on one of them (sys.sysrowsets) returns

Msg 2567, Level 14, State 1, Line 1

DBCC INDEXDEFRAG cannot be used on system table indexes

So it's more than "not supported". You can't do it.

It doesn't help that Microsoft is muddying the waters by calling a table a "System Table" in SSMS when it's actually a User Table used by the system, and hiding the actual system tables.

sjimmo SSChampion Points: 11139 More actions · Answer 7

Homebrew
Doesn't that mean it worked on a system table ?

Since the link was for a 2008 R2 system, I tested it there.

I ran

DBCC INDEXDEFRAG(master,spt_values, 1)

which returned:

Pages Scanned Pages Moved Pages Removed
-------------------- -------------------- --------------------
15 0 0

which means to me that it worked.

Again, the link says unsupported on a deprecated. If we are going to have questions on things like this, than we should accept the real answer and not that it is going to be removed in a future release, so we don't accept all answers.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Paul White SSC Guru Points: 150467 More actions · Answer 8

homebrew01 (8/2/2011)
Doesn't that mean it worked on a system table?

I guess it depends what you consider to be a 'system table'. The term is overloaded - some would regard any table that appears under System Tables in SSMS to be a system table, others would limit the definition to tables in the sys schema, and still others would restrict it to tables like syspalvalues.

Just because "DBCC INDEXDEFRAG is not supported for use on system tables" doesn't mean it doesn't work.

This is true, but I still get an overwhelming sense of 'meh' when I try to empathise with this viewpoint.

I got this "wrong" based on my having done this in the past. I want my point !

You got it by posting 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

sjimmo (8/2/2011)
DBCC INDEXDEFRAG(master,spt_values, 1)

This example was mentioned earlier. spt_values is an ordinary table in the dbo schema:

SELECT t.type_desc

FROM master.sys.tables AS t

WHERE t.name = N'spt_values'

AND t.[schema_id] = 1

...that query returns 'USER_TABLE'. Yes, it happens to be used by the system, but whether that makes it a system table or not is open to interpretation. I say no.

Try the DBCC command on an object from sys.objects that returns a type of 'SYSTEM_TABLE'.

edit: to fix tags

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sjimmo SSChampion Points: 11139 More actions · Answer 10

Paul
but I still get an overwhelming sense of 'meh' when I try to empathise with this viewpoint
Yes, it happens to be used by the system, but whether that makes it a system table or not is open to interpretation. I say no.

Not looking for empathy, and this is one of those points where we can respectably decline to agree. I have stated my opinion, and you yours.

As for whether it is a system table, I have referred to http://msdn.microsoft.com/en-us/library/ms179932.aspx which I guess is also wrong. But then it is MS, and I can't say that they have always been right:-D

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Paul White SSC Guru Points: 150467 More actions · Answer 11

sjimmo (8/2/2011)
Not looking for empathy, and this is one of those points where we can respectably decline to agree.

Of course. I'm first up to criticize a poor QotD (and I have been on the other end of it too), but I'm unpersuaded that this is other than a good question. Anyway, time to go do other stuff.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

nice question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

August 2, 2011 at 12:32 pm

#1362488

Nice question, thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 14

SQLkiwi (8/2/2011)
bitbucket-25253 (8/1/2011)
DBCC INDEXDEFRAG (Master,"dbo.spt_values",ix2_spt_values_nu_nc)
DBCC INDEXDEFRAG (AdventureWorks_regular,"cdc.ddl_history",ddl_history_clustered_idx)-- a system table in the AdventureWorks_regular DB
Neither of those are system tables, they are ordinary tables that happen to be used by the system 🙂

In that case there is a bug in SSMS in SQL2008 R2, since it shows master.dbo.spt_values as a system table. What then should I believe?

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 15

bitbucket-25253 (8/1/2011)
IGNORE THIS ENTRY SEE CORRECTION BY SQLkiwi Posted Today @ 4:23 AM

Regardless of Paul's correction, DBCC indexdefrag in SQL Server 2008 R2 appears to work on some system tables. At least it does if what BoL says are system tables actually are system tables - I suspect BoL (and SSMS) has got this wrong.

For example msdb.dbo.backupfile is listed as a system table here in BoL, and also is listed as a system table by SSMS. DBCC INDEXDEFRAG seems to work on this table.

It also appears to work on sysalerts, log_shipping_primaries, systaskids, systargetservers, syspolicy_conditions_internal.... I could go on (and on and on) but there's not much point.

Perhaps someone will add some community content to the BoL pages in the tree below the system tables page to point out that most (?all?) of these tables are not system tables, raise a connect entry asking for a BoL correction, and raise another connect entry asking for SSMS not to list these things as system tables? . And of course another connect entry asking for the dbcc indexdefrag BoL page to be corrected to show system tables as an addition to the list of things that this utility can't cope with - and of course add community content to the page to let people know in the interim? And of course ask for a correction of script B on that page, which would presumably fail if this dbcc feature can't handle system tables (actually I modified that script to call dbcc indexdefrag on everything even if no index needed defragging - and it didn't produce a single failure message when run in master, in msdb, or in any of the user databases on my system).

Tom