SQL Server, query performance and Execution Plan panel

Noticed one interesting thing with sql management studio.

Was trying to pinpoint some performance issues with a query which had WHILE loop. I switched on the execution plan to analyse the query. It was strange – like if performance was degrading with each new iteration. After several rounds of optimizations there was no luck, same performance degradation.

I created very simple test shown below (Example 1). It does nothing but counts the rows in sys.objects table 500 times so it returns the same result each time. There are 1570 rows in  MyDatabase.sys.objects on my machine

Still no luck – average execution times were rising, total time was more than a minute

Switched off the execution plan and it complete in less than a second!

The conclusion is that Execution Plan panel should be used carefully when your queries have WHILE loops – use Profiler or use SET SHOWPLAN_ALL ON or SET SHOWPLAN_XML ON statements as alternative (see Example 2)

--EXAMPLE1:

USE MyDatabase

SET STATISTICS TIME OFF;

set nocount on;

DECLARE @indexRow INT = 0, @cnt int

SET STATISTICS TIME ON

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END          

SET STATISTICS TIME OFF

--EXAMPLE2:

SET SHOWPLAN_ALL ON;

GO

DECLARE @indexRow INT = 0, @cnt int

WHILE @indexRow <= 500

BEGIN

       SELECT @cnt=COUNT(*), @indexRow = @indexRow + 1 FROM sys.objects

END

GO

SET SHOWPLAN_ALL OFF;

GO