失眠网 > 索引sql server_SQL Server索引与统计顾问的困境或麻烦

索引sql server_SQL Server索引与统计顾问的困境或麻烦

时间：2021-09-04 12:28:54

索引sql server

As a DBA, I am often asked why is something performing slow, what and why statistics need to be updated or what will cause them to be “off”. My initial question to clients when they pose these questions to me is what changed on your end? Did the data change significantly and did the rebuild or reorganize index job run? Before I get into the answers to these questions from my clients, let me give you some background. So, just to clarify, for most of my clients, I work as a remote part-time DBA, that being said, I manage their database from every aspect including setting up servers, backups/restore, troubleshooting, managing their index’s, etc. and again remotely. So normally, I have setup jobs that will manage their index’s ranging from a weekly rebuild or even sometimes I use one that I’ve designed that makes a choice to either rebuild or reorganize an index based on fragmentation level. The “general rule of thumb” is reorganizing the index for fragmentation from 5% to 29% and rebuild when 30% plus. Those are pretty standard numbers I did not make them up.

作为一名DBA，我经常被问到为什么有些东西执行缓慢，什么以及为什么需要更新统计信息，或者是什么导致它们“关闭”。当客户向我提出这些问题时，我对客户的第一个问题是，您的最终改变是什么？数据是否发生了重大变化，并且重建或重组索引作业是否运行？在我从客户那里得到这些问题的答案之前，让我给您提供一些背景知识。因此，为了澄清我的大多数客户，我是远程兼职DBA，也就是说，我从各个方面管理他们的数据库，包括设置服务器，备份/还原，故障排除，管理他们的索引等。并再次远程。因此，通常情况下，我有一些设置工作来管理其索引的范围，从每周重建一次，甚至有时我使用我设计的索引，可以根据碎片级别选择重建或重新组织索引。 “一般经验法则”是将碎片指数从5％重组到29％，并在超过30％时重建。这些是标准的数字，我没有编出来。

情况 (The Situation)

Recently on a Monday, a client came to me with a Stored Procedure (sproc) that has always been very efficient and running without issue that was having major performance issues. The sproc normally took less than 20 seconds to run was taking up to an hour and then timing out. EEEEKKK! So as I stated above, I asked my 3 standard questions……. What changed on your end? Did the data change significantly and did the rebuild or reorganize index job run? The client of course told me nothing changed. Of course nothing changed, nothing ever does! So, I logged in to take a look around. Now first, I noticed that my job that makes a decision to rebuild or reorganize ran very early Sunday morning, so before the issues started. I then checked the fragmentation levels of the tables in the sproc and they ranged from 45-78% fragmented. Normally following our rebuild/reorganize they fall under the 5-18% range, so in less than 24 hours later, there was some significant fragmentation happening. Now let me remind you, the client said nothing changed. Do I believe them? Of course NOT!

最近在一个星期一，一位客户向我提供了一个存储过程（sproc），该过程一直非常高效，并且运行时没有出现主要性能问题的问题。该存储程序通常需要不到20秒才能运行，最多需要一个小时才能超时。 EEEKEKKK！因此，如上所述，我问了3个标准问题……。你到底发生了什么变化？数据是否发生了重大变化，并且重建或重组索引作业是否运行？客户当然告诉我什么都没有改变。当然，什么都没有改变，什么也做不了！因此，我登录后环顾四周。现在首先，我注意到我决定进行重建或重组的工作很早在星期日开始进行，因此是在问题开始之前。然后，我检查了存储过程中表的碎片级别，碎片级别介于45-78％之间。通常，在我们进行重建/重组后，它们会落在5-18％的范围内，因此在不到24小时后，就会发生一些明显的碎片。现在让我提醒您，客户说什么也没变。我相信他们吗？当然不是！

So my initial suggestion on Monday was let’s start with updating the stats in the event there was a major change in the data. By updates, I meant data either coming in or out of the database. I did not want to run the rebuild index job because it takes several hours and did not want to add that “extra drag” on the system just yet. The client promised me “no major data changes occurred. Something is wrong with the server”…I was not convinced…

因此，我周一的最初建议是，在数据发生重大变化的情况下，先更新统计信息。通过更新，我的意思是数据进出数据库。我不想运行重建索引作业，因为它要花费几个小时，而且现在还不想在系统上添加该“额外的拖累”。客户向我保证“不会发生重大数据更改。服务器出了点问题” ...我不敢相信...

复制问题 (Duplicating the Issue)

So being that this is a production database, we have a protocol to follow that says we must test all changes in the test or staging environments. So we brought down a copy of production to the staging environment. When the developer ran the sproc in this environment, it was taking up to an hour to run then timing out. So great! We can duplicate the issue. I love when we can duplicate the issue because that means we can fix it! Now, being that we just duplicated the data, on a new server and still got the same issue, I had to convince her it was neither the server or a database issue and again insisted that we update stats in the staging environment as a test. They told me no, it has to be something else… Now I pride myself in my customer service skills, so I complied with the clients wishes despite my better judgement and continued on my search for the root cause of this issue. 2 days later, still coming up with nothing new, I again insisted on updating the statistics. Finally after an exhausting 2 days and a very frustrated customer, they agreed to let me update statistics.

因此，既然这是一个生产数据库，我们要遵循一个协议，该协议规定我们必须测试测试或登台环境中的所有更改。因此，我们将生产副本放到了暂存环境中。当开发人员在此环境中运行该存储库时，要花一个小时才能运行然后超时。很好！我们可以重复该问题。我喜欢我们可以复制该问题的时间，因为那意味着我们可以解决它！现在，由于我们只是在新服务器上复制了数据，并且仍然遇到相同的问题，所以我不得不说服她，这既不是服务器也不是数据库问题，并且再次坚持要在登台环境中更新统计信息作为测试。他们告诉我不，这必须是别的……现在，我以自己的客户服务技能为荣，尽管我做出了更好的判断，但我仍然遵守客户的意愿，并继续寻找该问题的根本原因。 2天后，仍然没有新内容，我再次坚持更新统计信息。最终，在经过两天的疲惫和非常沮丧的客户之后，他们同意让我更新统计信息。

结果和我的“我告诉过你”时刻…… (The Results and my “I told you so” moment…)

What I find is that many users whether they are developers or application end users is that they do not want to blame their data or their code for causes of problems but rather pass it off on the hardware, server, database or dba. This is unfortunate and frustrating for a DBA since we are there to help. So after 2 days of working with this developer and her sproc, the client finally agrees to let me update stats for the tables in question. Now, I don’t want to brag, however, first the update stats only ran for about 1 minute or so, then we ran the sproc and BAM!….it was back to running in about 20 seconds. Now this is where I start my explanation to the customer on the importance of index’s and statistics and trusting your DBA. Indexes are ever changings entities in the database based on data and data usage. I cannot express this enough. They need to be babysat consistently. Personally I like to create a report that checks fragmentation levels as well as index quality. I have shared this before so be sure to look it up. When data comes and goes index’s may or may not always be valid and the same goes for statistics. Statistics are updated when indexes are rebuilt. So that weekly job I mentioned earlier should normally control statistics getting out of control……..until like in this example, there was a major change in the data almost immediately after rebuilding of Index’s but not caused by the rebuilding of the index. And in this case, it was only about 18 hours, so the client could not believe data could change that significantly in that short amount of time. However, it was so significant it threw it off enough to cause major issues.

我发现许多用户，无论是开发人员还是应用程序最终用户，都是他们不想将问题的原因归咎于数据或代码，而是将其传递给硬件，服务器，数据库或dba。对于DBA来说，这是不幸的，令人沮丧的是，因为我们在那里可以提供帮助。因此，在与该开发人员及其存储人员合作2天之后，客户最终同意让我为相关表格更新统计信息。现在，我不想吹牛，但是，首先，更新统计信息只运行了大约1分钟左右，然后我们运行了sproc和BAM！…。它在大约20秒后又恢复了运行。现在，在这里我开始向客户解释索引和统计数据的重要性，并信任您的DBA。索引是基于数据和数据使用情况的数据库中不断变化的实体。我不能足够表达这一点。他们需要保持一致。我个人喜欢创建一个报告来检查碎片级别以及索引质量。我之前曾分享过此内容，因此请务必仔细查看。当数据来去去去时，索引的有效性可能或不一定始终有效，而统计数据也是如此。重建索引时将更新统计信息。因此，我之前提到的每周工作通常应控制统计数据失控……..直到在此示例中一样，在重建索引之后，数据几乎立即发生了重大变化，但并非由重建索引引起。在这种情况下，大约只有18个小时，因此客户无法相信数据会在这么短的时间内发生重大变化。但是，它是如此重要，以至于足以引起重大问题。

更新统计 (Updating Statistics)

DBA很聪明！ (DBA’s are smart!)

In this situation, all signs pointed to what I like to call “data inefficiency”. Because we had a target table, I felt that running update statistics was the quickest and most efficient solution. I did hide my initial eye roll because I wasn’t allowed to run it for 2 days and in the end, I was right when I finally ran it, but I was also glad that something so simple corrected our issue. Do not be afraid to try the simple things first, sometimes they just might work! And as always trust your DBA, we really do know what we are doing, I promise!

在这种情况下，所有迹象都指向我所称的“数据效率低下”。因为我们有目标表，所以我认为运行更新统计信息是最快，最有效的解决方案。我确实隐藏了最初的视线，因为我不允许运行2天，最后，当我最终运行它时我是对的，但我也很高兴看到一些简单的方法可以解决我们的问题。不要害怕先尝试简单的事情，有时它们可能会起作用！和我一如既往地信任您的DBA，我保证，我们确实知道我们在做什么！