防止在SQL Server中进行缓存 [英] Prevent Caching in SQL Server

问题描述

使用Google叔叔环顾四周，我找不到以下问题的答案：

Having looked around the net using Uncle Google, I cannot find an answer to this question:

监视生产服务器的性能和响应性的最佳方法是什么？正在运行IIS和MS SQL Server 2005？

What is the best way to monitor the performance and responsiveness of production servers running IIS and MS SQL Server 2005?

我目前正在使用Pingdom，并且希望它指向一个基本上模仿真实世界查询的URL，但显然原因不希望查询从缓存中运行。该URL将每5分钟被调用一次。

I'm currently using Pingdom and would like it to point to a URL which basically mimics a 'real world query' but for obvious reasons do not want the query to run from cache. The URL will be called every 5 minutes.

我无法清除缓存，缓冲区等，因为这会对生产服务器产生负面影响。我尝试在SELECT语句中使用随机生成的数字以生成唯一查询，但是仍使用缓存的查询。

I cannot clear out the cache, buffers, etc since this would impact negatively on the production server. I have tried using a random generated number within the SELECT statement in order to generate unique queries, but the cached query is still used.

有没有办法在MySQL中模拟NO_CACHE？

Is there any way to simulate the NO_CACHE in MySQL?

问候

推荐答案

SQL Server没有像MySQL或Oracle这样的结果缓存，所以对于您的问题我有些困惑。如果希望服务器为存储过程重新编译计划缓存，则可以使用RECOMPILE执行它。您可以删除缓冲区缓存，但这会影响所有查询。

SQL Server does not have a results cache like MySQL or Oracle, so I am a bit confused about your question. If you want the server to recompile the plan cache for a stored procedure, you can execute it WITH RECOMPILE. You can drop your buffer cache, but that would affect all queries as you know.

在我公司，我们分别测试可用性和性能。我建议您使用此查询只是为了确保您的系统从前端到数据库协同工作，然后编写其他测试来检查各个组件以判断性能。 SQL Server提供了许多惊人的方法来检查您是否遇到瓶颈以及瓶颈在哪里。我广泛使用PerfMon和DMV。使用PerfMon，我检查了CPU和页面的预期寿命，以及查看我的磁盘队列有多长时间。使用DMV，我可以查明查询时间是否太长（sys.dm_exec_query_stats）或等待时间是否很长（sys.dm_os_wait_stats）。

At my company, we test availability and performance separately. I would suggest you use this query just to make sure you your system is working together from front-end to database, then write other tests that check the individual components to judge performance. SQL Server comes with an amazing amount of ways to check if you are experiencing bottlenecks and where they are. I use PerfMon and DMVs extensively. Using PerfMon, I check CPU and page life expectancy, as well as seeing how long my disk queue is. Using DMVs, I can find out if my queries are taking too long (sys.dm_exec_query_stats) or if wait times are long (sys.dm_os_wait_stats).

两个最大的瓶颈IIS往往是CPU和内存，而IIS带有自己的PerfMon对象套件来进行查询，但是我对它们并不熟悉。

The two biggest bottlenecks with IIS tend to be CPU and memory, and IIS comes with its own suite of PerfMon objects to query, but I am not as familiar with those.

这篇关于防止在SQL Server中进行缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！