Microsoft – SQL Server – Query Comparison Using SQL Profiler

Prelude

In a previous post we touched on the fact that we can use Quest Software’s Benchmark Factory and SQL Sentry’s Plan Explorer (  https://danieladeniji.wordpress.com/2014/09/01/microsoft-sql-server-query-comparison-using-sql-profiler/ ) to compare competing queries and query plans.

 

Background

Once again to get a generalized database, we downloaded SQL Server 2014 AdventureWorks from http://msftdbprodsamples.codeplex.com/releases/view/125550 and restored it.

 

SQL Objects

 

Once our AdventureWorks database is available, we created a  couple of Stored Procedures that we can use to retrieve data from it.

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_1

 

Here is the first SP – dbo.usp_PersonFetchFilteredOnName_1

 

use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]


    from   [Person].[Person] tblPerson

    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

Here is the second Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]
    where
            (

                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_3

 

Here is the third Stored Procedure ( dbo.usp_PersonFetchFilteredOnName_3 )

 

if object_id('dbo.usp_PersonFetchFilteredOnName_3') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_3 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_3
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]

    where
            (

                    (
                        Firstname = 
                                    case @Firstname
                                        when null then Firstname
                                        when '' then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case @LastName
                                        when null then Lastname
                                        when '' then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

 

SQL Management Studio

 

Here is the Query for invoking the Stored Procedures; along with the “Statistics IO” & “Statistics Time” measured.

 

Invoke SP

 

Here is the queries for invoking the SP.

 

exec dbo.usp_PersonFetchFilteredOnName_1
        @Firstname = 'Laura'
      , @Lastname  = 'Norman'


exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

    
exec dbo.usp_PersonFetchFilteredOnName_3
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

 

 

Statistics IO

 

StatisticsIO

 

 

Statistics Time

 

SQLServerTime

 

 

 

 

 

 

SQL Profiler

 

Here are the events we focused on using SQL Profiler.

 

Events Selection

 

Events Selection: Image

EventsSelection

 

Events Selection: Tabulate

 

Item Events & Columns  Item
Events & Columns
Stored Procedures
RPC:Completed
RPC:Starting
SP:Recompile
SP:Starting
 SP:StmtCompleted
Columns
TextData
CPU
Reads
Writes
SPID

 

 

 

 

 

Output

 

Output: Image

 

Here is the SQL Profiler Image.

SQLProfiler

 

Output:Tabulate

 

Metric SP-1 SP-2 SP-3
CPU 0 16  15
Reads 124 136  136
Writes 0 0  0
Duration 217 255  288

 

 

From the Screen-shot above, we can see that the Stored Procedures that use the “Case Clause” are a weeny bit more expensive.

 

Summary

 

Found that SQL Server Profiler when properly configured offers a slightly more concise view of a query’s performance data when compared to SQL Server “Set statistics time on”.

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s