Post reply

add snapshot Isolation for ad-hoc queries

  • September 26, 2011 at 1:09 pm

    #258004

    I have SQL Server 2008 R2 version and I have Java application.

    Can I add "set snapshot isolation ON" statement to the ad-hoc queries that are part of application. Will it take effect if I add the above statement

    In my application, there are no SP or UDFs only ad-hoc queries (include all DMLs and SELECT statement) I know it's weired but it is what it is

    Thanks for all advices

  • September 26, 2011 at 2:20 pm

    #1386802

    Providing snapshot isolation has been enabled on the database, yes. Do watch the additional load on TempDB

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 26, 2011 at 2:32 pm

    #1386812

    Hello GilaMonster,

    Thanks for the reply and me personally your answers will help a lot and thanks for that.

    I am watching TempDB size, but my question is can I add "Set Snapshot Isolation ON" in ad-hoc statement. I know that I can add to a Stored Procedure but I am not sure if I can add to ad-hoc queries or no?? if I can add will it work??

    Thanks

  • September 26, 2011 at 2:38 pm

    #1386818

    Yes you can, providing snapshot isolation has been enabled on the database level first.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 26, 2011 at 4:12 pm

    #1386863

    You'll need to edit all and every ad hoc queries you need to run under that isolation.

    Maybe you can set that at the connection level but I can't tell you exactly how.

    You can really go in the db and say use that isolation level for all adhoc queries and not those queries. Does that answer your question.

  • September 26, 2011 at 4:16 pm

    #1386864

    Perfect.. Thanks Ninja's_RGR'us and Gila

Viewing 6 posts - 1 through 5 (of 5 total)

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