March 17, 2015 at 4:30 am
We are trying to migrate from sql server 2012 to sql server 2014 on windows 2000 R2 OS.
After the upgrade, we are doing performance testing and observed huge difference in running a simple query from only one table (No joins at all).
The query is getting executed in 4 seconds in Sql 2012, but taking more than 1 minute in sql 2014. Both are running on the same OS with same hardware configurations.
From the execution plan, observed difference in the below property (EstimatedPagesCached).
In Sql Server 2014, we are getting below one :
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104844" EstimatedPagesCached="1280" EstimatedAvailableDegreeOfParallelism="2" />
whereas in 2012, we are getting below one :
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104844" EstimatedPagesCached="2128" EstimatedAvailableDegreeOfParallelism="2" />
Any help is highly appreciated.
Thanks in advance
Gopi Srinivas.
March 17, 2015 at 4:37 am
First question, as we can see a difference in caching:-
Do the settings for "memory made available to SQL Server" vary between the two instances?
March 17, 2015 at 4:55 am
--Removed
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
March 17, 2015 at 5:07 am
Haven't selected any memory options. Just followed the installation document given in the msdn site.
Also not sure from where that property is picked up by sql server. I have checked the page cache size of the OS and it is different.
Is there any way to set this property in sql server ?
March 17, 2015 at 5:16 am
Yes indeed - to quote from TechNet:-
To set a fixed amount of memory
In Object Explorer, right-click a server and select Properties.
Click the Memory node.
Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources.
This is, of course, where you can also see what settings are currently applied.
March 17, 2015 at 5:43 am
The max server memory (MB) has value of 2147483647 by default and the Min server memory is 0.
As this is pretty high value i haven't changed. I have changed the Minimum Memory for Query (KB) from default 1280 to 3000, but couldn't find any difference in the query execution or query execution plan.
Is the property EstimatedPagesCached dependent on this memory property ?
March 17, 2015 at 5:48 am
You shouldn't be touching the minimum memory for query without a really, really good reason. The max server memory however should almost always be changed from the default, as few servers have over 2000 TB of memory (what the default tells SQL it should use)
Few steps back... Why do you think that the EstimatedPagesCached property is related to the performance difference?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2015 at 5:51 am
The figure you've seen is the default and was what I expected once I had realised that you'd basically just followed the standard installation documentation.
I think this opens up a few more areas for discussion.
For clarity, is the physical server that the new instance has been installed on the actual same tin, or is it a separate machine?
Assuming for now that we're talking about the same machine, are both instances of SQL now on there or has the new one replaced the old one?
If two separate machines, does each only have one instance of SQL on it?
Are there other significant differences between the two machines, regarding other applications that may be installed, or the number of connections that are being made?
Again, if two machines, are the hard drives/storage and processors identical?
EDIT: Oops, crossed posts with Gila there - please address her points first.
March 17, 2015 at 5:52 am
I have done sql server configuration in 4 to 5 systems and observed the differences in the execution plan of these systems. I could see the query execution faster where this EstimatedPagesCached is having an high value.
March 17, 2015 at 5:53 am
gopisrinivas.t (3/17/2015)
I have done sql server configuration in 4 to 5 systems and observed the differences in the execution plan of these systems. I could see the query execution faster where this EstimatedPagesCached is having an high value.
I suspect you're mixing up cause and effect.
That value is high with inefficient execution plans, not the cause of inefficient execution plans. Can you post the plans so that we can examine the other differences?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2015 at 7:10 am
Look to the row estimates. I'm sure that's probably the issue. The 2014 cardinality estimator is new and if you're seeing differences in execution times, it could be because of the estimates based on your statistics.
"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
March 17, 2015 at 1:03 pm
Can you post the query and both plans with estimations? As Grant said, on of the first things you have to check in such cases is new Cardinality Estimator. It is not a surprise when a query that run few seconds under 2012 needs more than a minute under SQL Server 2014.
You can set the compatibility level of the database to 110 and then execute the query to check if it perfoms better.
___________________________
Do Not Optimize for Exceptions!
March 18, 2015 at 4:52 am
The SQL servers are installed on 2 different VMs (Different IP Addresses).
They have only one SQL Instance on each machine.
The processor / ram / hard drive space is same between 2 machines.
The machines have the same applications installed. We are running the same select query from the SQL management studio directly.
Attaching the execution plans.
March 18, 2015 at 5:03 am
The SQL servers are installed on 2 different VMs (Different IP Addresses).
They have only one SQL Instance on each machine.
The processor / ram / hard drive space is same between 2 machines.
The machines have the same applications installed. We are running the same select query from the SQL management studio directly.
Attaching the execution plans.
March 18, 2015 at 5:06 am
The SQL servers are installed on 2 different VMs (Different IP Adresses). They have only one SQL Instance on each machine. The processor / ram / hard drive space is same between 2 machines expect the sql server. The machines have the same applications installed. We are running the same select query from the SQL managment studio directly.
Attaching the execution plans.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply