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 (https://blog.sqlauthority.com)
13 Comments. Leave new
Awesome Pinal, i expect more from you this kind of tips..
Good
hello pinal and madhivan
my name is happy from delhi. i have some question in sql server 2005
i have create salary table
create table salary
(
sal_id int,
sal_salary money,
sal_pf money,
sal_hra money
);
insert into salary values(01,’12000′,”,”);
i want to say that sal_pf default come in pf column or sal_hra come in hra colomn
update salary set sal_pf=sal_salary*12/100 and sal_esic=sala_salary*1.75/100 where sal_id between 1 and 1000
but give me eroor when insert commmand run
plz sir help me about this query
2 question what is use of replication , cluster , mirroring and log shipping in sql server 2005
What is the error message you got?
Happy ,
The question 1 is not really clear. But I tried to check your query
create table salary
(
sal_id int,
sal_salary money,
sal_pf money,
sal_hra money
);
insert into salary values(01,’12000′,”,”);
select * from salary
update salary
set sal_pf=sal_salary*12/100 , sal_hra=sal_salary*1.75/100
where sal_id between 1 and 1000
Insert and update ran fine without nay issues .
Am i missing anything ?
Thanks,
Jo
jo and madhivanan char values not change to numeric if i am run insert command then error will come
plz reply me fast
Can you please post the error .
jo and mahhivanan sir error show :- char value not convert to numeric value
hello madhivanan sir its my mistake because i was using sal_pf decimal, sal_esic decimal but i have changed decimal to money then output will come
sorry madhivanan
great pinal
Hi Pinal
if a query is fired from outside SQL server probably from our .NET code can we achieve this performance improvement.
I want to disabled the query result messages for executing from sqlsrv specially for restoring database. But the message such as “Processed 444664 pages for database …” halted my query execution. So i am looking for the solution…. Thanks in advance !!
Pinal,
Excellent walk-through. I do wonder, is it possible to enable and disable “Discard Results After Query Execution” via TSQL? I have some across the need to do this while using xp_cmdshell to move files to an archive folder after processing them. When doing a MOVE with xp_cmdshell, the execution always outputs a result:
Output
1 file(s) moved
NULL
I would be handy to do something like
SET Discard_Results_After_Query_Execution ON
SET @SQL = ‘EXEC master..xp_cmdshell ” MOVE ‘+@SourcePath+@FileName+’ ‘+@ToPath+””
EXEC(@SQL)
SET Discard_Results_After_Query_Execution OFF
Or maybe I’m missing something simple that would already quiet the output?