July 5, 2005 at 4:45 am
Hi All,
I have few SQL Statements(4 select + 1 insert)in a SP (Sql Server 2000). This was a slow running SP. Hence modified some Select statments. (all perf optimising tips were used). Now I ran these staments in a batch - Visible Performance improvement can be seen. old statements taking 69 secs - new taking only 15 sec.
But When i created stored procedure for both old & new statements - no difference at all. Both SP's took same time i.e. 14 sec's.
I was not able to understand the above. Can anybody throw some light on this peculier behaviour?
July 5, 2005 at 5:41 am
To trace the times that each one takes you will need to flush the cache between runs of the stored-procedures. It sounds like the stored-procedures are being cached and the best execution plan is being used. I think it is a DBCC command but I can't find it at the moment
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 5, 2005 at 6:17 am
dbcc dropcleanbuffers
dbcc FREEPROCCACHE
July 5, 2005 at 6:27 am
did you try to get any information from the Index Tuning Wizard
My Blog:
July 5, 2005 at 6:33 am
Yes. But in vain
execution plan shows Index seek / Clustered Index seeks for all the joins
July 5, 2005 at 6:35 am
can you paste the plans... maybe we can spot something you missed?
July 6, 2005 at 2:40 am
use
set statistics io on and
set statistics time on
Find out how much time it takes to compile and parse each quires (old quires as well as new quires)
do it for SP as well. You can try this out with and without flushing the data and compiltation plans using the two DBCC commands.
Please look at the syscacheobjects system table to have an idea of the cached plans.
Hope this things would help you.
cheers
Rajesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply