I where planning to write a blog post about Change Data Capture (CDC) in SQL Server 2008, but then I stumbled upon Pinal Dave’s article Introduction to Change Data Capture (CDC) in SQL Server 2008.
It is a thorough overview of Change Data Capture (CDC).
MSDN reference for Change Data Capture (CDC).
I was using SQL Server Profiler on a SQL Server 2005 Enterprise Edition looking for performance culprits, when I stumbled upon this very long running process.
I think this must be a world record. 🙂
This statement has only used 16 milliseconds of CPU but it has been running for more than 500.000 years!
Watch out when using @@IDENTITY and SCOPE_IDENTITY() in your applications. Below is a quote from a Microsoft SQL Server engineer.
… whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistently and can’t be relied upon.
You can find more about the bug and workarounds at the SQL Server bug report.
UPDATE 28MAR2009: Pinal Dave has a more detailed blog post about the bug with workaround.
Run the below query to see which databases that uses read committed snapshot isolation:
SELECT name, is_read_committed_snapshot_on FROM sys.databases
To enable it on a specific database run the below query:
ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON
As a developer I am in love with read committed snapshot isolation feature for SQL Server 2005+. Transactions can read and write simultaneously the same piece of data – no read/write contention due to row versioning. Writers will block other writers though.
Normal conflicting scenarios:
Simultaneously write and read of the same data.
- Read committed isolation: The reader will wait for the writer to release the exclusive lock.
- Read committed snapshot isolation: The reader will not block, but receive the latest committed data (latest row version).
Simultaneously writes of the same data.
- Read committed isolation: The second writer will wait until the first transaction completes because of an exclusive lock.
- Read committed snapshot isolation: Same as read committed isolation.
Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.
Update 02FEB2011: Amended the article due to Tom’s comments.
Microsoft has released a poster for download, where you can see all the system views for SQL Server 2008 and the relationships between them.
Microsoft also released the system views poster for SQL Server 2005 a couple of years ago.
You need a huge printer/plotter to print these…