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

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 https://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

SQLServerProfiler - Events Selection - Performance (v2)

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:

SQLServerProfiler-EmbeddedSQL

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:

SQLServerProfiler-UsingStoredProcedure

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.

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