5 ms·

Things Developers Should Know About SQL Server (2013)

136 points by neiesc 10y ago

BrentOzar 10y ago
Daaaang, on the HN front page. Glad you liked it. I don't wanna take money from HN folks if I can avoid it though - if you wanna get the course listed on the post, use coupon code hnfrontpage to get it free this week. Enjoy. (And if you're a bootstrapped startup using MSSQL, holler - I have a soft spot in my heart for that, and I'll do what I can to help.)
- lancefisher 10y ago
  Cool! Thanks. I'll definitely check out the video. We use MSSQL at our startup. Initially, because that's what the founders were familiar with, but it really is a nice database to work with.
__s 10y ago
Function tip should give special mention to inline table valued functions
cyberferret 10y ago
Nice article, and well done to the OP, but this post has actually reinforced why Microsoft's SQL Server is the LEAST favourite SQL database I have ever worked with in over 30 years. Routines tasks like setting granular permissions for users is a major exercise in confusion and "Lets see if this actually works like we expect it to..." That we have to use a completely separate database collections in MS-SQL for temporary tables, rather than have built in memory or non volatile tables as part of the core system is mystifying indeed.
- aussie123 10y ago
  you can have local temp tables, global temp tables as well as indexed views. always depends on what you want to accomplish.
guard-of-terra 10y ago
Which one?
whatnotests 10y ago
> Avoid ORDER BY; sort in the app instead. If you have unlimited memory, network bandwidth isn't a problem and the additional garbage-collection won't kill your performance. Otherwise, yeah, totally.
- millstone 10y ago
  I don't understand this. Isn't sorting an in-place procedure that does not need to allocate on the heap?
  - snuxoll 10y ago
    Sure, as long as you're okay with mutable data structures and the headaches they cause with threading.
  - twic 10y ago
    Depends on the sort. A naively implemented mergesort needs a temporary array of the same size as the the array being sorted. But whilst that takes up a chunk of memory for a while, it shouldn't be the sort of thing that makes GC choke. And anyway, not many people will be using a naively implemented mergesort in this situation!
- patrickg_zill 10y ago
  Does this same problem occur with PostgreSQL?
  - kbaker 10y ago
    No - since the metric used in the article is in terms of licensing-$, not a technical reason. > "SQL Server Enterprise Edition goes for about $7,000 per CPU core – not per processor, but per core. A two-socket, 6-core-each server rings up at around $84k – and that’s just the licensing costs, not the hardware costs. You can buy a heck of a lot of application servers (even ones with 256GB or more of memory) for $84k. > Consume all of the query results as fast as possible into memory in your app, and then sort." BUT - if you are using Postgres, you might be able to use BRIN indexes as a cheap index to speed up this kind of operation, without needing a full B-tree type index.
kbaker 10y ago
Also, [2013]. Lots of things have changed in the DB market over the past 4 years!
- ZenoArrow 10y ago
  What changes are you referring to?
- BrentOzar 10y ago
  > Also, [2013]. Lots of things have changed in the DB market over the past 4 years! Nothing about the article has changed in current versions of SQL Server, actually.
  - kbaker 10y ago
    Yes, the article still gives good advice for SQL Server. I was meaning in the more general sense - Oracle hadn't released 12c yet, Postgres was at 9.2, MariaDB just released 10.0. Lots of changes in the other DBs to make them more competitive. One thing that has seemingly changed for SQL server 2016 is the licensing cost... $14,256 per core now??? Or is that only in a 2-pack? https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... (Open Level)
    - BrentOzar 10y ago
      > One thing that has seemingly changed for SQL server 2016 is the licensing cost... $14,256 per core now??? Or is that only in a 2-pack? That's for a 2-core pack with Software Assurance (maintenance), so it's roughly half that. It's the same cost it's been since SQL Server 2012 when Microsoft switched to per-core licensing. Prior to that, it was per-socket, but still roughly the same cost for quad-core servers.
exclusiv 10y ago
> 7. SQL functions rarely perform well. So SQL Server is not good at SQL. How does it compare performance wise between alternative SQL DBs?
- ZenoArrow 10y ago
  >"So SQL Server is not good at SQL." That's not what was implied. SQL functions are snippets of SQL you wrap in a function to use in a larger query. The issue being suggested is that SQL Server will go into single-threaded execution when SQL functions are used. However, you can use stored procedures for the same purpose as functions without incurring the same performance penalty, so the advice was a warning about a specific SQL Server feature rather than the platform as a whole.
  - exclusiv 10y ago
    My mistake. I realize now the author is talking about user defined SQL functions and not app layer SQL functions for composing SQL. Thanks for the clarification.
exclusiv 10y ago
> 7. SQL functions rarely perform well. So SQL Server is not good at SQL. How does it compare performance wise between alternative SQL DBs?
alecco 10y ago
The avoid ORDER BY is the main reason not to fall into per-cpu licensing (most commercial data engines). That's the licensing trap they make. Also it's the same for other things requiring memory/cpu like JOIN and all that. You don't want to end up walking on eggshells.
- BrentOzar 10y ago
  > The avoid ORDER BY is the main reason not to fall into per-cpu licensing (most commercial data engines). That's the licensing trap they make. I used to agree with you, but in the cloud, all databases are essentially per-CPU licensing. It's just that instead of paying once up front, you pay by the hour. That's also why I love database administration as a long term career bet, too. Now, I can go performance tune someone's database queries, and they see an instant reduction in their monthly bill, thereby justifying the DBA cost. If I tune someone's on-premises licensed server, they don't get an instant rebate - the licensing and hardware is a sunk cost.
  - alecco 10y ago
    Sorry but it's not the same. When you use your own hardware you have to scale it to handle the peak usage times. This is often a small fraction of the day. I don't like putting databases in the cloud, but there you pay closer to actual usage instead of 24x7 at max capacity. BTW I was thinking in open-source options. Or commercial ones with a less draconian licensing (if there are any left).
    - BrentOzar 10y ago
      > I don't like putting databases in the cloud, but there you pay closer to actual usage instead of 24x7 at max capacity. Bad news again - with Amazon RDS and Azure SQL DB, you pay a fee based on instance size, and you can't scale up/down instantly with demand. (You can scale automatically, but it takes quite a bit of work - you don't "pay actual usage."
LoSboccacc 10y ago
"This is the postgres installer"
brightball 10y ago
I learned a lot from this, so thank you.
jarulraj 10y ago
Any war stories on the SQL Server Database Engine Tuning Advisor? How significantly has it affected the performance of the system?
scarface74 10y ago
If you are doing an order by on a single table on an indexed column, is that expensive? If you are doing an order by on a clustered index, wouldn't the order by be essentially free? But I never thought about it, but it does make perfect sense to sort on the app server and filter on the database. When hosted locally, it's practically nothing in terms of money, time to spin up another API server and add it to the load balancer compared to another SQL Server instance.
- BrentOzar 10y ago
  > If you are doing an order by on a single table on an indexed column, is that expensive? No, but it sure is unusual. I don't know a lot of folks who only reference a single table in their queries. (Folks who do are generally using key/value stores, not RDBMS's.) > If you are doing an order by on a clustered index, wouldn't the order by be essentially free? Assuming you're using an identity (sequential) field, that's also incredibly unusual. The clustering key rarely has value to the select query.

136 points by neiesc 10y ago

Daaaang, on the HN front page. Glad you liked it. I don't wanna take money from HN folks if I can avoid it though - if you wanna get the course listed on the post, use coupon code hnfrontpage to get it free this week. Enjoy. (And if you're a bootstrapped startup using MSSQL, holler - I have a soft spot in my heart for that, and I'll do what I can to help.)

lancefisher 10y ago
Cool! Thanks. I'll definitely check out the video. We use MSSQL at our startup. Initially, because that's what the founders were familiar with, but it really is a nice database to work with.

__s 10y ago

Function tip should give special mention to inline table valued functions

cyberferret 10y ago

Nice article, and well done to the OP, but this post has actually reinforced why Microsoft's SQL Server is the LEAST favourite SQL database I have ever worked with in over 30 years. Routines tasks like setting granular permissions for users is a major exercise in confusion and "Lets see if this actually works like we expect it to..." That we have to use a completely separate database collections in MS-SQL for temporary tables, rather than have built in memory or non volatile tables as part of the core system is mystifying indeed.

aussie123 10y ago
you can have local temp tables, global temp tables as well as indexed views. always depends on what you want to accomplish.

guard-of-terra 10y ago

Which one?

whatnotests 10y ago

> Avoid ORDER BY; sort in the app instead. If you have unlimited memory, network bandwidth isn't a problem and the additional garbage-collection won't kill your performance. Otherwise, yeah, totally.

millstone 10y ago
I don't understand this. Isn't sorting an in-place procedure that does not need to allocate on the heap?
- snuxoll 10y ago
  Sure, as long as you're okay with mutable data structures and the headaches they cause with threading.
- twic 10y ago
  Depends on the sort. A naively implemented mergesort needs a temporary array of the same size as the the array being sorted. But whilst that takes up a chunk of memory for a while, it shouldn't be the sort of thing that makes GC choke. And anyway, not many people will be using a naively implemented mergesort in this situation!
patrickg_zill 10y ago
Does this same problem occur with PostgreSQL?
- kbaker 10y ago
  No - since the metric used in the article is in terms of licensing-$, not a technical reason. > "SQL Server Enterprise Edition goes for about $7,000 per CPU core – not per processor, but per core. A two-socket, 6-core-each server rings up at around $84k – and that’s just the licensing costs, not the hardware costs. You can buy a heck of a lot of application servers (even ones with 256GB or more of memory) for $84k. > Consume all of the query results as fast as possible into memory in your app, and then sort." BUT - if you are using Postgres, you might be able to use BRIN indexes as a cheap index to speed up this kind of operation, without needing a full B-tree type index.

kbaker 10y ago

Also, [2013]. Lots of things have changed in the DB market over the past 4 years!

ZenoArrow 10y ago
What changes are you referring to?
BrentOzar 10y ago
> Also, [2013]. Lots of things have changed in the DB market over the past 4 years! Nothing about the article has changed in current versions of SQL Server, actually.
- kbaker 10y ago
  Yes, the article still gives good advice for SQL Server. I was meaning in the more general sense - Oracle hadn't released 12c yet, Postgres was at 9.2, MariaDB just released 10.0. Lots of changes in the other DBs to make them more competitive. One thing that has seemingly changed for SQL server 2016 is the licensing cost... $14,256 per core now??? Or is that only in a 2-pack? https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... (Open Level)
  - BrentOzar 10y ago
    > One thing that has seemingly changed for SQL server 2016 is the licensing cost... $14,256 per core now??? Or is that only in a 2-pack? That's for a 2-core pack with Software Assurance (maintenance), so it's roughly half that. It's the same cost it's been since SQL Server 2012 when Microsoft switched to per-core licensing. Prior to that, it was per-socket, but still roughly the same cost for quad-core servers.

exclusiv 10y ago

> 7. SQL functions rarely perform well. So SQL Server is not good at SQL. How does it compare performance wise between alternative SQL DBs?

ZenoArrow 10y ago
>"So SQL Server is not good at SQL." That's not what was implied. SQL functions are snippets of SQL you wrap in a function to use in a larger query. The issue being suggested is that SQL Server will go into single-threaded execution when SQL functions are used. However, you can use stored procedures for the same purpose as functions without incurring the same performance penalty, so the advice was a warning about a specific SQL Server feature rather than the platform as a whole.
- exclusiv 10y ago
  My mistake. I realize now the author is talking about user defined SQL functions and not app layer SQL functions for composing SQL. Thanks for the clarification.

exclusiv 10y ago

> 7. SQL functions rarely perform well. So SQL Server is not good at SQL. How does it compare performance wise between alternative SQL DBs?

alecco 10y ago

The avoid ORDER BY is the main reason not to fall into per-cpu licensing (most commercial data engines). That's the licensing trap they make. Also it's the same for other things requiring memory/cpu like JOIN and all that. You don't want to end up walking on eggshells.

BrentOzar 10y ago
> The avoid ORDER BY is the main reason not to fall into per-cpu licensing (most commercial data engines). That's the licensing trap they make. I used to agree with you, but in the cloud, all databases are essentially per-CPU licensing. It's just that instead of paying once up front, you pay by the hour. That's also why I love database administration as a long term career bet, too. Now, I can go performance tune someone's database queries, and they see an instant reduction in their monthly bill, thereby justifying the DBA cost. If I tune someone's on-premises licensed server, they don't get an instant rebate - the licensing and hardware is a sunk cost.
- alecco 10y ago
  Sorry but it's not the same. When you use your own hardware you have to scale it to handle the peak usage times. This is often a small fraction of the day. I don't like putting databases in the cloud, but there you pay closer to actual usage instead of 24x7 at max capacity. BTW I was thinking in open-source options. Or commercial ones with a less draconian licensing (if there are any left).
  - BrentOzar 10y ago
    > I don't like putting databases in the cloud, but there you pay closer to actual usage instead of 24x7 at max capacity. Bad news again - with Amazon RDS and Azure SQL DB, you pay a fee based on instance size, and you can't scale up/down instantly with demand. (You can scale automatically, but it takes quite a bit of work - you don't "pay actual usage."

LoSboccacc 10y ago

"This is the postgres installer"

brightball 10y ago

I learned a lot from this, so thank you.

jarulraj 10y ago

Any war stories on the SQL Server Database Engine Tuning Advisor? How significantly has it affected the performance of the system?

scarface74 10y ago

If you are doing an order by on a single table on an indexed column, is that expensive? If you are doing an order by on a clustered index, wouldn't the order by be essentially free? But I never thought about it, but it does make perfect sense to sort on the app server and filter on the database. When hosted locally, it's practically nothing in terms of money, time to spin up another API server and add it to the load balancer compared to another SQL Server instance.

BrentOzar 10y ago
> If you are doing an order by on a single table on an indexed column, is that expensive? No, but it sure is unusual. I don't know a lot of folks who only reference a single table in their queries. (Folks who do are generally using key/value stores, not RDBMS's.) > If you are doing an order by on a clustered index, wouldn't the order by be essentially free? Assuming you're using an identity (sequential) field, that's also incredibly unusual. The clustering key rarely has value to the select query.