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 |
|
durairaj.arumugam
Starting Member
6 Posts |
 Posted - 2008-06-20 : 07:53:04
|
| Any one can tell me about procedure cache in sql 2000?I want to know how to check the performance of the stored procedure executed using syscacheobjects table. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-20 : 22:55:36
|
| You should check performance in execution plan and i/o statistics. |
 |
|
|
durairaj.arumugam
Starting Member
6 Posts |
Posted - 2008-06-21 : 01:36:09
|
| Ok thanx. How to check the procedure cache hit ratio for the particular sp. and what should be % of hit ratio. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-06-21 : 06:24:01
|
| Try running the SET STATISTICS IO ON and analyse the output . This will give you an idea of how much is being read from memory or diskJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-21 : 15:49:13
|
| You can't check the procedure cache hit ratio for the particular sp, but can query syscacheobjects to see if the sp is there. |
|
|
|
durairaj.arumugam
Starting Member
6 Posts |
Posted - 2008-06-22 : 23:48:46
|
| Can you pls explain more if sp is present in syscacheobjects then what is the meaning of that. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-23 : 22:10:41
|
| If it's there, means execution plan is cached. |
|
|
|
durairaj.arumugam
Starting Member
6 Posts |
Posted - 2008-06-24 : 00:03:22
|
| Thanx. Suppose I executed 10 sp's. One sp is present in syscacheobjects and other 9 sp's are not present in this table. This means I sp has got existing execution plan and other 9 sp's are created new execution plan. correct? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-24 : 23:22:03
|
| Yes, will build plan when run next time. |
|
|
|
durairaj.arumugam
Starting Member
6 Posts |
Posted - 2008-06-25 : 04:00:47
|
| Thanks for your respond. |
|
|
|
|
|
|
Procedure cache