Johan Åhlén

Johan Åhlén's blog about life, universe and everything.

August 2010 - Posts

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).


PAGE: (1:89)


BUFFER:


BUF @0x0000000085FE9080

bpage = 0x0000000085C2C000           bhash = 0x0000000000000000           bpageno = (1:89)
bdbid = 9                            breferences = 0                      bcputicks = 390
bsampleCount = 1                     bUse1 = 7672                         bstat = 0xc0010b
blog = 0xca2159bb                    bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x0000000085C2C000

m_pageId = (1:89)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594039828480                                
Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:94)
pminlen = 8                          m_slotCnt = 7                        m_freeCnt = 759
m_freeData = 7419                    m_reservedCnt = 0                    m_lsn = (43:39:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED          
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 1043

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1043                  
Memory Dump @0x000000000DD8A060

0000000000000000:   30000800 01000000 03000002 00030413 †0...............
0000000000000010:   842a2a2a 20526f77 2031202a 2a2a2a2a †„*** Row 1 *****
0000000000000020:   2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************
0000000000000030:   2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************
0000000000000040:   2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************

Good luck exploring SQL Server storage!