Wow! SQL Server 2019 is so much quicker than 2012.
-
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt -
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I think I need to add a head-slap reaction ;) SQL has indeed come a long way in 10 years. I'm trying hard to ween myself off it but it's too powerful a drug.
cheers Chris Maunder
-
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
The apps load maybe ten times faster?
Give it a few days. :laugh:
Latest Article:
Create a Digital Ocean Droplet for .NET Core Web API with a real SSL Certificate on a Domain -
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
There are some major differences between the two that result in the bulk of the performance improvements: 1) SQL Server Express 2012 is limited to using 1 GB of RAM and is single threaded 2) SQL Server Developer 2019 is the full blown SQL Server Enterprise 2019 with a developer only license and will use all memory and performs SQL Server specific thread management. 3) SQL Server Express (all versions) doesn't attempt to optimize queries before execution; SQL Server Professional and Enterprise have a rather good query optimizer. You would have seen similar performance enhancements moving from SQL Express 2012 to SQL Enterprise 2012. The remaining performance improvements are the result of Microsoft improving SQL Server's performance in general. Also, Microsoft did a ground up rewrite of SQL Server 2019 to make it run on Linux as well as Windows and in the process eliminated a lot of internal performance bottlenecks.
-
There are some major differences between the two that result in the bulk of the performance improvements: 1) SQL Server Express 2012 is limited to using 1 GB of RAM and is single threaded 2) SQL Server Developer 2019 is the full blown SQL Server Enterprise 2019 with a developer only license and will use all memory and performs SQL Server specific thread management. 3) SQL Server Express (all versions) doesn't attempt to optimize queries before execution; SQL Server Professional and Enterprise have a rather good query optimizer. You would have seen similar performance enhancements moving from SQL Express 2012 to SQL Enterprise 2012. The remaining performance improvements are the result of Microsoft improving SQL Server's performance in general. Also, Microsoft did a ground up rewrite of SQL Server 2019 to make it run on Linux as well as Windows and in the process eliminated a lot of internal performance bottlenecks.
Nice to know I'm not imagining it.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
The apps load maybe ten times faster?
I've been using it (2019 Dev) for a couple of years now in prod and never noticed performance gains over previous versions. I'll run some tests and get back to you on that. :thumbsup: I've been putting off installing a 2019 instance on my main dev system due to low disk space. This may be the determination of how soon I replace this non-upgradable tower and join the rest of you on 11.
"Go forth into the source" - Neal Morse "Hope is contagious"
-
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
changing to the new Server instance was a single location change and all my apps switch over
That's why I tend to use an alias[^]. :) (Although it technically requires two changes, since there's a separate list of aliases for x86 and x64.)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
OriginalGriff wrote:
The apps load maybe ten times faster?
I've been using it (2019 Dev) for a couple of years now in prod and never noticed performance gains over previous versions. I'll run some tests and get back to you on that. :thumbsup: I've been putting off installing a 2019 instance on my main dev system due to low disk space. This may be the determination of how soon I replace this non-upgradable tower and join the rest of you on 11.
"Go forth into the source" - Neal Morse "Hope is contagious"
See obermd's response. I guess the earlier editions of Express were single threaded and only used 10MB of RAM whereas the new ones do not have that limitation. You were probably running commercial servers and so you were not subject to that limitation.
To err is human. Fortune favors the monsters.
-
Due to one thing and another, I've upgraded from SQL Server Express 2012 for my dev system to SQL Server Developer Edition 2019 (I like to keep the DB engine at the same version as the production server - it means I don't use features that aren't available, and the hosting service was running 2012 until recently). And once I'd unpicked what the Data Migration Assistant wanted me to do (why is it that complicated, given you'll probably use it once or twice a decade?) my dev DB's moved over nicely. Fortunately, I did this a while ago: Instance Storage - A Simple Way to Share Configuration Data among Applications[^] so changing to the new Server instance was a single location change and all my apps switch over. So I test them ... and blow me but it's quick! The apps load maybe ten times faster? Almost immediately, instead of a couple of seconds delay while they sort out the initial data. Impressive stuff!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Doesn't express have memory and cpu core limitations that dev and the commercial editions don't? That would almost certainly explain it. Personally I like testing on low memory, low cpu SQL because it forces me to optimize performance.
-
I think I need to add a head-slap reaction ;) SQL has indeed come a long way in 10 years. I'm trying hard to ween myself off it but it's too powerful a drug.
cheers Chris Maunder
Chris Maunder wrote:
I'm trying hard to ween myself off
Why? :)
Wrong is evil and must be defeated. - Jeff Ello
-
See obermd's response. I guess the earlier editions of Express were single threaded and only used 10MB of RAM whereas the new ones do not have that limitation. You were probably running commercial servers and so you were not subject to that limitation.
To err is human. Fortune favors the monsters.
Good catch - SQL Server Express was limited to 1 GB RAM. I've updated the post. For SQL Server Express 2019: 16 cores (threads) 64 GB RAM max SQL Server Express 2019 is far more powerful than 2012 was but still doesn't have the performance enhancements of SQL Server 2019 Standard or Enterprise.
-
Good catch - SQL Server Express was limited to 1 GB RAM. I've updated the post. For SQL Server Express 2019: 16 cores (threads) 64 GB RAM max SQL Server Express 2019 is far more powerful than 2012 was but still doesn't have the performance enhancements of SQL Server 2019 Standard or Enterprise.
I thought 10MB seemed small, but these days anything in the MB range seems generous to me so I didn't question it. ;P
To err is human. Fortune favors the monsters.
