As most of the readers of my post know, I have always tried to find features of the SQL Server Management Studio (SSMS) that boost productivity & allow for efficient administration. One such feature is that SSMS allows us to customize the query options for any particular query window (Refer Appendix A at the end of this post).

Query Result options – Discard result after query executes

Recently, I was exploring the various query result settings available to us in the SSMS for Results to Grid and Results to Text modes. I spotted the “Discard result after query executes” option.

This option was new to me (I had never heard about it, let alone use it). So, I enabled the option for grid results and started exploring.

I ran the following query, after enabling the option:

SELECT * FROM HumanResources.Employee
GO

To my astonishment, no results were returned. I always have the actual execution plan enabled on my test instance, and surprisingly, the execution plan was produced.

1. Notice that in the screen-shot below, we do not have the results tab!
2. Upon studying the Properties window, we find that the query was executed and 290 records where fetched by the database engine
3. However, no records were returned to the SSMS query window

Performance Benefits?

I then started looking up in the Books On Line about this option, but found only one line:

BOL Page: http://msdn.microsoft.com/en-us/library/ms190144(SQL.110).aspx

Description: “Frees memory by discarding the query results after the screen display has received them.”

If the memory is being freed, it generally indicates a boost in performance. So, I generated a query that would take some noticeable execution time to execute on my box. I ran the query in two separate windows – one with the default query options, i.e. – “Discard results after query executes” switch was unchecked, whereas in the other connection, the switch was checked.

The result? A clear performance gain!

--Clear all buffers and plan cache
--WARNING: USE WITH CAUTION. THIS CODE IS PROVIDED AS-IS WITHOUT WARRANTY
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

SELECT msc.name + CONVERT(VARCHAR(10),ROUND(RAND()*1000,0))
FROM msdb.sys.objects mso (NOLOCK)
CROSS JOIN msdb.sys.columns msc (NOLOCK)

Results of query #1 (“Discard results after query executes” unchecked)

Results of query #2 (“Discard results after query executes” checked)

How long has this been around?

The final question that came in my mind was – how long has this option been around? I fired off my SQL 7 instance, and found that I was the one behind the times!

Conclusion

The only use I can think of for this configuration setting is to execute queries to study the actual execution plan – without returning any results. Are you aware of any other use? Do let me know.

Until we meet next time,

Be courteous. Drive responsibly.

Appendix A – How to customize the query options for a given query window?

Very briefly, I will show you how to customize the query options for any given query window in the SSMS:

In any window where a customized query option needs to be used, right-click in the query window to open the pop-up menu. Click on the “Query options” menu option.

This will open up the Query options window. Any configuration changes made this way remain in effect on the particular query window (all other windows follow the default settings) as long as the window is open.