Re: SP Execution Very Slow

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Quentin Ran (ab_at_who.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 09:34:51 -0600

The original post says "I GOT a Task for Optimizing Queries" which I think
you can reasonably assume that it is not going to happen right on the
production server. Also the code was for simplicity. For targeted
database, use

CHECKPOINT
dbcc dropcleanbuffers
--dbcc freeproccache -- this one drops proc cache in all db.
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM
  master.dbo.sysdatabases WHERE name = 'database_name')
DBCC FLUSHPROCINDB (@intDBID)

"Olu Adedeji" <anonymous@email.com> wrote in message
news:efbiUwwAEHA.2308@tk2msftngp13.phx.gbl...
> Sorry to be pedantic Quentin :) just to add that if this is on a
production
> server dbcc free proccache and drop cleanbuffers will severely affect all
> other procedures and processes with data in the cache buffers and in the
> procedure cache as this is global to the server.
>
> --
> Olu Adedeji
> "Quentin Ran" <ab@who.com> wrote in message
> news:e1j3#ivAEHA.2720@TK2MSFTNGP11.phx.gbl...
> > The first run you have loaded the data and indexes into the cache. If
you
> > have a lot of data, the disk i/o will take a lot of time. The second
run,
> > with the data and indexes loaded, no disk i/o is needed, so it runs much
> > faster.
> >
> > There can be other reasons. Without more detail, this seems to be the
> most
> > likely cause. Use
> >
> > CHECKPOINT
> > dbcc dropcleanbuffers
> > dbcc freeproccache
> >
> > to clear the cache between the runs.
> >
> > Quentin
> >
> >
> > "n S" <nssidhu100@hotmail.com> wrote in message
> > news:FC4AFC4A-56F2-40A1-AECA-484C27BD4154@microsoft.com...
> > > I GOT a Task for Optimizing Queries.
> > > Having Problem with one of the SP
> > >
> > > running an SP That has 86 SQL (UPDATE/DELTE/INSERT/DROP) queries in it
> > > The query in question is at no 23( 23rd from top)
> > >
> > > Total time taken by SP is 25 Min
> > >
> > > The query at No 23 , shows 95.97% as relative to bacth
> > > So i calculated that it is taking around 23 Minutes as 95.97% of time
is
> > taken by this query
> > > ? is my method of computing time based on 95.97 % Correct
> > > ? Next when i run this query in a sperate window, it runs under 2
> Minutes
> > >
> > > Not able to identify , what is wrong
> > >
> > > Wanted to speed the process of execution time for the SP
> > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: [RFC] Limit the size of the pagecache
    ... I have gotten repeatedly requests to be able to limit the pagecache. ... It may be useful to limit the size of the page cache for various reasons ... Insure rapid turnaround of pages in the cache. ... Send instant messages to your online friends http://au.messenger.yahoo.com - ...
    (Linux-Kernel)
  • Re: SP Execution Very Slow
    ... Sorry to be pedantic Quentin:) just to add that if this is on a production ... server dbcc free proccache and drop cleanbuffers will severely affect all ... procedure cache as this is global to the server. ...
    (microsoft.public.sqlserver.programming)
  • Re: which OutputStreams are buffered?
    ... I guess one of the reasons is that it can be very difficult to ... implement an API that make it 100% sure the data is at location that ... Cache in RAID controllers, cache in disk drives, ... in the war against terrorism and a new information agency would help ...
    (comp.lang.java.programmer)
  • Re: Dont use S-boxes!
    ... The most extreme AES encryption tables I've seen are ... > Where I come from this response would be considered VULGAR. ... >>the size of the L1 cache is not one of those reasons. ...
    (sci.crypt)
  • Re: Migration away from mvbase?
    ... and Cache, and I'm heavily bent toward Cache in this case. ... well as the advantages / disadvantages you found with migrating. ... Main reasons for looking at migrating: ...
    (comp.databases.pick)