The startup process of any system is a very interesting stage. Ever since I was exposed to the world of computers, I was fascinated with the POST (Power On Self Test) sequence, about which I learnt and explored in detail when I studied Electronics & Communications engineering. SQL Server is a server system on it’s own and therefore, I recently had a yearning for learning about the SQL Server startup sequence.

Now, I know that the SQL Server startup process involves an Undo-Redo phase for rolling forward/backward open transactions in each database, but the questions that kept cropping up in my mind were:

1. When the SQL Server starts, what is the first database that is made online?
   
2. When are user databases made available in the startup sequence?
   
3. When is the tempdb available?

In order to answer these questions, I postulated a theory and then tried to collaborate it with practice. The entire experience is documented in this post.

The Theory

When I started learning about SQL Server, I thought that the database_id determines when a database comes online. This would mean that the following DMV would be the only thing tat one needs to be query:

USE master

GO

–Get the database Ids

SELECT sd.database_id, sd.name

FROM sys.databases AS sd

ORDER BY sd.database_id

GO

My test server is a SQL Server 2012 instance with reporting services installed and the AdventureWorks2012 sample database deployed. The result for this test instance is shown below:

The one database that is not listed in the results of this DMV is the resource database.

Going by original hypothesis, the master should be started up first, followed by the resource, tempdb, model, msdb and so on.

The contradiction

Contradicting my thought process mentioned above were some of my pervious observations around tempdb. It is a confirmed point thing that the model database has to be online before the tempdb is made available.

• TempDB – Is it a copy of the Model database? – http://bit.ly/T6Ikjq
  
• TempDB v/s Model database – Minimum size considerations – CREATE DATABASE – Error Msg 1803 – http://bit.ly/RMAMPq

The above contradiction led me to hypothesize that the startup sequence would be similar to master –> resource->msdb –> model –> tempdb –> ReportServer –> ReportServerTempDB –> user databases. It was now time to confirm this line of thought.

The Test

To identify the real order in which databases are brought online, I looked at the SQL Server error log where all startup activity is logged by Microsoft SQL Server. I had referenced this in one of my pervious post related to identification of the startup time of the SQL server. To read the SQL Server error log through T-SQL, we use a system stored procedure sys.sp_readerrorlog (undocumented, to the best of my knowledge).  This system stored procedure takes up to 4 parameters:

• Error log file to read (0 = Current, 1 = Archive #1, and so on)
  
• Log File Type (NULL/1 = SQL Server Error log, 2 = SQL Agent Log)
  
• String 1 (first search string)
  
• String 2 (string to further refine the search)

We need to read the current file in the SQL Server Error Log, and only need to search for one string – “Starting up”. The call would therefore be similar to the one shown below, where I have captured the results into a table variable which allows us to sort by the log time.

–Check the sequence logged into the SQL Server Error Log

DECLARE @DatabaseStartupSequence TABLE (LogDate DATETIME, ProcessInfo VARCHAR(10), LogText VARCHAR(50))

INSERT INTO @DatabaseStartupSequence (LogDate, ProcessInfo, LogText)

EXEC sys.sp_readerrorlog 0, 1, ‘Starting up database’
SELECT CAST(dss.LogDate AS TIME) AS LogTime, dss.ProcessInfo, dss.LogText

FROM @DatabaseStartupSequence AS dss

ORDER BY dss.LogDate ASC

GO

The results of the query execution are as shown below:

The Result

The above is a very interesting result and tells me that I was not very wrong in my hypothesis. Here’s the interpretation of the log content.

• We know that recovery is a parallel process – this is confirmed by the fact that we have multiple ProcessIds in the results above
  
• The first database is recovered by process Id – 8 and is the master database – without the master database, the entire SQL Server fails to start
  
• The next database recovered is by process Id – 9 and is the resource database (“mssqlsystemresource”)
  
  
  
  • This very process goes on to recover, in sequence, the model and then the tempdb database
  
  
• The next process that SQL Server initiated was Id – 17 for recovers the msdb database
  
• Process Id – 18 then recovers the ReportServer database followed by Process Id – 19 which recovers the ReportServerTempDB
  
• Finally, process Id – 20 recovers the user database – AdventureWorks

The recovery interval (i.e. when the recovery for any database completes) depends on a number of factors and can be studied at: Understanding Recovery Performance in SQL Server.

Conclusion

In conclusion, I could now answer all my questions:

1. When the SQL Server starts, what is the first database that is made online? Answer: Master
   
2. When are user databases made available in the startup sequence? Answer: After all system databases have been recovered
   
3. When is the tempdb available? Answer: After the master, resource and model databases are recovered (in that sequence)

I learnt something new today, which is always fun. This topic is by no means complete. There is a lot more to explore, so, if you have any related observations/questions, please do share them via the blog comments, or send them on my Twitter handle (@nakulv_sql).

Until we meet next time,

Be courteous. Drive responsibly.

As part of my work, I often need to copy-paste the results of a query into an Excel sheet for multiple reasons. The purpose of such a requirement could be as simple as having a record of what the data looked like before and after a change I made (as in when doing data cleanup or custom data modification work) to as complex as running an ad-hoc data analysis. Almost all of my production-grade queries use aliases and it would be the same aliases that I would prefer to have as column headers on the Excel sheets for sake of consistency. The easiest way to achieve this is to copy the headers along-with the data from the results tab of the SSMS window.

Traditionally, I would choose the option “Copy with Headers” popup-menu option (or use the equivalent Ctrl+Alt+C keyboard shortcut):

However, our memories do fail us sometimes and remembering to use “Copy with Headers” and not the plain “Copy” is error-prone. Hence, I have the following setting turned on in my SSMS options:

Go to the Options window using Tools –> Options. Under Options, navigate to Query Results section.

Go to the “Results to Grid” option and check the check-box labeled “Include column headers when copying or saving the results”. Repeat the process, if required for “Results to Text” output format.

Re-launching the SSMS and attempting to copy (just plain copy) the result set also copies over the column headers to the applications of my choice, including Excel.

So, here are my questions – Do you use this built-in feature of the SSMS? Do you have any others to share with the group?

Until we meet next time,

Be courteous. Drive responsibly.

It’s a great feeling when you, the kind reader, share your feedback on my posts because generally, these provide a lot of learning opportunities for both. In the last couple of weeks, I explored the following SET options:

1. PARSEONLY
2. NOEXEC 
3. FMTONLY (SQL Server 2008 R2 and below)
4. sp_describe_first_result_set – replacement to FMTONLY (SQL Server 2012 and above)

SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics. While the SET option for PARSEONLY is quite straight-forward, a confusion may arise between NOEXEC and FMTONLY. Here’s why:

• NOEXEC – parses a query, produces and adds to the plan cache the execution plan of the query/workload in hand, without executing the query
• FMTONLY – parses the query, produces the execution plan and executes the query/workload to return the meta-data associated to the query without processing any records

When the FMTONLY option is exercised, the query is actually executed to return the meta-data, but no records are processed. However, this behaviour is not intuitive. Here’s a follow-up question that I received in my post related to FMTONLY.

When you try to execute a query that have side effects (like an INSERT) with FMTONLY ON, you can easily check that the row is NOT inserted. I just tried this on SQL 2008 (not R2). Can you confirm this? If this is true, how is FMTONLY different from NOEXEC?

Demo

In order to answer this question, I have come up with the following demo. The demo simply involves using the NOEXEC and FMTONLY options on a query around a non-existent table – MyTable. Since the table is non-existent, I should encounter a run-time error the moment the query is executed. The script below does the following:

• Sets the system to a base state where both NOEXEC and FMTONLY are set to OFF
• A query around the non-existent table – MyTable is executed with the NOEXEC option turned ON
• The same query is re-executed with the FMTONLY option turned ON

USE tempdb
GO

--Base State
SET NOEXEC OFF
GO
SET FMTONLY OFF
GO
PRINT '00. Setting base state - both NOEXEC and FMTONLY are now OFF.'
GO

PRINT '01. Now checking whether MyTest already exists or not.'
PRINT '    If the ObjectId is not printed after this line, object is non-existent.'
PRINT OBJECT_ID('MyTest')


PRINT '02. Running with NOEXEC ON now...'
--Set the NOEXEC option to ON, telling the database engine
--to Parse and Compile the query, but not execute it
SET NOEXEC ON
GO

--Test workload.
--NOTE: The MyTest table does not exist!
SELECT Id,
       MyValue
FROM MyTest

SET NOEXEC OFF
GO

PRINT '03. NOEXEC is now OFF.'
PRINT '04. Running with FMTONLY ON now...'

--Set the FMTONLY option to ON, telling the database engine
--to Parse, Compile, Execute the query, 
--and return meta-data, but not return any records
SET FMTONLY ON
GO

--Test workload.
--NOTE: The MyTest table does not exist!
SELECT Id,
       MyValue
FROM MyTest

SET FMTONLY OFF
GO

PRINT '05. FMTONLY is now OFF.'
GO

Running the entire script against my test SQL Server instance (tested on SQL 2008, SQL 2008 R2 and SQL 2012) yields the following results.

00. Setting base state – both NOEXEC and FMTONLY are now OFF.

01. Now checking whether MyTest already exists or not.

Msg 208, Level 16, State 1, Line 4

Invalid object name ‘MyTest’.

Conclusion

The above was a very simple demo, with a very clear output which helped me arrive at the following conclusion:

• Although the table is non-existent (as confirmed by the OBJECT_ID() call in step #01), NOEXEC did not return any errors
  • This indicates that NOEXEC did not actually “submit” the query to the query engine for execution
• The  attempt with the FMTONLY option set to ON however, did return a runtime error and that resulted in batch termination (no statements after that point were executed)
  • This indicates that FMTONLY did execute the query and as a result, encountered the error

Now, because the attempt with FMTONLY resulted in a batch termination, allow me to return my server back to the base state.

(To know more about SQL Server error states, please visit Pinal Dave, a.k.a. SQLAuthority’s [B|T] post: Introduction to SQL Error Actions – A Primer)

--Base State
SET NOEXEC OFF
GO
SET FMTONLY OFF
GO
PRINT '00. Setting base state - both NOEXEC and FMTONLY are now OFF.'
GO

I trust that the above demo answers all questions that one may have regarding the differences between NOEXEC and FMTONLY. If you have any other questions, please feel free to send them over to me. I will be glad to help!

Until we meet next time,

Be courteous. Drive responsibly.

Earlier, we discussed a situation wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. ADO uses this mechanism to make a “dry-run”.

In SQL Server 2008 R2 and below, one SET option came in very handy in realizing this requirement. This option was the FMTONLY option. The FMTONLY option is marked as a deprecated feature in SQL Server 2012, and is replaced by:

• sp_describe_first_result_set
• sys.dm_exec_describe_first_result_set
• sys.dm_exec_describe_first_result_set_for_object

sp_describe_first_result_set

You can revisit the discussion related to FMTONLY here. The core requirement is that at execute time, the SQL Server database engine should parse (PARSEONLY), compile (NOEXEC) and executed a query, but return only meta-data back to the client application. No data rows are to be returned. Please note that the name of the procedure suggests that information is returned only for the first result set (this could be applicable in case your procedures return more than one result set/MARS).

Here’s how the sp_describe_first_result_set stored procedure can be used to get this information:

USE AdventureWorks2012
GO
--SQL 2012 and above only!
EXEC sp_describe_first_result_set @tsql = N'SELECT Employee.BusinessEntityID,
                                                   Employee.BirthDate,
                                                   Employee.Gender,
                                                   Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee'
GO

One of the first things we see is that the result set is much more comprehensive and user-friendly when compared with the older FMTONLY.

The extended stored procedure also accepts:

• A set of parameters (@params), similar to sp_executesql
• A @browse_information_mode parameter which has the following possible values:
  • 0 = No information is returned
  • 1 = Query is analyzed as if it contains the FOR BROWSE option specified
  • 2 = Query is analyzed as if it would be used in preparing or executing a cursor

Return Value information:

1. When called from T-SQL, sp_describe_first_result_set will always have a return value of 0
2. If the procedure throws an error and the procedure is called as an RPC, return status is populated by the type of error described in the error_type column of sys.dm_exec_describe_first_result_set

sys.dm_exec_describe_first_result_set

This is the DMV equivalent of sp_describe_first_result_set. Sample execution is as shown below:

USE AdventureWorks2012
GO
SELECT * 
FROM sys.dm_exec_describe_first_result_set(N'SELECT Employee.BusinessEntityID,
                                                    Employee.BirthDate,
                                                    Employee.Gender,
                                                    Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee',
                                            NULL,
                                            0)
GO

As you may have noticed, the function takes 3 parameters which are same as that shown above for sp_describe_first_result_set. The result set is also the same.

sys.dm_exec_describe_first_result_set_for_object

Functionally equivalent to sys.dm_exec_describe_first_result_set dynamic management function, sys.dm_exec_describe_first_result_set_for_object accepts an Object Id as input. This Object Id can be that of a T-SQL stored procedure or a T-SQL trigger. If it is the ID of any other object (such as a view, table, function, or CLR procedure), an error will be specified in the error columns of the result.

The result set is same as that of sys.dm_exec_describe_first_result_set.

Please NOTE:

Since these procedures/functions return meta-data about the return result-set, one of the following will be true if the batch is executed after execution of sp_describe_first_result_set:

1. Result in an optimization-time error
2. Result in a run-time error (depending upon the parameters passed or the state of the server/database, or another general run-time error)
3. Return no result set (depending upon the parameters passed)
4. Returns a result-set conforming to the same meta-data

References:

1. sp_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878602.aspx
2. sys.dm_exec_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878258.aspx
3. sys.dm_exec_describe_first_result_set_for_object – http://msdn.microsoft.com/en-us/library/ff878236.aspx

Until we meet next time,

Be courteous. Drive responsibly.

One may encounter many situations wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. A real-world example is to ensure that the application is working with the correct version of the database. If the number or type of the columns returned are not as per expectations, the user can be presented with a well-formed message rather than an exception. I believe ADO uses this mechanism to make a “dry-run”.

In SQL Server 2008 R2 and below, one SET option comes in very handy in realizing this requirement. This option is the FMTONLY option.

NOTE: The FMTONLY option is marked as a deprecated feature in SQL Server 2012. I will be writing about the replacement in the immediate future.

SET FMTONLY

At execute time, if the SQL Server database engine encounters the option FMTONLY as being set to ON, no rows are processed or sent to the client. However, the query/batch is parsed (PARSEONLY), compiled (NOEXEC) and executed. Here’s a simple demo script:

USE AdventureWorks2012
GO

--Set the FMTONLY option to ON, telling the database engine
--to Parse, Compile, Execute the query, 
--and return meta-data, but not return any records
SET FMTONLY ON

SELECT '3' AS RoundNum,
       Employee.BusinessEntityID,
       Employee.BirthDate,
       Employee.Gender,
       Employee.JobTitle
FROM HumanResources.Employee AS Employee
GO

SET FMTONLY OFF

--Check for caching
SELECT '3' AS RoundNum, usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
        text like '%FROM HumanResources.Employee%'
    AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO

Executing the query returns two result sets – first one from the actual query (indicating that the query was actually executed), and the second result set showing that the query plan was compiled.

The messages tab supports the observation:

Please NOTE:

Please do not confuse the FMTONLY feature with the following:

1. Query result options – Discard result after query executes – this does not return meta-data back to the client/calling application
   
2. OPTION (FAST n)/Table Hint: FASTFIRSTROW – this returns n number of data rows back to the client/calling application

Until we meet next time,

Be courteous. Drive responsibly.