Hacker News Reader

> I assume that the linked blog intended to link to 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.)

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. 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).

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!

Yep, though you can add additional data files to reduce SGAM contention. (Thank you Brent Ozar)

HAHAHA, you're welcome, sir.

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.

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?

> 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.

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.

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).

That's a crazy deal, my Dell R610s with dual 6-core CPUs and 32GB RAM ran me about $4K each.

This was 2 1/2 years ago mind you, but I wouldn't mind the extra cores now.

I don't know much about MS SQL Server or database in general, what would you guys consider free and open source alternatives to MS and Oracle Enterprise offerings?

Where does mysql stand in all this?

Thanks.

Postgres has been getting a bit of interest from a few people in the MS dev community recently, e.g. http://datachomp.com/archives/im-leaving-sql-server-for-post...

Postgresql. Mysql is missing far too many basic features to be considered. One of the big reasons that so many fad driven developers jumped ship from mysql to nosql "dbs" was just because mysql can't even do online schema changes.

> Sorry, but 64GB of RAM is a LOT..

It's $500. When you consider that bringing in a performance tuning consultant can easily cost thousands of dollars per day, $500 worth of memory isn't much at all.

> for that matter a db install that would need 64GB of memory is probably not something you would call short of "Enterprise" ...

Remember that database servers use memory for at least 4 things: caching data, workspace for running queries, workspace for TempDB, and caching execution plans. If you run DMV queries against sys.dm_os_buffer_descriptors, you might be surprised at how little of your memory is being used to cache data. Even a 50GB database can get to a point where it's having to hit the disk in order to satisfy queries.

This is the age of "big data", as much as I hate to use that term. It's the age of 256GB USB thumb drives, laptops with 32GB of memory, and storing files in the database server. 64GB isn't large for databases anymore - in fact, it's a fairly unusual week when I'm dealing with a database that small.

Sorry I still don't get it. A database that actually used all 64GB of MEMORY--not disk--would store billions of customer records, and yet this is a small business? What small business stores billions of records of anything?

Yes I have seen many extremely poorly designed schemas that did take up huge amounts of memory, but that is easily corrected before launch. Don't store everything as a string, that's one way. But there are more reasons such a schema needs to be fixed, other than the $ cost of memory.

These days, it's fairly common to store larger fields or even files in the database. SQL Server's FileTable and FileStream fields are designed for that exact use.

Plus, remember that one server can be used for multiple databases. In SQL Server, it's fairly common to see a single server housing the accounting software, the payroll software, the email auditing back end, VMware Virtual Center's databases, etc.

How do you figure you could store "billions" of customer records in a 64GB memory space? That's 68 billion bytes, and you lose a very significant portion of it to things that aren't base table storage. Never mind cached query plans.... how about indexes? If you consider a table containing a customer name, address, telephone number, and a couple of other basic pieces, you could be looking at a few kb for each record. That'll get you closer to a total potential storage of 20m records. Not billions.

Oh, and I have seen small businesses running SQL Standard with databases exceeding 500GB and individual tables with over 1.5 billion rows -- and the tables were designed efficiently! They couldn't afford Enterprise because of the tight profit margin nature of their line of work. What I'm saying is, don't discount the data needs of small business.

Telecom. We store tight, tiny columns. We've got 100s of GB of data. Many records are transactions that earned us nothing (call didn't connect, yet tried several attempts). We're not a large business by any measure.

Even smaller companies in other fields might want to store tons of rows. User action data, for instance. Living in the past and insisting 64GB of MEMORY is somehow huge is just being silly.

64GB for database systems is still fairly large amount of ram for Databases and still largely considered an enterprise class server.. I know lots of several multi billion dollar companies running their financials and oltp systems on servers with <64gb ram. Also, people don't generally by over the counter ram for their servers, they're spending extra on ECC memory correcting ram.

The blog post did link to ECC RAM (4 x 16GB DDR3 RDIMMs)[1]. It does seem that most of them are above $600 though.

[1]: http://www.newegg.com/Product/ProductList.aspx?Submit=ENE&N=...

Most servers aren't serving a single large database, but multiple databases with multiple applications. Plus you add some ETL via SSIS, some reporting, a few ad-hoc queries, etc and memory goes like Doritos and beer at Mississippi Super Bowl party. My smallest test SQL Server for development that isn't virtualized has 32GBs and we are considering changing it's role to something less taxing...

I think the tension might be that looking back, we see many big (money making) operations that ran on far less than 64GB(1), but looking forward we can all think of fun hacks we can do on cheap and common 64GB machines.

It's another way of saying that yeah, that's old-world pricing.

(1 - I remember when friends told me they were real-time tracking all the telephone calling cards in a moderate sized country, with a Sun and 1G of memory.)

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.

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.

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.

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.

License compliance with Microsoft software is a lot of trouble and risk, and it, more than the paper cost, is often the worst thing about using Microsoft stuff.

"Simple" scenarios like maintaining a warm replicated data center becomes a ridiculous exercise of license guessing, where every Microsoft rep tells you something different. Licensing newer instances of Sharepoint is simply a riot.

I'm not saying there aren't advantages, but it's nice as platform and system architects to plan out hardware and software and be done with it, and not get distracted for months discussing exactly which stack of licenses you need from Microsoft.

Or you could just ignore it and hope they don't come knocking. BizSpark is actually a great example where Microsoft encourages that -- the graduation benefits are in many ways incredibly vague (not to mention that the program duration and benefits are changing constantly). Right now when you graduate you get-

up to 4 Windows Servers (Standard Edition) and 2 SQL Servers (Standard Edition)

What does that mean? Per user/device copies of SQL Server, because that would be completely useless to any company serving the internet in any way. Two core copies, because again that's laughably anemic. What does it mean?

Yet almost no one actually talks about what it means because who cares, right? Until the day that the BSA comes knocking because a disgruntled former employee left a tip.

Totally agree. The licensing is ridiculously opaque and complex.

As a simple example: If I understand correctly, BizSpark lets you install a couple of copies of Office and use it for regular word processing tasks within your startup. However, you cannot install the BizSpark Windows 8 desktop license -- those are only for testing and development. I think. There's no indication of any restrictions on the license key download page.

THIS! This is one of the two largest arguments in favor of OSS. With a PG server all I have to do is throw it up, and not worry about the licensing. That's extra time back in my day for real work. No stress, no fuss, no muss.

Rubbish.

HyperV is years too late. We've had VMware since 1998 and Xen, KVM etc for years as well.

PowerShell - Shit crock from end to end. It's a rotten mess of an over-complicated, inconsistent environment that barely works and performs abysmally at the best of times. They can't even get the story straight on dates. Half of it is UTC and half local time and when you mix, boom!

Desired State Config - We had this 20 years ago on Sun machines, plus it doesn't work very well and is not holistic.

VS2012 - Yeah a little better but still crashes 5 times a day with HRESULT errors and throws your workflow out of the window.

Exchange - "when deployed correctly". Enough said.

What! We get fuck all support. 30 support credits a year which we use up in a month and have to wait 6 months plus for a solution in most cases!

Have you ever tried to decipher microsoft licensing? Probably not - it's a rats nest that means you're always guilty of something.

We can't move to Azure - we have financial, insurance and healthcare clients. None of them want Azure.

I think you're looking through some rose-tinted glasses there.

Intrigued by your statement I googled about and haven't been able to find any articles that cast doubt on Azure's suitability for financial, insurance or healthcare. Nor any articles that claim poor attitude of these industries towards azure in general. Actually, most of what I have encountered - are fairly enthusiastic articles, case studies and white papers claiming just the opposite. Would you care to provide some information to substantiate your claims? Thanks.

Fair enough... TANSTAAFL. You at least get to keep all the licenses you obtained while in the program, right?

Personally, it's the license compliance overhead more than the fees that drives me nuts about Microsoft. They make it hard to give them money.

Yes you get to keep all of the licenses obtained during the program.

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.

Why is Postgres lack of query hints not scary for people?

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.

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.

The question is, what's cheaper- labor for optimizing the queries + paying (possibly reduced) SQL server licensing, or keeping the "mediocre" queries + throwing in more hardware?

Hardware is cheaper. Running postgres!

Actually no, it's load. We have 80 million page hits a day and each of these can run 10-20 queries on a 2TiB dataset. We're not a small outfit.

We use Nhibernate profiler to check our SQL output and all of our queries and make sure we're not doing anything stupid.

We know how to use relational databases, but it's expensive and hard representing our problem domain with them so an OO system is better.

We optimise later by switching the engine out to something cheaper. At least we CAN do this with an ORM abstraction without too much pain.

After all, premature optimisation is the root of all evil isn't it?

> We have 80 million page hits a day and each of these can run 10-20 queries on a 2TiB dataset.

Why is each page hit running 10-20 queries?

Because they are extremely complicated.

Because they are a solution in search of a problem. Or more accurately, they are a problem in search of a problem.

http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion

Hmm, I needed them a few times so far, specially when it comes to lots and complex sets of data and complex queries.

I had jobs run for a few hours on Dev only to take weeks (if I would have let them) on production, which was in ever aspect a beefier machine.

99% of the hinting I've seen people "need" was not needed at all, it was just the lazy way to temporarily work around a bad query plan. Other than actual bugs in the query planner, I can't even invent a hypothetical scenario where the tools postgresql already gives you to influence the query planner couldn't solve a problem.

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...

> 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.

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.

> 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.

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.