16

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.

More details:

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

Later update:

  • 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.

Improve this question
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White ♦
    Aug 29, 2017 at 10:15

6 Answers 6

Reset to default
8

I have had similar issues with SQL Server, it is possible that your server is not optimally configured. Newer Xeons come with TurboBoost, HT, etc. that can affect server performance significantly.

For example, we have had success with; Low Latency Configuration for Dell servers

The settings will be applicable to non-Dell servers, they just might have different names.

We also improved performance by setting the windows power management profile to high performance, from Balanced. A final piece is that it is recommended to reserve up to 8GB of memory for the OS on x64 servers, the default SQL install takes all memory. You might want to try 4/8GB reservation by setting your max SQL Server memory configuration to 4/8GB less than total memory.

My recommendation would be to revert to the old server if possible. If you do not have regression/automation/load scripts available, then the best you can do is to record your system activity for 1-4 hours during a high activity period. Then setup a web server the same as production, and a client machine to run the script. Run the same activity against the new server, make the configuration change and run the same activity again. Really you would want to do much more, but it doesn't appear that it would be viable and is outside the scope of this question.

Improve this answer
1
  • 2
    Greatest improvement so far achieved by setting windows power management from balanced to high power. So it really looks like a processor problem. SQL Server Execution Times: CPU time = 892 ms, elapsed time = 874 ms.
    – prog_sr08
    Feb 28, 2013 at 8:11
8

Let me know what to check/verify

You have a performance problem. Follow a performance troubleshooting methodology like Waits and Queues to identify the bottleneck. The linked methodology shows you what to measure and how. Post here the findings and we can help with specific advice based on your actual measurements. As it is is too open and is anybody's guess. Narrowing it down to a specific issue will eliminate guesswork.

After update

The plans are quite different. The old plan had a stream aggregate low on the stack which actually has a bad cardinality estimate (141k vs. 108k) and the hash math further mispredicts, the other way (35k vs. 108k). The new plan does not have the stream aggregate and has accurate estimates all the way to the top. Of course, this does not explain why the old plan was executing faster.

The bottom scans have a slightly different row number (not significant) but quite different costs: old is 2.49884 (IO 2.28979 CPU 0.20905) vs new 1.59109 (IO 1.53868 CPU 0.0524084). Again would point toward a better 2012 execution (the index rebuild has perhaps reduced fragmentation?).

What is very different is the number of threads: 32 in new (each getting ~23k rows) vs. 8 in old (each getting ~95k rows). The table is fairly narrow. It could be that the large number of threads is actually hurting performance because of much much more frequent cache invalidations. I would try:

  1. eliminate HyperThreading in the new server config (if any) and/or
  2. try the query with a DOP 8.

Noticed your comment:

Added the execution plan with maxdop 8 Query is actually faster this way

It probably is just CPUs stepping on each other toes. With SSDs in place the IO is probably next to nothing so and the table is definitely too small to warrant 32 scanners. That exchange swap is probably invalidating L1/L2 constantly.

Improve this answer
0
3

For most modern multi-core systems, and particularly multi-cpu systems, the hardware architecture is such that certain portions of memory are far away from certain cores/processors, and certain portions of memory are close to certain cores/processors. This is referred to as Non-Uniform Memory Architecture, or NUMA for short. You want your MAXDOP setting to match the number of cores per NUMA node to minimize the number of times a given numa node needs to go outside its own memory for data.

You can use the following to check the configuration of your new machine, and ensure MAXDOP is set to the best setting, hardware-wise:

DECLARE @CPUs int;
DECLARE @NumaNodes int;
DECLARE @ServerRAMInMB int;

SET @ServerRAMinMB = (SELECT (i.physical_memory_kb / 1024) AS ServerMemory 
    FROM sys.dm_os_sys_info i);
SET @CPUs = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (SELECT MAX(c.memory_node_id) + 1 FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64);

SELECT @ServerRamInMB, @CPUs, @NumaNodes;

IF @CPUs > 4 /* this would be 4 cores, not 4 CPUs */
BEGIN
    DECLARE @MaxDOP int;
    SET @MaxDOP = @CPUs * 0.75;
    IF @MaxDOP > (@CPUs / @NumaNodes) SET @MaxDOP = (@CPUs / @NumaNodes);
    EXEC sp_configure 'max degree of parallelism', @MaxDOP;
    EXEC sp_configure 'cost threshold for parallelism', 4; 
END

I included the @ServerRamInMB parameter here since I use it to setup the Max Server Memory and Min Server Memory configuration options to values that are appropriate for the given server.

Improve this answer
0
0

What edition and licensing mode are you in? You are probably not using all the cores. See the note on this page - http://msdn.microsoft.com/en-us/library/ms143760.aspx

"Enterprise Edition with Server + Client Access License (CAL) based licensing is limited to a maximum of 20 cores per SQL Server instance."

Improve this answer
2
  • 2
    This would only apply if he had CAL before and grandfathered in. Still even with only 20 cores the performance should not drop noticeably over the former system (which only had 8).
    – Aaron Bertrand
    Feb 27, 2013 at 15:03
  • I have the Web Edition (Limited to lesser of 4 Sockets or 16 cores). On the old server I only had 8 cores anyway.
    – prog_sr08
    Feb 27, 2013 at 15:04
0

I had the same problem as that described in this page: switching power settings from "balanced" to "high performance" made a dramatic difference - more than doubling response times. Now that we're using SSDs I don't think energy consumption is the issue it may have been.

Improve this answer
-2

I have also gone thru this issue for at least 2 weeks without any robust resolution rather than confusing one issue with the other.

Finally the resolution as follows:-

  1. I have reset the compatibility from 010 to 011

  2. Reset the compatibility of master database too. By default sql will retain the old compatibility setting. That we need to change manually.

All the best

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.