SQL SERVER – Discard Results After Query Execution – SSMS

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.
SQL SERVER - Discard Results After Query Execution - SSMS discard5
(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

SQL SERVER - Discard Results After Query Execution - SSMS discard4

SQL SERVER - Discard Results After Query Execution - SSMS discard2

After disabling Discard Results After Query Execution

SQL SERVER - Discard Results After Query Execution - SSMS discard3

SQL SERVER - Discard Results After Query Execution - SSMS discard1

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)

Previous Post
SQL SERVER – Validating Spatial Object as NULL using IsNULL
Next Post
SQL SERVER – Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You

Related Posts

13 Comments. Leave new

  • Awesome Pinal, i expect more from you this kind of tips..

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

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

    Reply
  • jo and madhivanan char values not change to numeric if i am run insert command then error will come
    plz reply me fast

    Reply
  • Can you please post the error .

    Reply
  • jo and mahhivanan sir error show :- char value not convert to numeric value

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

    Reply
  • Narendra Prakash
    July 18, 2012 1:10 pm

    great pinal

    Reply
  • Hi Pinal
    if a query is fired from outside SQL server probably from our .NET code can we achieve this performance improvement.

    Reply
  • Navrazz Shrestha
    September 19, 2013 5:16 pm

    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 !!

    Reply
  • 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?

    Reply

Leave a Reply

Menu