SQL Server 2000 caching

I have one question in order to speed up SQL Server 2000.

I want to use caching mechanism, but I don't know how to use.

I found some articles about it, but can you give an example for how to use.

For example:

there is a stored pro开发者_高级运维cedure - sp_stackOverFlow - it executes when every user enter to the program/web site and it is clear it makes slower running.

Is there a way of caching sp_stackOverFlow in every 2 minutes or another?

Your question isn't clear, not least because it isn't obvious what the stored procedure does. If the results are different for every execution and/or user then they cannot easily be cached anyway.

But more fundamentally, "I have a slow stored procedure" does not automatically mean "I need caching"; the database engine itself already caches data when it can. You need to understand why the stored procedure is running slowly: underpowered hardware, poor TSQL code, poor data model design and poor indexing are all very common issues that have major effects on performance.

You can find a lot of information on this site and by Googling about how to troubleshoot slow execution times for procedures, but you can start by reviewing the execution plan for the procedure in Query Analyzer and tracing the execution using Profiler. That will immediately tell you which statements are taking the most time, if there are table scans happening etc.

Because performance troubleshooting is potentially complex, if you need more assistance please post short, specific questions about individual issues. If the code for your stored procedure is very short (< 30 lines formatted) people may be willing to comment on it directly, otherwise it would be better to post only the individual SQL statements that are causing a problem.

Finally, mainstream support for MSSQL 2000 stopped 3 years ago, so you should definitely look into upgrading to newer version. The performance tools in newer versions will make resolving your issue much easier.