A Faster Alternative To sp

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.

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.

CREATE TABLE #variables
(
StartDate DateTime
)
GO
INSERT INTO #variables
SELECT GETDATE();
GO
EXEC sp_helpdb
DECLARE @t2 DATETIME;
SET @t2 = GETDATE();
DECLARE @t1 DATETIME;
SELECT @t1 = StartDate FROM #variables
SELECT DATEDIFF(MILLISECOND,@t1,@t2) AS 'TotalTime';
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.

if not exists (select * from master.dbo.sysdatabases
where (@dbname is null or name = @dbname))
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
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.

update #spdbdesc
/*
** 8 KB pages is 128 per MB. If we ever change page size, this
** will be variable by DB or file or filegroup in some manner
** unforseeable now so just hard code it.
*/
set dbsize = (select str(sum(convert(dec(17,2),size)) / 128,10,2)
+ N' MB' from [master].dbo.sysfiles)
WHERE current of ms_crs_c1
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.

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.

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.

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.

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = Getdate();
CREATE TABLE #dbsize
(
databasename VARCHAR(200),
size BIGINT
)
INSERT INTO #dbsize
SELECT d1.NAME,
CONVERT(VARCHAR, Sum(m.size) * 8 / 1024) AS 'Total disk space'
FROM sys.databases d1
INNER JOIN sys.master_files m
ON d1.database_id = m.database_id
GROUP BY d1.NAME
ORDER BY d1.NAME
SELECT CONVERT(VARCHAR(50), d.NAME) AS 'Name',
s.size AS
'DatabaseSize(MB)',
d.create_date,
d.compatibility_level,
CASE
WHEN d.is_auto_create_stats_on = 1 THEN 'True'
ELSE 'False'
END AS 'AutoStatsOn',
CASE
WHEN d.is_auto_update_stats_on = 1 THEN 'True'
ELSE 'False'
END AS
'AutoUpdateStatsOn',
b.NAME AS 'DBOwner',
CASE
WHEN d.state = 0 THEN 'ONLINE'
WHEN d.state = 1 THEN 'RESTORING'
WHEN d.state = 2 THEN 'RECOVERING'
WHEN d.state = 3 THEN 'RECOVERY_PENDING'
WHEN d.state = 4 THEN 'SUSPECT'
WHEN d.state = 5 THEN 'EMERGENCY'
WHEN d.state = 6 THEN 'OFFLINE'
WHEN d.state = 7 THEN 'COPYING'
WHEN d.state = 10 THEN 'OFFLINE_SECONDARY'
ELSE 'Unknown State'
END AS 'State',
Serverproperty('ProductMajorversion') AS
'ProductMajorVersion',
Isnull(Db_name(d.source_database_id), 'Not A Snapshot') AS 'SourceDBName'
,
create_date,
collation_name,
user_access_desc,
CASE
WHEN is_read_only = 1 THEN 'True'
ELSE 'False'
END AS 'IsReadOnly',
CASE
WHEN is_auto_close_on = 1 THEN 'True'
ELSE 'False'
END AS
'IsAutoCloseOn',
CASE
WHEN is_auto_shrink_on = 1 THEN 'True'
ELSE 'False'
END AS
'IsAutoShrinkOn',
state_desc,
Databasepropertyex(d.NAME, 'Recovery') AS
'RecoveryModel',
log_reuse_wait_desc,
containment_desc --This column will need be removed for older versions.
,
d.delayed_durability_desc,
CASE
WHEN is_memory_optimized_enabled = 1 THEN 'True'
ELSE 'False'
END AS
'IsMemoryOptimizedEnabled'
--This column will need to be removed for older versions.
,
Databasepropertyex(d.NAME, 'Updateability') AS
'UpdateAbility',
Databasepropertyex(d.NAME, 'SQLSortOrder') AS 'SQLSortOrder'
,
CASE
WHEN Databasepropertyex(d.NAME, 'IsFulltextEnabled') = 1 THEN 'True'
ELSE 'False'
END AS
'IsFulltextEnabled',
Databasepropertyex(d.NAME, 'Version') AS 'Version'
FROM sys.databases d
INNER JOIN sys.syslogins b
ON d.owner_sid = b.sid
INNER JOIN #dbsize s
ON d.NAME = s.databasename
DROP TABLE #dbsize
SET @t2 = Getdate();
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.

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.
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.
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!!!