DBA Quiz – 2 weeks left

If you missed my question in DBA Quiz about storage and locating records in database on beyondrelational.com, there are 2 more weeks left. There are only 4 answers so far – I don’t believe it’s that difficult.

My question at DBA Quiz

I was a little shocked when I got mail from Jacob Sebastian, who is a man behind beyondrelational.com, asking me to take part in DBA Quiz 2011 as an expert. I was flattered by the invitation, since I don’t consider myself an expert in SQL Server, so I tried hard to come up with something interesting.

At first I thought about index maintenance-related stuff (remember my posts about indexes?) but then I thought it’s already been explored so well since them fragmentation and missing index DMVs are with us since SQL Server 2005, so I started looking somewhere else. After two days of intensive thinking and reading I got idea – it will be storage-related. The only thing was to provide the example and the longest part was to get SQL Server 2008 R2 installed on my old laptop.

So there it is – first a little story, then a question itself. Imagine you’re a stubborn (or maybe creative ) DBA that does not take for granted all what you’re told. In the beginning of your career, you read that storage-wise you can get as deep as a filegroup when it comes to tables – you can only assign it to filegroup or partition. Now you want to show that it’s possible to find a data in a file.

You have a big database, with 3 filegroups, each of them consisting of 3 files. You create table t1 on first filegroup with two columns int and varchar(100) and t2 located on second filegroup which has only two columns of type varchar(20) and varchar(50). Both tables don’t have indexes. You insert into first table two records (1, ‘aaa’) and (666, ’666′), and insert into second table also two records (‘abcabc’, ‘abcdefghi’) and (‘defdef’, ‘qwerty’). What steps will you take to check in which file and on which position in this file record (‘defdef’, ‘qwerty’) resides?

As an answer, two things are required:

1. File name and offset where the requested record is located.
2. A procedure how it was obtained (stopping SQL Server and searching contents of files with text/hex editor will not be counted as solution).

Here’s the script to create required database and tables (you might have to adjust file paths):

CREATE DATABASE [TableLocateTest] ON  PRIMARY 
( NAME = N'TLT1', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT1.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [FG1] 
( NAME = N'TLT1_1', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT1_1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT1_2', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT1_2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT1_3', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT1_3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2] 
( NAME = N'TLT2_1', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT2_1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT2_2', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT2_2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT2_3', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT2_3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [FG3] 
( NAME = N'TLT3_1', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT3_1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT3_2', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT3_2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 
( NAME = N'TLT3_3', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT3_3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TLT_log', FILENAME = N'G:\Microsoft SQL Server 2008 R2\MSSQL10_50.SS2K8R2I1\MSSQL\DATA\TLT_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TableLocateTest] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [TableLocateTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [TableLocateTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [TableLocateTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [TableLocateTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [TableLocateTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [TableLocateTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [TableLocateTest] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [TableLocateTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [TableLocateTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [TableLocateTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [TableLocateTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [TableLocateTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [TableLocateTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [TableLocateTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [TableLocateTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [TableLocateTest] SET  DISABLE_BROKER 
GO
ALTER DATABASE [TableLocateTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [TableLocateTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [TableLocateTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [TableLocateTest] SET  READ_WRITE 
GO
ALTER DATABASE [TableLocateTest] SET RECOVERY FULL 
GO
ALTER DATABASE [TableLocateTest] SET  MULTI_USER 
GO
ALTER DATABASE [TableLocateTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TableLocateTest] MODIFY FILEGROUP [FG1] DEFAULT
GO
USE [TableLocateTest]
GO
CREATE TABLE dbo.t1
    (
    a int NULL,
    b varchar(100) NULL
    )  ON FG1
GO
CREATE TABLE dbo.t2
    (
    a varchar(20) NULL,
    b varchar(50) NULL
    )  ON FG2
GO
insert into dbo.t1 (a, b) values(1, 'aaa'), (666, '666')
insert into dbo.t2 (a, b) values ('abcabc', 'abcdefghi'), ('defdef', 'qwerty')
go
checkpoint
go

Enjoy! If you want to submit an answer, just visit question page at beyondrelational.com and drop a comment. The submissions are allowed until 15th October 2011.