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.

(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)

About these ads

14 thoughts on “SQL SERVER – Discard Results After Query Execution – SSMS

  1. Pingback: SQL SERVER – Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video « SQL Server Journey with SQL Authority

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

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

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

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

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

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s