How to study performance of using excution plan and trace:hehe:

Question

Post reply

How to study performance of using excution plan and trace:hehe:

Ponguru.SreenivasaRao

Right there with Babe

Points: 778
More actions
October 13, 2004 at 9:17 am

#59626

How to study performance of using excution plan and trace

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Not sure what you mean here. Are you looking for information on reading execution plans?

Ponguru.SreenivasaRao Right there with Babe Points: 778 More actions · Answer 2

How to calucalate t-sql query performance using executive plan and trace tools

i.e i have two same databases one with indexed one with non-index both having data(laks of records) so, i have show to my team performance difference between two querys having index and not having index

vinny_1973 SSCommitted Points: 1832 More actions · Answer 3

Not sure why you're having to prove the worth of Indexes to the rest of ur team, but hey!?

What you should see is that in the Non-Indexed version you will have a table "Scan" which has to look through the entire table ... in the Indexed version you will have an "Index Seek"!

The Cost involved will depend on the numbers of rows you have in the table ... but you will be talking Orders of magnitude difference when there are significant numbers of rows!

A quick way to get a reasonable test going is to run a WHILE loop, say, 10000 times for each scenario (on a quiet network!?) ... and you should see a marked difference in the timings!

Cheers

Mark

Ken Klaft-80806 Say Hey Kid Points: 693 More actions · Answer 4

This is just a WAG, but I believe what you're looking for is

SET SHOWPLAN_TEXT {ON | OFF}

Cheers,

Ken

Ken Klaft-80806 Say Hey Kid Points: 693 More actions · Answer 5

OK. I'm running a bit slow today... I re-read your original post and I believe this is what you were looking for (modify to suit your needs):

USE pubs

GO

SET SHOWPLAN_ALL ON

GO

-- First query.

SELECT au_id FROM authors WHERE au_id = '409-56-7008'

GO

-- Second query.

SELECT city

FROM authors

WHERE city LIKE 'San%'

GO

SET SHOWPLAN_ALL OFF

GO

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

You can also look at SET STATISTICS IO ON and see the reads and writes that are required.