Incorrect Actual Rows and other issues

Vlady Oselsky 2016-09-19 20:06:44

I ran a simple query against AdventureWorks2014 Database which is causing few issues for PE.

  • Actual Number of Rows is showing 4 times as many rows as actually returned.
  • Total Time of execution while waiting for the query to complete goes all the way to 16 before stopping and showing mouse spinning icon for few more seconds before completing.
  • Duration for the same query in SSMS with Actual Execution Plan is under 500 milliseconds!
  • Switching between history versions takes anywhere between 10-20 seconds.

Below is the query and scalar function, it is calling.

SELECT  p.FirstName
      , p.LastName
      , pp.PhoneNumber
      , dbo.VerifyPhoneFormat(pp.PhoneNumber) AS FormatType
FROM    Person.Person AS p
JOIN    Person.PersonPhone AS pp
        ON p.BusinessEntityID = pp.BusinessEntityID
 
GO
 
-- =============================================
-- Author:      Vlady Oselsky
-- Create date: 09-19-2016
-- Description: Demo
-- =============================================
CREATE FUNCTION dbo.VerifyPhoneFormat
    (
      @PhoneNumber VARCHAR(20)
    )
RETURNS VARCHAR(20)
AS 
    BEGIN
    -- Declare the return variable here
        DECLARE @Indx1 INT
        DECLARE @Indx2 INT
 
        SET @Indx1 = CHARINDEX('-', @PhoneNUmber, 0)
        SET @Indx2 = CHARINDEX('-', @PhoneNUmber, @Indx1 + 1)
 
        IF ( @Indx1 = 4
             AND @Indx2 = 8
           ) 
            BEGIN
                RETURN 'US'
            END 
 
        RETURN 'UNKNOWN'
    END
GO

alt text

Actual and Estimated plans

/storage/attachments/1740-plan2.pesession
September 19, 2016
No Comments