Blog

• Optimizer, what if I had more CPUs?

  

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

  In the March issue of SQL Server Pro Itzik Ben-Gan wrote about an undocumented DBCC command that was recently discovered in the wild by Eladio Rincon (http://www.sqlmag.com/article/sql-server/options-affecting-parallelism-141505). Itzik closed the article by expressing his hope to get this statement documented [more…]

• 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…]

• The Logic Problem in the EndOfMonth function

  

  2012-03-04 - General, TDD, tSQLt

  Test case heuristics can help picking the right test cases for the logic you are developing. This article is a brief introduction into that field, extending on the example of the EndOfMonth function. [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…]

• Purge Problems [TSQL Tuesday #027 – The Big Data Valentine’s Edition]

  

  2012-02-14 - General, Performance, T-SQL Tuesday

  T-SQL Tuesday #27 is hosted by Steve Jones (blog|twitter). This month’s topic is “Big Data”. Purge Problems in Big Data – Not only a SELECT needs an index Recently I was called to an issue at a 5+ TB customer. [more…]

• Purging a Table based on a Fixed Number of Rows to Keep

  

  2012-02-07 - Archiving and Purging, General, Performance

  Purging old data out of the database usually is driven by a date. If the business does not need records that are older than a Year, and if your records have a date column, you can purge by deleting all [more…]

• Selecting the entire Database as XML String – 2

  

  2012-02-01 - General, XML

  About two weeks ago I wrote about how to get the content of the entire database into an XML document. Today Daniel commented, that he would like the XML to be in a compacter format. In this post I am [more…]