Friday 14 December 2012

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

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete