SQL SERVER – Discard Results After Query Execution – SSMS
- by pinaldave
The first thing I do any day is to turn on the computer. Today I woke up and as soon as I turned on the computer I saw a chat message from a friend. He was a bit confused and wanted me to help him. Just as usual I am keeping the relevant conversation in focus and documenting our conversation as chat. Let us call him Ajit.
Ajit: Pinal, every time I run a query there is no result displayed in the SSMS but when I run the query in my application it works and returns an appropriate result.
Pinal: Have you tried with different parameters?
Ajit: Same thing. However, it works from another computer when I connect to the same server with the same query parameters?
Pinal: What? That is new and I believe it is something to do with SSMS and not with the server. Send me screenshot please.
Ajit: I believe so, let me send you a screenshot,
Pinal: (looking at the screenshot) Oh man, there is no result-tab at all.
Ajit: That is what the problem is. It does not have the tab which displays the result. This works just fine from another computer.
Pinal: Have you referred Nakul’s blog post – SSMS – Query result options – Discard result after query executes, that talks about setting which can discard the query results after execution.
(After a while)
Ajit: I think it seems like on the computer where I am running the query my SSMS seems to have the option enabled related to discarding results. I fixed it by following Nakul’s blog post.
Pinal: Great!
Quite often I get the question what is the importance of the feature. Let us first see how to turn on or turn off this feature in SQL Server Management Studio 2012.
In SSMS 2012 go to Tools >> Options >> Query Results > SQL Server >> Results to Grid >> Discard Results After Query Execution. When enabled this option will discard results after the execution. The advantage of disabling the option is that it will improve the performance by using less memory. However the real question is why would someone enable or disable the option.
What are the cases when someone wants to run the query but do not care about the result? Matter of the fact, it does not make sense at all to run query and not care about the result. The matter of the fact, I can see quite a few reasons for using this option. I often enable this option when I am doing performance tuning exercise. During performance tuning exercise when I am working with execution plans and do not need results to verify every time or when I am tuning Indexes and its effect on execution plan I do not need the results. In this kind of situations I do keep this option on and discard the results. It always helps me big time as in most of the performance tuning exercise I am dealing with huge amount of the data and dealing with this data can be expensive.
Nakul’s has done the experiment here already but I am going to repeat the same again using AdventureWorks Database.
Run following T-SQL Script with and without enabling the option to discard the results.
USE AdventureWorks2012
GO
SELECT *
FROM Sales.SalesOrderDetail
GO 10
After enabling Discard Results After Query Execution
After disabling Discard Results After Query Execution
Well, this is indeed a good option when someone is debugging the execution plan or does not want the result to be displayed. Please note that this option does not reduce IO or CPU usage for SQL Server. It just discards the results after execution and a good help for debugging on the development server.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology
