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

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.

Answer 2

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.

Answer 3

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 4

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

How can you test perfromcne 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 qury 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 becasue the info has to go across the network.

Answer 6

How about populating temp tables / table variables, and not selecting out of them? Something like:

declare @tbl table (foo int, bar bit);

insert into @tbl (foo, bar)
select baz, quux
from complicated.query
join big.tables;

select 'done';