July 28, 2016 at 2:57 pm
Hi
I am working on a justification to upgrade from SQL Server 2008 R2. One of the arguments was way faster. My boss countered with show me documentation that it is Faster. Well I googled that a few times and couldn’t find anything. Checked the Microsoft website and I didn’t find it there either.
Has anybody seen documentation for performance increases between the different versions of SQL Server?
For those of you who upgraded to SQL Server from any version of SQL Server to SQL Server 2016 has there been big increases in performance?
July 28, 2016 at 3:17 pm
Quinn-793124 (7/28/2016)
HiI am working on a justification to upgrade from SQL Server 2008 R2. One of the arguments was way faster. My boss countered with show me documentation that it is Faster. Well I googled that a few times and couldn’t find anything. Checked the Microsoft website and I didn’t find it there either.
Has anybody seen documentation for performance increases between the different versions of SQL Server?
For those of you who upgraded to SQL Server from any version of SQL Server to SQL Server 2016 has there been big increases in performance?
Have you seen this? https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-announcement/
July 28, 2016 at 3:43 pm
One of the arguments was way faster.
what were your other arguments?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 28, 2016 at 4:11 pm
If you use any SSRS components there should be plenty of compelling reasons to upgrade:
- new native SSRS portal
- rebuilt HTML 5 rendering engine
- pinning Power BI reports
- SSRS portal with KPIs
- better cross-browser and mobile device support
- R language support
Joie Andrew
"Since 1982"
July 28, 2016 at 5:10 pm
Documentation will not tell you if SQL 2016 will be faster, in your particular environment, with your particular queries, with or without additional work.
Documentation will tell you that you can gain
Pagination (OFFSET/FETCH) for your Web devs
Better spatial support
better TRY/CATCH
Lots of new functions (TRY_CONVERT, EOMONTH, FORMAT, COMPRESS, DECOMPRESS, etc.)
Sequences
More OVER clause capability (LAG, LEAD, FIRST_VALUE, PERCENTILE_*)
AlwaysOn HA (Enterprise edition except for Basic, which replaces Mirroring)
Columnstore indexes (Enterprise)
Backup Encryption
Memory-optimized tables that allow for (some) constraints (Enterprise)
(Better) Extended Events
DROP IF EXISTS
CREATE CERTIFICATE now creates 2048 bit private keys, which means they can actually comply with NIST SP800-131A rev.1
SESSION_CONTEXT
R language integration
TDE now supports AES-NI (Enterprise only; a huge CPU savings if you use TDE)
Lots of SSRS changes - other browsers, mobile, etc.
Live query statistics
Query store
Lots of other additions and improvements.
Oh... and it's not going to be out of support as soon:
Did I mention the COMPRESS and DECOMPRESS functions? Now you can compress huge, pointless, stupid out of row (VARCHAR(MAX)) data, even if you aren't allowed to tell people not to put it in your database!
July 28, 2016 at 5:21 pm
I forgot about the AES-NI support. Originally I thought that was only coming to Azure. I have been waiting for that for at least 2 years. I even put a connect item about that a couple of years back.
Fantastic!
Joie Andrew
"Since 1982"
July 29, 2016 at 2:16 pm
Thanks guys for pointing me in different direction.
I should beable to come up with a good list from alll the enhancemennt.
July 29, 2016 at 3:16 pm
The benefits of upgrading from 2008 to 2016 are cumulative. SQL Server 2012 was a huge upgrade from 2008. On the T-SQL side you have:
* LAG/LEAD - super huge
* Window Aggregate Function Specification (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
These are both game changers with respect to developing faster T-SQL code. You also have columnstore indexes and batch mode processing, again - game changers with respect to performance.
In 2014 MS introduced in-memory support; this was a game changer which makes 2014 a big upgrade from 2012. Other huge performance improvements in 2014 include faster SELECT INTO, a new/better/faster cardinality estimator, and clustered & updatable columnstore indexes
In 2016 (as mentioned) there's some big improvements to how in-memory works, filtered columnstore indexes and the ability to get batchmode processing against data not stored in columnstore indexes.
Other great features in 2016 are big data components like R support and Hadoop support. The query store is huge too. I personally would consider 2014 if you're not interested in the big data stuff. I like to wait for the first service pack to be released before upgrading but that's just me.
-- Itzik Ben-Gan 2001
August 1, 2016 at 6:34 am
Arguably, the best way to prove / disprove whether SQL2016 will be "faster" than SQL2008R2 is a test setup.
Ideally, you'd use Profiler or something similar to record a typical workload against your existing server(s), then set up a test server with your databases (obviously you would NOT want to do this against your production server!) First, you'd do a run through with SQL2008, and record various metrics (total run time, maybe dig in and get average query run times, min / max etc,) then, on the exact same system do the same with SQL2016.
Sure, this would be time-consuming, especially making sure that the starting state for each test is the same, but it would be a quick way to find out if SQL2016 will be faster without relying on marketing from MS.
August 1, 2016 at 6:39 am
It 'CAN' be faster in certain situations. SQL 2014 introduced a new cardinality estimator which can cause some queries to run much faster, also some will run much slower and you will have to look at those and see if any query rewrites or index tuning will help those. You cannot simply go by a newer version being faster. A lot of what you will see is if you are running on modern hardware with plenty of memory running SQL 2008R2 and upgrade to the same hardware running SQL 2016 you may not see any improvement. We have several small apps that we upgraded and there is no improvement from 2008R2 to 2014 because they are on the same VM back end with the same large memory allocation. They are low end apps that simply ran fine in 2008R2.
The only way to know if you can get any improvement is to install a test environment and restore your prod db into it and run processes against it to see if you have improvements or not.
August 1, 2016 at 9:10 am
2016 can potentially be several orders of magnitude faster than 2008, if you also want to invest in re-architecting your database to leverage things like Clustered ColumnStore, In-Memory OLTP, or Delayed Durability. However, if the argument is that simply running the 2016 upgrade on top of 2008 will yield an easy performance boost, then the argument is less compelling.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 1, 2016 at 3:06 pm
thanks much for all the great responces!
You guy have been a great resourse.
My guts feeling too was the system was going to kick but too from upghrading 3 levelof software.
We are running a GID+S ba system with 28 users.
Thanks.
Quinn
August 1, 2016 at 3:42 pm
Eric M Russell (8/1/2016)
2016 can potentially be several orders of magnitude faster than 2008, if you also want to invest in re-architecting your database to leverage things like Clustered ColumnStore, In-Memory OLTP, or Delayed Durability. However, if the argument is that simply running the 2016 upgrade on top of 2008 will yield an easy performance boost, then the argument is less compelling.
Enterprise Edition, perhaps; Standard Edition and below, not so much potential gain (no columnstore, no in-memory OLTP, no transparent data/index compression, etc.)
August 2, 2016 at 5:13 am
Nadrek (8/1/2016)
Eric M Russell (8/1/2016)
2016 can potentially be several orders of magnitude faster than 2008, if you also want to invest in re-architecting your database to leverage things like Clustered ColumnStore, In-Memory OLTP, or Delayed Durability. However, if the argument is that simply running the 2016 upgrade on top of 2008 will yield an easy performance boost, then the argument is less compelling.Enterprise Edition, perhaps; Standard Edition and below, not so much potential gain (no columnstore, no in-memory OLTP, no transparent data/index compression, etc.)
Just because you are jumping up three versions of SQL Server if you have a low end system with only 28 users and aren't maxing out your system you may not see any improvement. I know there are many databases I have migrated from SQL 2005 or 2008 to 2014 that I don't see any improvement on at all. Don't simply fall into the trap thinking you will see major improvements just because. The only way to know is to upgrade a test system and test, test, test... Remember in 2014 that some queries MAY run much slower so you will have to look at those and make some changes.
August 2, 2016 at 11:37 am
Nadrek (8/1/2016)
Eric M Russell (8/1/2016)
2016 can potentially be several orders of magnitude faster than 2008, if you also want to invest in re-architecting your database to leverage things like Clustered ColumnStore, In-Memory OLTP, or Delayed Durability. However, if the argument is that simply running the 2016 upgrade on top of 2008 will yield an easy performance boost, then the argument is less compelling.Enterprise Edition, perhaps; Standard Edition and below, not so much potential gain (no columnstore, no in-memory OLTP, no transparent data/index compression, etc.)
Yes, for a database architect, the only excitement in SQL Server for the past 8 years has been the introduction of new and incrementally improved enterprise features.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply