Re: Avoiding the use of CACHE in SQL2000

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

From: AA (aa_at_personal.net.py)
Date: 06/08/04 

Date: Tue, 8 Jun 2004 06:04:58 -0400

I'll read each command. :)

Thanks a lot Uri

AA

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:%23Lzhe0TTEHA.3944@TK2MSFTNGP12.phx.gbl...
> AA
> I see what you mean
> DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
> be recompiled
>
> if you want a stored procedure to be compiled you will need to use the
WITH
> RECOMPILE option
>
> if you want to clear the data cache you will need to use DBCC
> DROPCLEANBUFFERS
>
> DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
> specific database on a SQL Server, not the entire SQL Server. The database
> ID number to be affected must be entered as part of the command.
>
> You may want to use this command before testing to ensure that previous
> stored procedure plans won't negatively affect testing results.
>
> Example:
>
> DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM
> master.dbo.sysdatabases WHERE name = 'database_name')
> DBCC FLUSHPROCINDB (@intDBID)
>
>
>
>
> "AA" <aa@personal.net.py> wrote in message
> news:%23VV77tTTEHA.1232@TK2MSFTNGP09.phx.gbl...
> > To the production /live database has only access the user of our System
> > but to the historical database has access peoples of marketing etc, to
> make
> > simple query, usually two or three times per week, and that is the
> problem,
> > I don't want that any query (including not optimized queries) in the
> > historical database affect the live (production) database.
> >
> > I can't limit the CPU and I can't limit the use of Memory so, I want to
> > limit at least the use of CACHE
> >
> > Any suggest?
> >
> > Two instances?
> >
> > I have a SQL2000 and Windows 2003 in cluster.
> >
> > Thanks a lot
> >
> > AA
> >
> >
> > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > news:e6OfBNSTEHA.1368@TK2MSFTNGP11.phx.gbl...
> > > AA
> > > I think if you do a query against your historical database for
instance
> > > some stored procedure so optimyzer will insert into the cache the
> > execution
> > > plan and you cannot prevent it. What do you try to accomplish? Don't
you
> > > have enough memory for your live database?
> > >
> > >
> > > "AA" <aa@personal.net.py> wrote in message
> > > news:OmG0RESTEHA.332@TK2MSFTNGP11.phx.gbl...
> > > > Do exists the possibility to avoid the use of cache for certain
> Database
> > > or
> > > > Query
> > > >
> > > > I have a production Database and a historical database.
> > > > I want to cache all transaction in the production database, but
avoid
> > the
> > > > cache in the historical database.
> > > >
> > > > Is possible to do that with SQL2000?
> > > >
> > > >
> > > > Thanks a lot
> > > >
> > > > AA
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: DBCC error 913
    ... Then I run the command sp_dboption dbname,'Single user',true also. ... this command runs inside a cursor and DBCC checkdb commands starts after that. ... >> This is simple DBCC script I am running on the server every sunday. ... >> database is online and in live. ...
    (microsoft.public.sqlserver.server)
  • Re: How to deny DBCC SQLPERF(logspace)
    ... DBCC statements that act as Database Console Commands for Microsoft? ... I know that if we can find the place where the permissions of each DBCC ... command stored, we can update the table directly to change the permissions. ...
    (microsoft.public.sqlserver.security)
  • dbcc shrinkdatabase
    ... I'm trying to run the "dbcc shrinkdatabase" command on a specific database. ... I don't see any error messages but its obvious that the command never ran. ...
    (comp.databases.ms-sqlserver)
  • Re: Shrinkfile does not find the file
    ... could you execute the below ... command to get the logical name for that database. ... After that use this logical name in DBCC SHRINKFILE command and try. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 --> Replicate Production DB to Development DB
    ... The DBCC CHECKIDENT command now ... SQL2005 SP2 fixes a DBCC CHECKIDENT bug with the exact symtoms that you ... We are using push replication so it looks like I will have to execute the ...
    (microsoft.public.sqlserver.replication)