Technical: Microsoft – SQL Server – Query – Like Clause (using sqlcmd.exe)

Introduction

This is a short follow-up to our last discussion on SQL Server optimization path when it processes SQL Like Clause.

BTW, the URL to that discussion is http://danieladeniji.wordpress.com/2013/06/27/technical-microsoft-sql-server-query-like-clause/

Measuring

Measuring Tools – SQL Server Profiler

Though it is much easier to use SQL Server Management Studio to compare and tune SQL, as we are interested in what happens when we come over the wire from external applications we will use SQL Server Profiler to get metrics.

To use SQL Server Profiler we will capture the following events: 

• Performance : Showplan XML Statistics Profile
• TSQL : SQL Batch Completed

 

Scenarios

Inline SQL

This is what occurs when we use embedded SQL.

File name :- getStudentsWithinZipCode.sql

declare @zipcode varchar(10)

set @zipcode = '1718103%'

select *
from   tempdb.dbo.student
where  zipcode like @zipcode

File name :- getStudentsWithinZipCode.cmd

sqlcmd -S (local) -d tempdb -i getStudentsWithinZipCode.sql

We prepared our payload file getStudentsWithinZipCode.sql and invoke it via getStudentsWithinZipCode.cmd.

And, monitor it via SQLServerProfiler we get the chart pasted below:

 

Explanation:

• The EventClass “SQL:BatchStarting” covers our SQL statement
• The EventClass “Showplan XML Statistics Profile” covers our SQL Plan
• From the Statistics Profile we can see that SQL Server is using “Clustered Index Scan” and also using Parallelism
• Via “SQL:Batch Completed”, our metrics are CPU - 1982, Reads – 55309, Duration 1151
  

 

Stored Procedure

Using SQL Server Management Studio, let us create a Stored Procedure (SP).

File name :- getStudentsWithinZipCodeSP.sql

use tempdb
go

if OBJECT_ID('dbo.usp_getStudentsWithinZipCode') is null
begin

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

alter procedure dbo.usp_getStudentsWithinZipCode
(
	@zipcode varchar(10)
)
as

	select *
	from   tempdb.dbo.student
	where  zipcode like @zipcode

go

File name :- getStudentsWithinZipCodeUsingSP.cmd

sqlcmd -S (local) -d tempdb -Q 
     "exec tempdb.dbo.usp_getStudentsWithinZipCode @zipcode = '1718103%' "

We created a Stored Procedure getStudentsWithinZipCodeUsingSP.sql and invoke it via getStudentsWithinZipCodeUsingSP.cmd.

And, monitor it via SQLServerProfiler we get the chart pasted below:

Explanation:

• The EventClass “SQL:BatchStarting” covers our SQL statement
• The EventClass “Showplan XML Statistics Profile” covers our SQL Plan
• From the Statistics Profile we can see that SQL Server is now using our Index Seek and we are also rid of our Parallelism block
• Via “SQL:Batch Completed”, our metrics are CPU – 16, Reads – 41, Duration 44

Conclusion

And, so again use Stored Procedure when you can.