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

Asked 6/8, 2009 at 16:29 Answered 30/4, 2021 at 9:27

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.

Changsha answered 6/8, 2009 at 16:29 Comment(0)

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.

Finish answered 6/8, 2009 at 16:50 Comment(1)

The costs for SET ROWCOUNT 1 can be entirely different than without this value. Execution stops after the first row is returned so it will under report true costs and the plan may be different with and without ROWCOUNT too. – Fertilize 27/11, 2013 at 15:35

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?

Frau answered 6/8, 2009 at 17:10 Comment(0)

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

Fertilize answered 13/3, 2012 at 13:25 Comment(0)

There is a lot of more correct answers of answers but I assume real question here is the one I just asked myself when I stumbled upon this question: I have a query A and a query B on the same test data. Which is faster? And I want to check quick and dirty. For me the answer is - temp tables (overhead of creating temp table here is easy to ignore). This is to be done on perf/testing/dev server only!

Query A:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS (to clear statistics
SELECT * INTO #temp1 FROM ...

Query B

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT * INTO #temp2 FROM ...

Hispidulous answered 30/4, 2021 at 9:27 Comment(0)

The best thing you can do is to check the Query Execution Plan (press Ctrl+L) for the actual query. That will give you the best guesstimate for performance available.

Rebellious answered 6/8, 2009 at 16:33 Comment(1)

This will give me relative costs, but not actual numbers. I need actual numbers to test performance. – Changsha 6/8, 2009 at 16:38

I'd think that the where clause of WHERE 1=0 is definitely happening on the SQL Server side, and not Management Studio. No results would be returned.

Is you DB engine on the same machine that you're running the Mgmt Studio on?

You could :

Output to Text or
Output to File.
Close the Query Results pane.

That'd just move the cycles spent on drawing the grid in Mgmt Studio. Perhaps the Resuls to Text would be more performant on the whole. Hiding the pane would save the cycles on Mgmt Studio on having to draw the data. It's still being returned to the Mgmt Studio, so it really isn't saving a lot of cycles.

Confute answered 6/8, 2009 at 16:34 Comment(1)

Yeah, the WHERE 1=0 was easy to test and wasn't workable. I'm running the SQL Server and Mgmt Studio on my local box. – Changsha 6/8, 2009 at 16:36

How can you test performance of your query if you don't output the results? Speeding up the testing is pointless if the testing doesn't tell you anything about how the query is going to perform. Do you really want to find out this dog of a query takes ten minutes to return data after you push it to prod?

And of course its going to take some time to return 600,000 records. It will in your user interface as well, it will probably take longer than in your query window because the info has to go across the network.

Unconventionality answered 6/8, 2009 at 18:40 Comment(1)

Query in question will never go to prod and never be used in a UI. – Changsha 7/8, 2009 at 20:29

Recommended topics

Hot tags