Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
- How to find privilege changes with SQL Server Default Trace
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
SSMS Query Options – Discard results after execution
26 February,2016 by Tom Collins
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.
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: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |