Category: SQL Server Internals

• Key Lookup Operator in Update Statements

  

  2012-03-20 - General, Performance, SQL Server Internals

  When thinking about the indexes for update statements it is easy to overlook an important peculiarity in the way SQL Server finds the rows to update before updating them. Those pesky Lookups If you have an UPDATE statement that filters [more…]

• The Gap in the Identity Value Sequence

  

  2012-03-13 - General, SQL Server Internals

  With this blog post I am going to dive a little deeper into how SQL Server generates and handles identity values and what the implications for your code are. Your New Identity SQL Server manages identity values on a per [more…]

• The Story of the Evil Assert Operator

  

  2012-03-06 - General, Performance, SQL Server Internals

  Today I am going to talk about the evil side of the Assert Operator. To start out, let me introduce this operator to you: The Assert Operator The Assert Operator is used by SQL Server in execution plans that require [more…]

• Why CXPACKET Waits are not your Performance Problem

  

  2012-02-28 - General, Performance, SQL Server Internals

  There is a lot of information out there recommending to do this or that to deal with the performance problems that you must have if you see CXPACKET waits. All these recommendations build on the notion that the CXPACKET wait is [more…]

• Deferred Deallocations of Pages in TempDb

  

  2012-02-19 - General, SQL Server Internals, TempDb

  The other day I ran into a question on msdn:Tempdb Deallocation (dm_db_session_space_usage). A person with the pseudonym DontPageMeBro noticed an odd behavior in his SQL Server installation: The sum of the reported usage of tempdb across all sessions was often [more…]