Database Startup Times

Have you have ever wondered what your database is doing as it starts and works through the various stages (including crash recovery) up to the point when it is open and available for user connections? Maybe your database seems to take a long time, but you know it was cleanly shutdown so the number of outstanding transactions (and hence the crash recovery process) should be small...

Here's how...

[sql]
DBCC DBTABLES

[/sql]

Note: this is an unsupported command, so use it at your own risk - and NOT on Production!

I won't go into the details of DBTABLE, or how it is populated - just suffice to say that there are a lot of useful values relating to startup times. So, how to get at them...

[sql]
USE master;
GO
IF OBJECT_ID('tempdb.dbo.#StartupTimes') IS NOT NULL
BEGIN
DROP TABLE #StartupTimes
END;

CREATE TABLE #StartupTimes ( ParentObject VARCHAR(255), Object VARCHAR(255), Field VARCHAR(255), Value VARCHAR(255));
INSERT INTO #StartupTimes
EXECUTE ('DBCC DBTABLE (''AdventureWorks2014'') WITH TABLERESULTS');

SELECT * FROM #StartupTimes
WHERE Field = 'StartupPhase'
[/sql]

Now, we should be able to see the various 'Startup Phases' that the database goes through whilst getting to the 'on-line' state. As an example, the first line in my resultset:-

DBTABLES: DBTABLE @0x000002338862B080 StartupPhase FCBOpenTime 43 ms

Tells me that FCBOpenTime took 43ms. This is the time it took SQL Server to call into the Windows OS, find, open and return a valid file handle for the database files (FCB - File Control Block). The various other values may or may not be populated depending on what features you currently have enabled within your database. So, for example, the Hekaton values:-

DBTABLES: DBTABLE @0x000002338862B080 StartupPhase HekatonRecoveryTime 0 ms

may well be '0 ms' if you are not using in-memory constructs.