复制数据库后SQL Server速度变慢

I recently moved a bunch of tables from an existing database into a new database due to the database getting rather large. After doing so I noticed a dramatic decrease in performance of my queries when running against the new database.

由于数据库变得相当大,我最近将一堆表从现有数据库移动到新数据库中。在这样做之后,我注意到在针对新数据库运行时查询性能急剧下降。

The process I took to recreate the new database is this:

我重新创建新数据库的过程如下:

Generate Table CREATE scripts using sql servers automatic script
generator.

使用sql server自动脚本生成器生成表CREATE脚本。

Run the create table scripts

运行create table脚本

Insert all data into new database using INSERT INTO with a select from the existing database.

使用INSERT INTO将所有数据插入到新数据库中,并从现有数据库中选择。

Run all the alter scripts to create the foreign keys and any indexes

运行所有alter脚本以创建外键和任何索引

Does anyone have any ideas of possible problems with my process, or some key step I'm missing that is causing this performance issue?

有没有人对我的流程可能出现的任何问题有任何想法,或者我错过了导致此性能问题的一些关键步骤?

Thanks.

5 个解决方案

#1

Did you script the indexes from the tables in the original database? Missing indexes could certainly account for poor performance.

您是否编写了原始数据库中表的索引?缺少索引肯定会导致性能不佳。

#2

first I would an a mimimum make sure that auto create statistics is enabled you can also set auto update statistics to true

首先,我将最小化确保启用自动创建统计信息,您还可以将自动更新统计信息设置为true

after that I would update the stats by running

之后我会通过运行来更新统计数据

sp_updatestats

UPDATE STATISTICS

Also realize that the first time you hit the queries it will be slower because nothing will be cached in RAM. On the second hit should be much faster

还要意识到,第一次点击查询时它会更慢,因为没有任何内容会缓存在RAM中。在第二次打击应该快得多

#3

Have you tried looking at the execution plans on each server when running these queries - that should allow you to easily see if they're doing something different e.g. table scanning due to a missing index, poor statistics etc.

您是否尝试在运行这些查询时查看每台服务器上的执行计划 - 这应该可以让您轻松查看他们是否正在执行不同的操作,例如:因缺少索引,统计数据不佳等而进行表扫描

Are both DBs sat on the same box with their data files on the same drive arrays?

两个DB是否位于同一个盒子上,其数据文件位于同一个驱动器阵列上?

#4

Can you tell what about those queries got slower? New access plans? Same plans but they perform slower? Do they execute slower or are they suspended more? Did all queries got slower or just some? And last but not least, how do you know, ie. what exactly did you measure and how?

你能告诉那些查询变慢了吗?新的访问计划?相同的计划,但他们表现较慢?它们执行速度较慢还是暂停更多?所有查询都变慢了还是只是一些?最后但并非最不重要的是,你怎么知道,即。你究竟测量了什么以及如何衡量?

Some of the usual suspects could be:

一些常见的嫌犯可能是:

The new storage is much slower (.mdf on slow disk, or on a busy disk)

新存储速度要慢得多(慢速磁盘上的.mdf或繁忙磁盘上的.mdf)

You changed the data structure during move (ie. some indexes did not get ported)

您在移动期间更改了数据结构(即某些索引未被移植)

You changed the data size (ie. compression options) resulting on more pages for the same data

您更改了数据大小(即压缩选项),导致相同数据的页面更多

Did anything else change at the same time, new app code or anything the like?

还有其他任何改变,新的应用程序代码或类似的东西吗?

By extending the data size (you do no mention deleting the old tables) you are now trashing the buffer pool (did the page lifetime expectancy decreased in performance counters?)

通过扩展数据大小(您没有提及删除旧表),您现在正在废弃缓冲池(性能计数器中的页面生命周期预期会减少吗?)

#5

Look on how you set up the initial size and growth options. If you didn't give it enough space to begin with or if you are growing by 1MB at a time that could be a cause of performance issues.

了解如何设置初始大小和增长选项。如果你没有给它足够的空间开始,或者你的增长速度为1MB,这可能是导致性能问题的原因。

#1