Category: Uncategorized

RCSI and LOB data – how tempdb version store is used?

Row Version Store We know RCSI (Read Committed Snapshot Isolation), as well as SI (Snapshot Isolation) use row versioning to store previous verions of rows in part of tempdb called “row version store”. If you are using default isolation level

Posted in Uncategorized

Page Life Expectancy and 300 sec limit

People blogged about it, including excellent Jonathan’s post here (highly recommended to read), but I will give a little more insight here. Basically, Microsoft says if PLE is below 300 seconds, we should worry about memory pressure (an old recommendation

Posted in Uncategorized

CHECKPOINT vs LAZY WRITER

Checkpoint and Lazy Writer are somewhat similar: they both write pages from buffer pool memory to the disk. So, what is the difference?

Posted in Uncategorized

Sort records without ORDER BY?

How often you see some smartie “optimizes” the query by removing ORDER BY, justifying that query always goes by that index, and index is in desired order for the result? Or they say: “I executed this query thousands of times

Posted in Uncategorized

Do you measure query IO with SET STATISTICS IO ON ?

Introduction In our tuning work and often in presentations we see people use SET STATISTICS IO ON as a handy way to measure IO, especially logical reads. But, not many people know that it skips measuring IO from certain types

Posted in Uncategorized

READ COMMITTED sucks!

Are you still using READ COMMITTED transaction isolation level? Default transaction isolation level on all SQL Server versions (2000-2014) has serious inconsistency problems, by design. Not many people are familiar how and why is it happening. I am writing this

Posted in Uncategorized
Visit Us On TwitterVisit Us On FacebookVisit Us On LinkedinVisit Us On YoutubeVisit Us On Google PlusCheck Our Feed