Tables and Files – What’s the Connection?

What is the relationship between a table and the files that contain it?  This isn’t as easy a question as you might think because there is not a direct relationship between a logical object like a table and a physical object like a file.  For a small database like AdventureWorks (< 200 MB) all you need is a single data file to hold all your tables.  As things start to get larger and more complex you may add files as a way of increasing performance.  For example if you have a single table that is large and heavily queried you may want to put it on a faster (and probably more expensive) drive.  You may also have non-clustered indexes that cover your query that would benefit from being separated.  If terms like non-clustered index and covering query are unfamiliar you might benefit from this excellent article by Joe Webb.

So, how do you control table and index placement?  You do it by using filegroups.  Every relational index (clustered or non-clustered) is assigned to a single filegroup that is composed of one or more files.  The relationship looks like this:

In the picture above the Table01 is actually the clustered index and Table01/Index01 is a non-clustered index.  Note that a file can be in exactly one filegroup, but the components of the table can be located and managed through the use of different filegroups.
Here is a script you can use to explore these relationships:

–Create a test DB.  Does the DB exist?  Drop if it does.
            Use tempdb
            If Exists (Select name from sys.databases Where name = ‘TabFile’)
                                    Drop Database TabFile

–Create DB with two filegroups.  You will need a “C:\temp”
–folder to use the script as written.
            Create Database TabFile On
                        Primary                                                                                                –FileGroup
                                    ( Name = N’TabFileF01′,
                                                Filename = N’C:\Temp\TabFileF01.mdf’),
                         Filegroup FG02                                                                               –FileGroup
                                    ( Name = N’TabFileF02′,                                                   –File
                                                Filename = N’C:\Temp\TabFileF02.ndf’ ),
                                    ( Name = N’TabFileF03′,                                                   –File
                                                Filename = N’C:\Temp\TabFileF03.ndf’)
                         Log ON                                                                                                –Log
                                    ( Name = N’TabFile_log’,
                                    Filename = N’C:\Temp\TabFile_log.ldf’)

–Create table with two indexes
            Use TabFile
            Create Table dbo.Table_1
                        (Col1 int Constraint PK_Table_1 Primary Key Clustered Not Null Identity (1, 1),
                        Col2 int Not Null)  On [Primary]
            Create Nonclustered index IX02_Table_1 ON dbo.Table_1
                        (Col1     ) On FG02
            Create Table dbo.Table_2
                        (Col1 int Constraint PK_Table_2 Primary Key Clustered Not Null Identity (1, 1),
                        Col2 int Not Null)  On FG02

At this point you can take a look at the various structures using either the Object Explorer in SSMS or TSQL.  I prefer TSQL because it is repeatable and far more flexible.  The following script shows the relationship between tables, filegroups, and files.

–View tables and files
      Use TabFile
            Select Distinct
                                       As TableName
                        ,                         As IndexName
                        ,I.type_desc               As IndexType
                        ,I.index_id                  As IndexID
                        ,                     As FileGroupName
                        ,                        As FileLogicalName
                        ,F.physical_name    As FilePhysicalName
                        sys.indexes                As I
                        Inner Join
                        sys.filegroups            As FG
                                    On I.data_space_id = FG.data_space_id
                        Inner Join
                        sys.master_files       As F
                                    On FG.data_space_id = F.data_space_id
                                    And F.database_id = DB_ID()
                        Inner Join
                        sys.all_objects          As O
                                    On I.[object_id] = O.[object_id]
                        I.data_space_id = FG.data_space_id
                        And O.[type] = ‘U’
            Order By

I recommend you play with this script on the sample DB above but also on of your own DBs.  Not only will it familiarize you with the various catalog views it will also point out how your data is arranged and may help you in managing it even better.

Ad Hoc Automation On TSQL Tuesday


Ad hoc Automation – isn’t that an oxymoron?  Isn’t it hard to automate something?  That answer depends on what you mean by automation.  If you want to build SSIS packages and SQL Agent jobs to comb your network and return all kinds of information about the servers and instances and track changes over time then you’re not going to knock that out before your second cup of coffee.  But suppose you just need to know what the SQL Server version is for each instance you own.  Or what if you are tired of cleaning AdventureWorks tables out of master and you want to set everybody’s default database to tempdb?  These tasks are a slice of pastry using the multi-server query feature of SQL Server’s Central Management Servers (CMS).

I’m not going to go over the details of how to set up a CMS  as Kimberly Tripp has already done a great job.  Instead I’d like to focus on some of the things you can do with them to automate repetitive tasks.  

SQL Server CMS allows you to interact with a group of servers much as you would with a single server.  The grouping of servers is entirely up to you.  You can have as many groups as you wish and you can configure them as you please.  This is important because, with a little thought you can slice and dice your environment by version (2K, 2005, 2008), by environment (Dev, Test, Prod), by function (Log Shipping, Replication, Clustered) and so on.  This allows you to customize your code and multiply the power of your CMS.

For example, suppose you have a server discovery query that returns SQL server metadata for an instance like this:

Wouldn’t it be handy to be able to generate that same information for every server in your enterprise in a single, Excel ready, recordset!

At one client site we decided to make every login use tempdb for their default DB.  Connecting to each of the 100+ instances and doing this manually didn’t appeal to me so I wrote and tested a TSQL script to do it on a single server, connected to every server at once, and made the change in about 2 minutes.  Notice I mentioned that I TESTED the script.  Remember, as Uncle Ben reminds us “with great power comes great responsibility.”   

I believe SQL Server’s CMS is a very useful but under-appreciated feature  limited only by your creativity.

I Have HOW MANY filegroups!?!

For small DBs one or maybe two data files (.mdf/.ndf) works fine.  With larger DBs though there is an opportunity to gain performance by the judicious use of additional file groups and files.   Unfortunately some folks go a little crazy and you can end up with more files than you can shake a dead cat at (my apologies to cat lovers).

Here’s a quick script to give you the lay of the land regarding file groups and their files.


                                                             As FGName

            ,Count(                                                 As FileCount

            ,Convert (Integer, Avg((F.size*8096.0)/(POWER(1024.0,2))))

                                                                                    As AvgFileSize_MB


            <DBName,sysname,MyDB>.sys.filegroups         As FG

            Inner Join

            sys.master_files                                              As F

                        On FG.data_space_id = F.data_space_id


            F.database_id = DB_ID(‘<DBName,sysname,MyDB>’)

Group By



            COUNT( > 1

Order By


Actually I like cats, I just can’t eat a whole one by myself.

How Bad Are My Statistics?

Here’s a quick query that tells you how many rows have been changed (table & index) since the last statistics update.

Select     OBJECT_NAME(            As TableName

    ,Sum(Convert(BigInt,I.rowmodctr))    As RowsModSinceStats


From     sys.sysindexes                As I


    (    Select     OBJECT_NAME(PS.object_id)    As TableName

            ,SUM(PS.row_count)            As RowCnt

        From     sys.dm_db_partition_stats        As PS


            sys.indexes                As I

                ON I.object_id
= PS.object_id

                AND I.index_id = PS.index_id

        Where I.type_desc IN

            AND I.object_id
> 100


By PS.object_id)    As C

= C.TableName            

> 0



By TableName

    –RowsModSinceStats Desc

Server Discovery

Need a quick and easy way to gather basic information about the SQL Server instance you are on?  Keep this script in your SSMS Template Explorer and the information is only a double-click away.  This script works for SQL Server 2005 and SQL 2008 only because it uses the DMV sys.dm_os_sys_info.  If you are on SQL Server 2000 just highlight the code down to the comment and execute.

    @@ServerName                    AS ServerName
    ,Case Left(Convert(Varchar(128),SERVERPROPERTY(‘productversion’)),2)
        When ‘8.’ Then ‘SQL 2000’
        When ‘9.’ Then ‘SQL 2005′
        When ’10’ Then ‘SQL 2008’
        Else ‘Unknown’
        End                                As SQLVersion
    ,SERVERPROPERTY(‘productlevel’)        As ServicePackLevel
    ,SERVERPROPERTY(‘productversion’)        As BuildNumber
    ,SERVERPROPERTY(‘edition’)            As Edition
    ,SERVERPROPERTY(‘LicenseType’)        As LicenseType
    ,SERVERPROPERTY(‘NumLicenses’)        As NumLicenses   
            WHEN ‘Windows NT 5.0’ THEN ‘Windows 2000’
            WHEN ‘Windows NT 5.1’ THEN ‘Windows XP’
            WHEN ‘Windows NT 5.2’ THEN ‘Windows Server 2003 or Windows XP (64-bit)’
            WHEN ‘Windows NT 6.0’ THEN ‘Windows Server 2008’
            WHEN ‘Windows NT 6.1’ THEN ‘Windows Server 2008 R2 or Windows 7’
            ELSE ‘Unknown’
        END                            AS WindowsVersion
    ,CASE WHEN SERVERPROPERTY(‘IsClustered’) = 1
            THEN ‘Yes’
            ELSE ‘No’
        END AS IsClustered
–For SQL Server 2K run to here
    ,cpu_count                            AS LogicalCPUs
    ,cpu_count / hyperthread_ratio            AS PhysicalCPUs
    ,max_workers_count                    AS Threads
    ,physical_memory_in_bytes/1048576        AS PhysicalMemory_MB

Here is an example of the result set on SQL Server 2008:


Philergia –

Philos (Love)  +   Ergos (Work)  =  Diligence

Not a popular virtue in our day but one needed for success.

“Diligent hands will rule but laziness ends in slave labor.”   –Proverbs 12:24

Spelunking for SQL Server Using PowerShell

In a new environment I find I waste a lot of time trying to figure out what everybody already knows. For example, what SQL Server instances are running on a given machine? Is Reporting Services running on this server?

Here’s a handy little script you can paste into a PowerShell window and see what’s on any computer. With a little tinkering you can get it to work on a list of servers — but that’s for another day. Here’s the script:

get-service -computername DWHSQL1`

-displayname “*sql*” `

sort-object -property DisplayName `

format-table -auto

And the results:

Status Name DisplayName
Stopped TSSQLPublisher$Default DM TS SQL Publisher [Default]
Stopped TSSQLListener$Default DM TS SQL TCP/IP Listener Service [Default]
Stopped MSSQLServerADHelper SQL Server Active Directory Helper
Stopped SQLAgent$DWHSQL2 SQL Server Agent (DWHSQL2)
Running SQLBrowser SQL Server Browser
Stopped msftesql$DWHSQL2 SQL Server FullText Search (DWHSQL2)

As you can see, there are two instances on the server but only one is running, the default instance, DWHSQL1.

If you need to look at another server simply change the computername. You can modify the filtered column (displayname) to suit your needs as well.

You could get the same information by remoting in to the box and looking at services, but this is quicker and easier, especially when you are dealing with several servers.