I installed SQL Server 2012, and attached a database originally generated by SQL Server 2008 R2.
Everything appeared to work perfectly, with one problem: merges dropped from 1000 per second to 10 per second (a 100x slowdown).
I'm surmising that its because I am accessing a SQL Server 2008 R2 database from SQL Server 2012. Is there some way to convert the database to SQL Server 2012 format? Or is there something else thats going on that might explain the 100x slowdown in performance?
Please make sure that you set the compatibility mode of the database to 110, and update statistics.
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 110;
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += CHAR(13) + CHAR(10) + 'UPDATE STATISTICS '
+ QUOTENAME(SCHEMA_NAME(schema_id))
+ '.' + QUOTENAME(name) + ' WITH FULLSCAN;'
FROM sys.tables;
PRINT @sql;
--EXEC sp_executesql @sql;
When I ran the SQL in the answer the nvarchar overflowed. The problem is when your database has too many tables the SQL is too long for an nvarchar. My database had enough tables to overflow a varchar (twice as long as a nvarchar). So I edited the SQL to loop through each table and execute separate statements. This way you wont miss updating the stats on any of your tables.
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 110;
DECLARE @SQL NVARCHAR(MAX) = N'';
Declare @Tables table
([Schema] nvarchar(50)
,[TableName] nvarchar(100))
Insert into @Tables
Select QUOTENAME(SCHEMA_NAME(schema_id)),QUOTENAME(name)
FROM sys.tables;
Declare @Schema nvarchar(50), @TableName nvarchar(100)
While Exists(Select * From @Tables)
Begin
Select Top 1 @Schema = [Schema], @TableName = [TableName] From @Tables
Set @SQL = 'UPDATE STATISTICS ' + @Schema + '.' + @TableName + ' WITH FULLSCAN;'
Begin Try
EXEC SP_ExecuteSql @SQLToExecute = @SQL
Print 'Completed: ' + @SQL
End Try
Begin Catch
DECLARE @ErrMsg nvarchar(4000)
SELECT @ErrMsg = SubString(ERROR_MESSAGE(),0,900)
Select GetDate(), 'Failed updating stats on ' + @Schema + ' ' + @TableName + '. Error: '+@ErrMsg
End Catch
Delete From @Tables Where [Schema] = @Schema and [TableName] = @TableName
End
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With