June 9, 2015 at 11:05 am
I'm migrating from an old server to a new one .... but it would surprise me to see rates lower in SQL queries 2014 than in SQL 2008, and I feel very frustrated about it ...
Here the facts of comparative:
1) NEW PC: SQL 2014 on Dell machines with Intel Core I7 CPU 4790, 3.6Ghz and 16 GB RAM with Windows 2014 Server Standard R2
The average query execution normal speed IN 2910 records is 475 milliseconds
2) OLD PC: has SQL 2008 R2, pc HP Xeon X3430 2.4GHz processor, 4 GB Ram Windows 2008 Server Standard
The same database, same table and query gives me 236 milliseconds !!!
According to next link, Core i7 4790 CPU is much more effective than Xeon 3430 2.4 ...
So my question is, why not get best performance in my new server ????
SQL 2014 is running in more modern, more robust PC ....
Why the speed of queries on the same copy of the database gives me lower speed ???
June 9, 2015 at 11:20 am
When you performed the migration of the database to 2014, what steps did you take?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 9, 2015 at 11:39 am
It would also help if you posted the actual execution plans for the query from both systems.
June 9, 2015 at 11:58 am
Adding on to Phil's question, what is the compatibility mode of the database? What are the two storage configurations etc.? Lots of variables left blank.
😎
June 9, 2015 at 12:11 pm
What are the settings for Max Degree of Parallelism and Cost Threshold for Parallelism?
Did you do an update statistics after migrating the database to the new server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 9, 2015 at 4:05 pm
I agree there are some things left out, particularly the storage specifications of both.
Joie Andrew
"Since 1982"
June 9, 2015 at 4:39 pm
Steps I done:
OLD SQL 2008> task > backup DB
NEW SQL 2014 task > Restore DB
1) SET COMPATIBILITY_LEVEL = 120
2) REBUILD ALL INDEXES
3) EXEC sp_updatestats
Any way the miliseconds in simple 'select' are the same than before...
June 10, 2015 at 12:17 am
Disk layouts.
Execution plans.
Need these items.
Also, is the new server producing these numbers consistently or is this result from the first time the query is run? It could be related to a lot of things, such as differences in the speed of the disks, differences in execution plans or differences between one server reading from disk while another has results cached in the buffer pool.
Joie Andrew
"Since 1982"
June 10, 2015 at 12:23 am
The query uses a normal select , and milliseconds are the result of repetitive executions .
In relation to the implementation plans and disk settings , I must review.
June 10, 2015 at 12:29 am
luis_carlosh (6/10/2015)
The query uses a normal select , and milliseconds are the result of repetitive executions .In relation to the implementation plans and disk settings , I must review.
Execution plan, not implementation plan.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 10, 2015 at 12:41 am
I know it doesn't help except that misery loves company. I've never seen a new revision of SQL Server where there weren't some horrible slowdowns even if you did an in-place upgrade. I can't help but blame it on "improvements" to the optimizer.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 10, 2015 at 12:48 am
Quick thought, piecemeal'ing the information like this can go on forever, far too many pieces of information missing. Do post the storage configurations (as complete as possible) and in addition, run the following queries on both servers and post the output:
😎
USE master;
GO
SET NOCOUNT ON;
SELECT
SCFG.configuration_id
,SCFG.name
,SCFG.value
,SCFG.minimum
,SCFG.maximum
,SCFG.value_in_use
,SCFG.description
,SCFG.is_dynamic
,SCFG.is_advanced
FROM sys.configurations SCFG;
SELECT
SOW.wait_type
,SOW.waiting_tasks_count
,SOW.wait_time_ms
,SOW.max_wait_time_ms
,SOW.signal_wait_time_ms
FROM sys.dm_os_wait_stats SOW
WHERE SOW.waiting_tasks_count > 0;
SELECT
SQS.*
,SQT.text AS THE_QUERY
,SQP.query_plan AS THE_EXECUTION_PLAN
FROM sys.dm_exec_query_stats SQS
OUTER APPLY sys.dm_exec_sql_text(SQS.sql_handle) AS SQT
OUTER APPLY sys.dm_exec_query_plan(SQS.plan_handle) AS SQP
WHERE SQT.text LIKE N'%[INSERT AN IDENTIFYING PART OF THE SELECT STATEMENT HERE]%';
June 10, 2015 at 12:55 am
Jeff Moden (6/10/2015)
I know it doesn't help except that misery loves company. I've never seen a new revision of SQL Server where there weren't some horrible slowdowns even if you did an in-place upgrade. I can't help but blame it on "improvements" to the optimizer.
Quick thoughts, I don't even consider an in-place upgrade as an option, much more likely to go wrong than a side by side installation.
As for the new optimizer goes, yes there are some quirks which takes time getting used to but overall there is a substantial performance gain in the 2014 version.
😎
June 10, 2015 at 3:33 am
Piling on, Eirikur's request for information is really the only way to get to the bottom of this.
For the most part, I've usually seen improved performance on most queries. It's the ones that were already edge cases that get radically worse. It's possible we're seeing a regression here, but not enough evidence to support it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2015 at 8:05 am
Not sure if you're seeing any memory pressure. Here is my experience which I posted just a few days ago:
I also find that overall performance of SQL 2014 to be inferior to SQL 2008 R2(In my case). As someone else pointed out in one of the replies, the "new and improved" cardinality estimator has issues, which will cause some of your queries to go way, way slower than SQL 2008/2008 R2. In my case, tacheting down the compatibility mode from 120 to 110 took care of that particular issue with some queries that were affected. Have you tried running with compatibility mode set to 110 ?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply