I am running a really simple query on a really small table (<10 rows, 5 columns) on SQL Server 2005 and usually it returns results instantly, but sometimes it takes very long to complete (like 5-10s). I am aware, that our server is quite heavily loaded and this is probably the cause (as I don't think that it can happen because of locks - nobody's writing to that table) - but I need to find the bottleneck somehow.
我在SQL Server 2005上的一个非常小的表（<10行，5列）上运行一个非常简单的查询，通常它会立即返回结果，但有时需要很长时间才能完成（如5-10秒）。我知道，我们的服务器负载很重，这可能是原因（因为我认为它不会因锁而发生 - 没有人写入该表） - 但我需要以某种方式找到瓶颈。
Any suggestions on how could I find the exact server resource, that's making such simple queries run so long?
关于如何找到确切的服务器资源的任何建议，这样做这么简单的查询运行这么久？
4 个解决方案
#1
1
The only thing that you need is profiling. You need to have an idea about memory, input/output and processor. You need to know which of these 3 is causing the server to slow down. There are a lot of products that does it (there is even an okay performance monitor that comes with windows installed).
你唯一需要的是分析。您需要了解内存，输入/输出和处理器。您需要知道这3个中的哪个导致服务器速度变慢。有很多产品可以做到这一点（甚至还有一个安装了Windows的性能监视器）。
Don't "think" about it, you need to see data in order to understand the fundamental issue.
不要“思考”它，你需要查看数据才能理解根本问题。
#2
5
In SSMS right-click connected server in Object Explorer and choose Activity Monitor.
在SSMS中，在对象资源管理器中右键单击已连接的服
There you can see Recent Expensive Queries and other performance data.
在那里，您可以看到最近的昂贵查询和其他性能数据。
#3
4
In addition to running profiler and checking also for page life expectancy and also buffer cache hit ratio See : Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters
除了运行探查器并检查页面预期寿命以及缓冲区缓存命中率之外，请参阅：使用sys.dm_os_performance_counters获取缓冲区缓存命中率和页面预期寿命计数器
it could also be (but you have to test) is what happens is that the data you are looking for got bumped out of the cache in RAM, now it has to get it from disk and that will take longer, when you run it again a second later it will be fast again
它也可能（但你必须测试）发生的是你正在寻找的数据被缓存在RAM中的缓存中，现在它必须从磁盘中获取它并且这将需要更长时间，当你再次运行它时一秒钟后它会再次快速
you can check by running with statistics io on
你可以通过运行统计io来检查
SET STATISTICS IO ON
select * ..your query
you should see something like this
你应该看到这样的东西
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2
表'TableNAme'。扫描计数1，逻辑读取4，物理读取2
if you see physical reads above 0, it grabbed it from disk
如果你看到物理读数高于0，它会从磁盘中抓取它
you can verify this (not on production)
你可以验证这个（不是生产）
by dropping the data from RAM
从RAM中删除数据
DBCC freeproccache
DBCC DROPcleanbuffers
now when you run a query twice, you will see something like this, the first run will be from disk, the second from RAM
现在，当您运行两次查询时，您将看到类似这样的内容，第一次运行将来自磁盘，第二次运行来自RAM
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2
表'TableNAme'。扫描计数1，逻辑读取4，物理读取2
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 0
表'TableNAme'。扫描计数1，逻辑读取4，物理读取0
#4
1
You don't need to write to the table to get a lock on it. You could try modifying one of your SELECT statements to use WITH (NOLOCK) Another statement (insert/update/delete) which is very slow and joins to this table might be locking it.
您不需要写入表来获取锁定。您可以尝试修改一个SELECT语句以使用WITH（NOLOCK）另一个语句（插入/更新/删除）非常慢并且加入此表可能会锁定它。
#1
1
The only thing that you need is profiling. You need to have an idea about memory, input/output and processor. You need to know which of these 3 is causing the server to slow down. There are a lot of products that does it (there is even an okay performance monitor that comes with windows installed).
你唯一需要的是分析。您需要了解内存，输入/输出和处理器。您需要知道这3个中的哪个导致服务器速度变慢。有很多产品可以做到这一点（甚至还有一个安装了Windows的性能监视器）。
Don't "think" about it, you need to see data in order to understand the fundamental issue.
不要“思考”它，你需要查看数据才能理解根本问题。
#2
5
In SSMS right-click connected server in Object Explorer and choose Activity Monitor.
在SSMS中，在对象资源管理器中右键单击已连接的服
There you can see Recent Expensive Queries and other performance data.
在那里，您可以看到最近的昂贵查询和其他性能数据。
#3
4
In addition to running profiler and checking also for page life expectancy and also buffer cache hit ratio See : Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters
除了运行探查器并检查页面预期寿命以及缓冲区缓存命中率之外，请参阅：使用sys.dm_os_performance_counters获取缓冲区缓存命中率和页面预期寿命计数器
it could also be (but you have to test) is what happens is that the data you are looking for got bumped out of the cache in RAM, now it has to get it from disk and that will take longer, when you run it again a second later it will be fast again
它也可能（但你必须测试）发生的是你正在寻找的数据被缓存在RAM中的缓存中，现在它必须从磁盘中获取它并且这将需要更长时间，当你再次运行它时一秒钟后它会再次快速
you can check by running with statistics io on
你可以通过运行统计io来检查
SET STATISTICS IO ON
select * ..your query
you should see something like this
你应该看到这样的东西
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2
表'TableNAme'。扫描计数1，逻辑读取4，物理读取2
if you see physical reads above 0, it grabbed it from disk
如果你看到物理读数高于0，它会从磁盘中抓取它
you can verify this (not on production)
你可以验证这个（不是生产）
by dropping the data from RAM
从RAM中删除数据
DBCC freeproccache
DBCC DROPcleanbuffers
now when you run a query twice, you will see something like this, the first run will be from disk, the second from RAM
现在，当您运行两次查询时，您将看到类似这样的内容，第一次运行将来自磁盘，第二次运行来自RAM
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2
表'TableNAme'。扫描计数1，逻辑读取4，物理读取2
Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 0
表'TableNAme'。扫描计数1，逻辑读取4，物理读取0
#4
1
You don't need to write to the table to get a lock on it. You could try modifying one of your SELECT statements to use WITH (NOLOCK) Another statement (insert/update/delete) which is very slow and joins to this table might be locking it.
您不需要写入表来获取锁定。您可以尝试修改一个SELECT语句以使用WITH（NOLOCK）另一个语句（插入/更新/删除）非常慢并且加入此表可能会锁定它。