Geek Answers Handbook

How can I test performance in Mgmt Studio Sql Server without data output?

Using SQL Server Management Studio.

How can I test the performance of a large selection (say, 600 thousand rows) without a result window affecting my test? Other things being equal, this does not matter much, since both requests will be displayed in the same place. But I would like to speed up the testing cycle, and I think the output settings of SQL Server Management Studio are bothering me. The output to the text is what I am currently using, but I hope for a better alternative.

I think this affects my numbers because the database is in my local field.

Edit: the question arose that WHERE 1 = 0 here (thinking that the connection will happen, but there is no output), but I tested it and it did not work - it is not a valid indicator of the query performance.

+5
source share
6 answers

You can do SET ROWCOUNT 1before request. I'm not sure that this is exactly what you want, but it will avoid waiting for a large amount of data to be returned, and therefore will give you the exact cost of the calculation.

However, if you add client statistics to your request , one of the numbers is the server response timeout, which will give you server calculation not including the time required to transfer data over the network.

+10

, . Query/ (Shift + Alt + S) SSMS, . , SQL- , , .

, , . , , . , SSMS?

+2

, ? , , . , , , prod?

, , , 600 000 . , , , , , .

+1

SET ROWCOUNT 1 , , , .

SELECT * FROM TableX

TableX.

SET ROWCOUNT 1 . , TableX 1 1 , .

SELECT , , , SSMS, .

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

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

" " SSMS / TSQL

+1

, , ( Ctrl + L) . .

0

, where WHERE 1=0 SQL Server, Management Studio. .

DB , Mgmt Studio?

:

  • .
  • .

This will simply move the cycles spent on meshing in Mgmt Studio. Perhaps Resuls to Text will be more effective overall. Hiding the panel will save cycles on Mgmt Studio when you have to draw data. It is still returning to Mgmt Studio, so it really doesn't save many cycles.

0
source

More articles:


All Articles