存储过程执行时间增加50％ [英] Stored procedure execution time increased by 50%

问题描述

我有一个包含插入语句的Spoc。没有代码更改。但是自从过去两周以来，它的执行时间增加了50％。是否有人能够帮助我理解可能导致这种情况的原因？。

I have a Spoc that has insert statements. There was no code change. But since past 2 weeks it has been taking 50% more time to execute. Would anybody be able to help me in understanding what could have made this?.

推荐答案

仅仅通过查看问题，很难确定究竟是什么原因。



但是，你需要考虑以下因素：



1.）有没有数据集的重大变化，即数据库/表的数据量。



2.）通常，SQL Server会为存储过程保留最佳执行计划。清除执行计划，修补等会重置这些计划并创建新计划。您的新计划可能会做出不同或不正确的假设。检查执行计划 [ ^ ]使用SQL事件探查器。



3.）请使用 SQL Profiler [ ^ ]了解正在发生的事情。这是你最好的选择。



4.）了解没有代码变更不是影响绩效的唯一因素，而且涉及很多其他因素。

Just by looking at the question, it's very difficult to pinpoint what is the real reason.

However, you need to look at the following factors:

1.) Has there been a substantial change in the dataset i.e. the amount of data your database/tables.

2.) Usually, SQL Server keeps an optimal execution plan saved for the stored procs. Clearing the execution plan, patching etc. resets those plans and a new plan is created. You new plan might be making different or incorrect assumptions. Check the execution plan [^]using SQL Profiler.

3.) Please use SQL Profiler[^] to see what's going on. It's your best bet.

4.) Understand that NO CODE CHANGE is not the only factor which impacts performance and their are lot of other factors involved.

我同意Manas Bhardwaj并建议您阅读数据库/查询性能 [ ^ ]。几乎没有办法改善查询执行的时间。

I agree with Manas Bhardwaj and suggest you to read about database / query performance[^]. There is few ways to improve the time of query execution.

这篇关于存储过程执行时间增加50％的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！