Is there a way to tell SQL Server to not cache certain types of plans? For example, if you have a large number of users queries coming from SQL Server Management Studio, is there a way to tell these to not be cached in the plan cache, thus taking up memory and other resources? Are there other behavioral tweaks that you can do to prevent adhoc queries from taking up resources on a production server?
有没有办法告诉SQL Server不缓存某些类型的计划?例如,如果您有来自SQL Server Management Studio的大量用户查询,是否有办法告诉这些查询不会缓存在计划缓存中,从而占用内存和其他资源?是否还有其他行为调整可以防止临时查询占用生产服务器上的资源?
3 个解决方案
#1
1
I don't know of any ... but, in general, I think that in most high-performance situations where you'd be worried about this level of detail, you'd probably want a production database that is protected from user-submitted queries in the first place. In production, it's not the little bit of memory for these query plans that will hurt you, it's users submitting queries that issue massive locks and grind your performance to a halt. As a basic architectural issue, you might consider either:
我不知道有什么...但是,一般来说,我认为在大多数高性能情况下,你会担心这种细节,你可能想要一个不受用户保护的生产数据库 - 首先提交了查询。在生产中,这些查询计划的内存不会对您造成伤害,而是用户提交的查询会发出大量锁定并使您的性能停滞不前。作为一个基本的架构问题,您可以考虑:
- getting a replicated offline server that users can hit with their ad-hoc queries; or
- restricting user interactions with the DB to a set of given stored procedures
获得一个复制的离线服务器,用户可以使用他们的临时查询来获取;要么
限制用户与DB的交互到一组给定的存储过程
If you really can't do either of those, and user-submitted query plans are really getting you down, you must be in a pretty unique situation. If you want to edit the question with more details, you might get better suggestions.
如果您真的无法做到这些,并且用户提交的查询计划确实让您失望,那么您必须处于非常独特的情况。如果您想要编辑更详细的问题,可能会得到更好的建议。
(Sorry I didn't answer the question you actually asked; hopefully someone more knowledgeable than me will do so.)
(对不起,我没有回答你实际问过的问题;希望比我更了解情况的人会这样做。)
#2
1
You can affect how SQL server treats adhoc queries and the caching of plans by setting the 'optimise for adhoc workloads' setting as per http://msdn.microsoft.com/en-us/library/cc645587.aspx
您可以根据http://msdn.microsoft.com/en-us/library/cc645587.aspx设置“优化adhoc工作负载”设置,从而影响SQL Server处理adhoc查询和计划缓存的方式
In essence what this does is to say 'the first time that an adhoc query is called, don't cache the plan but store that this query has been called. The second time that the same adhoc query is called the plan will e cached.
本质上,这是说'第一次调用adhoc查询时,不要缓存计划,而是存储已调用此查询。第二次调用相同的adhoc查询时,计划将被缓存。
We set this on one of our production servers and freed up 2 GB of RAM straight away, which says more about the current workload against that server than anything (and not good things at that)
我们在我们的一个生产服务器上设置了这个,并立即释放了2 GB的RAM,这更多地说明了针对该服务器的当前工作负载(而不是那些好东西)
#3
0
I think it's probably unnecessary to worry about restricting this, but you can start here and the discussion linked there
我认为可能没有必要担心限制这一点,但你可以从这里开始,讨论与此相关联
#1
1
I don't know of any ... but, in general, I think that in most high-performance situations where you'd be worried about this level of detail, you'd probably want a production database that is protected from user-submitted queries in the first place. In production, it's not the little bit of memory for these query plans that will hurt you, it's users submitting queries that issue massive locks and grind your performance to a halt. As a basic architectural issue, you might consider either:
我不知道有什么...但是,一般来说,我认为在大多数高性能情况下,你会担心这种细节,你可能想要一个不受用户保护的生产数据库 - 首先提交了查询。在生产中,这些查询计划的内存不会对您造成伤害,而是用户提交的查询会发出大量锁定并使您的性能停滞不前。作为一个基本的架构问题,您可以考虑:
- getting a replicated offline server that users can hit with their ad-hoc queries; or
- restricting user interactions with the DB to a set of given stored procedures
获得一个复制的离线服务器,用户可以使用他们的临时查询来获取;要么
限制用户与DB的交互到一组给定的存储过程
If you really can't do either of those, and user-submitted query plans are really getting you down, you must be in a pretty unique situation. If you want to edit the question with more details, you might get better suggestions.
如果您真的无法做到这些,并且用户提交的查询计划确实让您失望,那么您必须处于非常独特的情况。如果您想要编辑更详细的问题,可能会得到更好的建议。
(Sorry I didn't answer the question you actually asked; hopefully someone more knowledgeable than me will do so.)
(对不起,我没有回答你实际问过的问题;希望比我更了解情况的人会这样做。)
#2
1
You can affect how SQL server treats adhoc queries and the caching of plans by setting the 'optimise for adhoc workloads' setting as per http://msdn.microsoft.com/en-us/library/cc645587.aspx
您可以根据http://msdn.microsoft.com/en-us/library/cc645587.aspx设置“优化adhoc工作负载”设置,从而影响SQL Server处理adhoc查询和计划缓存的方式
In essence what this does is to say 'the first time that an adhoc query is called, don't cache the plan but store that this query has been called. The second time that the same adhoc query is called the plan will e cached.
本质上,这是说'第一次调用adhoc查询时,不要缓存计划,而是存储已调用此查询。第二次调用相同的adhoc查询时,计划将被缓存。
We set this on one of our production servers and freed up 2 GB of RAM straight away, which says more about the current workload against that server than anything (and not good things at that)
我们在我们的一个生产服务器上设置了这个,并立即释放了2 GB的RAM,这更多地说明了针对该服务器的当前工作负载(而不是那些好东西)
#3
0
I think it's probably unnecessary to worry about restricting this, but you can start here and the discussion linked there
我认为可能没有必要担心限制这一点,但你可以从这里开始,讨论与此相关联