SSMS – Query result options – Discard result after query executes


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.

image

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

image

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)

image

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

image

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!

image

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.

image

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.

image

Advertisements

4 thoughts on “SSMS – Query result options – Discard result after query executes

  1. Hardik Doshi

    Nice post Nakul. I never used this option.

    As per me, this option is useful when we want to generate the actual execution plan (without checking the retrieved result set is correct or not) for our optimization purpose. It also frees memory so it will be useful for other operations.

    Like

    Reply
  2. Nakul Vachhrajani

    Thank-you, Hardik. Yes, using this for checking the actual execution plan generation is indeed a very good point.

    Like

    Reply
  3. Jeff Moden

    One of the biggest uses I get out of it is for comparative performance testing. Lots of people will return rows to the screen during testing and, if you’re testing on a million rows, the amount of time to return the rows becaomes the “great equalizer” because it takes longer to return the rows than it takes for the code to run.

    Another method for doing the same thing is to assign the outputs to “throwaway” variables.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s