Sql server 2012全内存缓冲区，查询速度慢 [英] Sql server 2012 full memory buffer, slow query

问题描述

大家好，

我编写了一个程序，每10秒点击一次sql server 2012 db中的查询，然后刷新图形页面;此页面是来自数据库中4个不同表的单个记录的函数。



数据库已将其最大大小设置为1024 MB，而其最小大小为0。

数据库每分钟保存一行，而不是仅仅1个月每个表就有超过50.000条记录，因此不断增长的内存消耗（通过Windows性能测量仪观察）会提高最大大小。



当达到最大值时，从这4个表中选择的后续查询（仅记录）大约需要15秒，UI看起来像是冻结的。

我观察到，只有一条记录被问到，SQL返回整个表;我认为因为它被缓存...



有什么建议可以提高速度吗？



谢谢< br $> b $ b

我尝试了什么：



DBCC ...缓冲区干净。 .. eccetera

Hi all,
i have written a program that each 10 seconds hit a query in sql server 2012 db and than refresh a graphic page; this page is function of a single record from 4 different table in the database.

The db has set his maximum size as 1024 MB, while his minimum size is 0.
The db save a row each minutes, than in just 1 Month there are over 50.000 record each table, so the growing memory consumption (observed by Windows perfomance meter) raise the maximum size.

When the maximum has achieved, a later query (just a record) selected from these 4 table, takes about 15 seconds and the UI looks like freezed.
I have observed that instead a single record asked, SQL returns the entire table; i think cause it is cached...

Any suggestion to improve the speed?

Thanks

What I have tried:

DBCC... buffer clean... eccetera

推荐答案

1 - 确保您的查询已经过优化，例如避免SELECT *您应该单独包含您需要的特定列。



2 - 分析查询执行计划



3 - 在SQL调优模式下配置数据库并尝试使用数据库引擎调整顾问



4 - 如果可以增加SQL可以使用的内存

1 - Make sure that your query is optimized, for example avoid SELECT * you should individually include the specific columns that you need.

2 - Analyse query Execution Plan

3 - Profile your database on SQL Tuning mode and try to use Database Engine Tuning Advisor

4 - If is possible increase memory that SQL can use

这篇关于Sql server 2012全内存缓冲区，查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！