SQL Server缓存问题 [英] SQL Server cache question

问题描述

当我第一次运行某个存储过程时，大约需要2分钟才能完成。当我第二次运行它时，它在约15秒内完成。我认为这是因为所有内容在首次运行后都会被缓存。在我第一次运行此过程之前，是否可以预热缓存？是仅当我再次使用相同的参数调用相同的存储过程时才使用缓存的信息，还是如果我使用不同的参数调用相同的存储过程时将使用缓存的信息？

When I run a certain stored procedure for the first time it takes about 2 minutes to finish. When I run it for the second time it finished in about 15 seconds. I'm assuming that this is because everything is cached after the first run. Is it possible for me to "warm the cache" before I run this procedure for the first time? Is the cached information only used when I call the same stored procedure with the same parameters again or will it be used if I call the same stored procedure with different params?

推荐答案

执行查询时，数据将以块的形式读入内存。这些块保留在内存中，但是会老化。这意味着这些块被标记为具有最后访问权限，并且当Sql Server需要另一个块进行新查询并且内存缓存已满时，最近使用最少的块（最旧的块）就会被踢出内存。 （在大多数情况下-全表扫描块会立即老化，以防止全表扫描超出内存并阻塞服务器）。

When you peform your query, the data is read into memory in blocks. These blocks remain in memory but they get "aged". This means the blocks are tagged with the last access and when Sql Server requires another block for a new query and the memory cache is full, the least recently used block (the oldest) is kicked out of memory. (In most cases - full tables scan blocks are instantly aged to prevent full table scans overrunning memory and choking the server).

这里发生的是，第一个查询中的内存中的数据块尚未被踢出内存，因此可以用于第二个查询，即磁盘避免访问并提高性能。

What is happening here is that the data blocks in memory from the first query haven't been kicked out of memory yet so can be used for your second query, meaning disk access is avoided and performance is improved.

所以您真正要问的是是否可以将需要的数据块读入内存而不将其读入内存（实际上是在执行查询）？。答案是否定的，除非您要缓存整个表并将它们永久地驻留在内存中，否则从您描述的查询时间（以及数据大小）出发，这可能不是一个好主意。

So what your question is really asking is "can I get the data blocks I need into memory without reading them into memory (actually doing a query)?". The answer is no, unless you want to cache the entire tables and have them reside in memory permanently which, from the query time (and thus data size) you are describing, probably isn't a good idea.

提高性能的最佳选择是查看查询执行计划，看看是否更改索引可能会带来更好的结果。这里有两个可以提高性能的主要领域：

Your best bet for performance improvement is looking at your query execution plans and seeing whether changing your indexes might give a better result. There are two major areas that can improve performance here:

• 创建索引，在该索引中查询可以使用该索引来避免低效率的查询和全表扫描


• 向索引添加更多列，以避免再次读取磁盘。例如，您有一个查询，该查询返回A和B列以及A和C上的where子句，并且在A列上有一个索引。查询将对A列使用索引，需要读取一个磁盘，但又需要第二个磁盘如果索引包含所有A，B和C列，则可以避免第二个磁盘获取数据。

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