SQL Server Performance Forum – Threads Archive

How to find out what DBCC USEROPTIONS is accessing

I want to find out what objects/coding does the command DBCC USEROPTIONS access/running. When I trace it using Profiler, all I see is the DBCC USEROPTIONS.

If you need to find out source, look for "ApplicationName" data in your profiler trace, once you know application.. on test/dev box start running ‘application’ modules one by one and keep looking into profiler data. This process can help you trace source of DBCC USERSOPTIONS command. DBCC USERSOPTIONS rerurns the active(set) options for current connection.
http://msdn2.microsoft.com/en-us/library/ms180065.aspx Deepak Kumar
MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

Hi Deepak Kumar, I’m looking for what system tables/views and the T-SQL statements executed when DBCC USEROPTIONS is executed.

You can execute DBCC USEROPTIONS on sql server qa, and see below connection based settings get executed: Set Option Value
————————
textsize2147483647
languageus_english
dateformatmdy
datefirst7
lock_timeout-1
quoted_identifierSET
arithabortSET
ansi_null_dflt_onSET
ansi_warningsSET
ansi_paddingSET
ansi_nullsSET
concat_null_yields_nullSET
isolation levelread committed For more details, Pls visit: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f7e107f8-0fcf-408b-b30f-da2323eeb714.htm Deepak Kumar
MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

Hi Deepak Kumar, Does DBCC USEROPTIONS execute any T-SQL Statements and access any SQL Server system tables internally? When you trace system stored procedure like sp_helpdb, you can see a lot of T-SQL statements and what are being accessed.

Check this link, perhaps it has better explanation <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> http://www.sqldev.net/misc/fn_setopts.htm</a> Need more details: http://www.siteadvisor.com/sites/windowsitpro.com/downloads/1180953/</a> Download the code from http://www.windowsitpro.com/roadshows/sqlserverbrussels/slides/code_resource_bottlenecks_SQL_server_2000.zip</a> Deepak Kumar MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

Hi Deepak Kumar, Thanks for the useful links. However, I don’t think it explains where SQL Server gets the information of the settings returned by DBCC USEROPTIONS. When an application (such as ODBC) connects to SQL Server, does SQL Server create something like a connection/session object? I’m thinking that DBCC USEROPTIONS retrieve the settings from that object.

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
I don’t think underlying Code executed by DBCC COMMANDS are not TSQL commands.
Mohammed U.

]]>