how can I test performance in Sql Server Mgmt Studio without outputting data?

Question

Using SQL Server Management Studio.

How can I test the performance of a large select (say 600k rows) without the results window impacting my test? All things being equal it doesn't really matter, since the two queries will both be outputting to the same place. But I'd like to speed up my testing cycles and I'm thinking that the output settings of SQL Server Management Studio are getting in my way. Output to text is what I'm using currently, but I'm hoping for a better alternative.

I think this is impacting my numbers because the database is on my local box.

Edit: Had a question about doing WHERE 1=0 here (thinking that the join would happen but no output), but I tested it and it didn't work -- not a valid indicator of query performance.

Answer 1

You could do SET ROWCOUNT 1 before your query. I'm not sure it's exactly what you want but it will avoid having to wait for lots of data to be returned and therefore give you accurate calculation costs.

However, if you add Client Statistics to your query, one of the numbers is Wait time on server replies which will give you the server calculation time not including the time it takes to transfer the data over the network.

Answer 2

You can SET STATISTICS TIME ON to get a measurement of the time on server. And you can use the Query/Include Client Statistics (Shift+Alt+S) on SSMS to get detail information about the client time usage. Note that SQL queries don't run and then return the result to the client when finished, but instead they run as they return results and even suspend execution if the communication channel is full.

The only context under which a query completely ignores sending the result packets back to the client is activation. But then the time to return the output to the client should be also considered when you measure your performance. Are you sure your own client will be any faster than SSMS?

Answer 3

SET ROWCOUNT 1 will stop processing after the first row is returned which means unless the plan happens to have a blocking operator the results will be useless.

Taking a trivial example

SELECT * FROM TableX

The cost of this query in practice will heavily depend on the number of rows in TableX.

Using SET ROWCOUNT 1 won't show any of that. Irrespective of whether TableX has 1 row or 1 billion rows it will stop executing after the first row is returned.

I often assign the SELECT results to variables to be able to look at things like logical reads without being slowed down by SSMS displaying the results.

  SET STATISTICS IO ON
  DECLARE @name nvarchar(35),
          @type nchar(3)

  SELECT @name = name, 
         @type = type
  FROM master..spt_values

There is a related Connect Item request Provide "Discard results at server" option in SSMS and/or TSQL