Things Developers Should Know About SQL Server (2013)

https://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/

by neiesc • 9 years ago

136 34 9 years ago

BrentOzar8• 9 years 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• 9 years 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• 9 years ago

Function tip should give special mention to inline table valued functions

cyberferret• 9 years 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• 9 years 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• 9 years ago

Which one?

whatnotests• 9 years 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• 9 years ago

I don't understand this. Isn't sorting an in-place procedure that does not need to allocate on the heap?

snuxoll• 9 years ago

Sure, as long as you're okay with mutable data structures and the headaches they cause with threading.

twic• 9 years 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_zill2• 9 years ago

Does this same problem occur with PostgreSQL?

kbaker3• 9 years 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.

BrentOzar8• 9 years ago

If you have unlimited memory, network bandwidth isn't a problem and the additional garbage-collection won't kill your performance.

News flash: your database server doesn't have unlimited memory either.

About network performance - if you have such a large result set that you're worried about network performance, than the sorting work in the database is going to be very non-trivial as well. App servers scale out inexpensively - SQL Server does not.

olavk• 9 years ago

It doesn't take more bandwidth to transfer unsorted than sorted data, and the memory cost will presumably be the same for sorting on the server as on the client. Filtering should always happen in the database for the sake of bandwidth and performance, but this doesn't apply to sorting the resultset since sorting doesn't change the amount of data.

(Unless the sorting is an intermediate step before further filtering, in which case the advice obviously does not apply.)

kbaker3• 9 years ago

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

ZenoArrow2• 9 years ago

What changes are you referring to?

BrentOzar8• 9 years 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.

kbaker3• 9 years 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-p... (Open Level)

exclusiv2• 9 years 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?

ZenoArrow2• 9 years 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.

exclusiv2• 9 years 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.

alecco2• 9 years 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.

BrentOzar8• 9 years 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.

alecco2• 9 years 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).

LoSboccacc• 9 years ago

"This is the postgres installer"

brightball• 9 years ago

I learned a lot from this, so thank you.

jarulraj• 9 years ago

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

scarface74• 9 years 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.

BrentOzar8• 9 years 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.

Things Developers Should Know About SQL Server (2013)

Related Stories