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.
- Notice that in the screen-shot below, we do not have the results tab!
- Upon studying the Properties window, we find that the query was executed and 290 records where fetched by the database engine
- However, no records were returned to the SSMS query window
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!
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.
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.
Thank-you, Hardik. Yes, using this for checking the actual execution plan generation is indeed a very good point.
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.
Assigning outputs to “throwaway” variables – that’s a good one. Thanks, Jeff!