How to find performance difference before and after changing the query in SP

Question

Post reply

How to find performance difference before and after changing the query in SP

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
July 27, 2011 at 6:02 am

#241462

Hi,
There was a search sp that I changed and created a new sp. I had to show the difference as the requirement was to bring data first by finding in title column then on other columns. the older sp was searching in all columns and there was only 1 query to bring results. as the requirement i had to make two queries and then put title with TOP priority in searching and it would be displayed first, so I made it with two queries and then return final result.
By now i have to check the performance of both and I am using following way of testing, not sure if this is correct or NOT, because the stats. are sometimes differenent with single query and the two queries are sometimes more better. so I am confused if I am doing wrong way of testing.
declare @DBID int
Select @DBID =db_id('Mydb')
DBCC FLUSHPROCINDB(@DBID)
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Declare @StartTime Datetime = GetDate()
declare @EndTime Datetime
exec [SearchRecordsWithKeyword] 'classroom',0,1000,1037, '', '', '','', 'RANK', 1, 2, 1, 0, 0
set @EndTime = GetDate()
select Datediff(ms, @startTime, @EndTime)
The difference in milisecond is what I am looking and it is not justifying me. An earliest reply will be highly appreciated...
Shamshad Ali.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Personally I prefer to use:

SET STATISTICS TIME ON

and / or

SET STATISTICS IO ON

simple and informative 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

In addition to what Eugene said, i usually create the replacement proc under a new, temporary name,a dn test them side by side, comparing execution plans;

yoiu know exec pr_myproc @param vs exec pr_myprocNEW @param .

after they've been run once to build the execution plan, the second and subsequent runs are what i'm interested in for the exection plans.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

STATISTICS IO is never but once you get to a 30 steps procs it becomes pretty hard to work with.

I like to use profiler and display all statements (and the whole batch / proc).

That way you really see what's going on.

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 4

Could you plz. tell me how do you get stats using profiler? what events and their columns to choose in profiler? plz. write in detail.

Shamshad Ali.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2

I just use the basic cpu, reads, writes, duration columns.

Those events are nice to use in their own rights :

Performance:Showplan XML Statistics Profile

SP:StmtCompleted

SQL:StmtCompleted

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Ninja's_RGR'us (7/27/2011)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2
I just use the basic cpu, reads, writes, duration columns.
Those events are nice to use in their own rights :
Performance:Showplan XML Statistics Profile
SP:StmtCompleted
SQL:StmtCompleted

I also throw in ROWCOUNT... it sometimes tells the tale for accidental cross joins and other Hidden RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Jeff Moden (7/27/2011)
Ninja's_RGR'us (7/27/2011)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2
I just use the basic cpu, reads, writes, duration columns.
Those events are nice to use in their own rights :
Performance:Showplan XML Statistics Profile
SP:StmtCompleted
SQL:StmtCompleted
I also throw in ROWCOUNT... it sometimes tells the tale for accidental cross joins and other Hidden RBAR.

I love that coutner too but I can't see how it can help detect the RBAR part.

Crosss join is easy, just look for 1 baziliion rows in that counter.

RBAR??? Only looking for AVG returns < 2 I guess?!?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

With exceptions, of course, many While loops will register 2 or 3 times the number of rows as there end up being in the result set.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)