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

sql-server database-performance
jcollum picture jcollum · Aug 6, 2009 · Viewed 16.4k times · Source

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

Robin Day picture Robin Day · Aug 6, 2009

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.

Related questions

What does exec sp_updatestats do?

What is the use of sp_updatestats? Can I run that in the production environment for performance improvement?

Read More
how to load data faster with talend and sql server

I use Talend to load data into a sql-server database. It appears that the weakest point of my job is not the dataprocessing, but the effective load in my database, which is not faster than 17 rows/sec. The funny point …

Read More
select top 1 * vs select top 1 1

I know there's a lot of these questions, but I can't find one that relates to my question. Looking at this question, Is Changing IF EXIST(SELECT 1 FROM ) to IF EXIST(SELECT TOP 1 FROM ) has any side effects? Specifically referring …

Read More