3 ms·

I cringe every time I have to use SQL Server. 1) Weird gaps in ANSI compatibility (no concatenation with ||, no current_date, top n instead of limit/offset, et

I cringe every time I have to use SQL Server. 1) Weird gaps in ANSI compatibility (no concatenation with ||, no current_date, top n instead of limit/offset, etc.) 2) Just yesterday a client's SQL Server started selecting deadlock victims to be killed, so the DBA's workaround was to throw (nolock) hints on every table and read dirty data. It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. 3) The query planner seems amazingly naive for such a mature product. Hard to understand how a query with three where-clause constraints can execute in 20 ms, but adding a fourth constraint makes it run 240000 ms, but I see this kind of insanity every day.

gaius 8y ago
It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. SQL Server has like 4 different isolation levels + MVCC, you just pick the one you need. Postgres just does MVCC so it sounds like you should just switch SQL Server to that mode. A poor workman blames his tools. Unless that tool is MongoDB.
- panarky 8y ago
  > just switch SQL Server to MVCC In the real world of mediocre DBAs and databases managed by application vendors, it's never that easy, is it? Postgres just works with default settings while SQL Server chokes on the same transactions with the same volumes. SQL Server is legendary [0] for deadlocks even when the workload is mostly reads and rarely writes. You just don't see this with Oracle, Postgres or MySQL with default settings. Not sure why I'd pay for the privilege of extra headaches. [0] It was a problem in 2008, still not resolved https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/
  - manigandham 8y ago
    Yes the defaults are more pessimistic but why is such a problem to change the locking level? SQL Server has one of the most advanced query optimizers. Are you running on an old version or using out of date statistics?

gaius 8y ago

It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. SQL Server has like 4 different isolation levels + MVCC, you just pick the one you need. Postgres just does MVCC so it sounds like you should just switch SQL Server to that mode. A poor workman blames his tools. Unless that tool is MongoDB.

panarky 8y ago
> just switch SQL Server to MVCC In the real world of mediocre DBAs and databases managed by application vendors, it's never that easy, is it? Postgres just works with default settings while SQL Server chokes on the same transactions with the same volumes. SQL Server is legendary [0] for deadlocks even when the workload is mostly reads and rarely writes. You just don't see this with Oracle, Postgres or MySQL with default settings. Not sure why I'd pay for the privilege of extra headaches. [0] It was a problem in 2008, still not resolved https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/
- manigandham 8y ago
  Yes the defaults are more pessimistic but why is such a problem to change the locking level? SQL Server has one of the most advanced query optimizers. Are you running on an old version or using out of date statistics?