Johan Åhlén

StreamInsight introduction and BizTalk adapter

I stumbled upon this blog post, which I think gives a very good introduction to building a StreamInsight Application:
http://blogs.msdn.com/b/masimms/archive/2010/08/10/building-your-first-end-to-end-streaminsight-application.aspx

It's rather long and more detailed than my introduction articles. I can really recommend it if you want to get started with StreamInsight!

I can also recommend this article which describes how to build an adapter that communicates with BizTalk (or any other SOAP/REST endpoint):
http://seroter.wordpress.com/2010/07/09/sending-streaminsight-events-to-biztalk-through-new-web-soaprest-adapter/

Try your SQL optimizing skills and win great honor

As winner of the previous round, I’ve been given the honor to write the problem for Phil Factor Speed Phreak Challenge #6. This problem is from the real world (algorithmic trading) and the sample data is real.

The challenge is to reproduce highest buy and lowest sell prices from data captured from a stock exchange. There are four competition categories, all of which give you great honor and nice prizes:

• Overall best performance (first prize)
• Best SQL CLR solution
• Best cursor-based solution
• Best unconventional solution (be creative!)

Don't hesitate - give it a try!
http://ask.sqlservercentral.com/questions/17499/phil-factor-speed-phreak-challenge-6-the-stock-exchange-order-book-state-problem 

Have fun and good luck!

Exploring SQL Server under the hood using undocumented DBCC commands

Have you ever wanted to see the details on how data is organised in a SQL Server database? Exactly how tables, indexes, blogs, etc are stored. How SQL Server can find a specific row in a table. This is possible through two undocumented DBCC commands: DBCC IND and DBCC PAGE.

Here's a description of the DBCC commands.

DBCC IND

Lists all of a table's data and index pages. Syntax:

DBCC IND
(
  'database_name' | database_id,
  table_name,
  index_id
)

where index_id is either a value from sys.indexes or 
-1 to show all indexes and IAMs 
-2 to show all IAMs.

DBCC PAGE

Displays the content of data and index pages.

DBCC PAGE
(
  'database_name' | database_id,
  file_number,
  page_number,
  detail_level
)

where detail_level is a number between 0 (least detailed) and 3 (most detailed).

Note that both these DBCC commands require that you set trace flag 3604:

DBCC TRACEON (3604)

An example

Let's start by creating a database with a few files.

CREATE DATABASE [TEST] ON  PRIMARY
( NAME = N'TEST_Primary1', FILENAME = N'C:\Your_path_here\TEST_Primary1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'TEST_Primary2', FILENAME = N'C:\Your_path_here\TEST_Primary2.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),  
FILEGROUP [SECONDARY]
( NAME = N'TEST_Secondary', FILENAME = N'C:\Your_path_here\TEST_Secondary.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
LOG ON

( NAME = N'TEST_log', FILENAME = N'C:\Your_path_here\TEST_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

Next, let's create a table.

USE [TEST]
GO 
CREATE TABLE [dbo].[TestTable]
(           
  [ID] [int] IDENTITY(1,1) NOT NULL,          
  [SmallText] [varchar](2000) NOT NULL,          
  [LargeText] [text] NOT NULL,          
  [SparseText] [varchar](20) SPARSE  NULL, 
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(          
  [ID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [SECONDARY]

Now let's insert some data into the table.

WITH CTE1 AS
(          
 SELECT 1 AS [Ctr]          
 UNION ALL          
 SELECT [Ctr] + 1          
 FROM CTE1          
 WHERE [Ctr] < 2000
)
,CTE2 AS
(          
 SELECT 
 '*** Row ' + CAST([Ctr] AS VARCHAR) + ' ' + REPLICATE('*', 1000) [Str]                      
  ,[Ctr]          
 FROM CTE1
)
INSERT INTO TestTable(SmallText, LargeText, SparseText)
SELECT [Str], [Str], NULL
FROM CTE2
OPTION (MAXRECURSION 0)

Let's modify one of the rows.

UPDATE TestTable
SET SparseText = 'Hello world!'
WHERE ID = 2

Now let's have a look a the table details through a DMV.

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('TEST'), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED')

The interesting output here is that the IN_ROW_DATA (ID, SmallText and SparseText) has 2000 records (same as amount of rows) while the LOB_DATA (LargeText) has 4000 records! That is something you could investigate using the DBCC commands.

First let's find the page numbers.

DBCC TRACEON (3604)DBCC IND('TEST', TestTable, -1)

You'll get a long list of rows (one for each data/index page). Some of the interesting columns are: PageFID and PagePID which gives you the file_number and page_number of the page. We'll see three different values for PageFID: 1 for Primary1, 3 for Primary2 and 4 for Secondary if you have used my database creation statement. There is also a PageType field that has indicates type of page: 1 for data page, 2 for index page, 3 and 4 for text pages, 8 for GAM page, 9 for SGAM page, 10 for IAM page and 11 for PFS page.

Now you can display the detailed contents of any page using the DBCC PAGE command. Let's have a look at the first data page of the table - in my example PageFID 1 / PagePID 89.

DBCC PAGE('TEST', 1, 89, 3)

The number 3 says we want to get as much detail as possible. You'd then get very detailed info about the page. Some of the output is shown below (run the command yourself to see all output).