Sql Server 2012 比 2005 慢? [英] Sql Server 2012 slower than 2005?

问题描述

我们已经部署了 SQL Server 2012 Enterprise，但存在性能问题:

We have deployed SQL Server 2012 Enterprise and we have performance issues:

• 相同的数据(从我们的 SQL Server 2005 Enterprise 备份并在 2012 年恢复)
• 3200 条 sql SELECT 语句的测试脚本

我们使用 Management Studio 进行测试:

We do tests using Management Studio:

1. 结果为纯文本
2. 生成一个文件

在同一台电脑上:

1. 2005 年:15 秒，2012 年:2 分钟
2. 2005 年:14 秒，2012 年:30 秒

即使使用更强大的计算机，2012 年仍然比 2005 年慢.

Even with a more powerful computer, 2012 is still slower than 2005.

有什么问题?我们安装SQL Server 2012的方式和默认参数?我们恢复备份的方式?我们能做什么?

What can be wrong? The way we installed SQL Server 2012 and default parameters? The way we restored the backup? What can we do?

推荐答案

当我看到这样的变化时，我的第一个想法是确保您为所有表重新生成统计信息.网络上有很多用于执行此操作的脚本，并且有很多关于是否使用内置 sproc、是否进行全扫描等的讨论.这是我在进行比较之前会运行的一个快速而脏的脚本.

My first thought when I see variations like that are to ensure that you have regenerated statistics for all of your tables. There are many scripts on the web for doing this and lots of discussion about whether to use the built-in sprocs, whether to do fullscan etc. Here is one quick and dirty script through that I would run before doing comparisons.

CREATE  PROCEDURE sp_UtilityUpdateStats AS
SET NOCOUNT ON

DECLARE @iCounter       INT
DECLARE @iCounterMax    INT

DECLARE @TableList TABLE
(
    iTable INT IDENTITY(1,1) PRIMARY KEY,
    szTableName VARCHAR(128)
)

INSERT @TableList (szTableName)
SELECT [name] FROM sysobjects
WHERE [type] = 'u'
ORDER BY [name] DESC


SET @iCounterMax = (SELECT MAX(iTable) FROM @TableList)
SET @iCounter = 0
DECLARE @szTableName VARCHAR(128)

RAISERROR(N'------STARTING sp_UtilityUpdateStats------', 10, 1) WITH LOG
WHILE @iCounter < @iCounterMax
BEGIN
    SET @iCounter = @iCounter + 1

    SELECT  @szTableName = szTableName
    FROM @TableList
    WHERE iTable = @iCounter

    RAISERROR(N'UPDATE STATISTICS YourDB.dbo.%s', 10, 1, @szTableName) WITH LOG
    EXEC ('UPDATE STATISTICS YourDB.dbo.' + @szTableName)

END
RAISERROR(N'------FINISHING sp_UtilityUpdateStats------', 10, 1) WITH LOG

SET NOCOUNT OFF
GO

这篇关于Sql Server 2012 比 2005 慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！