SSMS Query Options – Discard results after execution

26 February,2016 by Jack Vamvas

During SQL Server performance tuning consultancies one of the goals is to recreate the problem in the actual environment. In many cases this is not always possible.  You may be sent a query that is experiencing difficulties, but you may not be allowed to use the client application .  In these cases I may need to use SQL Server Management Studio (SSMS)

One of the benefits in using SSMS is the various Query Options available. An option I use frequently when sql performance tuning is the Discard results after query executes.    

If the query returns a large recordset resources are being consumed writing to the interface. Avoiding writing to the interface is a way to focus on the compilation and execution time . Avoiding writing to the interface is one way of discarding the output and not including as part of the run time analysis.

To enable the Discard Results after execution , from SSMS the path is : Query Options >> Results >> Grid >> Discard Results after execution

The one disadvantage of using this method is when there is an error in the code, they are not displayed. When you see the message “Query completed with errors” you may need to execute the query again having unchecked the Discard Results after execution.

The other option is to have two query windows open . One set with discarding the results, the other displaying the results.

Discard_results

Read More on SSMS

Open SSMS without the splash screen - SQL Server DBA

SQL Server - Open SSMS on the Command Line - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SSMS Query Options – Discard results after execution


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer