{"id":1184,"date":"2025-09-10T09:00:00","date_gmt":"2025-09-10T14:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1184"},"modified":"2025-09-09T21:26:16","modified_gmt":"2025-09-10T02:26:16","slug":"common-transaction-pitfalls-oracle-to-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1184","title":{"rendered":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server"},"content":{"rendered":"

When applications move from Oracle to SQL Server, it\u2019s common to see unexpected blocking and performance regressions<\/strong>. Most issues trace back to transaction handling differences<\/strong> and assumptions carried over from Oracle.<\/p>\n

At a glance<\/h2>\n
    \n
  • Oracle\u2019s MVCC<\/strong> design lets readers and writers proceed without blocking<\/strong> each other.<\/li>\n
  • SQL Server\u2019s default<\/strong> is autocommit ON<\/strong> under READ COMMITTED<\/strong> isolation, implemented with locks<\/strong> (unless you enable row versioning).<\/li>\n
  • Teams sometimes try to mimic Oracle by disabling autocommit or enabling IMPLICIT_TRANSACTIONS<\/code>, which can lead to long\u2011lived transactions that hold locks<\/strong>, causing blocking, deadlocks, and log growth.<\/li>\n<\/ul>\n
    \n

    Important:<\/strong> Always validate Oracle behaviors and environment\u2011specific defaults in official docs during migration planning. Oracle\u2019s concurrency model is MVCC, but autocommit is a client\/driver setting<\/strong> (see note below).<\/p>\n<\/blockquote>\n

    \n

    Why the mismatch happens<\/h2>\n

    Oracle (conceptual defaults):<\/strong> MVCC with READ COMMITTED<\/strong> isolation; readers don\u2019t block writers and vice versa<\/strong>, thanks to undo\/consistent read.<\/p>\n

    Note on autocommit:<\/strong> Oracle Database itself doesn\u2019t have a server\u2011wide \u201cautocommit default.\u201d Tools like SQL*Plus<\/strong> default to autocommit OFF<\/strong>, while JDBC connections default to autocommit ON<\/strong> unless changed by the app. Don\u2019t assume a single universal setting.<\/p>\n

    SQL Server defaults:<\/strong> Autocommit ON<\/strong>, IMPLICIT_TRANSACTIONS<\/code> OFF<\/strong>, isolation level READ COMMITTED<\/strong> implemented with locking<\/strong>\u2014so readers and writers can block<\/strong> each other.<\/p>\n

    Common migration misstep:<\/strong> Apps ported from Oracle sometimes turn on IMPLICIT_TRANSACTIONS<\/code> or disable autocommit to \u201cfeel Oracle\u2011like.\u201d On SQL Server, that choice often yields open transactions for an entire session<\/strong>, which hold locks and block others<\/strong>.<\/p>\n

    \n

    Why SQL Server\u2019s defaults are sensible (for SQL Server)<\/h2>\n
      \n
    • Autocommit ON<\/strong>: Avoids forgotten, long\u2011running transactions that hold locks.<\/li>\n
    • READ COMMITTED<\/strong>: Reasonable integrity (no dirty reads) with minimal overhead; switch to row\u2011versioning for higher concurrency.<\/li>\n
    • IMPLICIT_TRANSACTIONS<\/code> OFF<\/strong>: Prevents accidental long\u2011lived transactions.<\/li>\n<\/ul>\n
      \n

      A pragmatic SQL Server configuration for \u201cOracle\u2011like\u201d concurrency<\/h2>\n
        \n
      1. Keep defaults<\/strong> for most workloads:\n
          \n
        • autocommit = ON<\/code><\/li>\n
        • IMPLICIT_TRANSACTIONS = OFF<\/code><\/li>\n
        • Default isolation = READ COMMITTED<\/code> (locking by default)<\/li>\n<\/ul>\n<\/li>\n
        • Enable READ_COMMITTED_SNAPSHOT (RCSI)<\/strong> to make READ COMMITTED<\/code> use row versioning<\/strong> instead of reader\/writer locks:\n
          ALTER DATABASE YourDB \n  SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;<\/code><\/pre>\n<\/li>\n
        • Optionally allow SNAPSHOT isolation<\/strong> for explicit use where repeatable, statement\u2011stable reads are required:\n
          ALTER DATABASE YourDB \n  SET ALLOW_SNAPSHOT_ISOLATION ON;<\/code><\/pre>\n
          Caution:<\/em> Under SNAPSHOT, update conflicts (Msg 3960)<\/strong> can occur if two transactions try to update the same row (writers still block writers).<\/p>\n<\/li>\n
        • Educate developers<\/strong>: Keep transactions short; always COMMIT<\/code> or ROLLBACK<\/code> promptly.<\/li>\n<\/ol>\n
          \n
          Where row versions live (and what to monitor)<\/h2>\n
            \n
          • If you enable RCSI<\/strong> or SNAPSHOT<\/strong>, SQL Server uses row versioning<\/strong>.<\/li>\n
          • Without ADR<\/strong> (pre\u2011SQL Server 2019 or ADR OFF): versions live in tempdb<\/strong>.<\/li>\n
          • With ADR enabled<\/strong> (SQL Server 2019+ or Azure SQL Database): row versions live in the per\u2011database Persistent Version Store (PVS)<\/strong>.<\/li>\n<\/ul>\n
            What to monitor<\/strong><\/p>\n
              \n
            • tempdb version store usage<\/strong> (when ADR is off):\n
              SELECT * FROM sys.dm_tran_version_store_space_usage;<\/code><\/pre>\n<\/li>\n
            • PVS (when ADR is on):<\/strong>\n
              SELECT * FROM sys.dm_tran_persistent_version_store_stats;<\/code><\/pre>\n<\/li>\n
            • Long\u2011running transactions<\/strong> (they delay version cleanup and cause growth):\n
              SELECT * FROM sys.dm_tran_active_snapshot_database_transactions;<\/code><\/pre>\n<\/li>\n<\/ul>\n
              Note:<\/em> In Azure SQL Database, RCSI is enabled by default<\/strong> and ADR is always on<\/strong>—plan capacity accordingly.<\/p>\n
              \n
              Oracle vs. SQL Server defaults (and SQL Server + RCSI)<\/h2>\n\n\n\n\n\n\n\n\n\n
              Feature<\/th>\nOracle (typical)<\/th>\nSQL Server (default)<\/th>\nSQL Server with RCSI<\/th>\n<\/tr>\n<\/thead>\n
              Autocommit default<\/td>\nClient\/tool specific (e.g., SQL*Plus OFF; JDBC ON)<\/td>\nON<\/td>\nON<\/td>\n<\/tr>\n
              Default isolation<\/td>\nREAD COMMITTED (MVCC)<\/td>\nREAD COMMITTED (locking)<\/td>\nREAD COMMITTED (row\u2011versioning)<\/td>\n<\/tr>\n
              Concurrency model<\/td>\nMVCC<\/td>\nLock\u2011based<\/td>\nRow versioning<\/td>\n<\/tr>\n
              Readers vs. writers<\/td>\nNo blocking<\/td>\nCan block<\/td>\nNo blocking<\/td>\n<\/tr>\n
              Row\u2011version storage<\/td>\nUndo segments<\/td>\nN\/A (locking)<\/td>\ntempdb<\/code> (no ADR) or PVS (ADR)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
              \n
              Detecting \u201cimplicit transactions\u201d problems after migration<\/h2>\n
              Find sessions with open implicit transactions<\/h3>\n
              ;WITH implicit_tx AS\n(\n  SELECT\n      at.transaction_id,\n      at.transaction_begin_time,\n      st.session_id\n  FROM sys.dm_tran_active_transactions AS at\n  JOIN sys.dm_tran_session_transactions AS st\n    ON st.transaction_id = at.transaction_id\n  WHERE at.name = N'implicit_transaction'\n)\nSELECT\n    s.session_id,\n    s.login_name,\n    s.host_name,\n    s.program_name,\n    s.status,\n    s.open_transaction_count,\n    x.transaction_begin_time,\n    DATEDIFF(SECOND, x.transaction_begin_time, SYSUTCDATETIME()) AS open_seconds\nFROM implicit_tx AS x\nJOIN sys.dm_exec_sessions AS s\n  ON s.session_id = x.session_id\nORDER BY open_seconds DESC;<\/code><\/pre>\n
              Flag sleeping sessions with open transactions<\/h3>\n
              SELECT\n  s.session_id,\n  s.login_name,\n  s.host_name,\n  s.program_name,\n  s.status,\n  s.open_transaction_count\nFROM sys.dm_exec_sessions AS s\nWHERE s.is_user_process = 1\n  AND s.status = 'sleeping'\n  AND s.open_transaction_count > 0;<\/code><\/pre>\n
              Capture who\u2019s enabling implicit transactions (Extended Events)<\/h3>\n
              Create a lightweight XE session to capture SET<\/code> options on connect and reuse:<\/p>\n
              CREATE EVENT SESSION [CaptureImplicitTxns] ON SERVER\nADD EVENT sqlserver.login(SET collect_options_text = (1)),\nADD EVENT sqlserver.existing_connection(SET collect_options_text = (1))\nADD TARGET package0.ring_buffer;\nALTER EVENT SESSION [CaptureImplicitTxns] ON SERVER STATE = START;<\/code><\/pre>\n
              Then filter results for options_text<\/code> containing implicit_transactions on<\/code>. For ongoing analysis, prefer an event_file<\/strong> target instead of ring_buffer<\/code> on busy servers.<\/p>\n
              \n
              Optimizing and monitoring tempdb<\/code> \/ PVS<\/h2>\n
                \n
              • tempdb<\/code> layout<\/strong> (if ADR is off or for other versioning features): Use multiple equally sized data files<\/strong> to reduce allocation contention. A good starting point is 1 file per logical CPU up to 8<\/strong>, then grow by 4 at a time only if contention persists<\/strong>.<\/li>\n
              • Instant File Initialization (IFI)<\/strong> for faster growth of data files<\/strong>, including tempdb<\/code> data files (does not apply to the log): enable Windows \u201cPerform volume maintenance tasks<\/em>\u201d for the SQL Server service account.<\/li>\n
              • Watch version store growth<\/strong> continuously (DMVs above). Long\u2011running transactions delay cleanup and can fill tempdb<\/code> or the PVS.<\/li>\n<\/ul>\n
                \n
                Quick commands & checks<\/h2>\n
                Enable row\u2011versioning behaviors<\/strong><\/p>\n
                -- Make READ COMMITTED use row versioning:\nALTER DATABASE YourDB \n  SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;\n\n-- Allow explicit SNAPSHOT isolation:\nALTER DATABASE YourDB \n  SET ALLOW_SNAPSHOT_ISOLATION ON;<\/code><\/pre>\n
                Confirm settings \/ environment<\/strong><\/p>\n
                -- Per-database snapshot\/RCSI switches\nSELECT name,\n       is_read_committed_snapshot_on,\n       snapshot_isolation_state_desc\nFROM sys.databases\nWHERE name = 'YourDB';\n\n-- ADR \/ PVS signal\nSELECT name, is_accelerated_database_recovery_on\nFROM sys.databases\nWHERE name = 'YourDB';<\/code><\/pre>\n
                \n
                DBA checklist (post\u2011migration)<\/h2>\n
                  \n
                • ✅ Decide on RCSI<\/strong> for migrated databases; test critical read\/write paths before enabling in production.<\/li>\n
                • ✅ Validate tempdb<\/code><\/strong>:\n
                    \n
                  • Multiple equally sized data files (start with cores \u2264 8 \u2192 that many files; > 8 \u2192 start with 8).<\/li>\n
                  • IFI<\/strong> enabled for data files.<\/li>\n<\/ul>\n<\/li>\n
                  • ✅ If ADR<\/strong> is enabled, monitor PVS<\/strong> with sys.dm_tran_persistent_version_store_stats<\/code>.<\/li>\n
                  • ✅ Monitor version store usage in tempdb<\/code> with sys.dm_tran_version_store_space_usage<\/code>.<\/li>\n
                  • ✅ Detect long\u2011running transactions<\/strong>: sys.dm_tran_active_snapshot_database_transactions<\/code>.<\/li>\n
                  • ✅ Audit implicit transactions<\/strong> with DMVs\/XE as shown above.<\/li>\n
                  • ✅ Coach developers<\/strong> to avoid long\u2011lived transactions and always COMMIT<\/code>\/ROLLBACK<\/code> promptly.<\/li>\n<\/ul>\n
                    \n
                    Conclusion<\/h2>\n
                    Migrating from Oracle to SQL Server isn\u2019t just a connection\u2011string change; it\u2019s a shift in concurrency and transaction semantics<\/strong>. Oracle\u2019s MVCC generally prevents reader\/writer blocking. SQL Server\u2019s defaults are different and, if you try to emulate Oracle by disabling autocommit or enabling implicit transactions, you\u2019re likely to run into blocking and scalability issues<\/strong>.<\/p>\n
                    The pragmatic approach on SQL Server is to keep the defaults<\/strong>, enable RCSI<\/strong> for Oracle\u2011like read behavior where appropriate, and monitor<\/strong> version store usage (in tempdb<\/code> or PVS<\/strong> when ADR is on). With the right configuration and developer practices, you can achieve high concurrency and predictable performance<\/strong> on SQL Server.<\/p>\n
                    \n
                    Sources & further reading<\/h2>\n
                      \n
                    • Oracle Database Concepts<\/strong>: Data Concurrency and Consistency; Read Consistency; Isolation Levels (default READ COMMITTED).<\/li>\n
                    • Microsoft Docs \u2013 SQL Server<\/strong>: Transaction Isolation Levels; Read Committed Snapshot (RCSI); Snapshot Isolation; Row Versioning behavior.<\/li>\n
                    • Accelerated Database Recovery (ADR)<\/strong> and Persistent Version Store (PVS)<\/strong>: Concepts, behavior, and monitoring DMVs.<\/li>\n
                    • DMVs<\/strong>: sys.dm_tran_version_store_space_usage<\/code>, sys.dm_tran_persistent_version_store_stats<\/code>, sys.dm_tran_active_snapshot_database_transactions<\/code>.<\/li>\n
                    • Extended Events<\/strong>: sqlserver.login<\/code> \/ sqlserver.existing_connection<\/code> with collect_options_text<\/code>; inspect options_text<\/code> to compare SET<\/code> options.<\/li>\n
                    • tempdb guidance<\/strong>: Number of data files (start up to 8 and scale as needed), instant file initialization (IFI) for data files.<\/li>\n
                    • Azure SQL Database<\/strong>: RCSI default ON; ADR always on.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
                      This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.<\/p>\n","protected":false},"author":49,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[82,182,21],"tags":[551,636,637,633,639,635,131,390,638,640,634],"class_list":["post-1184","post","type-post","status-publish","format-standard","hentry","category-database-configuration","category-migration","category-tutorial","tag-accelerated-database-recovery","tag-implicit-transactions","tag-mvcc-vs-locking","tag-oracle-to-sql-server","tag-persistent-version-store","tag-read-committed-snapshot","tag-sql-server","tag-sql-server-migration","tag-sql-server-performance-tuning","tag-tempdb-optimization","tag-transaction-blocking"],"yoast_head":"\nCommon Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltabletalk.com\/?p=1184\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Common Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=1184\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2025-09-10T14:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Jon Russell\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jon Russell\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184\"},\"author\":{\"name\":\"Jon Russell\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/8f5916388cc3b793a960dea33ff1ed86\"},\"headline\":\"Common Transaction Pitfalls After Migrating from Oracle to SQL Server\",\"datePublished\":\"2025-09-10T14:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184\"},\"wordCount\":987,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"Accelerated Database Recovery\",\"implicit transactions\",\"MVCC vs locking\",\"Oracle to SQL Server\",\"Persistent Version Store\",\"READ COMMITTED SNAPSHOT\",\"SQL Server\",\"SQL Server Migration\",\"SQL Server performance tuning\",\"tempdb optimization\",\"transaction blocking\"],\"articleSection\":[\"Database Configuration\",\"Migration\",\"Tutorial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1184#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=1184\",\"name\":\"Common Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2025-09-10T14:00:00+00:00\",\"description\":\"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=1184\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=1184#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Common Transaction Pitfalls After Migrating from Oracle to SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/8f5916388cc3b793a960dea33ff1ed86\",\"name\":\"Jon Russell\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f35049bc3903cae9c596247b2dfe95c0d9b003dd0c758b9690cc00544216aa7c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f35049bc3903cae9c596247b2dfe95c0d9b003dd0c758b9690cc00544216aa7c?s=96&d=mm&r=g\",\"caption\":\"Jon Russell\"},\"url\":\"https:\/\/www.sqltabletalk.com\/?author=49\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk","description":"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltabletalk.com\/?p=1184","og_locale":"en_US","og_type":"article","og_title":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk","og_description":"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.","og_url":"https:\/\/www.sqltabletalk.com\/?p=1184","og_site_name":"SQL Table Talk","article_published_time":"2025-09-10T14:00:00+00:00","author":"Jon Russell","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jon Russell","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=1184#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1184"},"author":{"name":"Jon Russell","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/8f5916388cc3b793a960dea33ff1ed86"},"headline":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server","datePublished":"2025-09-10T14:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1184"},"wordCount":987,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["Accelerated Database Recovery","implicit transactions","MVCC vs locking","Oracle to SQL Server","Persistent Version Store","READ COMMITTED SNAPSHOT","SQL Server","SQL Server Migration","SQL Server performance tuning","tempdb optimization","transaction blocking"],"articleSection":["Database Configuration","Migration","Tutorial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=1184#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=1184","url":"https:\/\/www.sqltabletalk.com\/?p=1184","name":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2025-09-10T14:00:00+00:00","description":"This post explains common transaction pitfalls when migrating from Oracle to SQL Server. It highlights key differences in concurrency models, autocommit behavior, and isolation levels. Readers will learn why disabling autocommit or enabling implicit transactions can cause blocking and performance issues. The article provides guidance on enabling READ_COMMITTED_SNAPSHOT (RCSI), monitoring tempdb or Persistent Version Store (PVS), and detecting long-running transactions. A DBA checklist and best practices are included to help ensure smooth performance after migration.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=1184#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=1184"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=1184#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Common Transaction Pitfalls After Migrating from Oracle to SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"]},{"@type":"Person","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/8f5916388cc3b793a960dea33ff1ed86","name":"Jon Russell","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/f35049bc3903cae9c596247b2dfe95c0d9b003dd0c758b9690cc00544216aa7c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f35049bc3903cae9c596247b2dfe95c0d9b003dd0c758b9690cc00544216aa7c?s=96&d=mm&r=g","caption":"Jon Russell"},"url":"https:\/\/www.sqltabletalk.com\/?author=49"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/49"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1184"}],"version-history":[{"count":3,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1184\/revisions"}],"predecessor-version":[{"id":1188,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/1184\/revisions\/1188"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}