September 2, 2014 at 1:24 am
Hi,
We are using 2008 environment but we are planning to migrate to 2014. We have to show to the team 2014 has more optimization than 2008. We are planning to test and show to the team. Now we want to do the test in one database.
To take the memory optimization of 2014 what we have to do? I mean to show that particular query is better in 2014 compared to 2008 how can we prove?
September 2, 2014 at 2:35 am
In-memory OLTP is for very, very, very specific scenarios (very high speed inserts mostly). It's not a magic silver bullet for everything. It can easily be slower for inappropriate uses and it's got a lot of maintenance and monitoring requirements.
As for normal queries, you may see performance improvements just by moving to SQL 2014, you may not. Run the queries against 2008, run them against 2014 and see. Don't expect massive gains.
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
September 2, 2014 at 10:30 am
The basic approach is to record the behavior of your queries in 2008. Get the execution times, resources used, and, for really interesting queries, the execution plan. Then do the same thing in 2014. Just be sure you change the compatibility level on the database so that you see all the new functionality in use in 2014.
"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
September 2, 2014 at 3:16 pm
Hi,
Thanks for the replies. Please let me know
How to check my application is compatible with 2014 instance. How to find what features are removed in 2014 but that are used in 2008 application
September 2, 2014 at 3:23 pm
Look in the 2008 and 2012 Books Online for deprecated features.
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
September 2, 2014 at 3:41 pm
You should run the SQL Server 2014 Upgrade Advisor.
"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
September 2, 2014 at 3:49 pm
Here we are using side by side migration.
We are testing mainly one database then it works fine we are planning to migrate all. For that we use the test box in that we have existing 2008 and now we installed 2014. We restore the database by using the production full backup. We run trace in production to get workload. Now we need to have replay that trace in the test box in both 2008 and 2014. We don't have application connection to that test box.
We want to find the our code works fine without any breakages in 2014. We need to take three workloads.
1) To compare 2008 & 2014
2) Work loads to compare without enabling the memory optimization and with memory optimization.
How can we do replay of trace when there is no application connection to the test box
September 2, 2014 at 3:53 pm
Distributed Replay will do the job. See Books Online for details
Seriously though, In-memory tables are NOT for general usage. They're a specialised solution to specific problems. Not something to use everywhere without thinking. To start, Bad Things happen when you run out of memory (do you even have physical memory > ~120% of the entire size of the DB?)
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
September 2, 2014 at 4:06 pm
What I understand about the memory optimized tables is
1)We can't use for frequently where schema changes frequently. If schema changes every time memory optimized tables need to be recreate
2) We can't use for very large queries because it will impact the memory
3) Just this feature is for small queries and which is not getting performance improvement through the normal features
September 3, 2014 at 3:40 am
There are also a number of limits on data types. There are no foreign keys supported. Not all T-SQL statements are supported. It's a very specialized set of functionality.
"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
September 3, 2014 at 4:14 am
ramana3327 (9/2/2014)
2) We can't use for very large queries because it will impact the memory
Nope, nothing to do with the query size.
3) Just this feature is for small queries and which is not getting performance improvement through the normal features
Not really. It's not about the size of the query, though data-warehouse style queries are very unlikely to benefit.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply