I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL Server 2012 SP1 / 64 GB RAM / 2 x 2.1 GHz 16 Core processors / SSD disks).
I detached the database files on the old server, copied and attached them on the new server. Everything went very well.
After that, I changed to compatibility level to 110, updated statistics, rebuild indexes.
To my huge disappointment, I noticed that most sql queries are much slower (2-3-4 times slower) on the new SQL 2012 server than on the old SQL 2008 server.
For example, on a table with around 700k records, on the old server a query on index took around 100ms. On the new server, the same query takes around 350 ms.
Same happens for all queries.
I would appreciate some help here. Let me know what to check/verify. Because I find it very hard to believe that on a better server with a newer SQL Server, the performance is worse.
Memory is set to max.
I have this table and index:
CREATE TABLE [dbo].[Answer_Details_23]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NOT NULL, [SurveyID] [int] NOT NULL, [CustomerID] [int] NOT NULL default 0, [SummaryID] [int] NOT NULL, [QuestionID] [int] NOT NULL, [RowID] [int] NOT NULL default 0, [OptionID] [int] NOT NULL default 0, [EnteredText] [ntext] NULL, CONSTRAINT [Answer_Details_23_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE NONCLUSTERED INDEX [IDX_Answer_Details_23_SummaryID_QuestionID] ON [dbo].[Answer_Details_23] ( [SummaryID] ASC, [QuestionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I executed this query:
set statistics time on; select summaryid, count(summaryid) from Answer_Details_23 group by summaryid order by count(summaryid) desc; set statistics time off;
OLD SERVER - SQL Server Execution Times: CPU time = 419 ms, elapsed time = 695 ms.
NEW SERVER - SQL Server Execution Times: CPU time = 1340 ms, elapsed time = 1636 ms.
EXECUTION PLANS uploaded here: http://we.tl/ARbPuvf9t8
- AMD 2.1GHz Opteron 16 core processors look much worse than Intel 2.5GHz quad core processors
- Great improvement changing windows power options from ballanced to high power
- Further improvement changing max degree of parallelism to 8 and cost threshold to 4
Now, SQL Server Execution Times: CPU time = 550 ms, elapsed time = 828 ms.
It's still worse than the old server, but not that bad. If you have any other suggestions (other than local query optimizations) please feel free to comment.