SQL Server Performance

Server/DB settings precedence

Discussion in 'Performance Tuning for DBAs' started by mick66, Feb 17, 2003.

  1. mick66 New Member

    I'm trying to get my head around Server and DB settings. Am I wrong or are the only server/database settings that overlap, those that are listed under server settings 'user options'.

    I thought that Database options override server settings but this appears not to be the case. If Arithabort is set ON for the server but OFF for the database, it appears to be ON(when connecting with osql.exe, which doesn't explicitly SET Arithabort).

    Can anyone provide an example of a server setting that can be overriden by a database setting.

    Also, is it me or does BOL contradict itself....someplaces it differs in the list of database settings available.

    Cheers

    Mick
  2. bradmcgehee New Member

    The best discussion I have seen on this is under the article "SET Options" in the Books Online. Of course, you may have already seen this.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. mick66 New Member

    Brad

    sorry to disagree but the BOL is ambiguous. If you read most of the pages under SET...like...'options that affect results', you are left with the impression that SET overrides database settings which in turn override Server settings. In fact Server settings that are ON override database settings that are OFF....the final session environment is actually a combination of Server & Database ON settings...plus whatever your driver/application then SET ON/OFF.

    Would you agree that thats a fair statement.

    I know its splitting hairs but BOL is a bit confusing in this area.

    Cheers

    Mick
  4. bradmcgehee New Member

    When these SET commands are issued, they take affect for that specific session, and override all other related settings. What is sometimes confusing is that they take affect only for that session, not any others. If you want SET commands to come into play all the time (replacing any global options), then you will need to issue them each time a new session is established.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page