5 Feb 12

How to List Size of Tables

Even though disk space is cheap it continues to the be the first resource to run out on a server.  And once it runs out it’s a constant battle to keep the database size in check.

For sure there are things that can be done to keep database growth in control:

  • Archive.  I’ve seen databases where we have 6 years worth of data in it.  Yet our contract with our client only required 4 years.  That means we are storing 50 percent more data than we need to.  Why don’t we set up an automatic archival process?  Probably because we don’t have a framework that we can drop in place so we have to build it from scratch.  With all of the day to day operational demands who has the time to build archive systems?  That’s fodder for another post…
  • Sliding Window Partitions.  This is similar to archiving with a twist.  It requires us to have a fairly sophisticated partitioning process in place where when new data comes rolling in we automatically roll the oldest data out of the table.  Not only to maintain a consistent table size but in theory, that data that just “slid out of the table” is now going to archive storage (ie., slow and cheaper mediums).  There would be more sliding windows deployed if there was a wizard driven process to create the partitions and all of the supporting functions.  Since that is not the case, there will probably continue to be a bunch of opportunities for the consultant specializing in partitioning to speed up ETL and report queries.
  • Junk/temp tables.  We all do it so fess up.  We’re on the production server and we create a “temp” table, except it’s not in tempDB.  It’s a permanent table.  Temp tables are usually easy to spot.  The look like tmp_data_im_loading_toady, dgt_new_customer_fulfillments, temp_20120215, etc.  You need to be a little careful deleting these tables because I have seen ETL process that actually rely on temp tables… so they’re really not temp are they.  Identifying junk tables has saved us GIGs of space.
  • Too many indexes.  I’ve seen tables where the sum of the non-clustered indexes was three times the size of the data itself.  If you come across this, chances are there’s something wrong.  Not only are you wasting space to maintain these indexes, but ETL loads will need to update these indexes slowing down loads.  The optimizer for basic query selections will have a ton of indexes to consider (and probably never use) making query plan generation that much more complex and costly.

Today’s query will list an instances tables for all user databases.  Included in the result set are the size of the heap or clustered index (which is essentially the size of the data, since the index component of a clustered index is on the order of 1-2 percent of the index size).  It also lists the number and aggregate size of non-clustered indexes.

Using this data you will be able to review your table structures, identify junk tables, and identify tables that probably have unnecessary non-clustered indexes.  What you do with this information…  All depends if you are swamped with operational fires or can actually take the time to clean house.

I’m including the code in this post but will also make it available in the downloads section in the sidebar of this post.

List of Tables by Storage
EXEC sp_MSforeachdb
'    USE ?;
        
    IF db_id() > 4    
    BEGIN         
        WITH nci AS (    SELECT    i.[object_id]
                                , COUNT(i.[object_id]) AS [NumIndexes]
                                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) AS GBytes
                        FROM        sys.indexes i
                                INNER JOIN sys.partitions p
                                    ON    p.[object_id] = i.[object_id]
                                    AND    p.index_id = i.index_id
                                INNER JOIN sys.allocation_units au
                                    ON    au.container_id = p.partition_id
                        WHERE    i.index_id >= 2
                        AND        i.[object_id] > 100
                        GROUP BY i.[object_id] )

        SELECT    db_name() AS DBName
                , i.object_id
                , OBJECT_NAME(i.object_id) AS [TableName]
                , i.name AS [ClusteredIndexName]
                , i.index_id
                , i.type_desc AS index_type
                , SUM(p.[rows]) AS row_cnt
                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) AS CI_GBytes
                , nci.[NumIndexes]
                , nci.GBytes AS [NCI_IndexGBytes]
                , CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) + nci.GBytes AS [TotalTableSize]
        FROM        sys.indexes i
                INNER JOIN sys.partitions p
                    ON    p.[object_id] = i.[object_id]
                    AND    p.index_id = i.index_id
                INNER JOIN sys.allocation_units au
                    ON    au.container_id = p.partition_id
                INNER JOIN nci nci
                    ON    nci.[object_id] = i.[object_id]
        WHERE    i.index_id <= 1
        AND        i.[object_id] > 100
        GROUP BY i.[object_id]
                ,    i.name
                , i.index_id
                , i.type_desc
                , nci.NumIndexes
                , nci.GBytes
        ORDER BY CAST((SUM(au.total_pages) * (8.0 * 1024.0)) / 1000000000.0 AS NUMERIC(12, 4)) + nci.GBytes DESC
    END';

Stay curious; keep learning…

0 Comments
Filed in T-SQL
  Posted by
29 Jan 12

Size of Filegroups and Files

In recent posts I looked at queries that show us data at the instance, the database, backups and now we will look at filegroups and the individual physical files.

Too many times disk space is not an issue, it is THE issue.  This query will show several key attributes of our database at the file level including:

  • List of filegroups for a database
  • List of files that are members of the filegroup
  • The amount of space ALLOCATED for the data file
  • The amount of space USED/FREE for the data file
  • The amount of remaining FREE SPACE for the drive that the file is on

Too many times we have a drive that is running out of storage and need to move tables/indexes to other files.  This query is a great starting point for identifying which files are in trouble.  It also gives us a birds eye view of other drives that may be candidates to move crowded tables to another drive.

Filegroups and Files
–drop table #sfs
–drop table #fixed_drives
–drop table #output_table
–drop table #databases
–drop table #dbf
–drop table #fg

—————————————
– Save result set from showfilestats
—————————————
CREATE TABLE #sfs(    fileid            tinyint
                    , filegroupid    tinyint
                    , totalextents    int
                    , usedextents    int
                    , dbfilename    sysname
                    , physfile        varchar(255) );
    
——————————————–
– Save result set from sys.database_files
——————————————–
CREATE TABLE #dbf(    [file_id]                int
                    , file_guid                uniqueidentifier
                    , [type]                tinyint
                    , type_desc                nvarchar(60)
                    , data_space_id            int
                    , [name]                sysname
                    , physical_name            nvarchar(260)
                    , [state]                tinyint
                    , state_desc            nvarchar(60)
                    , size                    int
                    , max_size                int
                    , growth                int
                    , is_media_ro            bit
                    , is_ro                    bit
                    , is_sparse                bit
                    , is_percent_growth        bit
                    , is_name_reserved        bit
                    , create_lsn            numeric(25, 0)
                    , drop_lsn                numeric(25, 0)
                    , read_only_lsn            numeric(25, 0)
                    , read_write_lsn        numeric(25, 0)
                    , diff_base_lsn            numeric(25, 0)
                    , diff_base_guid        uniqueidentifier
                    , diff_base_time        datetime
                    , redo_start_lsn        numeric(25, 0)
                    , redo_start_fork_guid    uniqueidentifier
                    , redo_target_lsn        numeric(25, 0)
                    , redo_target_fork_guid    uniqueidentifier
                    , back_lsn                numeric(25, 0) );
                    
——————————————–
– Save result set from sys.filegroups select * from sys.filegroups
——————————————–
CREATE TABLE #fg(    [name]                sysname
                    , data_space_id        int
                    , [type]            char(2)
                    , type_desc            nvarchar(60)
                    , is_default        bit
                    , [filegroup_id]    uniqueidentifier
                    , log_filegroup_id    int
                    , is_read_only        bit );
                    
– Populate #disk_free_space with data
CREATE TABLE #fixed_drives(DriveLetter    char(1) NOT NULL
                            , FreeMB    int NOT NULL );

INSERT INTO #fixed_drives
    EXEC master..xp_fixeddrives;
    
CREATE TABLE #output_table(DatabaseName    sysname
                            , FG_Name        sysname
                            , GB_Allocated    numeric(8, 2)
                            , GB_Used        numeric(8, 2)
                            , GB_Available    numeric(8, 2)
                            , DBFilename    sysname
                            , PhysicalFile    sysname
                            , Free_GB_on_Drive    numeric(8, 2) );
                            
SELECT    name AS DBName
INTO    #databases
FROM    sys.databases
WHERE    database_id > 4
AND        state_desc = ‘ONLINE’;

DECLARE    @dbname    sysname;

SELECT    @dbname =(    SELECT    TOP (1) DBName FROM    #databases);
DELETE FROM #databases WHERE DBName = @dbname;

WHILE @dbname IS NOT NULL
BEGIN

    – Get the file group data
    INSERT INTO #sfs            
        EXEC(‘USE ‘+ @dbname + ‘; DBCC showfilestats;’);
        
    INSERT INTO #dbf
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.database_files;’);
        
    INSERT INTO #fg
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.filegroups;’);
        
    – Wrap it up!
    INSERT INTO #output_table    (DatabaseName
                                , FG_Name
                                , GB_Allocated
                                , GB_Used
                                , GB_Available
                                , DBFilename
                                , PhysicalFile
                                , Free_GB_on_Drive )
        SELECT    @dbname AS DATABASE_NAME
                , fg.name AS [File Group Name]
                , CAST(((sfs.totalextents * 64.0) / 1024000.0) AS numeric(8, 2)) AS GB_Allocated
                , CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
                , CAST((((sfs.totalextents - sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
                , sfs.dbfilename
                , sfs.physfile
                , CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
        FROM        #sfs sfs
                INNER JOIN #dbf dbf
                    ON    dbf.[file_id] = sfs.fileid
                INNER JOIN #fg fg
                    ON fg.data_space_id = sfs.filegroupid
                INNER JOIN #fixed_drives fd
                    ON    fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);
    
    SELECT    @dbname =(    SELECT    TOP (1) DBName FROM    #databases);
    IF @dbname IS NOT NULL
        DELETE FROM #databases WHERE DBName = @dbname;
        
    TRUNCATE TABLE #sfs;
    TRUNCATE TABLE #dbf;
    TRUNCATE TABLE #fg;
END

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , DatabaseName
        , FG_Name
        , GB_Allocated
        , GB_Used
        , GB_Available
        , DBFilename
        , PhysicalFile
        , Free_GB_on_Drive
FROM    #output_table
ORDER BY DatabaseName
        , FG_Name

 

I’ll add the code for this to the downloads on the right side of this post…

Hopefully you’ll find this query useful for getting a detailed picture of how your databases are physically laid out.  Without this info it’s impossible to figure out how to reconfigure / redistribute your data.

Stay curious; keep learning…

0 Comments
Filed in SysAdmin, T-SQL
  Posted by
Tagged
22 Jan 12

Database Backup Snapshot

 

Every Monday morning I always make it a point to run some basic checks on our production database servers.  Yes, there are alerts… but sometimes those alerts get over looked, don’t get delivered, etc.  I like to know implicitly that we are in decent shape…

In previous posts I showed how to look at the server at the instance level:

http://www.daveturpin.com/2011/04/dba-query-instance-information/

And at the database level:

http://www.daveturpin.com/2011/07/list-of-databases/

Today I will show how I take a look at the database backups… probably THE MOST IMPORTANT service a DBA provides to the organization.  Without good backups a loss of data is a real possibility… which would probably be followed up with a loss of a job…

Reviewing database backups can by tricky because we have 3 possible backup varieties to consider: FULL, DIFFERENTIAL and TRANSACTION LOGS.  FULL backups apply to all databases.  DIFFERENTIAL definitely do not apply to the system databases and may apply to user databases.  TRANSACTION log backups do not apply to databases in SIMPLE recovery mode.

To tame this beast, I wrote a script the includes the recovery model, the number of days since the last FULL, DIFF and TRN backups.  If the database is SIMPLE, the TRN backup info will be minus one (-1).

So here’s the query:

Last Backups
– Get the last FULL backup
– drop table #full
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'FULL' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #FULL
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'D'
WHERE    bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'D'
                                ORDER BY bus2.backup_set_id DESC)
UNION

– Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'FULL' AS [Backup Type]
        , 'No FULL Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'D'
WHERE    bus.database_name IS NULL;

—- Differential Backups —-
– Get the last FULL backup
– drop table #DIFF
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'DIFF' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #DIFF
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON bus.database_name = d.name
WHERE    bus.[type] = 'I'
AND        bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'I'
                                ORDER BY bus2.backup_set_id DESC)
                                
UNION

– Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'DIFF' AS [Backup Type]
        , 'No DIFF Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'I'
WHERE    bus.database_name IS NULL;

—- Log Backups —-
– drop table #LOG
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'LOG' AS [Backup Type]
        , Convert(varchar(12), bus.backup_finish_date, 101)    AS LastBackUpTaken
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
INTO #LOG
FROM        sys.databases d
        INNER JOIN msdb.dbo.backupset bus
            ON bus.database_name = d.name
WHERE    bus.[type] = 'L'
AND        d.recovery_model = 1
AND        bus.backup_set_id IN (    SELECT    TOP 1
                                        bus2.backup_set_id
                                FROM        msdb.dbo.backupset bus2
                                WHERE    bus2.database_name = d.name
                                AND        bus2.[type] = 'L'
                                ORDER BY bus2.backup_set_id DESC)
                                
UNION

– Identify any dbs that never had a backup taken.
SELECT    d.database_id
        , d.recovery_model AS recovery_model_id
        , bus.backup_set_id
        , d.Name as DatabaseName
        , 'LOG' AS [Backup Type]
        , 'No LOG Backups'
        , CAST(NULL AS numeric(8, 2)) AS [Backup Time(min)]
        , Convert(varchar(12), bus.[user_name], 101)    AS UserName
FROM        sys.databases d
        LEFT OUTER JOIN msdb.dbo.backupset bus
            ON    bus.database_name = d.name
            AND    bus.[type] = 'L'
WHERE    bus.database_name IS NULL
AND        d.recovery_model = 1;

– Get the backup run time:    
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #FULL t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;
            
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #DIFF t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;
            
UPDATE    t
SET        [Backup Time(min)] = (DATEDIFF(ms, bus.backup_start_date, bus.backup_finish_date) * 1.0) / (60 * 1000)
FROM        #LOG t
        INNER JOIN msdb.dbo.backupset bus
            ON bus.backup_set_id = t.backup_set_id;

– Pull it all together:
SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , f.database_id
        , f.DatabaseName
        , CASE f.recovery_model_id
            WHEN 1 THEN 'FULL'
            WHEN 2 THEN 'BULK'
            WHEN 3 THEN 'SIMPLE'
            ELSE 'Unknown'
          END AS [Recovery_Model]
         , f.backup_set_id AS [FULL_Backup_Set_ID]
         , f.LastBackUpTaken AS [Last_FULL_Backup_Date]
         , DATEDIFF(DAY,
                    CAST(    CASE f.LastBackUpTaken
                                WHEN 'No FULL Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE f.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last FULL]
         , f.[Backup Time(min)] AS [FULL_Backup_Time_min]
         , f.UserName AS [FULL_Backup_Operator]
         
         , d.backup_set_id AS [DIFF_Backup_Set_ID]
         , d.LastBackUpTaken AS [Last_DIFF_Backup_Date]
         ,  DATEDIFF(DAY,
                    CAST(    CASE d.LastBackUpTaken
                                WHEN 'No DIFF Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE d.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last DIFF]
         , d.[Backup Time(min)] AS [DIFF_Backup_Time_min]
         , d.UserName AS [DIFF_Backup_Operator]
         
         , l.backup_set_id AS [LOG_Backup_Set_ID]
         , l.LastBackUpTaken AS [Last_LOG_Backup_Date]
         ,  DATEDIFF(DAY,
                    CAST(    CASE l.LastBackUpTaken
                                WHEN 'No LOG Backups' THEN DATEADD(DAY, 1, CURRENT_TIMESTAMP)
                                ELSE l.LastBackUpTaken
                            END AS SMALLDATETIME),
                    CURRENT_TIMESTAMP) AS [#Days Since Last LOG]
         , l.[Backup Time(min)] AS [LOG_Backup_Time_min]
         , l.UserName AS [LOG_Backup_Operator]
FROM        #FULL f
        LEFT OUTER JOIN #DIFF d ON d.database_id = f.database_id
        LEFT OUTER JOIN #LOG l ON l.database_id = f.database_id
– Exclude tempdb
WHERE    f.database_id <> 2
ORDER BY f.database_id;

 

OK, it’s a little long… but worth the effort.  If you manage more than one database server, which most DBAs do, you can plug this query into a Central Management Server query window and get a snapshot of all of your database backups in one easy view.

I like to put the result set into an excel file and filter on the Recovery model to make sure my FULL recovery databases are running transaction log backups.  How many times have you answered the call to a developer or end user complaining about a full transaction log.  I’m happy to say that because of this simple review process, I never get the call for PRODUCTION trans log backup issues due to backups not running.  (I’m not as diligent with the DEV and TEST servers so yes, sometimes stuff does happen there).

Note: I did add the TSQL scripts for the INSTANCE, DATABASE and BACKUPs to the download section of this blog.  Apparently this code object is not very good at copying out code.

If you’ve been slack with reviewing your backups you might just find some nuggets of interest in the result set from this script…

Stay curious… keep learning…

0 Comments
Filed in T-SQL
  Posted by
27 Oct 11

SQL Server Backup Compression Default

In SQL Server 2008 Microsoft added the long overdue backup compression feature.  You may have noticed in the backup dialog box the pull down menu to use the DEFAULT SERVER SETTING for compression.

image

To determine what the DEFAULT value actually is:

image

Out of the box, SQL Server 2008 backup compression is turned OFF.  To avoid the need to select COMPRESSION every time a backup is taken (assuming that is your intent), you can enable backup compression at the server level:

Enable Backup Compresion:
  1. sp_configure ‘backup compression default’, 1
  2. reconfigure
  3. go

This will simply allow you to not have to worry about explicitly selecting the backup COMPRESSION option in the backup dialog box.

If all of your servers are in SQL Server’s new CENTRAL MANAGEMENT SERVER, you can enable backup compression on all of the servers by executing the above command ONE time.

Stay curious… keep learning…

0 Comments
Filed in SysAdmin
  Posted by
Tagged ,
24 Jul 11

Monitor Transaction Log Rollback

I recently had a situation where an index was being updated but the process hung in the middle of the job.  Apparently this isn’t all that far fetched since it appears it has happened to other folks out there (the internet is the best… I rarely have a NEW problem)…

After stopping the query it went into a rollback.  A rollback that wouldn’t end.

How long should the query be rolling back?  How can I tell if the rollback is actually doing something, or is this thing never going to finish?

It turns out you can monitor the bytes reserved in the transaction log during a rollback.  As long as the reserved bytes are getting smaller your transaction rollback is working.

In my case, the rollback was NOT working and the reserved bytes remained flat.  The workaround, unfortunately, was to restart the SQL Server service.

Here’s the simple query to test the reserved bytes:

    select      current_timestamp as tod,
                database_transaction_log_bytes_reserved,
                database_transaction_log_bytes_used
    from  sys.dm_tran_database_transactions
    where transaction_id > 1000
    and database_id = 7 – AdventureWorks
    – and transaction_id = whatever it is…

Just to prove the point, I’ll demonstrate how it works by running a transaction in AdventureWorks.

The first thing I’ll do is setup a query to collect the bytes reserved every 1 second.  The results will be saved to a temp table with the very unique name, #t.

Save Bytes Reservered
create table #t (    tod    datetime
                    , bytes_reserved    int
                    , bytes_used        int )
    
while 1=1
begin    
    insert into #t            
    select      current_timestamp as tod,
                database_transaction_log_bytes_reserved,
                database_transaction_log_bytes_used
    from  sys.dm_tran_database_transactions
    where transaction_id > 1000
    and database_id = 7 – AdventureWorks
    – and transaction_id = whatever it is…
    
    waitfor delay '00:00:01'
end

 

Now I’ll do a little update in AdventureWorks, then rollback the transaction.

Kick off Xaction and Rollback
begin tran

    – 16s
    update Sales.SalesOrderDetail
    set ModifiedDate = CURRENT_TIMESTAMP
    
rollback    – 2 s

 

The update took about 16s to run; followed by a few second pause before I ran the rollback, which took 2 seconds.

Here’s what the result set looks like:

image

 

As the update transaction is running we see the transaction log bytes reserved increasing.  Then there is a delay before I did the rollback… Finally the last two entries shows the reserved bytes decreasing.

Although this tool may not tell you how LONG the ROLLBACK will take place, it will at least tell you that it is in fact ROLLING back and is not “hung”.

You can narrow down the query a bit by adding the transaction ID to ensure you are monitoring the transaction you need to look at.

Stay curious… Keep learning…

0 Comments
Filed in T-SQL
  Posted by
Tagged
24 Jul 11

List of Databases

Back in April I posted a query that shows instance information.  This post is the next level down in the hierarchy that I collect on my database platforms each week…  The databases in an instance.

This query is certainly one of the more straight forward queries to capture database data.  It probably doesn’t really need to be executed / collected weekly, but I do it anyway to be consistent with my collection process.

If you plug this query into a SQL Server Central Management server you’ll have a snapshot of all your instances that are part of the CMS.

This query will return the database name, the overall size of the database including all data and log files, the create date, owner, compatibility level, online vs offline, and update stats info.

It’s interesting to find databases owned by staff that are no longer around, compatibility levels to old versions for no obvious reason, and create and update stats that are turned off.

Like they say on the History Channel’s Pawn Star show… you never know WHAT will be coming through that door.

So here’s the query:

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , d.database_id
        , d.[name]
        , CAST(SUM(mf.size * 8096.0 / 1000000000) AS NUMERIC(18, 2)) AS [db_size (G)]
        , d.recovery_model_desc AS [Recovery Model]
        , d.create_date
        , suser_sname(d.owner_sid) AS [Owner]
        , d.[compatibility_level]
        , d.state_desc AS [State]
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on
FROM        sys.databases d
        INNER JOIN sys.master_files mf
            ON    mf.database_id = d.database_id
WHERE    d.database_id > 4    — Exclude the system databases.
GROUP BY d.database_id
        , d.[name]
        , d.recovery_model_desc
        , d.create_date
        , d.owner_sid
        , d.[compatibility_level]
        , d.state_desc
        , d.is_auto_create_stats_on
        , d.is_auto_update_stats_on

My next post in this series will be on database backups, which is a much more interesting query AND really does need to be monitored weekly, minimum.

Stay curious… keep learning…

0 Comments
Filed in T-SQL
  Posted by
Tagged ,
17 Jul 11

How to Drop a TempDB Database File

Now The SQL Server tempDB is arguably the most important database in any installed instance.  A poorly configured tempdb will trump all the best efforts for design of good OLTP or datawarehouse systems.

The standard recommendation is to have one tempdb database file for each CPU.  It’s also generally recognized that this rule of thumb us just that, a guess.  For busy OLTP databases one tempdb for each cpu may make sense.  But not always…

Last week I signed up to do a simple change to a production servers’s tempdb config.  Specifically, drop 4 data files.  I’ve recently added tempdb data files to existing databases… how hard can it be to drop them.

It turns out that I was right and not so right.  My approach to deleting the tempdb data files was spot on; I just didn’t work quite the way I anticipated.

I this post I will run through a few scenarios of adding a tempdb data file and then attempting to drop the file.

Just to set the record… the tempdb file I am creating is not a fixed size and does allow for autogrowth.  That is NOT the point of this exercise.  Please visit other sites for info on configuring your tempdb for your application.

Let’s first add a tempdb file to our instance:

Add tempDB file:
ALTER DATABASE tempdb
    ADD FILE (
        NAME = tempdb2
        , FILENAME = N‘D:\tempdb\tempdb2.ndf’
        , SIZE = 512MB
        , FILEGROWTH = 50MB )

 

Now let’s remove the file:

Drop the tempdb file:
– Try to delete the file just added
ALTER DATABASE tempdb REMOVE FILE tempdb2

Since I’m running this test on a test server (translation, no one else is banging on it) the above code worked fine.  I got the following confirmation:

The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
The file ‘tempdb2′ has been removed.

The next scenario will add a new file to tempdb, write out temp table, then attempt to drop the file:

Drop file after temp table:
alter database tempdb
    add file (
        NAME = tempdb2
        , FILENAME = N‘D:\tempdb\tempdb2.ndf’
        , size = 512MB
        , filegrowth = 50MB )

– Use tempdb, before trying to remove the old table.
SELECT    *
INTO    #t
from    SYS.OBJECTS

– Try to delete the file just added
ALTER DATABASE tempdb REMOVE FILE tempdb2

– It still worked!
The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
The file ‘tempdb2′ has been removed.

I anticipated the above test to generate an error message telling me that the tempdb file could not be deleted because it was in use.  After all, that’s what I got on the production server…

Maybe the small temp table wasn’t enough to use the new file.

Let’s create a larger temp table and see what happens:

BIG Tempdb Table:
alter database tempdb
    add file (
        NAME = tempdb2
        , FILENAME = N‘D:\tempdb\tempdb2.ndf’
        , size = 512MB
        , filegrowth = 50MB )

–drop table #test
– Let’s put significantly more data in tempdb:
CREATE TABLE #test (    id    int identity (1, 1),
                        data char(8000) )

declare @i    int

set @i = 1

while @i <= 50000
begin
    insert into #test values (convert(char, @i))

    select @i = @i + 2
end

I have a rather lengthy query that I use to see how much space is in each database file.  I’ll post it here, but it really has more data/columns than is needed for this post:

Check TempDB Files
– Is there data in the second file of tempdb?
–drop table #sfs
–drop table #fixed_drives
–drop table #output_table
–drop table #databases
–drop table #dbf
–drop table #fg

—————————————
– Save result set from showfilestats
—————————————
CREATE TABLE #sfs (    fileid            tinyint
                    , filegroupid    tinyint
                    , totalextents    int
                    , usedextents    int
                    , dbfilename    sysname
                    , physfile        varchar(255) );
    
——————————————–
– Save result set from sys.database_files
——————————————–
CREATE TABLE #dbf (    [file_id]                int
                    , file_guid                uniqueidentifier
                    , [type]                tinyint
                    , type_desc                nvarchar(60)
                    , data_space_id            int
                    , [name]                sysname
                    , physical_name            nvarchar(260)
                    , [state]                tinyint
                    , state_desc            nvarchar(60)
                    , size                    int
                    , max_size                int
                    , growth                int
                    , is_media_ro            bit
                    , is_ro                    bit
                    , is_sparse                bit
                    , is_percent_growth        bit
                    , is_name_reserved        bit
                    , create_lsn            numeric(25, 0)
                    , drop_lsn                numeric(25, 0)
                    , read_only_lsn            numeric(25, 0)
                    , read_write_lsn        numeric(25, 0)
                    , diff_base_lsn            numeric(25, 0)
                    , diff_base_guid        uniqueidentifier
                    , diff_base_time        datetime
                    , redo_start_lsn        numeric(25, 0)
                    , redo_start_fork_guid    uniqueidentifier
                    , redo_target_lsn        numeric(25, 0)
                    , redo_target_fork_guid    uniqueidentifier
                    , back_lsn                numeric(25, 0) );
                    
——————————————–
– Save result set from sys.filegroups select * from sys.filegroups
——————————————–
CREATE TABLE #fg (    [name]                sysname
                    , data_space_id        int
                    , [type]            char(2)
                    , type_desc            nvarchar(60)
                    , is_default        bit
                    , [filegroup_id]    uniqueidentifier
                    , log_filegroup_id    int
                    , is_read_only        bit );
                    
– Populate #disk_free_space with data
    
CREATE TABLE #fixed_drives (DriveLetter    char(1) NOT NULL
                            , FreeMB    int NOT NULL );

INSERT INTO #fixed_drives
    EXEC master..xp_fixeddrives;
    
CREATE TABLE #output_table (DatabaseName    sysname
                            , FG_Name        sysname
                            , GB_Allocated    numeric(8, 2)
                            , GB_Used        numeric(8, 2)
                            , GB_Available    numeric(8, 2)
                            , DBFilename    sysname
                            , PhysicalFile    sysname
                            , Free_GB_on_Drive    numeric(8, 2) );
                            
SELECT    name AS DBName
INTO    #databases
FROM    sys.databases
WHERE    database_id <= 4
AND        state_desc = ‘ONLINE’;

DECLARE    @dbname    sysname;

SELECT    @dbname = (    SELECT    TOP (1) DBName FROM    #databases);
DELETE FROM #databases WHERE DBName = @dbname;

WHILE @dbname IS NOT NULL
BEGIN

    – Get the file group data
    INSERT INTO #sfs            
        EXEC(‘USE ‘+ @dbname + ‘; DBCC showfilestats;’);
        
    INSERT INTO #dbf
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.database_files;’);
        
    INSERT INTO #fg
        EXEC(‘USE ‘+ @dbname + ‘; SELECT * FROM sys.filegroups;’);
        
    – Wrap it up!
    INSERT INTO #output_table    (DatabaseName
                                , FG_Name
                                , GB_Allocated
                                , GB_Used
                                , GB_Available
                                , DBFilename
                                , PhysicalFile
                                , Free_GB_on_Drive )
        SELECT    @dbname AS DATABASE_NAME
                , fg.name AS [File Group Name]
                , CAST(((sfs.totalextents * 64.0) / 1024000.0) AS numeric(8, 2)) AS GB_Allocated
                , CAST(((sfs.usedextents * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Used
                , CAST((((sfs.totalextents - sfs.usedextents) * 64.0) / 1024000.0) AS NUMERIC(8, 2)) AS GB_Available
                , sfs.dbfilename
                , sfs.physfile
                , CAST((fd.FreeMB / 1000.0) AS NUMERIC(8, 2)) AS Free_GB_on_Drive
        FROM        #sfs sfs
                INNER JOIN #dbf dbf
                    ON    dbf.[file_id] = sfs.fileid
                INNER JOIN #fg fg
                    ON fg.data_space_id = sfs.filegroupid
                INNER JOIN #fixed_drives fd
                    ON    fd.DriveLetter = SUBSTRING(sfs.physfile, 1, 1);
    
    SELECT    @dbname = (    SELECT    TOP (1) DBName FROM    #databases);
    IF @dbname IS NOT NULL
        DELETE FROM #databases WHERE DBName = @dbname;
        
    TRUNCATE TABLE #sfs;
    TRUNCATE TABLE #dbf;
    TRUNCATE TABLE #fg;
END

SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
        , DatabaseName
        , FG_Name
        , GB_Allocated
        , GB_Used
        , GB_Available
        , DBFilename
        , PhysicalFile
        , Free_GB_on_Drive
FROM    #output_table
ORDER BY DatabaseName
        , FG_Name

 

OK, this time we have confirmed that we have data in tempdb.  Let’s see if we can drop it:

Drop Non-empty Tempdb file:
– Now try to remove it!
ALTER DATABASE tempdb REMOVE FILE tempdb2

– Cool, we have data in tempdb2!!!
The file “D:\tempdb\tempdb2.ndf” has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 1
The file ‘tempdb2′ cannot be removed because it is not empty.

 

This is the error I got with my production server.  It turns out, if you actually read the error message (which I can’t say I truly did) it does indicate that the system catalog has been updated BUT the change will not take affect until the service is restarted.

By simply restarting the SQL Server service the tempdb file(s) will be removed on startup.

I’ve seen some posts online that attempted to use DBCC to empty the tempdb data file.  For me it didn’t work.

Of course the above approach does require a restart of SQL Server.  Besides the obvious issue of the database going offline for 30 seconds or so (make sure no jobs are running) restarting the service will zero out all of the server counters.  This may be an issue if your using DMVs to monitor performance.

It’s been quite a while since I did a post… Hopefully this one is worthy of being remembered.

Stay curious… keep learning…

0 Comments
Filed in SysAdmin, T-SQL
  Posted by
Tagged
30 Apr 11

DBA Query: Instance Information

As part of my weekly routine I like to take a holistic view of the production servers I am responsible for.  After backups and maintenance plans, the next most important role of the DBA is to keep an eye on the overall health of the production databases.

I have several queries that I use to provide me with a summary view of the databases.

Today’s post will be a query that provides a nice view at the instance level.  Of course there are always more points to pull, but this is what I culled the query down to.  You may want to add more, or delete something.

Rather than running this query for every instance, all you need to do is set up your central management servers (providing you have at least one SQL2008 or later server).  This will allow you to run the query and get the result set for each server.  For more info on central management servers see BOL.

So here’s my query for instance reporting…  Enjoy.

Instance Properties
  1. DECLARE    @xp_msver table(    id                    int
  2.                             , name                sysname
  3.                             , internal_value    int
  4.                             , character_value    nvarchar(4000) );
  5.  
  6. INSERT INTO @xp_msver                    
  7.     EXEC    master.dbo.xp_msver ProcessorCount;
  8. INSERT INTO @xp_msver                    
  9.     EXEC    master.dbo.xp_msver PhysicalMemory;
  10.  
  11. SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
  12.         , SERVERPROPERTY (‘ServerName’) AS [ServerName]
  13.         , SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS [ComputerNamePhysicalNetBIOS]
  14.         , SERVERPROPERTY (‘MachineName’) AS [MachineName]
  15.         , SERVERPROPERTY (‘InstanceName’) AS [InstanceName]
  16.         , SERVERPROPERTY (‘Edition’) AS [SQL Server Edition]
  17.         , SERVERPROPERTY (‘ProductVersion’) AS [ProductVersion]
  18.         , SERVERPROPERTY (‘ProductLevel’) AS [ProductLevel]
  19.         , SERVERPROPERTY (‘IsClustered’) AS [IsClustered]
  20.         , SERVERPROPERTY (‘IsIntegratedSecurityOnly’) AS [IsIntegratedSecurityOnly]
  21.         , SERVERPROPERTY (‘IsSingleUser’) AS [IsSingleUser]
  22.         , SERVERPROPERTY (‘LicenseType’) AS [LicenseType]
  23.         , SERVERPROPERTY (‘NumLicenses’) AS [NumLicenses]
  24.         , SERVERPROPERTY (‘ProcessID’) AS [ProcessID]
  25.         , CAST(xp1.ProcessorCount AS int) AS ProcessorCount
  26.         , CAST(xp2.PhysicalMemory AS int) AS PhysicalMemory
  27.         , dt.DefaultTraceEnabled
  28.         –, df.DefaultTracePath
  29.         , rde.RemoteDACEnabled
  30.         , (    SELECT    create_date
  31.             FROM    sys.databases
  32.             WHERE    database_id = 2) AS SQLServerStartDate
  33.         , (    SELECT    DATEDIFF(day, create_date, current_timestamp)
  34.             FROM    sys.databases
  35.             WHERE    database_id = 2) As DaysSinceSQLServerStart
  36. FROM    (SELECT    character_value AS ProcessorCount FROM @xp_msver WHERE name = ‘ProcessorCount’) xp1
  37.         CROSS JOIN
  38.          (SELECT    internal_value AS PhysicalMemory FROM @xp_msver WHERE name = ‘PhysicalMemory’) xp2
  39.         CROSS JOIN
  40.          (SELECT value_in_use AS DefaultTraceEnabled FROM sys.configurations WHERE name = ‘default trace enabled’) dt
  41.         CROSS JOIN
  42.         (SELECT value_in_use AS RemoteDACEnabled FROM sys.configurations WHERE name = ‘remote admin connections’) rde
  43.         –CROSS JOIN
  44.         –(SELECT NULL AS DefaultTracePath) df
  45.         –(SELECT VALUE AS DefaultTracePath FROM fn_trace_getinfo(0) WHERE property = 2) df

Stay curious… keep learning.

0 Comments
Filed in SysAdmin, T-SQL
  Posted by
Tagged ,
25 Mar 11

Monitor Transaction Isolation Levels

As part of a performance improvement effort we decided to make sure our enterprise reporting tools are operating under the READ UNCOMMITTED transaction isolation level (ie., allow dirty reads).  While our reporting application experts were certain the tools were at the desired isolation level we decided to monitor the actual transactions just to be sure.

Monitoring the isolation seemed like a pretty straight forward, common task.  While most things I don’t know how to do off the top of my head are readily available through my preferred search engine, I had a tougher time finding an a clear description of how to monitor the isolation level using T-SQL.

There were lots of posts about how to SET the isolation level, how to get the current isolation level for the current session, and even how to get the default isolation level at the instance level.

Without elaborating further, here is how I did it, so I don’t ever have to search for it again…

Monitor Trans Iso Levels
SELECT    original_login_name
        , nt_user_name
        , CASE transaction_isolation_level
            WHEN 0 THEN ‘Unspecified’
            WHEN 1 THEN ‘ReadUncommitted’
            WHEN 2 THEN ‘ReadCommitted’
            WHEN 3 THEN ‘Repeatable’
            WHEN 4 THEN ‘Serializable’
            WHEN 5 THEN ‘Snapshot’
            ELSE ‘???’
        END AS  [TransactionIsolationLevel]
        , status
        , last_request_start_time
        , *
FROM    sys.dm_exec_sessions
WHERE    session_id > 50
order by 3;

Like most tasks in life, it’s simple once you know how.

Stay curious; keep learning…

0 Comments
Filed in SysAdmin, T-SQL
  Posted by
Tagged
15 Mar 11

An unexpected honor…

The purpose of this blog, for the most part, is to provide me with a paper trail of technical SQL Server topics I feel are worth remembering.  Things that are easier for me to lookup later on and read my own explanation, if and when I need to re-learn the topic.  A secondary benefit is the learning process is enhanced simply by documenting my thoughts.

Today’s topic is not focused on SQL Server, but it is one that I would like to remember, for a long time…

I’ve been teaching a 6 part course on SQL Server T-SQL querying for about 2 years.  My audience are internal new hires, who often have very technical formal educations but not necessarily in programming or SQL at all.  The concept of the class was borne out of my own selfish desire to have my colleagues better equipped to solve resolve their own data quality issues with SQL.  The alternative was I would get many of their questions fired in my direction.

I’ve always enjoyed helping new hires…  Often times they are fresh out of college and are about as enthusiastic of an audience as you could ever ask for.  My secret intention is to inspire them to take their careers higher and faster than I did myself.  For sure, with their elite degrees they are certainly well prepared, so this is an easy task for me.

My recent batch of “students” was a very bright group of young ladies, four in number… none of them from the US of A.  One is from Romania, two from India, one from China.  The courage it takes for someone in their 20’s, maybe 30’s (one of those questions I don’t dare ask) to leave home and go to a foreign country simply baffles me.

At the end of each course a certificate of completion is presented to each individual who satisfied all of the course requirements.  The certificate is signed by the most senior executive manager in our group and is made part of the employees permanent training record.

The last class is a little more casual for the students, since it is a demo that takes a slighter deeper look at execution plans.  It’s the only class that does not have a “homework” so everyone is a little lighter in spirit.

In this particular case, however, I decided to defer the certificate presentation to the students…  A group staff meeting was scheduled in two days and I opted to have the certificates presented in front of their peers and managers.

Instead of me giving them their certificate… they gave ME mine…

The image below does not do justice to the actual document.  The art work was all hand done, was on a parchment style paper, rolled up like a scroll, tied with a red ribbon.  Of course when they handed it to me I had no idea what it was.

Suffice it to say I was deeply moved… the spirit of appreciation this group of young ladies expressed far exceeded my wildest imagination.  Just when you think no one is listening or that your contribution is rote, something like this happens to wake you up. 

To all of my own teachers, mentors and professors of the past, those who sometimes pushed me but often pulled and carried me, thank you.

To these young ladies of my present, thank you!

The following image is an excerpt from an email that consisted of a scanned image of the certificate and their personal comments.  It was sent to my management team up to 3 levels up.  This was one reward I never expected or even had a hint could happen.

View album

Stay curious, keep learning…

Dave

0 Comments
Filed in Career Management
  Posted by
6 Mar 11

Maintaining Accurate SQL Server Statistics in Small Maintenance Windows

The single most important component of the SQL Server product is the query optimizer. The SQL Server storage engine utilizes a cost-based optimizer to determine optimal query plans. The job of the optimizer is to determine an optimal plan that will result in exceptional performance.

Note: An “optimal plan” is not necessarily the same as the lowest cost in terms of system resources. Because of the exponentially complex number of possible plan choices, the storage engine would have to determine every possible plan in order to determine which plan would run the fastest. The time to compute all of the possible plans would take more time than coming up with a pretty good plan and running it. In a sense, the query plans that SQL Server generates are actually heuristics; really good but not necessarily the absolute fastest plans.

The challenges of the optimizer are numerous. The number of possible combinations that the optimizer has to choose from is exponentially complex. As the number of tables in a single query grow, go grows the number of potential indexes, keys, constraints, etc.

For example, a simple query that has two tables linked together with a single INNER JOIN may present several options for the query optimizer, one of which is to choose the optimal PHYSICAL join operator:

  • HASH
  • MERGE
  • NESTED LOOPS

The 3 possible physical join types will take into consideration the relative number of rows between the two tables, if there is an index on one or both of the tables that will allow it to avoid a SORT before joining the tables, which index(s) can be used to return all of the needed columns without doing a bookmark (key) lookup using the clustered index, are there any foreign keys that can be used to guarantee the existence of a value in a column.

Statistics Overview

Every time new rows are inserted into a table or are updated, the profile of the data in the table changes. SQL Server relies on the data profiles as yet another input parameter to determining an optimal query plan. For example, if we have a table with 100,000 rows and one of the columns is the customer BIRTH DATE. The statistics on that table would group the 100,000 birthdates into a histogram (bar chart) with ranges or “bands” of birth dates. These bands of data can be used to determine the selectivity of a query…

If the nature of the data is such that most customers are in the 18-25 age group, the bands of data for those years (such as 15-20 has 35,000 records and 20-25 has 40,000 records) than 75 percent of the customers are in that age range.

If a query restricts the data being selected to all 22 year olds, the SQL engine will know that there are many 22 year old in the data and will probably choose to do a clustered index scan (read each record) rather than try to use an index that has poor selectivity. If the query is looking for 35 year olds, now the selectivity of the data might be better suited to utilize an index to identify the appropriate records.

There are two types of statistics maintained in tables in SQL server:

1. Column statistics

2. Index statistics

Each index has an associated statistics histogram that attempts to profile the data. The greater the selectivity of the data (few records) for a given predicate (WHERE birthdate_date BETWEEN ‘1/1/1985’ and ‘1/31/21986’) the more likely that index will improve the performance of the query (index seek). If the index selectivity is too low (many records qualify), doing a straight index scan will work just as fast.

If a column that does not have an index on it is referenced as part of a predicate (WHERE clause) SQL Server will compute statistics on that column on the fly. The presumption is that predicate will be reused in the future, and subsequent queries will run faster. Column statistics can be easily identified in SQL Server Management Studio because their name starts with _WA.

By default, any INSERTED or UPDATED records into a table will result in the statistics on the column or index to be auto-updated. The auto update functionality can be disabled but it is widely agreed as best practice to leave auto update enabled, which is the default.

Depending on the size of the table, the statistics on each column or index may not be based on every record in that table. For small tables (those that occupy less than 8 pages), all rows in the table are used to determine the statistics (FULLSCAN or 100 PERCENT). As the table size grows (typically more rows but large rows in terms of data type size also impacts the number of rows per database page), and more pages are required to be read in order to read all of the rows, smaller sample sizes are utilized.

For large tables that have many insert and update operations, the statistics will become skewed to the point where they may actually start providing BAD data, which is worse than no data. Query plans that are built on outdated statistics may run so poorly that they never finish.

Note: Please reference the Microsoft white paper on statistics for a true description of stats: Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

But between the time when the statistics are first initialized and the time the statistics are so far out of whack that the plan essentially fails, is a period where the performance of queries are increasingly becoming less efficient. These inefficiencies in query plan performance can result in progressively slower loads and reports.

It is for this reason that one of the universally agreed upon best practice is to update index and column statistics on a periodic basis to ensure accurate and efficient query plan generation. The better information we can give the optimizer, the better are our chances of having solid and consistent query.

Summary and related facts:

  • By default, statistics are maintained and auto updated on all indexes and any columns that are used as predicates in a query.
  • Due to time constraints, the larger the table, the smaller the sample set of the table is used to compute the statistics.
  • Large tables with many updates and inserts will have statistics degrade if quality over time.
  • In order to maintain good statistics SQL Server tables require regular maintenance to prevent the statistics from becoming skewed due to inadequate sample sizes.
  • SQL Server does not provide any mechanism to selectively update individual statistics. Statistics are updated at the table level. So if a table has 10 columns/indexes with statistics on them, if 3 are out of date there is no way to update only those 3. All 10 of the statistics will need to be refreshed.
  • For partitioned tables, there is no way in SQL Server to update the statistics at the partition level. Again, the statistics must be updated for the entire table. (This is widely regarded as a major weakness in Microsoft’s statistics solution. The irony is that partitioned tables tend to be the largest, and often times, only the most recent data changes. Once statistics are achieved on the oldest data, it usually does not change. Microsoft STILL has not announced if this issue is going to be resolved in Denali or not).
  • Index maintenance should include index REBUILDS which addresses internal and external fragmentation of the index. Anytime an index is rebuilt (usually as a part of a maintenance plan) the statistics on the LEADING COLUMN of the index is computed with a 100 percent sample rate. In a sense, re-computing the statistics on an index after an index REBUILD is doing the same work twice. However, since we cannot update statistics at the column level, it may be necessary to re-compute the statistics on a table after index maintenance in order to update other columns that were not part of an index rebuild.
  • The last date/time the statistic for each column/index is easily determined with the STATS_DATE T-SQL built-in function.

Statistics Updates in Maintenance Plans

Index and column statistics are normally updated as part of the recurring maintenance plan jobs. Tables that have a high percentage of their data changing (inserts and updates) will benefit the most from statistic updates.

Since statistics are updated at the table level, the time to compute the statistics are a function of the number of rows in the table AND the number of column/index statistics on the table. The largest tables, with the highest number of column statistics will take the longest amount of time in the ever decreasing maintenance windows to complete.

The other parameter that determines how fast the statistics can be computed on a table is the sample size used to compute the statistics. Smaller sample sizes will run faster, but higher sample sizes will arrive at statistically correct results.

For databases with large numbers of tables, indexes and column statistics, the time to re-calculate statistics can easily outstrip the amount of time of the maintenance window. In order to address this concern a statistics update procedure has been developed that will allow us to update column and index statistics within fairly narrow maintenance windows, while maintaining fairly high statistic sample rates.

As part of my maintenance plan I am running a TSQL script that will distribute the update of statistics over a multi-day period.

Here’s how it works…

  1. Instead of attempting to compute all of the statistics that need updating in a single session the column and index statistics will be spread out over several days. Initially, I have chosen four (4) days to distribute the statistic updates, but this could easily be setup as an input parameter.
  2. In order to evenly distribute the work load over all four days it may be tempting to use the number of rows in a table to estimate the relative length of time to compute the statistics for that table. However, a truer estimate of the time to compute the statistics is actually the number of rows * the number of column/index statistics. I called this factor the effective statistics count. For example, a table with 100000 rows and one index will have an effective stats count = 100000. A different table with 100000 rows but 75 columns with statistics will have a 7,500,000 effective count and will take 75 times longer to compute than the first table.
  3. In order to break the statistics computations over 4 days a modified NTILE algorithm was used. Basically, all tables that are candidates for statistic updates where ranked by effective statistics count ascending. Then each table was sequentially assigned an NTILE bucket (0-3). The net results are 4 buckets of tables that have a similar aggregated effective stats count.
  4. Tables are selected as candidates for statistic updates based on the following rules:

    a.  Any table that has one or more statistics older than 7 days will be updated

    b. If the table has a disabled clustered index, the table is excluded from the list

    c. Only user tables are included. Any system tables are excluded.

V.    In order to balance compute time vs. quality of statistics, the following rules are in place to gradually reduce the sample rate for very large tables:

VI.    The results of the statistic updates are logged in [master].dbo.dba_UpdateStatsLog.

By maintaining current and statistically accurate column and index statistics, we will have one less factor to eliminate when trouble shooting query performance issues.

Update Statistics
  1. USE master;
  2. GO
  3.  
  4.  
  5. If Not Exists(Select [object_id] From sys.tables Where name = N'dba_UpdateStatsLog')
  6. Begin
  7.     – Drop Table dbo.dba_UpdateStatsLog
  8.     Create Table dbo.dba_UpdateStatsLog ( UpdateStats_id        int identity(1,1)   NOT Null
  9.                                         , databaseID            int                 NOT Null
  10.                                         , databaseName            nvarchar(128)       NOT Null
  11.                                         , NTILE_id                tinyint                NOT NULL
  12.                                         , TableName                nvarchar(128)       NOT Null
  13.                                         , objectID                int                 NOT Null
  14.                                         , LastStatsUpdate        smalldatetime        NULL
  15.                                         , NumStats                smallint            NOT NULL
  16.                                         , [RowCount]            int                    NOT NULL
  17.                                         , EffectiveStatCount    bigint                NOT NULL
  18.                                         , dateTimeStart            smalldatetime       NULL
  19.                                         , durationSeconds        int                 NULL
  20.                                         , sampleRate            tinyint                NULL
  21.                                         CONSTRAINT PK_UpdateStatsLog
  22.                                             PRIMARY KEY CLUSTERED (UpdateStats_id)    );
  23.  
  24.     Print 'dba_UpdateStatsLog Table Created';
  25. End
  26.  
  27. IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_UpdateStats'), N'IsProcedure') = 1
  28. Begin
  29.     DROP PROCEDURE dbo.dba_UpdateStats;
  30.     PRINT 'Procedure dba_UpdateStats dropped';
  31. End;
  32. GO
  33.  
  34. –USE master;
  35. –GO
  36. –sp_msforeachdb 'EXEC dbo.dba_load_UpdateStatsLog
  37. –                    @database            = ''?''
  38. –                    , @@num_days_old    = 7;'
  39.  
  40. –drop procedure dba_load_UpdateStatsLog
  41. CREATE PROCEDURE dbo.dba_load_UpdateStatsLog @databaseName    nvarchar(128)
  42.                                             , @num_days_old     tinyint = 7
  43.  
  44. AS
  45.  
  46. – @databaseName – Name of DB to have statistics updated.
  47. – @num_days_old – Ignore any tables that had indexes rebuilt within the last
  48. –                    @num_days_old.
  49. –
  50. – USAGE: 1. Populate the dba_UpdateStats with tables that need to have status updated:
  51. –                EXEC dbo.dba_load_UpdateStatsLog 'usta', 7
  52. –          2. Call procedure that updates the stats:
  53. –                EXEC dbo.dba_updateStats
  54. –
  55. – SELECT * FROM dba_UpdateStatsLog where datetimestart is null ORDER BY ntile_id
  56. – select ntile_id, sum(effectivestatcount) FROM dba_UpdateStatsLog where datetimestart is null group by ntile_id
  57.  
  58. – Credits and References:
  59. – Concepts such as the use of the log table in the master db and general architecture are
  60. – borrowed from Michelle Ullman's defrag procedure.
  61. –        http://sqlserverpedia.com/wiki/Index_Maintenance
  62.  
  63. – Reference Paul Randall's comments on the interaction of index rebuilds and statistic updates:
  64. –        http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx#id0230050
  65. –
  66.  
  67. ————————————————————-
  68. ————————————————————-
  69. – Revision History:
  70. –
  71. – 20110224    D Turpin        Original creation.
  72. – 20110225    D Turpin        Limit to tables with DBO schema.
  73.  
  74. DECLARE @sql_string    nvarchar(max);
  75.  
  76. – Check to see if there are still stats to be updated from the previous cycle:
  77. IF (SELECT    count(*)
  78.     FROM    master.dbo.dba_UpdateStatsLog
  79.     WHERE    databaseName = @databaseName
  80.     AND        dateTimeStart IS NULL) > 0
  81.     
  82.     OR
  83.     
  84.     @databaseName IS NULL
  85. BEGIN
  86.     PRINT 'Please update the exisiting stats in master.dbo.dba_UpdateStatsLog'
  87.     RETURN
  88. END
  89.  
  90. IF @databaseName IS NULL
  91.  
  92.     OR
  93.     
  94.     (    SELECT    COUNT(*)
  95.         FROM    sys.databases
  96.         WHERE    name = ISNULL(@databaseName, '') ) = 0
  97. BEGIN
  98.     PRINT 'Invalid database name submitted: ' + @databaseName
  99.     RETURN
  100. END        
  101.  
  102. – Identify which statistics need to be updated:
  103. – Some ground rules:    Only look at user tables
  104. –                        Get the row count based on the clustered index or heap
  105. –                        Ignore tables with disabled clustered indexes
  106. –                        Since stats updates do not work at the partition level (as of 2008R2)
  107. –                        sum the rows to get the row count.  Otherwise our counts will be off
  108. –                        plus we will update the stats on the big table n-partitions times.
  109. SELECT    @sql_string = 'USE ' + @databaseName + ';
  110.     SELECT    OBJECT_NAME(t.[object_id]) AS TableName
  111.             , t.[object_id]
  112.             , MIN(STATS_DATE(t.[object_id], s.stats_id)) AS LastStatsUpdate
  113.             , COUNT(s.stats_id) AS [NumTableStats]
  114.             , SUM(p.[rows]) AS [RowCount]
  115.             , SUM(p.[rows]) * COUNT(s.stats_id) AS [EffectiveStatCount]
  116.     INTO    ##raw_stats
  117.     FROM        sys.stats AS s
  118.             INNER JOIN sys.tables AS t
  119.                 ON    t.[object_id] = s.[object_id]
  120.                 AND    t.type = ''U''
  121.                 AND    t.schema_id = 1
  122.             INNER JOIN sys.indexes AS i
  123.                 ON    i.[object_id] = s.[object_id]
  124.                 AND    i.index_id <= 1
  125.                 AND    i.is_disabled = 0
  126.             INNER JOIN sys.partitions AS p
  127.                 ON    p.[object_id] = i.[object_id]
  128.                 AND    p.[index_id] = i.[index_id]
  129.                 AND    p.[rows] > 0
  130.     WHERE    STATS_DATE(t.[object_id], s.stats_id) < DATEADD([day], -' +
  131.                                                             RTRIM(LTRIM(CONVERT(char, @num_days_old))) + ',
  132.                                                             CURRENT_TIMESTAMP)
  133.     GROUP BY t.[object_id]
  134.             , p.[rows];'
  135.             
  136. EXEC SP_EXECUTESQL @sql_string;
  137.  
  138. – Spread the workload over 4 days (Reference MOD operator):
  139. INSERT INTO [master].dbo.dba_UpdateStatsLog (    
  140.                             databaseID
  141.                             , databaseName
  142.                             , NTILE_id
  143.                             , TableName
  144.                             , objectID
  145.                             , LastStatsUpdate
  146.                             , NumStats
  147.                             , [RowCount]
  148.                             , EffectiveStatCount )
  149.     SELECT    db_id(@databaseName)
  150.             , @databaseName
  151.             , (ROW_NUMBER() OVER(ORDER BY EffectiveStatCount)) % 4 AS NTILE_Modified
  152.             , TableName
  153.             , [object_id]
  154.             , LastStatsUpdate
  155.             , NumTableStats
  156.             , [RowCount]
  157.             , EffectiveStatCount
  158.     FROM    ##raw_stats
  159.     ORDER BY 1 ASC;
  160.  
  161. DROP TABLE ##raw_stats;
  162.  
  163. RETURN;
  164.  
  165. ——————————————
  166. ——————————————
  167. ——————————————
  168.  
  169. IF    (    SELECT    count(*)
  170.         FROM    [master].dbo.dba_UpdateStatsLog
  171.         WHERE    dateTimeStart IS NULL ) > 0
  172.     EXEC sp_msforeachdb 'EXEC dbo.dba_UpdateStats
  173.                             @database = ''?'';'
  174.  
  175. ————————————————————-
  176. ————————————————————-
  177. ————————————————————-
  178. CREATE PROCEDURE dbo.dba_UpdateStats @databaseName    nvarchar(128)
  179.                                     –, @sample_rate    tinyint = 100
  180.  
  181. AS
  182.  
  183. – @databaseName – Name of DB to have statistics updated.
  184. – @sample_rate – Percent of the table to check to compute stats.
  185. –
  186. – USAGE: 1. Call procedure that updates the stats:
  187. –                EXEC dbo.dba_updateStats 'USTA'    –, 5
  188. –
  189. ————————————————————-
  190. ————————————————————-
  191. – Revision History:
  192. –
  193. – 20110224    D Turpin        Original creation.
  194. – 20110226    D Turpin        Handle tables with spaces in the name.
  195. –                            Verify the table has not been dropped before updating the statistics.
  196. –
  197. –drop procedure dba_UpdateStats
  198. –
  199. – The EffectiveStatCount = #Rows in the Table * #Statistics on the table.  The
  200. – concept is large tables statistic updates can explode if they have many columns
  201. – that have statistics on them.
  202. –
  203. – This routine uses a sliding scale to prevent huge tables from monopolizing system
  204. – resources.
  205. –
  206.  
  207. DECLARE    @ntile_id                tinyint
  208.         , @sql_string            nvarchar(max)
  209.         , @UpdateStats_id        int
  210.         , @ObjectID                int
  211.         , @TableName            nvarchar(128)
  212.         , @start_time            datetime
  213.         , @EffectiveStatCount    bigint
  214.         , @sample_rate            tinyint;
  215.  
  216. – Get the lowest tile for this db:
  217. SELECT    @ntile_id = MIN(ntile_id)
  218. FROM        [master].dbo.dba_UpdateStatsLog
  219. WHERE    databasename = @databaseName
  220. AND        datetimeStart IS NULL;
  221.  
  222. IF @ntile_id IS NULL
  223. BEGIN
  224.     PRINT 'No Statistics to be updated.  Please run dbo.dba_load_UpdateStatsLog';
  225.     RETURN;
  226. END
  227.  
  228. DECLARE stats CURSOR FOR
  229.     SELECT    UpdateStats_id
  230.             , ObjectID
  231.             , TableName
  232.             , EffectiveStatCount
  233.     FROM    [master].dbo.dba_UpdateStatsLog
  234.     WHERE    datetimeStart IS NULL
  235.     AND        NTILE_id = @ntile_id;
  236.     
  237. OPEN stats;
  238. FETCH NEXT FROM stats INTO @UpdateStats_id, @ObjectID, @TableName, @EffectiveStatCount;
  239.  
  240. WHILE @@fetch_status <> -1
  241. BEGIN
  242.  
  243.     SELECT    @sample_rate =
  244.         CASE WHEN @EffectiveStatCount > 10000000000                        THEN 50    – In PERCENT
  245.              WHEN @EffectiveStatCount BETWEEN 750000000 AND 10000000000 THEN 60
  246.              WHEN @EffectiveStatCount BETWEEN 500000000 AND  7500000000 THEN 70
  247.              WHEN @EffectiveStatCount BETWEEN 250000000 AND  5000000000 THEN 80
  248.              WHEN @EffectiveStatCount BETWEEN  50000000 AND  2500000000 THEN 90
  249.              ELSE 100
  250.         END
  251.  
  252.     SELECT @sql_string = N'USE ' + @databaseName + ';
  253.         IF EXISTS (    SELECT    name
  254.                     FROM    sys.tables
  255.                     WHERE    [Object_ID] = ' + CONVERT(nchar, @ObjectID) + '
  256.                     AND        schema_id = 1)
  257.             UPDATE STATISTICS [' + @TableName + N'] WITH SAMPLE ' +
  258.                 LTRIM(RTRIM(CONVERT(nchar, @sample_rate))) + ' PERCENT';
  259.         
  260.     SELECT @start_time = CURRENT_TIMESTAMP;
  261.     
  262.     EXEC sp_executesql @sql_string;
  263.         
  264.     UPDATE    [master].dbo.dba_UpdateStatsLog
  265.     SET        dateTimeStart = @start_time
  266.             , durationSeconds = DATEDIFF(second, COALESCE(@start_time, current_timestamp), current_timestamp)
  267.             , samplerate = @sample_rate
  268.     WHERE    UpdateStats_id = @UpdateStats_id;
  269.     
  270.     PRINT @sql_string;
  271.         
  272.     FETCH NEXT FROM stats INTO @UpdateStats_id, @ObjectID, @TableName, @EffectiveStatCount;
  273.         
  274. END;
  275.  
  276. CLOSE stats;
  277. DEALLOCATE stats;
  278.  
  279. RETURN;

2 Comments
Filed in SysAdmin
  Posted by
Tagged ,
12 Jan 11

Identify All Computed Columns

Use the following query to identify all computed columns in a database:

 

List all computed columns
  1. SELECT    object_name(object_id) AS [Table Name]
  2.         , [name] AS [Column Name]
  3. FROM        sys.columns
  4. WHERE    is_computed = 1;

0 Comments
Filed in T-SQL
  Posted by
28 Nov 10

Optimizing Clustered Indexes for Data Warehouse Staging Tables

Most of my database experience over the past 15 years has been in data warehousing.  During this time the confusion about what to pick for table primary keys and more specifically, the clustered index, has either been hotly contested or, more typically, flat out ignored.

By “ignored” I don’t mean the  primary key doesn’t exist.  It’s more that little if any thought goes into it.  It’s “how we do it”.  In most cases the primary key is simply a surrogate column, which is a fancy way of saying it is an IDENTITY column that has absolutely no bearing on the data.

One of class of tables in a data warehouse is the ubiquitous “staging” table.  These tables hold the data from the originating  data source before any transformations are performed.  The only additional data in the staging tables is the surrogate column (something_id) which is almost always the primary key, and a column to indicate the date the data was loaded (load_date).

Other than the primary key, staging tables do not generally have any other indexes on them.  Once the data is loaded to the target table(s) the data in these tables is simply there as a means to check the correctness of the target load.

If a data warehouse is loaded daily, for example, we will be loading NEW records with an INSERT/SELECT.  Existing records will need to be UPDATEd, based on whatever the unique combination of attributes apply.  Regardless,  INSERTs and UPDATEs will be driven by the most current data in the staging table, more  specifically, the rows with the most current load_date.

In this posting I will look at four (4) different clustered index scenarios for a staging table.  Each of the four scenarios will have the exact same data.  The four scenarios include:

Stage Table Name Primary Key Description
1. STG_Customer_IDENTITY Primary key, clustered, on the surrogate key (IDENTITY column)
2. STG_Customer_IDENTITY_Load_Date Primary key, clustered, on the primary key and the load_date.
3.  STG_Customer_Load_Date_IDENTITY Same as #II, except the load_date will be the first column in the index with the surrogate key second.
4.  STG_Customer_Load_Date A clustered index is on load_date but no primary key constraint exists.

Continue Reading »

0 Comments
Filed in T-SQL
  Posted by
Tagged , , , , , , , , ,
23 Nov 10

Manage File Space using DBCC SHOWFILESTATS

I recently had to allocate some new database files for an existing production database.  It can be confusing between File Groups, Files and the space available in each  group / file.

The confusion is eliminated when running DBCC SHOWFILESTATS.  When concerned about available storage space left at the individual file level you’ll want to look at two columns:

  • Total Extents
  • Used Extents

Remember, an extent is 8, 8k (8096) pages, or 64k each. 

To determine how much space is left in a data file:

Free Space = (Total Extents – Used Extents ) * (8 * 8096).

Note: For large db files you may experience an arithmetic overflow.  To avoid this divide the Total and Used extents by a million to get the FREE SPACE in Gbytes.

DBCC SHOWFILESTATS

image

Take some of the mystery out of your physical database layouts with this  simple to use and understand DBCC command.

Stay curious; keep learning…

Dave

0 Comments
Filed in T-SQL
  Posted by
22 Nov 10

Mixing INNER JOINS with OUTER JOINS

Last month I posted on how the location of the predicate can change the optimizer’s resulting query plan.  The post was inspired by an article by Itzik Ben-gan in SQL Server Magazine.  Another key take away from the article highlights the optimizer’s response to having an INNER JOIN that follows and OUTER JOIN.

In this post I will show how the query plan is not the intended plan and recap why this is the  case.  I know I have personally seen this happen in production code many times over the years and have to admit I never took the time till now to really investigate how the SQL engine handles these queries.

Query 1: All INNER JOINS

I’ll start with the most straight forward and least confusing query that joins 3 tables, Contact, Order Header (master) and Order Detail.

All INNER JOINs
  1. SELECT    TOP 50
  2.         c.FirstName
  3.         , c.LastName
  4.         , c.EmailAddress
  5.         , oh.SalesOrderID
  6.         , od.OrderQty
  7. FROM        Person.Contact c
  8.         INNER JOIN Sales.SalesOrderHeader oh
  9.             ON    oh.ContactID = c.ContactID
  10.         INNER JOIN Sales.SalesOrderDetail od
  11.             ON    od.SalesOrderID = oh.SalesOrderID;

 

This basic query will produce the following query plan.  The important part is to note the two Nested Loops Joins for the two join operators in the query.

image

 

Query 2 is a Bug: OUTER / INNER JOIN

Now let’s tweak our query a little bit.  In this case, we want to list contact information even if the contact never  placed an order.  Seems simple enough; we just change the join to the ORDER table to an OUTER JOIN.

List Contacts Even If No Order
  1. SELECT    TOP 50
  2.         c.FirstName
  3.         , c.LastName
  4.         , c.EmailAddress
  5.         , oh.SalesOrderID
  6.         , od.OrderQty
  7. FROM        Person.Contact c
  8.         LEFT OUTER JOIN Sales.SalesOrderHeader oh
  9.             ON    oh.ContactID = c.ContactID
  10.         INNER JOIN Sales.SalesOrderDetail od
  11.             ON    od.SalesOrderID = oh.SalesOrderID;

 

Here’s the query plan for this query:

image

But wait!  Something is wrong.  Our query has an OUTER JOIN in it but the query plan has two INNER JOINs.  In fact, this query plan is identical to the query that has only INNER JOINs.

For this query, the OUTER JOIN followed by the INNER JOIN are incompatible.  Basically the query optimizer has a couple of choices it can use to handle this query.  It can either enforce the OUTER JOIN and treat the INNER JOIN like an OUTER JOIN or it can treat the OUTER JOIN like an INNER JOIN.  We can see from the query plan above the optimizer converts the OUTER JOIN to an INNER JOIN.

Bug Override #1: Two OUTER JOINS

Fortunately there are at least a couple of ways to overcome this anomaly.   The first approach is to explicitly declare the INNER JOIN as an OUTER JOIN.

Two OUTER JOINS
  1. SELECT    TOP 50
  2.         c.FirstName
  3.         , c.LastName
  4.         , c.EmailAddress
  5.         , oh.SalesOrderID
  6.         , od.OrderQty
  7. FROM        Person.Contact c
  8.         LEFT OUTER JOIN Sales.SalesOrderHeader oh
  9.             ON    oh.ContactID = c.ContactID
  10.         LEFT OUTER JOIN Sales.SalesOrderDetail od
  11.             ON    od.SalesOrderID = oh.SalesOrderID;

The following is the query plan produced by this query.

image

But there’s something still not quite right here.  The OUTER JOIN connecting the order  DETAIL record with the order HEADER isn’t exactly what we have in mind.  This could result in the contact information and the SalesOrderID being returned with a NULL OrderQty.  In the real world the MASTER and DETAIL records will always be connected, so adding this logic to satisfy the OUTER JOIN of the CONTACT with the ORDER records is a kluge.  If there are any orders that do not have DETAIL records they will be displayed by this query.

Bug Override #2: Separate Table Operators

In our final query we finally get what we are  really looking for.  A loosely coupled JOIN between CONTACTS and ORDERS but tightly coupled ORDER master and detail records.  We can accomplish this by making the JOIN between the order HEADER and DETAIL  tables treated separately relative to the JOIN with the CONTACT table.  This is the equivalent  to the difference between:

A = B * C + D and A = B * (C + D).

Here’s the query:

Rank JOIN Precedence
  1. SELECT    TOP 50
  2.         c.FirstName
  3.         , c.LastName
  4.         , c.EmailAddress
  5.         , oh.SalesOrderID
  6.         , od.OrderQty
  7. FROM        Person.Contact c
  8.         LEFT OUTER JOIN
  9.             (Sales.SalesOrderHeader oh
  10.                 INNER JOIN Sales.SalesOrderDetail od
  11.                     ON    od.SalesOrderID = oh.SalesOrderID)
  12.             ON    oh.ContactID = c.ContactID;

 

The query plan FINALLY  gives us what we are really looking for, an INNER JOIN and an OUTER JOIN. 

image

 

Conclusion:

Mixing INNER and OUTER joins can result in some unintended and unexpected results.  Checking the query plans can reveal nuances of the query that are contrary to the intended result set.  Specifically, an INNER JOIN followed by an INNER JOIN effectively converts the OUTER JOIN into an INNER JOIN.  A closer solution where two OUTER JOINS are  used may work but it may also generate some unintended records if the data is not squeaky clean.

The  ultimate solution is to compartmentalize the ORDER header and detail JOINs by incorporating parentheses in the JOIN operators.  This attention to detail will produce the exact intended result sets every time.

Stay curious; keep learning…

Dave

0 Comments
Filed in T-SQL
  Posted by
25 Oct 10

Equality Filters: ON vs. WHERE

It’s been a while since I actually saw this topic “in the wild” but we all know we see it all of the time.  It’s been several months but there was a point where I actually identified this kind of bug.

In the September 2010 issue of SQL Server magazine Itzik Ben-Gan does a nice job (as always) of clarifying the use of predicate filters in the ON clause vs. the WHERE clause.

I teach a class to beginner SQL users so knowing the fine points of these type of topics helps answer the unexpected questions that students ask.  It’s also REALLY helpful to know these points when writing production code.  Being unaware can be dangerous (ignorance is not always acceptable).

So let’s pose the topic as a question: Should a predicate EQUALITY FILTER be in the ON clause or should it be in the WHERE clause?

Of course, as is usually the case with T-SQL, the answer is it depends…

Let’s start with a simple pair of queries that join two tables with an INNER JOIN.  The first query has the EQUALITY filter in the WHERE clause the second has the filter in the ON clause:

USE adventureworks;
GO

SELECT    c.customerid
        , oh.SalesOrderID
        , oh.OrderDate
FROM        Sales.Customer c
        INNER JOIN Sales.SalesOrderHeader oh
            ON    oh.customerid = c.customerid
WHERE    oh.OrderDate = '7/31/2004';

SELECT    c.customerid
        , oh.SalesOrderID
        , oh.OrderDate
FROM        Sales.Customer c
        INNER JOIN Sales.SalesOrderHeader oh
            ON    oh.customerid = c.customerid
            AND    oh.OrderDate = '7/31/2004';

My standard response to the question is “it’s a matter of style”.  Sometimes that advices is not true.

In this case it is true.  Both of these queries are equivalent.  We can validate that by looking at the execution plans.

image

Based on the execution plans, it turns out we were correct, placing the equality filter in the ON clause vs. the WHERE clause is a matter of style.  My personal style is to tend to place the filter in the WHERE clause (which is probably a throwback to my ANSI 89 coding days).  It turns out this is not the best choice in all cases.

If we modify our test queries to use an outer join we create a different scenario… the queries are no longer equivalent.

SELECT    c.customerid
        , oh.SalesOrderID
        , oh.OrderDate
FROM        Sales.Customer c
        LEFT OUTER JOIN Sales.SalesOrderHeader oh
            ON    oh.customerid = c.customerid
WHERE    oh.OrderDate = '7/31/2004';

SELECT    c.customerid
        , oh.SalesOrderID
        , oh.OrderDate
FROM        Sales.Customer c
        LEFT OUTER JOIN Sales.SalesOrderHeader oh
            ON    oh.customerid = c.customerid
            AND    oh.OrderDate = '7/31/2004';

If we run the two queries we get 40 rows in the first query and 19845 rows in the second query.  Obviously these queries are not equivalent at all.

Taking a looking at the query plans…

image

From this we can see that not only are the plans strikingly different, but the first query is the same plan as the INNER JOINs above.  In essence, the first query gets converted into an INNER JOIN by the optimizer.

Why is this?  OUTER JOINS are characterized by one (LEFT and RIGHT JOINS) or both (FULL JOIN) tables being preserved.  Without the WHERE filter the LEFT side of the join is preserved; in this case the Sales.Customer.  The intended result set of this query SHOULD include ALL customers, regardless if they had an order on 7/31/2004 or not.  If they do NOT have an order than the order information would be NULL.  As written, the optimizer would create the result set with ALL customers and their order information.  The final step of the query execution will be to FILTER out all members of the result set where the ORDERDATE = ‘7/31/2004’.

The optimizer recognizes that the least expansive plan this scenario is with an INNER JOIN so that’s exactly how it puts together the plan.

The second OUTER JOIN query has the filter on the ORDER DATE on the RIGHT side of the LEFT JOIN.  By doing this the optimizer knows that all customers will be represented in the final result set but the ORDER information (RIGHT side of the JOIN) will only be populated if the EQUALITY filter is satisfied.

Conclusions:

  • For INNER JOINS is doesn’t matter where the EQUALITY filter is located.  The query plans and result sets will be the same.
  • For OUTER JOINs, placing the equality filter in the WHERE clause causes the query plan to be converted an INNER JOIN.  Probably not the intended behavior…
  • For OUTER JOINS, placing the equality filter in the non-preserved side of the OUTER JOIN will give the intended results.

If you don’t want to remember how this works, as a matter of style, ALWAYS PLACE YOUR EQUALITY FILTERS IN THE ON CLAUSE. (Which means I need to adjust my default style!)

This post will help me personally remember this important but subtle point.  As long as we maintain the concept of the PRESERVED side of the OUTER JOIN we don’t have to memorize this; we can understand it.

Please reference Itzik’s article in SQL SERVER Magazine for the inspiration behind this post.

As always, Stay Curious… Keep Learning…

Dave

0 Comments
Filed in T-SQL
  Posted by
Tagged , , ,
19 Sep 10

Move Tables to New Filegroup

About a year ago I showed how to move data in a table to a new partition by modifying the clustered index.  Since the clustered index contains the data for the table, moving the clustered index is equivalent to moving the table.

This post is a simpler example of using the clustered index to move the data.  We recently had a need at the office to add more disk space to an existing database.  Rather than increasing the size of the existing filegroups we created one or more new filegroups.

The following script outlines how to do it.

1. Create db on PRIMARY (default) filegroup with a separate log file.

2. Create a secondary filegroup (logical file for db).

3. Add a physical db file to the secondary filegroup

4. Create a table with clustered index constraint on the PRIMARY filegroup and put some data in it.

5. Show the table is in the PRIMARY filegroup.

6. Drop the clustered index constraint and move it to the SECONDARY filegroup.

7. Show the table is now in the secondary filegroup.

CREATE DATABASE test_clustered_index
ON ( name = test1_dat
    , filename = 'C:\Documents and Settings\turpind\My Documents\sql_files\tci.mdf'
    , size = 10
    , maxsize = 50
    , filegrowth = 15% )
LOG ON (name = test1_log
        , filename = 'C:\Documents and Settings\turpind\My Documents\sql_files\tci.ldf'
        , size = 5
        , maxsize = 50
        , filegrowth = 10% ) ;

-- Add a second file group
ALTER DATABASE test_clustered_index
    ADD FILEGROUP SecondFG;

-- Add a new file
ALTER DATABASE test_clustered_index
    ADD FILE ( name = test2_dat
    , filename = 'C:\Documents and Settings\turpind\My Documents\sql_files\tci2.ndf'
    , size = 10
    , maxsize = 50
    , filegrowth = 15% )
TO FILEGROUP SecondFG;

use test_clustered_index
go

CREATE TABLE table1 (    id    int    identity (1, 1) not null
                        , datavalue    char(2000)
                        CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED (ID)
                            ON [PRIMARY] );

INSERT INTO table1     VALUES (replicate('a', 2000));
INSERT INTO table1     VALUES (replicate('b', 2000));

-- so what does our database look like
sp_helpdb test_clustered_index

-- So what is on the  file groups?
SELECT    DISTINCT(object_name(id))
        , filegroup_name(groupid)
FROM    sysindexes
WHERE    object_name(id) = 'table1';

-- ok, now move the table to the secondary filegroup
ALTER TABLE table1
    DROP CONSTRAINT [PK_table1]
        WITH (MOVE TO SecondFG);

-- Now what filegroup is the data on?
SELECT    DISTINCT(object_name(id))
        , filegroup_name(groupid)
FROM    sysindexes
WHERE    object_name(id) = 'table1';

The secret to success of this script is the ALTER TABLE / DROP CONSTRAINT…

Stay curious…  Keep learning…

Dave

0 Comments
Filed in SysAdmin, T-SQL
  Posted by
Tagged ,
18 Sep 10

SQL Server and Muscle Memory

If you learned to play golf by taking lessons the first time the golf pro sets you up to take your first swing it feels awkward.  After a while, if you listened to your instructor, your swing becomes more natural, you can stop thinking about every aspect of the swing, you can grip it and rip it, and the ball’s flight is usually on target.

If you learned to play golf by hacking at the driving range without any instruction you are enforcing the “bad practice makes a bad shot” rule.  After a while you figure out that you’re self study efforts aren’t paying off and so now you go to a golf instructor with all of your bad swing habits deeply engrained.

Maybe you don’t play golf so you can’t relate to a golf swing.

Fold your hands together as if you’re going to pray.  Don’t think about it, just do it.

Now take note… which thumb is on top of the other thumb, your RIGHT or your LEFT?  Whatever it is, change the way you interlock your fingers such that the OTHER thumb is now on top.

For starters you really have to think about it.  The ACT of folding your hands “the other way” takes a conscious effort.  How does it feel?  It’s awkward… it doesn’t feel right, and you instinctively want to fold your hands your “natural” way.

Both of these examples are examples of what kinesiologists call motor engrams.  Motor engrams are learned patterns of muscle movement.  Since the brain is responsible for all muscle movement, both conscious movement patterns such as picking up a pencil, and unconscious movement patterns such as the beating of the heart, motor engrams are ultimately all about training the brain to move the body.

If you do it often enough, you can learn new motor engrams.  In fact, researchers have determined that it takes 200-300 repetitions of a complex motor pattern, such as a golf swing, to make the movement pattern “effortless”.  Unfortunately for the self taught golfer, it takes 3000-5000 repetitions of a movement pattern to “unlearn” an existing motor engram.  That explains why it’s more difficult to develop a new golf swing.

So what does this have to do with SQL Server?

Every day I sit down to my computer and type in my password.  I’ll bet I type my password no less than 10 times a day.  Every time I change my password I have to go through that awkward period where I want to enter my last password and the new password doesn’t feel right yet.

How do you go about picking your passwords?  A combo of your dog’s and cat’s names?  A favorite movie or celebrity?  The day of the week?  Your birth date?

All those things you already know and that’s what makes them attractive for your password.

Why not take a different approach to password selection?  Why not pick your password as something you want to learn/remember?  Recently, I’ve been picking passwords that are based on DMV and DMFs.  There are a ton of them and I have problems learning them because they can tend to be rather long and frankly, not intuitive.

Does sys.dm_io_virtual_file_stats really flow off of your finger tips?  How about sys.dm_exec_sessions?  For me they do now.  Why?  There was a period of time when they were part of my passwords (you’ll need to throw some case, numbers and special characters in there to make them more hardened).

Every time I enter my password I am training my brain to move my fingers in a pattern that will make me a better SQL developer.  Imagine being a great developer without having to think about it?

Another side benefit…  Changing my password it’s no longer a drudgery.  I usually have a SQL keyword already lined up for me to learn.

Make your passwords a learning experience and develop the motor engrams to give you the SQL Server razor’s edge.

Stay curious… Keep learning…

Dave

0 Comments
Filed in Career Management, Tips and Tricks
  Posted by
22 Aug 10

Add Default Constraint to an Existing Column

This post is going to be short and sweet for two reasons:

1.  It is a short subject and

2.  I’m learning the Dvorak keyboard…

I recently needed to add a default constraint to an existing column.  It’s not something I have to do often so when I do do it I need to look it up.  The website Google sends me to has variations for several platforms but the Microsoft version isn’t working for me.

So here’s one I can reference any time I need to…

-- SQL 2005+
ALTER TABLE f_response
  ADD CONSTRAINT new_contact_flag_def
     DEFAULT 0
       FOR new_contact_flag;

This Dvorak is going to take some serious practice.

Stay curious… keep learning…

Dave

0 Comments
Filed in T-SQL
  Posted by
Tagged
28 Jul 10

Location of SQL Server Default Trace

This is one of those posts for myself.  Sort of like a self-note.

When things go wrong, especially unexpectedly, sometimes the only clue is the SQL Server default trace.  Where is it?

SELECT * FROM ::fn_trace_getinfo(0)

Now you know…  But do you have permissions to get to the file?  That’s another issue.

For a more in-depth post on this topic see Adam Haines’ post.

Stay curious… Keep learning…

Dave

0 Comments
Filed in T-SQL
  Posted by
Tagged ,

« Older Posts

Dave Turpin, SQL Server Practitioner is powered by WordPress using theme Tribune