After reading Kalen Delaney's post about single insert causing 10 page splits, I wanted to see those splits in detail - their order at first place. And in SQL Server 2008 there is a way to trace splits - using new Extended Events infrastructure. Here is simple script that creates the trace and afterwards displays results.

First of all, create and populate table in tempdb as described in the Kalen's post.

Now, let's create and start Extended Events session. The only event we would like to monitor is page_split. On the way we'll capture sql text in order to be sure that it is our insert that caused split.

Now execute INSERT command from Kalen's script.

SET IDENTITY_INSERT split_page  ON;
GO
INSERT INTO split_page (id, id2, data1, data2)
      SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000);
GO
SET IDENTITY_INSERT split_page  OFF;
GO

Afterwards we'll close the session and display results.

OK, the result is:

So, we see 10 splits, among them 4 splits of page 148, another 3 of page 178 etc. It makes sense. When split occurs, ~half of the data from the old page goes to the new one. So if the new row - the one that caused split - should originally have entered first half of the page, after the split it would still try to enter the old page - not the new one. In our case originally we had 56 rows before the new one (id from 0 to 110 step 2) and 385 - 56 = 329 rows after. I would still expect 3 and not 4 splits of the initial page because (((385 / 2) / 2) / 2) = ~48 < 57 (new row's place). So I have expected that after the third split new row would at last leave the initial page. But I was wrong - don't know whether that's just not strict math or there're other factors I didn't think of.

Recently had an interesting experience with NHibernate (I was against working with it from the beginning for many reasons, but unfortunately I wasn't the one to decide). I added trigger to a table that performs several update / insert operation to other tables. Suddenly updating table via NHibernate returned error:

NHibernate.AdoNet.TooManyRowsAffectedException: Unexpected row count: 6; expected: 1

So NHibernate doesn't support triggers that perform DML operations? Not quite. The solution is: add SET NOCOUNT ON before trigger performs DML operations. Looks like NHibernate doesn't really count DML operations, but messages of the type "2 rows updated" returned by SQL Server. And NOCOUNT setting suppresses these messages.

Recently I have noticed 2 new columns added to sys.dm_exec_query_stats and sys.dm_exec_requests DMVs in SQL Server 2008: query_hash and query_plan_hash. Those columns can greatly enhance performance monitoring process. In SQL 2005 main query I'm using for query performance monitoring is:

SELECT TOP 10
   qs.execution_count,
   (qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) AS [Total IO],
   (qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
   SUBSTRING(qt.[text], qs.statement_start_offset/2, (
       CASE 
           WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2 
           ELSE qs.statement_end_offset 
       END - qs.statement_start_offset)/2 
   ) AS query_text,
   qt.[dbid],
   qt.objectid,
   tp.query_plan
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
   OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
ORDER BY [Avg IO] DESC
--ORDER BY [Total IO] DESC

It returns top 10 heaviest queries by average IO that exist in cache. I won't go deeper discussing plan cache memory pressure conditions that can force query plan out of cache thus preventing its detection. Let's just say that it works right in 99% of cases which is good enough. Memory pressure can be detected by other queries.

Which queries would be candidates for tuning? First, those with highest total IO. Second, queries with highest average IO that pass certain minimum number of executions criteria (we usually won't tune query, even the heaviest one, that runs once a month in some offline batch). As a side node - you're probably asking, why do I ignore CPU counters like total_worker_time. The reason is simple: in sys.dm_exec_query_stats this counter is unreliable. It shows incorrect numbers in case of parallel execution.

So, Houston,  do we have a problem here? Unfortunately, we do - when application that works with the database doesn't make proper usage of parameterization (and we don't want to force parameterization via database level setting). In such a case we'll see lots of similar queries with 1 or 2 in execution_count and different values of should-be-parameters in query_text. We can miss such queries because every single one is not heavy enough to be of interest or because many of the queries aren't in cache anymore pushed out by new queries - even of the same type. It is especially realistic scenario for 32 bit systems where entire non-data cache is limited to 1GB of space.

What are our options with poorly parameterized queries? We can use CLR user-defined function provided by Itzik Ben-Gan in his book "Inside SQL Server 2005: T-SQL Querying" that uses regular expressions functionality in order to parameterize query text (this function is widely used for Profiler trace analysis). Query text can be passed through the function and used as grouping column. But even if we don't count performance and CPU price of grouping by text column, I know several organizations that just won't let you create your objects in their database.

Taking all the above into account, I was delighted to find out that in SQL 2008 Microsoft added query_hash and query_plan_hash columns to sys.dm_exec_query_stats DMV. query_hash would be the same for queries with similar logic, query_plan_hash would be the same for queries with similar execution plan. And what's the difference? For column with uneven data distribution, execution plan can be different depending on parameter value. If we have the same value in 90% of a table, Optimizer would sure choose scan option. For another value which is responsible for 0.1% of rows Optimizer will prefer index seek with key or RID lookup depending on table's structure. For those two queries we'll see the same query_hash but different query_plan_hash.

So new SQL 2008 version of the query is:

Another great usage of new columns is to create repository and monitor execution plan changes over time, i.e. changes in query_plan_hash for the queries with the same query_hash value. In SQL 2005 such monitoring was pretty complicated and required on-the-fly parameterization. In SQL 2008 it looks pretty straightforward.

Recently I had to use GUID (Global Unique Identifier) as primary key in order to ensure cross-server uniqueness. In many cases surrogate key (some sort of concatenation between server id and intra-server identity) would give a better performance but in my case due to application requirements surrogate key wasn't an option.

Next step is partitioning by GUID column. Say, you want 3 partitions. Ho do you build partition function? Sure, you can perform a calculation and find the exact 2 values that will divide all the GUID range to 3 equal parts. I needed at least 20 partitions, so I used an approximation. If you write down long hexadecimal number, you'll receive something like A09F76... I decided that partitioning by first 2 tabs is enough. Example for decimal: let's divide values from 1 to 10000 to 3 partitions - what would be the ranges? The exact answer is: 1 - 3333, 3334 - 6666, 6667-10000. Using approximation by 2 first digits will give the following ranges: 1 - 3300, 3301 - 6600, 6601 - 10000. 2 orders of magnitude (first 2 digits) are good enough approximation. In hexadecimal first 2 digits (00 - FF) represent 0-255 in decimal. I'm not accustomed to hexadecimal calculations, so first I divide decimal range to 3 parts: 1 - 85, 86 - 170, 171 - 255. Next step is converting it to hexadecimal: 01 - 55, 56 - AA, AB - FF.

OK, done with values, how do I translate them into GUID? For example, 01 in hexadecimal - what is the GUID representation of it (taking into account that those should be first 2 digits - not the last ones). Took me awhile to understand. First I tried "01000000-0000-0000-0000-000000000000" - surprisingly all the rows (more then a million) came to the first partition. OK, another try: "00000001-0000-0000-0000-000000000000". Still the same. The catch is that GUID should be read in a byte groups order from right to left while inside every byte group reading order is from left to right. And every 2 tabs are 1 byte. So the right representation for 01 as first 2 digits in GUID is: "00000000-0000-0000-0000-010000000000".

To finish with, partition function that partitions GUID to 20 range groups:

We know it from comments in system procedure's code. Here is what I've found while digging for some undocumented stuff about statistics:

-- I don't like this copy&paste stuff, but here it boosts performance by 50%

You can find it too - in sys.sp_table_statistics2_rowset procedure. It returns first 2 parts of DBCC SHOW_STATISTICS result (stat header and density vector) in table format. The procedure header is:

EXEC sys.sp_table_statistics2_rowset
    @table_name = ?, --  sysname
    @table_schema = ?, --  sysname
    @table_catalog = ?, --  sysname
    @stat_name = ?, --  sysname
    @stat_schema = ?, --  sysname
    @stat_catalog = ? --  sysname

If you supply only @table_name and @stat_name, you'll receive the same output as from undocumented (I think) syntax of DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS(@table_name, @stat_name) WITH STAT_HEADER JOIN DENSITY_VECTOR

Recently I've been asked by one of my clients to write a simple script that would find out whether particular database had ever been backed up (full backup). The reason for a requirement was semi-automatic application which would enable end users to manage backup activity including manually backup transaction log - not via Management Studio but via much more friendly UI designed for non-DBAs. As you know, for database running in Full Recovery Mode, transaction log can't be backed up before full backup is done at least for the first time. OK, I thought. Let's just check whether full backup exists for the database:

IF EXISTS( SELECT 1 FROM msdb..backupset WHERE database_name = 'MyDB' AND [type] = 'D' ) ...

 Here comes the first catch. Imagine following scenario:

1. Create database MyDB.

2. Create full backup of 'MyDB'.

3. Drop database 'MyDB' (without deleting backup history). 

4. Create new database with the name 'MyDB' or restore from backup.

What do we have now? Query will return true while actually backup we have belongs to another non-existing database. Try to backup transaction log of the new DB - you'll receive error.

First try failed - let's give another one. Both sys.databases and msdb..backupset contain creation date of the database. In sys.databases column name is create_date, in msdb..backupset it is database_creation_date. So let's just compare database_creation_date from the latest full backup row with database's create_date. If they're equal - we have full backup. Otherwise backup belongs to the previous incarnation of our database while full backup of currently existing db had never been taken. Sure, it should also work for the normal scenario - without deleted and restored databases on the way. So first let's check whether our theory works after second step of the scenario described above. But what happened? How comes, our database creation times are different? 

Here comes second catch. For some strange reason, database_creation_date column in msdb..backupset doesn't contain milliseconds! Minutes, seconds - everything is there but for milliseconds its always 000. While create_date in sys.databases contains full date including milliseconds. Don't know who and for what reason cut milliseconds from the backup history table, but that's the way it works now.

To make long story short - it was the last barrier. After removing milliseconds part from database creation date in sys.databases, everything began to work. Here is the full script that will tell you whether some particular database had ever been backed up (full backup).

DECLARE @DBName SYSNAME
SET @DBName = 'YourDatabase'