August 25, 2014 at 10:06 am
Someone claimed that the query:
INSERT INTO [table1] SELECT * FROM [table1]
was a result of running DBCC DBREINDEX.
I wasn't able to replicate this when I did a trace on my local machine, but SQL Monitor from Red-Gate shows that query happening for at least some of the tables that are being reindexed via a job.
This just seems odd to me, can anyone shed some light on this?
Second question: Is there anyway to examine the actual code inside DBREINDEX to see exactly what commands it may execute?
August 25, 2014 at 10:09 am
Yup, the guy's right.
Please note that DBCC IndexRebuild is deprecated, has been so since SQL 2005. Use ALTER INDEX.
As for examining the code of DBCC Index Rebuild, you'd have to ask Microsoft for access to their source code.
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
August 25, 2014 at 10:13 am
That was my response that is is depreciated, but I still wanted to understand it.
So I wonder why my profiler trace isn't showing that activity, maybe a function of table size or something? (Or I am missing some columns)
Also,why is DBCC doing that? Does it delete data and re-add it what is the purpose of that statement (the insert all rows from table into self)?
August 25, 2014 at 10:19 am
Maxer (8/25/2014)
So I wonder why my profiler trace isn't showing that activity, maybe a function of table size or something? (Or I am missing some columns)
Probably the events your capturing.
Also,why is DBCC doing that? Does it delete data and re-add it what is the purpose of that statement (the insert all rows from table into self)?
No deletion. It's creating a new copy of the index.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply