Often when dealing with different filegroups or even different files in SQL Server 2005, I end up wanting to know where certain objects are logically and physically located.  Here’s a query that will give that information:

select
                'Object Name' = so.name,
                'Index Name' = si.name,
                'Type Description' = si.type_desc,
                'FileGroup Name' = sfg.Name,
                'System FileName' = sdf.Name
from
                sys.indexes si
                join sys.objects so on si.object_id = so.object_id
                join sys.schemas ss on so.schema_id = ss.schema_id
                join sys.database_files sdf on sdf.data_space_id = si.data_space_id
                join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
where
                so.type = 'U'
                and so.Name not in ('dtproperties')
order by
                3 desc, 4, 1, 2

So I’ve been running a trace on some SQL 2005 servers for awhile now and I’m starting to analyze them.  One of the things I look at is statement top 10 duration.  It was interesting to see all 10 entries to on every day I monitored to be:

TextData – exec sp_readrequest @receive_timeout=600000
ApplicationName – DatabaseMail90 – Id<2224>
Duraction – 609325251

The Id varied and the duration went up and down a little but that seemed interesting to me.  I initially thought I had a Database mail problem and went to investigate.  Nothing seemed out of place.  So I went to BOL and looked up sp_readrequest only to find out it is an undocumented sp.

In fact doing a google search only comes back with 14 entries and only 1 really gives decent information which you can see here.  But basically it said to ignore the entries as it’s not doing any Reads, Writes, or CPUs as it is essentially doing a WAITFOR.  Evidently this is due to the fact that Database Mail utilizes Service Broker to operate.

While I understand and don’t have a problem with things happening behind the scenes that should be ignored, I do think it’s poor that these things we’re supposed to ignore are undocumented anywhere.  In effect, how do we know it’s performing "by design" and should be ignored if we don’t have any information about it?

One of the things I’ve been doing lately for an upgrade from SQL 2000 to SQL 2005 is restructuring databases, adding filegroups and files to split out the data and non clustered indexes, leaving only the system tables in the primary filegroup. 

This is actually fairly simple as we’re just dropping the clustered indexes and recreating them in the new filegroup which moves the data.  However we have some tables that don’t have clustered indexes (i.e., HEAP tables) so I wrote a script that moves the HEAP tables into another filegroup.  First it checks for an identity column and if one exists, I create a clustered index on the identity column for the new filegroup, then remove the clustered index.  If an identity column doesn’t exist, I create one with a clustered index on the new filegroup then drop the index and the column.  Fairly simple.

On another note if anyone knows an easy way on how to move text, ntext data between to a different filegroup, let me know.

-- Aaron Lowe
-- 8/24/2007

SET NOCOUNT ON
Print 'Starting'
DECLARE @SchemaName nvarchar(128),
        @TableName nvarchar(128),
        @SQLcmd nvarchar (1024),
        @ColumnName nvarchar(128),
        @FullQualTable nvarchar(261),
        @FileGroup nvarchar(128)

SELECT @FileGroup = DB_NAME() + '_DATA'

DECLARE HeapTables_CRSR Cursor FOR
    select ss.name, so.name
    from
        sys.indexes si
        join sys.objects so on si.object_id = so.object_id
        join sys.schemas ss on so.schema_id = ss.schema_id
        join sys.database_files sdf on sdf.data_space_id = si.data_space_id
        join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
    where
        si.type = 0
        and so.type = 'U'
        and sfg.name = 'PRIMARY'
    order by
        so.name

OPEN HeapTables_CRSR

FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FullQualTable = '[' + @SchemaName + '].[' + @TableName + ']'
        Print 'Currently working on: ' + @FullQualTable
        IF EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(@FullQualTable)
             and is_identity = 1)
            BEGIN
                SELECT @ColumnName = name from sys.columns WHERE OBJECT_ID =
                    OBJECT_ID(@FullQualTable) and is_identity = 1
                SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON
                [' + @SchemaName + '].[' + @TableName + '] ([' +
                @ColumnName + '] ASC) ON [' + @FileGroup + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
                @TableName + '];'
                exec sp_executesql @SQLCmd
            END
        ELSE
            BEGIN
                SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName
                + '] ADD [TempIdent] int IDENTITY (1,1);'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON [' +
                @SchemaName + '].[' + @TableName +
                '] ([TempIdent] ASC) ON [' + @FileGroup + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
                @TableName + '];'
                exec sp_executesql @SQLCmd
                SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName +
                '] DROP COLUMN [TempIdent];'
                exec sp_executesql @SQLCmd
            END
        FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
    END
CLOSE HeapTables_CRSR
DEALLOCATE HeapTables_CRSR
Print 'Done'