Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

SSMS – Query result options - Discard result after query executes

Aug 4 2011 12:00AM by Nakul Vachhrajani   

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

Tags: 


Nakul Vachhrajani
4 · 36% · 11635
5
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • 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.

    commented on Aug 4 2011 3:50AM
    Hardik Doshi
    20 · 9% · 2863
  • Thank-you, Hardik. Yes, using this for checking the actual execution plan generation is indeed a very good point.

    commented on Aug 4 2011 3:55AM
    Nakul Vachhrajani
    4 · 36% · 11635
  • 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.

    commented on Aug 16 2011 1:30AM
    Jeff Moden
    159 · 1% · 305
  • Assigning outputs to "throwaway" variables - that's a good one. Thanks, Jeff!

    commented on Aug 22 2011 2:22AM
    Nakul Vachhrajani
    4 · 36% · 11635

Your Comment


Sign Up or Login to post a comment.

"SSMS – Query result options - Discard result after query executes" rated 5 out of 5 by 5 readers
SSMS – Query result options - Discard result after query executes , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]