Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bobanjayan
Yak Posting Veteran
67 Posts |
 Posted - 2004-04-02 : 06:34:23
|
| Hi friends,I am very new in MS-SQL Server.Can anybody tell me how to pin procedures into memory?Regards.BobanBoban |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2004-04-02 : 07:06:16
|
| in MS-SQL server the execution plan for the stored procedure is already saved in the procedure cache when it is compiled until the cache is made free or a new execution plan is available. u can explictly pin tables using dbcc pintable.check out:http://www.extremeexperts.com/sql/articles/SQLCacheObjects.aspxHe is a fool for five minutes who asks , but who does not ask remains a fool for life! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-02 : 12:18:41
|
| And pinning of tables isn't recommended by MS. The command DBCC PINTABLE and DBCC UNPINTABLE will be no-op in SQL Server 2005.Tara |
|
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-03 : 00:58:17
|
| Thanks for the reply.But I have some more doubts.Where is the procedure cache configured ? Is there any parameter file ?What happends when the procedure cache get overflowed?BobanBoban |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-03 : 01:21:21
|
| The procedure cache is managed by SQL Server. It cannot get overflowed because it's dynamically managed to not overflow. It stores execution plans for the stored procedures and as plans recompile, age, or space is needed for other procedures these plans move out of the procedure cache.DBCC PROCCACHE will give you detailed information about the proccache on your machine as will DBCC MEMUSAGE. You can also monitor it with performance monitor.If the counters are bad for the procedure cache, they will generally also be bad for other memory counters. You will need to buy more RAM for the system, or tune items in the database to make them more friendly to the memory subsystem.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
Pinning procedures in memory