要检查慢速运行SQL查询的事情 [英] Things to check for a slow running SQL query

问题描述

我在存储过程中嵌入了SQL查询。每天运行时需要30分钟才能执行。但对于过去的一周，该查询的执行时间正在增加，如第2天需要40分钟，第3天45分钟和第4天55分钟等等...我不知道为什么它的表现如此，任何建议是什么当我遇到这种情况时我需要寻找吗？



存储过程只包含SELECT的大查询。



我尝试了什么：



我刚刚查找了SELECT中使用的表上的任何锁定查询。

解决方案

正如Dave Kreskowiak所说，有很多事情要检查。



我' d建议按照 CodeProject知识库 [ ^ ]。例如：

提高绩效使用SQL Server的查询：第1部分 [ ^ ]

使用SQL Server提高查询性能：第2部分 [ ^ ]

SQL Server性能提示和指南 [ ^ ]

有一堆s可能导致问题的凝灰岩，来自写得不好的查询，编写的存储过程写得不好，视图编写不当，表格索引设置不当，服务器上的资源问题，......



我们没有什么可以继续下去，所以你无法得到像这样的明确答案并期望它能解决任何问题。



你的第一个步骤是运行SQL事件探查器以了解瓶颈所在。看看执行计划，并与DBA一起帮助您。

可能是您的索引需要维护，您可以通过右键单击SQL Server Management Studio来执行此操作。索引并选择' rebuild '或'重新组织'。

请参阅：如何：重建索引（SQL Server Management Studio） [ ^ ]

请参阅：重组和重建索引 [< a href =https://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx\"target =_ blanktitle =New Window> ^ ]

I have a SQL query embedded in a stored procedure. Everyday it takes 30 min of run time for its execution. but for the past on week the execution time for that query is increasing like day 2 it took 40 min and day three 45 min and day 4 55 min and so on... i am not sure why its behaving like that and any suggestions what I need to look for when I encounter this kind of scenarios?

The Stored procedure contains just a big query of SELECT.

What I have tried:

I just looked for any locks on the tables that is being used in the SELECT query.

解决方案

As Dave Kreskowiak mentioned, there's a lot of things to check.

I'd suggest to follow the instructions provided on the CodeProject Knowledge Base[^]. For example:
Improving the performance of queries using SQL Server: Part 1[^]
Improving the performance of queries using SQL Server: Part 2[^]
SQL Server Performance Tips and Guidelines[^]

There's a bunch of stuff that can cause problems, from a poorly written query, poorly written stored procedures, poorly written views, improper indexing setup on tables, resource problems on the server, ...

We have nothing to go on at all so there's no way you're getting a definite answer like do this" and expect it to fix anynthing.

Your first step would be to run the SQL Profiler to get an idea of where the bottlenecks are. Take a look at the execution plans and get with a DBA to help you on this.

It could be your indexes need maintenance, you can do this from SQL Server Management Studio by right clicking the index and select 'rebuild' or 'reorganize'.
See: How to: Rebuild an Index (SQL Server Management Studio)[^]
See: Reorganizing and Rebuilding Indexes[^]

这篇关于要检查慢速运行SQL查询的事情的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！