A Faster Alternative To sp_helpdb

Sometimes we find things that are hidden that are very good  . . . Easter eggs in video games can be fun, the unexpected $20 bill in the dryer or even the pot of gold at the end of the rainbow. And then there are the times that we find something hidden that isn't as good as we would like. This is where the hidden processing in the sp_helpdb system stored procedure lands.
 
On the surface sp_helpdb looks great, and for the most part it is. It returns some great information with one line of code. However, when looking at the actual execution plan, this is where it starts to get ugly.
 
Let's first take a look at the data sp_helpdb does return. It returns some pretty useful information. Such as database name when it was created and owner. For me, one of the most important data points it returns is the database size. Although you don't see it in the image below, sp_helpdb also returns the compatibility level.
 
A Faster Alternative to sp_helpdb
 
All columns return a single data point except for the status column. This column returns a number of important data points. The text below is an example of what you might find in this column.
 
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=904, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
 
I was curious how long it would take to run this process. To get the duration of this process I ran the query below. The reason for the temporary table is because of the GO just before the EXEC sp_helpdb.
  1. CREATE TABLE #variables  
  2. (  
  3.    StartDate DateTime   
  4. )  
  5. GO  
  6. INSERT INTO #variables  
  7. SELECT GETDATE();  
  8.   
  9. GO  
  10. EXEC sp_helpdb  
  11.   
  12. DECLARE @t2 DATETIME;  
  13. SET @t2 = GETDATE();  
  14. DECLARE @t1 DATETIME;  
  15.   
  16. SELECT @t1 = StartDate FROM #variables  
  17.   
  18. SELECT DATEDIFF(MILLISECOND,@t1,@t2) AS 'TotalTime';  
  19.   
  20. DROP TABLE #variables  
When I run this on my computer, it usually takes between 500 and 1000 MS. More on this later.
 
Now let's take a look what is happening behind the scenes with sp_helpdb. The first step is to populate a temporary table, #spdbdesc with database name, owner, when it was created and compatibility level. The code for the first step is below.
  1. if not exists (select * from master.dbo.sysdatabases  
  2. where (@dbname is null or name = @dbname))  
  3. insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)  
  4. select nameisnull(suser_sname(sid),'~~UNKNOWN~~'), convert(nvarchar(11), crdate),  
  5. dbid, cmptlevel from master.dbo.sysdatabases  
  6. where (@dbname is null or name = @dbname)  
  7. fetch ms_crs_c1 into @name  
Then you will see a bunch of statements similar to below. This step calculates the size of the database and completes an update of the temporary table.
  1. update #spdbdesc  
  2. /*  
  3. ** 8 KB pages is 128 per MB. If we ever change page size, this  
  4. ** will be variable by DB or file or filegroup in some manner  
  5. ** unforseeable now so just hard code it.  
  6. */  
  7. set dbsize = (select str(sum(convert(dec(17,2),size)) / 128,10,2)  
  8. + N' MB' from [master].dbo.sysfiles)  
  9. WHERE current of ms_crs_c1  
  10. fetch ms_crs_c1 into @name  
With a more in depth look at the execution plan there are a  few things that could be leading to the performance of this stored procedure. You will see an update of the temporary table and then a FETCH from the cursor for the next database. With the FETCH you will also see an index scan on the temporary table. The combination of multiple index scans and the cursor are contributing to the less than ideal performance of this stored procedure.
 
A Faster Alternative to sp_helpdb
 
The final step is a SELECT from the temporary table, #spdbdesc. Notice that this is the 111th query in the execution plan, even though there are only 32 databases on the server.
 
A Faster Alternative to sp_helpdb
 
Now let's take a look at an alternative to sp_helpdb. This alternative utilizes the sysdatabases table, master_files table and a number of server properties. Below you will see a comparison of sp_helpdb and the alternative below. Consistently the alternative performed significantly faster, up to 88% faster. Below you will find the data of a few tests on different servers with a different number of databases on each.
 
A Faster Alternative to sp_helpdb
 
Below is the code for the alternative. Like the code for sp_helpdb, I included the code to populate the temporary table to get the duration of the query. This query returns all the data in the stored procedure as well a number of additional data points. This code removes the need for a temporary table and the cursor.
  1. DECLARE @t1 DATETIME;   
  2. DECLARE @t2 DATETIME;   
  3.   
  4. SET @t1 = Getdate();   
  5.   
  6. CREATE TABLE #dbsize   
  7.   (   
  8.      databasename VARCHAR(200),   
  9.      size         BIGINT   
  10.   )   
  11.   
  12. INSERT INTO #dbsize   
  13. SELECT d1.NAME,   
  14.        CONVERT(VARCHARSum(m.size) * 8 / 1024) AS 'Total disk space'   
  15. FROM   sys.databases d1   
  16.        INNER JOIN sys.master_files m   
  17.                ON d1.database_id = m.database_id   
  18. GROUP  BY d1.NAME   
  19. ORDER  BY d1.NAME   
  20.   
  21. SELECT CONVERT(VARCHAR(50), d.NAME)                            AS 'Name',   
  22.        s.size                                                  AS   
  23.        'DatabaseSize(MB)',   
  24.        d.create_date,   
  25.        d.compatibility_level,   
  26.        CASE   
  27.          WHEN d.is_auto_create_stats_on = 1 THEN 'True'   
  28.          ELSE 'False'   
  29.        END                                                     AS 'AutoStatsOn',   
  30.        CASE   
  31.          WHEN d.is_auto_update_stats_on = 1 THEN 'True'   
  32.          ELSE 'False'   
  33.        END                                                     AS   
  34.        'AutoUpdateStatsOn',   
  35.        b.NAME                                                  AS 'DBOwner',   
  36.        CASE   
  37.          WHEN d.state = 0 THEN 'ONLINE'   
  38.          WHEN d.state = 1 THEN 'RESTORING'   
  39.          WHEN d.state = 2 THEN 'RECOVERING'   
  40.          WHEN d.state = 3 THEN 'RECOVERY_PENDING'   
  41.          WHEN d.state = 4 THEN 'SUSPECT'   
  42.          WHEN d.state = 5 THEN 'EMERGENCY'   
  43.          WHEN d.state = 6 THEN 'OFFLINE'   
  44.          WHEN d.state = 7 THEN 'COPYING'   
  45.          WHEN d.state = 10 THEN 'OFFLINE_SECONDARY'   
  46.          ELSE 'Unknown State'   
  47.        END                                                     AS 'State',   
  48.        Serverproperty('ProductMajorversion')                   AS   
  49.        'ProductMajorVersion',   
  50.        Isnull(Db_name(d.source_database_id), 'Not A Snapshot'AS 'SourceDBName'   
  51.        ,   
  52.        create_date,   
  53.        collation_name,   
  54.        user_access_desc,   
  55.        CASE   
  56.          WHEN is_read_only = 1 THEN 'True'   
  57.          ELSE 'False'   
  58.        END                                                     AS 'IsReadOnly',   
  59.        CASE   
  60.          WHEN is_auto_close_on = 1 THEN 'True'   
  61.          ELSE 'False'   
  62.        END                                                     AS   
  63.        'IsAutoCloseOn',   
  64.        CASE   
  65.          WHEN is_auto_shrink_on = 1 THEN 'True'   
  66.          ELSE 'False'   
  67.        END                                                     AS   
  68.        'IsAutoShrinkOn',   
  69.        state_desc,   
  70.        Databasepropertyex(d.NAME'Recovery')                  AS   
  71.        'RecoveryModel',   
  72.        log_reuse_wait_desc,   
  73.        containment_desc --This column will need be removed for older versions.   
  74.        ,   
  75.        d.delayed_durability_desc,   
  76.        CASE   
  77.          WHEN is_memory_optimized_enabled = 1 THEN 'True'   
  78.          ELSE 'False'   
  79.        END                                                     AS   
  80.        'IsMemoryOptimizedEnabled'   
  81.        --This column will need to be removed for older versions.   
  82.        ,   
  83.        Databasepropertyex(d.NAME'Updateability')             AS   
  84.        'UpdateAbility',   
  85.        Databasepropertyex(d.NAME'SQLSortOrder')              AS 'SQLSortOrder'   
  86.        ,   
  87.        CASE   
  88.          WHEN Databasepropertyex(d.NAME'IsFulltextEnabled') = 1 THEN 'True'   
  89.          ELSE 'False'   
  90.        END                                                     AS   
  91.        'IsFulltextEnabled',   
  92.        Databasepropertyex(d.NAME'Version')                   AS 'Version'   
  93. FROM   sys.databases d   
  94.        INNER JOIN sys.syslogins b   
  95.                ON d.owner_sid = b.sid   
  96.        INNER JOIN #dbsize s   
  97.                ON d.NAME = s.databasename   
  98.   
  99. DROP TABLE #dbsize   
  100. SET @t2 = Getdate();   
  101. SELECT Datediff(millisecond, @t1, @t2) AS elapsed_ms;   
While the improvement in performance is small, the lessons learned I think are really the key. Even though this post was about a system stored procedure, the same thought process can be applied to any query. As far as troubleshooting goes, these are only a few of the important steps.
  1. Look for hidden processing - In this case I looked at the execution plan of sp_helpdb and found the cursor. I did this mostly because I was curious. My first thought was there has to be a better way.
  2. Many times, cursors can and should be avoided - I knew there had to be a better way using T-SQL, I just needed to find it. We should always be looking for ways to improve our code.
  3. Review the Execution plans for problem queries, you never know what you might find. This applies even for system stored procedures sometimes - Of course this is how I found the potential issues
Performance tuning is a critical responsibility of the DBA. I had a student years ago tell me that milliseconds don't matter. Of course the entire class kind of shook their head after that statement. In reality, they do. A few milliseconds saved on a small query can equate to a really noticeable time improvement for larger queries or queries that might run thousands of times a day.
 
Thanks for visiting my blog!!!