In one of my applications I have a 1gb database table that is used for reference data. It has a huge amounts of reads coming off that table but there are no writes ever. I was wondering if there's any way that data could be loaded into RAM so that it doesn't have to be accessed from disk?
在我的一个应用程序中,我有一个1gb数据库表,用于参考数据。它从表中读出了大量的读数,但是从来没有写过。我想知道是否有任何方法可以将数据加载到RAM中,以便不必从磁盘访问它?
I'm using SQL Server 2005
我正在使用SQL Server 2005
4 个解决方案
#1
5
If you have enough RAM, SQL will do an outstanding job determining what to load into RAM and what to seek on disk.
如果你有足够的内存,SQL将做出出色的工作,确定加载到RAM中的内容以及在磁盘上搜索的内容。
This question is asked a lot and it reminds me of people trying to manually set which "core" their process will run on -- let the OS (or in this case the DB) do what it was designed for.
这个问题被问了很多,它让我想起人们试图手动设置他们的进程将运行的“核心” - 让操作系统(或者在这种情况下是数据库)完成它的设计目的。
If you want to verify that SQL is in fact reading your look-up data out of cache, then you can initiate a load test and use Sysinternals FileMon, Process Explorer and Process Monitor to verify that the 1GB table is not being read from. For this reason, we sometimes put our "lookup" data onto a separate filegroup so that it is very easy to monitor when it is being accessed on disk.
如果要验证SQL实际上是从缓存中读取查找数据,那么您可以启动负载测试并使用Sysinternals FileMon,Process Explorer和Process Monitor来验证是否未读取1GB表。出于这个原因,我们有时会将“查找”数据放在一个单独的文件组中,以便在磁盘*问它时非常容易监视。
Hope this helps.
希望这可以帮助。
#2
2
You're going to want to take a look at memcached. It's what a lot of huge (and well-scaled) sites used to handle problems just like this. If you have a few spare servers, you can easily set them up to keep most of your data in memory.
你想要看看memcached。这就是许多大型(和规模较大的)网站用来处理问题的原因。如果您有一些备用服务器,您可以轻松设置它们以将大部分数据保存在内存中。
http://en.wikipedia.org/wiki/Memcached
#3
1
Just to clarify the issue for the sql2005 and up:
只是为了澄清sql2005及以上的问题:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
此功能是为SQL Server 6.5版中的性能而引入的。 DBCC PINTABLE具有非常不必要的副作用。这些包括可能损坏缓冲池。 DBCC PINTABLE不是必需的,已被删除以防止出现其他问题。此命令的语法仍然有效,但不会影响服务器。
#4
-1
DBCC PINTABLE will explicitly pin a table in core if you want to make sure it remains cached.
如果要确保缓存,DBCC PINTABLE将明确地将表固定在核心中。
#1
5
If you have enough RAM, SQL will do an outstanding job determining what to load into RAM and what to seek on disk.
如果你有足够的内存,SQL将做出出色的工作,确定加载到RAM中的内容以及在磁盘上搜索的内容。
This question is asked a lot and it reminds me of people trying to manually set which "core" their process will run on -- let the OS (or in this case the DB) do what it was designed for.
这个问题被问了很多,它让我想起人们试图手动设置他们的进程将运行的“核心” - 让操作系统(或者在这种情况下是数据库)完成它的设计目的。
If you want to verify that SQL is in fact reading your look-up data out of cache, then you can initiate a load test and use Sysinternals FileMon, Process Explorer and Process Monitor to verify that the 1GB table is not being read from. For this reason, we sometimes put our "lookup" data onto a separate filegroup so that it is very easy to monitor when it is being accessed on disk.
如果要验证SQL实际上是从缓存中读取查找数据,那么您可以启动负载测试并使用Sysinternals FileMon,Process Explorer和Process Monitor来验证是否未读取1GB表。出于这个原因,我们有时会将“查找”数据放在一个单独的文件组中,以便在磁盘*问它时非常容易监视。
Hope this helps.
希望这可以帮助。
#2
2
You're going to want to take a look at memcached. It's what a lot of huge (and well-scaled) sites used to handle problems just like this. If you have a few spare servers, you can easily set them up to keep most of your data in memory.
你想要看看memcached。这就是许多大型(和规模较大的)网站用来处理问题的原因。如果您有一些备用服务器,您可以轻松设置它们以将大部分数据保存在内存中。
http://en.wikipedia.org/wiki/Memcached
#3
1
Just to clarify the issue for the sql2005 and up:
只是为了澄清sql2005及以上的问题:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
此功能是为SQL Server 6.5版中的性能而引入的。 DBCC PINTABLE具有非常不必要的副作用。这些包括可能损坏缓冲池。 DBCC PINTABLE不是必需的,已被删除以防止出现其他问题。此命令的语法仍然有效,但不会影响服务器。
#4
-1
DBCC PINTABLE will explicitly pin a table in core if you want to make sure it remains cached.
如果要确保缓存,DBCC PINTABLE将明确地将表固定在核心中。