12 ms·

I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx http://msdn.microsoft.com/en-us/library/cc645

I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).... SQL Server has been getting much more expensive because their current userbase is captive (meaning the cost, complexity and risk of migrating to another database system is enormous because of a heavy integration with SQL Server specific features). I don't imagine a large number of new systems are being built around SQL Server, apart from those at shops that are already captive. It is an excellent database, but I do get a chuckle that by far the greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had -- at least for temporary tables -- back in the 6.5/7.0 era, but then had removed). While it is hardly identical, an approach we did on one team is to have SQL Server take 64GB (note that it is per instance, and most server deploys see many instances on a single server, so that isn't quite as prohibitive as it might sound) and then have an enormous RAMDISK on which tempdb would be created. Made use of the RAM, and saved enormous amounts of IO (tempdb is the weak link of almost all SQL Server platforms, as everything hits it, especially if you make use of snapshot isolation / row versioning).

BrentOzar 13y ago
> I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)..... Great catch - fixed, thanks. > greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had back in the 6.5/7.0 era, but then had removed) Sorta kinda - back then, we had DBCC PINTABLE, and it would cache the data, but it would still require that the transaction logs and data pages be written to disk. Now with Hekaton, you can specify that a table never hits disk, ever. (Don't get me wrong, I don't think people are going to adopt that particular feature due to other limitations around datatypes and supported syntax.)
- corresation 13y ago
  I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb as being RAM resident automatically - http://support.microsoft.com/kb/115050 http://support.microsoft.com/kb/115050. Load up some global temp tables on db startup and rock and roll. It made tremendous sense because that's often an area of enormous churn (I saw a ServerFault post or something where someone did this and saw 30 iops to it, wondering why it didn't improve their system. That is nothing. One product I worked on saw literally hundreds of thousands of IOPS, and everything that sped tempdb sped the world).
  - chris_wot 13y ago
    Ah, temporary tables. Tell me, does SQL Server still have a single shared tempdb that is used by ALL databases on the server? If so, I can't understand why. Hash Joins? Often uses tempdb. Snapshot Isolation (otherwise known as multi version consistency by every othe major database vendor)? Uses tempdb. Reindexing? Uses tempdb. Temp tables, cursors or user objects used across your databases? Uses tempdb. I can't for the life of me understand why Microsoft built in this bottleneck into their database server!
    - hobs 13y ago
      Yep, though you can add additional data files to reduce SGAM contention. (Thank you Brent Ozar)
      - BrentOzar 13y ago
        HAHAHA, you're welcome, sir.
        chris_wot 13y ago
        Don't mean to shoot the messenger here, but if SGAM contention happens due to high tempdb usage, surely that's an architectural problem? From my reading this only happens when a lot of user objects are allocated - again in the tempdb! I'd love to know why Microsoft do so many things in a shared resource. In Oracle you can setup multiple temporary tablespaces, curious to see what Postgres does.
eli 13y ago
Microsoft's BizSpark (free software like SQL Server for startups and free credits for Azure) is chugging along. They're at least making an effort to convince new startups to build on their stack.
- corresation 13y ago
  Absolutely, and I'm an alumni of it (yuk yuk love that saying). The program is an incredible, ridiculous value. While you're in it. Then you leave it and start to try to scale out that Windows/.NET/SQL Server/App Fabric deploy you've built, and the expenses start to become very real.
  - harrytuttle 13y ago
    We're a Microsoft shop ( "gold partner" ). We have to throw nearly $1m at them every year and they reward us with expensive license audits and absolutely fuck all support. It still takes us weeks to get a hotfix for something yet if they think we owe them something, it's instant audit. It's bloody depressing really: products are going to shit, prices rising, support declining and we're treated like criminals. That's where you BizSpark customers are going to be the moment it expires. You have been warned.
    - supernovae 13y ago
      That's funny, I see it differently. Windows 2012 is finally coming of age with R2, HyperV is starting to shine competitively, PowerShell is kicking ass and taking names - Desired State Config FTW, Visual Studio is finally getting better C++ support, Windows 8 has HyperV built in, much better performance overall. Exchange has improved leaps and bounds when deployed correctly (hardware load balancer instead of its crappy mac address cloning hack) so on and so forth. The fact you essentially got 1m of free software and support over the what, 3-4 years in the program should have allowed you to better leverage your known expenses and plan accordingly. Also, Microsoft isn't nearly this evil unless you're doing something wrong, just license datacenter copies or hell, move to Azure and save even more money. Quite frankly, i'm calling BS.
CurtHagenlocher 13y ago
I would guess that SQL Server has been getting more expensive largely because the people who decide these things see themselves as competing with Oracle more than with free alternatives, and (I believe) SQL Server is still cheaper than Oracle. Disclaimer: I work at Microsoft in "the SQL org" but I'm just a peon so what do I know?
- BrentOzar 13y ago
  > and (I believe) SQL Server is still cheaper than Oracle. That explains the pricing of Enterprise, but not the crippled memory (64GB) or the lack of decent features in Standard Edition. Standard doesn't compete with Oracle - or if it did, it would get laughed out of town for a 64GB memory limit. Standard used to be a gateway drug that would get people hooked on good-enough performance and easy-enough management, but these days, crippled with $500 worth of memory, I don't think that reputation's going to hold out.
  - tracker1 13y ago
    Sorry, but 64GB of RAM is a LOT.. for that matter a db install that would need 64GB of memory is probably not something you would call short of "Enterprise" ... Beyond that, if you need something at that scale, often SQL isn't necessarily the right choice.
    - corresation 13y ago
      For $4,000 you can get yourself a shiny new Dell rack server with dual 8-core E5 Xeon CPUs and 192GB of memory. To license SQL Server Standard on that inexpensive device would cost $28,640, and would, as the blog post mentions, limit you to 1/3rd of that memory per instance. Databases live on memory, and 64GB just isn't a lot these days (nor is it "Enterprise" when it is vastly exceeded by a very small workgroup server). Their point is absolutely valid, and it is very strange that while memory capacities have exploded, the memory limit is the same that it was with SQL Server 2008 R2 (prior editions didn't handicap memory like this).
      - 13y ago
chacham15 13y ago
I thought (although I could easily be mistaken) that one of the big features about SQL Server 2014 was its lock free tables [1]. [1] http://sqlserverrider.wordpress.com/2012/12/31/hekaton-recap-sql-server-in-memory-technology/ http://sqlserverrider.wordpress.com/2012/12/31/hekaton-recap...
- BrentOzar 13y ago
  > I thought (although I could easily be mistaken) that one of the big features about SQL Server 2014 was its lock free tables [1]. Same feature, just a different benefit. Hekaton's tables are pinned to memory, use a different storage structure, can have their T-SQL statements compiled to native code, and avoid locking.
adamconroy 13y ago
All tables in sqlserver will be memory resident if there is available ram. But it happens dynamically, or is non-deterministic if you like. I assume the in memory feature allows you to force a table into ram and force it to stay there.
- BrentOzar 13y ago
  > All tables in sqlserver will be memory resident if there is available ram. Right, but Hekaton introduces the option of never requiring your tables to hit the disk if you don't want them to. Think data warehouse staging tables, web session state, or data marts that are easily recreated from source data.
- corresation 13y ago
  For sure, and a common strategy when you have large amounts of RAM is to pre-emptively prime the cache by doing unnecessary selects of all of the principal data and index sets. However the issue I'm talking about is primarily churn -- the writing to disk that is often the weak link in larger data systems. Many completely banal queries generate potentially enormous loads on the tempdb, without the user ever knowing otherwise. And if you're using temporary working tables for large-scale processes (this is common in financial data where based upon the requested data you need to build up from the base data, climbing the mountain until you yield the final rendered resultset. In between there may be hundreds of GBs of churn to tempdb). And then there's just generally ephemeral data that never, ever needs to live through a server restart, and that you never want fighting for the probably very limited IO.
harrytuttle 13y ago
They think we're captive. We're mostly not these days. We have our platform going through testing against Postgres and SQL Server at the same time thanks to the use of NHibernate. We have already done a successful trial migration which moved an entire vertical subsystem over. There are a few issues to iron out but we can switch out. We're not paying for SQL 2014 having been totally fucked over for SQL 2012 licenses due to the CPU to Core license change and our hefty DB cluster has lots of cores.
- Farox 13y ago
  Why is Postgres lack of query hints not scary for people?
  - harrytuttle 13y ago
    Because they are another form of coupling which is hellish to get out of if you want stuff to be portable, which as we found out when SQL pricing went through the roof is definitely a desirable feature in your application... Also we use an ORM (NHibernate) which abstracts the entire query and schema away from us. We load/perf test that and get on with life. If there are any blockers, it's 99.9% architectural or loading related which we cover with test cases. For us, the database is the hole we put our shit in when we don't want it in memory any more. Nothing more.
    - PommeDeTerre 13y ago
      In your other comment, you mention that "our hefty DB cluster has lots of cores", and that this caused licensing to be much more expensive. Have you considered that maybe the abstractions and attitudes you're using have inflated the amount of hardware you need? NHibernate, for example, can make it extremely easy to generate extremely poorly-performing queries. It often takes much more care and effort to have it generate mediocre queries than it takes to write good queries and any binding code by hand. The "abstracts the entire query and schema away from us" and "database is the hole we put our shit in when we don't want it in memory any more" attitudes don't help reinforce the idea that you guys know how to use relational databases properly. When teams go out of their way to remain as ignorant as possible about relational databases, while also using abstractions that are often inefficient, they shouldn't be surprised if their hardware needs (and their licensing costs, in some cases) balloon due to this inefficiency.

BrentOzar 13y ago

> I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)..... Great catch - fixed, thanks. > greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had back in the 6.5/7.0 era, but then had removed) Sorta kinda - back then, we had DBCC PINTABLE, and it would cache the data, but it would still require that the transaction logs and data pages be written to disk. Now with Hekaton, you can specify that a table never hits disk, ever. (Don't get me wrong, I don't think people are going to adopt that particular feature due to other limitations around datatypes and supported syntax.)

corresation 13y ago
I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb as being RAM resident automatically - http://support.microsoft.com/kb/115050 http://support.microsoft.com/kb/115050. Load up some global temp tables on db startup and rock and roll. It made tremendous sense because that's often an area of enormous churn (I saw a ServerFault post or something where someone did this and saw 30 iops to it, wondering why it didn't improve their system. That is nothing. One product I worked on saw literally hundreds of thousands of IOPS, and everything that sped tempdb sped the world).
- chris_wot 13y ago
  Ah, temporary tables. Tell me, does SQL Server still have a single shared tempdb that is used by ALL databases on the server? If so, I can't understand why. Hash Joins? Often uses tempdb. Snapshot Isolation (otherwise known as multi version consistency by every othe major database vendor)? Uses tempdb. Reindexing? Uses tempdb. Temp tables, cursors or user objects used across your databases? Uses tempdb. I can't for the life of me understand why Microsoft built in this bottleneck into their database server!
  - hobs 13y ago
    Yep, though you can add additional data files to reduce SGAM contention. (Thank you Brent Ozar)
    - BrentOzar 13y ago
      HAHAHA, you're welcome, sir.
      - chris_wot 13y ago
        Don't mean to shoot the messenger here, but if SGAM contention happens due to high tempdb usage, surely that's an architectural problem? From my reading this only happens when a lot of user objects are allocated - again in the tempdb! I'd love to know why Microsoft do so many things in a shared resource. In Oracle you can setup multiple temporary tablespaces, curious to see what Postgres does.

eli 13y ago

Microsoft's BizSpark (free software like SQL Server for startups and free credits for Azure) is chugging along. They're at least making an effort to convince new startups to build on their stack.

corresation 13y ago
Absolutely, and I'm an alumni of it (yuk yuk love that saying). The program is an incredible, ridiculous value. While you're in it. Then you leave it and start to try to scale out that Windows/.NET/SQL Server/App Fabric deploy you've built, and the expenses start to become very real.
- harrytuttle 13y ago
  We're a Microsoft shop ( "gold partner" ). We have to throw nearly $1m at them every year and they reward us with expensive license audits and absolutely fuck all support. It still takes us weeks to get a hotfix for something yet if they think we owe them something, it's instant audit. It's bloody depressing really: products are going to shit, prices rising, support declining and we're treated like criminals. That's where you BizSpark customers are going to be the moment it expires. You have been warned.
  - supernovae 13y ago
    That's funny, I see it differently. Windows 2012 is finally coming of age with R2, HyperV is starting to shine competitively, PowerShell is kicking ass and taking names - Desired State Config FTW, Visual Studio is finally getting better C++ support, Windows 8 has HyperV built in, much better performance overall. Exchange has improved leaps and bounds when deployed correctly (hardware load balancer instead of its crappy mac address cloning hack) so on and so forth. The fact you essentially got 1m of free software and support over the what, 3-4 years in the program should have allowed you to better leverage your known expenses and plan accordingly. Also, Microsoft isn't nearly this evil unless you're doing something wrong, just license datacenter copies or hell, move to Azure and save even more money. Quite frankly, i'm calling BS.

CurtHagenlocher 13y ago

I would guess that SQL Server has been getting more expensive largely because the people who decide these things see themselves as competing with Oracle more than with free alternatives, and (I believe) SQL Server is still cheaper than Oracle. Disclaimer: I work at Microsoft in "the SQL org" but I'm just a peon so what do I know?

BrentOzar 13y ago
> and (I believe) SQL Server is still cheaper than Oracle. That explains the pricing of Enterprise, but not the crippled memory (64GB) or the lack of decent features in Standard Edition. Standard doesn't compete with Oracle - or if it did, it would get laughed out of town for a 64GB memory limit. Standard used to be a gateway drug that would get people hooked on good-enough performance and easy-enough management, but these days, crippled with $500 worth of memory, I don't think that reputation's going to hold out.
- tracker1 13y ago
  Sorry, but 64GB of RAM is a LOT.. for that matter a db install that would need 64GB of memory is probably not something you would call short of "Enterprise" ... Beyond that, if you need something at that scale, often SQL isn't necessarily the right choice.
  - corresation 13y ago
    For $4,000 you can get yourself a shiny new Dell rack server with dual 8-core E5 Xeon CPUs and 192GB of memory. To license SQL Server Standard on that inexpensive device would cost $28,640, and would, as the blog post mentions, limit you to 1/3rd of that memory per instance. Databases live on memory, and 64GB just isn't a lot these days (nor is it "Enterprise" when it is vastly exceeded by a very small workgroup server). Their point is absolutely valid, and it is very strange that while memory capacities have exploded, the memory limit is the same that it was with SQL Server 2008 R2 (prior editions didn't handicap memory like this).
    - 13y ago

chacham15 13y ago

I thought (although I could easily be mistaken) that one of the big features about SQL Server 2014 was its lock free tables [1]. [1] http://sqlserverrider.wordpress.com/2012/12/31/hekaton-recap-sql-server-in-memory-technology/ http://sqlserverrider.wordpress.com/2012/12/31/hekaton-recap...

BrentOzar 13y ago
> I thought (although I could easily be mistaken) that one of the big features about SQL Server 2014 was its lock free tables [1]. Same feature, just a different benefit. Hekaton's tables are pinned to memory, use a different storage structure, can have their T-SQL statements compiled to native code, and avoid locking.

adamconroy 13y ago

All tables in sqlserver will be memory resident if there is available ram. But it happens dynamically, or is non-deterministic if you like. I assume the in memory feature allows you to force a table into ram and force it to stay there.

BrentOzar 13y ago
> All tables in sqlserver will be memory resident if there is available ram. Right, but Hekaton introduces the option of never requiring your tables to hit the disk if you don't want them to. Think data warehouse staging tables, web session state, or data marts that are easily recreated from source data.
corresation 13y ago
For sure, and a common strategy when you have large amounts of RAM is to pre-emptively prime the cache by doing unnecessary selects of all of the principal data and index sets. However the issue I'm talking about is primarily churn -- the writing to disk that is often the weak link in larger data systems. Many completely banal queries generate potentially enormous loads on the tempdb, without the user ever knowing otherwise. And if you're using temporary working tables for large-scale processes (this is common in financial data where based upon the requested data you need to build up from the base data, climbing the mountain until you yield the final rendered resultset. In between there may be hundreds of GBs of churn to tempdb). And then there's just generally ephemeral data that never, ever needs to live through a server restart, and that you never want fighting for the probably very limited IO.

harrytuttle 13y ago

They think we're captive. We're mostly not these days. We have our platform going through testing against Postgres and SQL Server at the same time thanks to the use of NHibernate. We have already done a successful trial migration which moved an entire vertical subsystem over. There are a few issues to iron out but we can switch out. We're not paying for SQL 2014 having been totally fucked over for SQL 2012 licenses due to the CPU to Core license change and our hefty DB cluster has lots of cores.

Farox 13y ago
Why is Postgres lack of query hints not scary for people?
- harrytuttle 13y ago
  Because they are another form of coupling which is hellish to get out of if you want stuff to be portable, which as we found out when SQL pricing went through the roof is definitely a desirable feature in your application... Also we use an ORM (NHibernate) which abstracts the entire query and schema away from us. We load/perf test that and get on with life. If there are any blockers, it's 99.9% architectural or loading related which we cover with test cases. For us, the database is the hole we put our shit in when we don't want it in memory any more. Nothing more.
  - PommeDeTerre 13y ago
    In your other comment, you mention that "our hefty DB cluster has lots of cores", and that this caused licensing to be much more expensive. Have you considered that maybe the abstractions and attitudes you're using have inflated the amount of hardware you need? NHibernate, for example, can make it extremely easy to generate extremely poorly-performing queries. It often takes much more care and effort to have it generate mediocre queries than it takes to write good queries and any binding code by hand. The "abstracts the entire query and schema away from us" and "database is the hole we put our shit in when we don't want it in memory any more" attitudes don't help reinforce the idea that you guys know how to use relational databases properly. When teams go out of their way to remain as ignorant as possible about relational databases, while also using abstractions that are often inefficient, they shouldn't be surprised if their hardware needs (and their licensing costs, in some cases) balloon due to this inefficiency.