Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 27 '13 at 14:09

This question came from our site for professional and enthusiast programmers.

3  
The server has 64 GB of RAM, but do you have max server memory set? Can you take actual execution plans for a query on the new server and the same query on the old server, to try and demonstrate any potential differences? Did you update statistics with fullscan? –  Aaron Bertrand Feb 27 '13 at 14:12
3  
@AaronBertrand Snap. My only observation from the plans was 32 DOP on new server and 8 on old. I'd be tempted to test server wide MAXDOP 16 (as its 16 physical cores). –  Mark Storey-Smith Feb 27 '13 at 15:25
3  
Other than the fact that parallelism isn't free (thread exchanges can slow things down), now you'll be getting into a low-level hardware analysis, e.g. if you are using hyper-threading that's not really 32 cores, and with 16 cores per chip there may be a lot of exchange overhead that doesn't occur on your 2x4 system. Also, what model are these 16-core chips? If they're not Intel I wouldn't expect blazing performance at the high end - I think there are plenty of studies out there that show AMD is inferior. I hear they even have a 16-core x86 chip and I hope that's not what you bought. –  Aaron Bertrand Feb 27 '13 at 15:40
1  
@flores2013 Try this particular query at MAXDOP 1 also. The row count (760k) and data size (8MB) seems low for a parallel plan to be chosen... If you run sp_configure, what is the value reported for 'cost threshold for parallelism'? –  Mark Storey-Smith Feb 27 '13 at 17:49
2  
Please check the Windows Power settings on the server as AceCTO suggests in his answer. Easy to check, often a cause. –  Paul White Feb 28 '13 at 3:28

5 Answers 5

up vote 5 down vote accepted

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.

share|improve this answer
    
The load is not that high on the server. SQL Server usually sits in 20-35 GB of memory. At any moment we had more than 16 GB of free memory. Also the processor does not ususally pass 10-15% usage. –  prog_sr08 Feb 27 '13 at 15:12
1  
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 '13 at 8:11

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.

share|improve this answer
1  
Everything is much slower on 2012 than in 2008. I am not trying to optimize the queries here. I would be happy to have at least the same performance with the exact same database on this new server. –  prog_sr08 Feb 27 '13 at 12:47
1  
Waits and queues is not about optimizing queries. Is about identifying bottlenecks. –  Remus Rusanu Feb 27 '13 at 13:00
    
I downloaded the document. Looks very interesting. I am on it right now, but it looks like it's going to take me a while. Can you suggest where to look first? –  prog_sr08 Feb 27 '13 at 13:03
1  
wait stats. reset them on both 2008 and 2012, run the load for 5-10 minutes on both, then compare differences between 2008 and 2012. –  Remus Rusanu Feb 27 '13 at 13:10
    
I am afraid I cannot compare the stats between the 2 servers now, because the new server hosts a live site/database. On the old server remained the database that is not under load anymore. –  prog_sr08 Feb 27 '13 at 13:41

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.

share|improve this answer
1  
I have 64 GB RAM, 32 processor core, 4 numa nodes. I set max degree of parallelism to 8 and cost threshold to 4. With this setting and with power option set to high power, SQL Server Execution Times: CPU time = 550 ms, elapsed time = 828 ms. –  prog_sr08 Feb 28 '13 at 8:24
    
So that's a win, then? Glad to see that works for you! –  Max Vernon Feb 28 '13 at 14:12

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

share|improve this answer
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 '13 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 '13 at 15:04

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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