November 11, 2002 at 9:27 pm
When the Index Tuning Wizard analyzes a workload does it actually execute all (or some) of the queries in the workload or does it just generate execution plans for them?
I tried to answer this question using the profiler to capture all statements executed by the Index Tuning Wizard. I see the queries from the workload as completed batches, however they are always preceded and followed by a few DBCC autopilot commands. Does anyone know what this undocumented DBCC does?
I also saw an undocumented set command:
set autopilot on/off
Each query in the workload is always immediately preceded by set autopilot on
and immediately followed by set autopilot off. Does anyone know what this does to the execution of the query?
I got the following error when I tried to run a query after executing set autopilot on:
Server: Msg 8601, Level 17, State 63, Line 1
Internal Query Processor Error: The query processor could not obtain access to a required interface.
The query ran fine after I executed set autopilot off.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 12, 2002 at 4:05 am
Sorry, no documentation on this, I checked everywhere I know of and even so old 6.5 reference material. I suspect it may be part of the internal threading process based on the name and past run ins with similar name for thread proccesses, and it may be an internal use only item. This is just my best guess.
November 12, 2002 at 1:50 pm
Interesting that you received an error. I tried using it and got back very interesting results. It basically showed me a breakdown of each command my query was executing, what the execution plan was, estimations of system utilization, etc. Pretty neat stuff, I'll have to explore.
Matthew Burr
November 12, 2002 at 2:09 pm
I did some more investigating and found out that I got that error because I didn't ever try to execute DBCC autopilot which seems to do something when I execute DBCC autopilot (0).
Now I see what you are seeing. Then I thought this was the same thing you get when executing SET SHOWPLAN_ALL ON. So I executed that. However using DBCC autopilot with set autopilot displays more information than SET SHOWPLAN_ALL.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 12, 2002 at 2:27 pm
Some further investigation into DBCC AUTOPILOT seems to suggest to me that it is used to "configure" the environment for SET AUTOPILOT. I believe, but this is just inference, that DBCC AUTOPILOT is used to create indexing scenarios. It essentially says things like, "this index is turned on on this table, but this other index is turned off," etc. Each execution of it set another condition. Then, when you execute SET AUTOPILOT it is set with those conditions, so that when you execute your statement(s) it acts as though certain indexing exists or doesn't exist. Fascinating stuff (to me).
Matthew Burr
November 12, 2002 at 2:37 pm
Me too. Also if you set Query Analyzer to show the execution plan you won't get one when using the autopilot commands which seems to support the fact that the Index Tuning Wizard does not execute the query, it simply generates an execution plan and probably some statistics too.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 12, 2002 at 2:47 pm
After getting home I tried looking for again and found something this time (I hate filtering sites on firewalls).
http://www.umachandar.com/technical/SQL70Scripts/Main15.htm
quote:
-- General Note:-- Use 'WITH TABLERESULTS' option for most of these DBCC statements to get an output
-- that can be piped to a temporary table for analysis. This has been implemented for
-- most of the DBCC statements in SQL2000.
1. DBCC AUTOPILOT
- Used by the index analysis tool in SQL70.
- I haven't figured out how to use this though & what purpose it serves.
2. DBCC SHOWFILESTATS
- Used in the SQL70 Enterprise Manager HTML code to get
- the database files information. This one is pretty useful.
3. DBCC SHOWTABLEAFFINITY
- To view table information like data, index, statistics pages etc.
- This one is real cool. Used again in EM.
4. DBCC PERFMON
- To see all the performance counters.
5. DBCC FLUSHPROCINDB
- I don't know what this does. Probably flushed cached procs for the db.
6. DBCC DBTABLE(pubs)
7. DBCC DBINFO( pubs )
- Gives low level information about the database files
8. DBCC BUFCOUNT([1..10])
- This works till about 10 i think.
- Gives the buffer chain information. Not much help for us.
9. DBCC PSS
- Documented in Technet.
10. DBCC DBREPAIR(@database, repairindex, @table, @indid)
- Can be used to fix indexes on system tables only. Used in sp_fixindex.
11. DBCC DBREPAIR(@database, dropdb, noinit)
- Can be used to drop a faulty database. Use this only in SQL6x. In SQL70 &
- above, DROP DATABASE will suffice.
12. DBCC DBCONTROL(@database, online|offline)
- Same as using sp_dboption.
13. DBCC LOCKOBJECTSCHEMA(@tab)
- Can be used when modifying system table data.
- Used in some upgrade scripts.
14. DBCC DBRECOVER(@database)
- To run recovery on database.
15. DBCC REINDEXALL(@Database, 240)
- I believe this reindexes the system tables.
- May work for user tables also i think. Haven't tested this out.
16. DBCC ADDEXTENDEDPROC(function, dll) & DBCC DROPEXTENDEDPROC(function)
17. DBCC UPGRADEDB(@database)
- Don't know what this does.
18. DBCC DETACHDB(@database)
19. DBCC DBREPAIR('', 'dropdevice', device_name, 1|0)
- '1' will drop device & delete the physical file too.
- '0' will just drop the device.
20. DBCC DBREPAIR(@database, markdirty)
- Used when you rename the database to force sql server to
- update its internal resources i guess.
21. DBCC PINTABLE(@database_id, @table_id) & UNPINTABLE(@database_id, @table_id)
- Pin / unpin table in memory. Can be used in sql60/65 also i think.
22. DBCC bcptablock(@@dbid, @@tableid, 1/0)
- Used by bcp for fast loading.
23. DBCC GAMINIT
- This reinitializes the internal structures for each database.
24. DBCC DES
- Prints the internal object descriptors.
- Documented in technet kb article.
25. declare @dbcc_current_version integer
dbcc getvalue('current_version')
select @dbcc_current_version = @@error
select @dbcc_current_version
- This i believe gives some internal version number.
26. DBCC PGLINKAGE( dbid, start, number, printopt={0|1|2}, target, order={1|0})
- Documented in technet kb article.
27. DBCC PrtIPage (dbID, TableID, IndexID, IndexPage)
28. DBCC LocateIndexPgs (dbID, TableID, Page, IndexID, IndexLevel)
29. DBCC PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )
--- SQL70
DBCC PAGE ( {dbid | dbname }, file#, page# [,printopt] [, cache])
dbid = ID of the database
dbname = Name of the database
file# = number of file containing page
page# = number of page within file
printopt = 0-2
---------------0(default) = print buffer & page headers
---------------1 = 0 + each row and row offset table
---------------2 = 0+ whole page and offset table
cache = 0/1
---------0 = print page as on disk
---------1(default) = print page as in cache (if present) or on disk (if not)
30. DBCC LOGINFO
- Provides virtual log file(s) details. Can be used to determine which virtual
- log file contains the active portion of the transaction log. The Status value
- is the one to watch out for. A value greater than zero means the corresponding
- virtual log file is in use.
- Please see the "sp_loginfo" SP under "SQL70Scripts\UtilitySps" page
31. DBCC CALLFULLTEXT
- To invoke all the fulltext commands like reinitialize catalog etc.
32. DBCC STACKDUMP
- Will generate a dump file on the server LOG directory. This will capture
- OS configuration, memory, a dump of all threads running on the server.
33. DBCC MEMORYSTATUS
- Provides some memory related counters
November 12, 2002 at 3:02 pm
Here are some typical commands as sent by the Index Tuning Wizard:
dbcc autopilot (1, 19, 718625603, 1, 0)
dbcc autopilot (6, 19, 718625603, 1, 1, 0, 10)
I suspect one of these numbers could be an index ID.
The number 718625603 is the ID of the table included in the query I asked the ITW to analyze.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 12, 2002 at 11:00 pm
Yes, so far I believe that the second number is the database id, the third number is the object id, and the fourth number is an index id.
I found one newsgroup message from someone else who had stumbled across this command, and that person indicated that the first number had something to do with statistical information in system tables, but she was guessing as much as we are.
Matthew Burr
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply