Techniques for Microsoft SQL database design and optimization | Hacker News Reader

Techniques for Microsoft SQL database design and optimization

apriorit.com

99 pointsDenTurpitka47 comments

Here's my SQL Database Optimization Guide:

Ignore all the advice about "rules". Create a realistic dataset and run performance tests before you release anything into production. Check your query-plans to find the pain-points. The database is frequently treated black box surrounded by superstition and cargo-cult "rules" that are either out of date or just educated guesses.

Wrap anything weird in a view so you can rebuild it as you see fit.

Avoid getting fancy as much as possible until the simple, normalized, naive implementation fails.

This IS the answer for all databases. Start simple, do straight forward queries, and then measure, measure, measure.

Unfortunately query plan builders can do odd things. Is IN faster than EXISTS? Sometimes yes, sometimes no. The only way to know is to test, and this is true across the board.

Not only is it sometimes faster, and sometimes slower, but the answer can change, for the same schema, for the same query, depending on the volume and distribution of data.

Can you please provide an example of answer changing?

Offhand, I don't remember the specifics - I observed this a decade ago. Sorry. I don't have a SQL Server around to try to craft it either.

The gist of it is, the query planner for some database engines ( pg and mssql ) often takes cardinality into account when selecting plans. Therefore those plans change with the size of the dataset.

EDIT: It occurs to me to wonder if you're asking if the resultset changed. No, I've not seen that answer change.

It's been while since I have spent time with MSSQL, but I remember seeing this with sprocs and cardinality. The sproc would compile with 1 plan that dealt with tables with little data. The plan would not always get updated as the tables fill with data.

The underlying reason is that as data changes the plan changes. For example, on a small table, it is faster/more efficient to do a table scan than go to the index and then to the table for data. At some threshold this changes. If table size can change the plan, then the query itself could also need changing as the table grows, indexes grow, and differing index cardinality emerges.

Yes. Every day. The developers here write SQL that runs acceptably for a given schema at a given point in time. Unfortunately, the code pattern isn't scalable (which is generally why people say that RDBMS' can't "scale" anyway, but that's a different rant) and performance changes, sometimes dramatically. Where a LEFT OUT JOIN ... WHERE outer.value is NULL worked great for an anti-semijoin earlier, NOT EXISTS may be better. What jpitz is talking about are statistics that are tracked by the engine. They are a key part of how SQL Server's CBO determines the best plan to access the data. As data grows, chances are you're going to get skew on the keys that you've defined. If your data is such that the counts and distinct counts of your keys are guaranteed to remain consistent, then you have a lower chance of plan changes as data scales.

The very fact that IN can produce different query plans than an equivalent EXISTS is a very nasty smell. As a developer, I shouldn't have to care. The database is supposed to figure that out. That's why we're paying no small sum of money for this.

> Create a realistic dataset and run performance tests before you release anything into production.

This is so true.

Depending on your write patterns and your queries, a realistic dataset can mean:

1. Same distribution of keys as production

2. Same rate of writes as production

3. Same order of writes as production

4. Same volume of data at rest as production

( edited for formatting )

Agreed, fancy and clever in optimization/design is super dangerous especially since you don't know what will/won't work until you measure. Also if you do "Wrap anything weird in a view" (which is an acceptable approach) realize that any and all of that part of what you've built could be the problem.

The best query plan can look good even on a representative data set but your customers don't always have representative data sets and they don't really care if it works on the bench with 10 million records if their 10 million record data set does not work.

I have found that Use The Index, Luke is a good reference when looking at optimizations. It's also nice that it covers most of the major SQL databases, so you can see differing optimizations and how to handle them.

http://use-the-index-luke.com/sql/preface

That is a great resource.

Here are some I've used:

Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL Querying https://books.google.com/books?id=FZlCAwAAQBAJ&printsec=fron...

Good resource for designing high performing queries and investigating performance problems.

PostgreSQL 9.0: High Performance https://books.google.com/books?id=OWOAu0GcsqoC&printsec=fron...

Greg lays out a great foundation for doing performance analysis that is largely applicable to any rdbms platform.

Optimizing Oracle Performance https://books.google.com/books?id=D7ElCwAAQBAJ&printsec=fron...

Another great book that explains the process of approaching and solving performance problems.

Joe Celko's SQL For Smarties https://books.google.com/books?id=i-19BAAAQBAJ&printsec=fron...

Everyone writing SQL should read Joe.

What intrigues me about Joe Celko's book is that he is completely anti all forms of auto indexing (and it makes sense) but is the assumption for all the web frameworks that I come across. Most recently I hit this when looking at yesod.

Good list here. A few things I would add about execution plans specifically.

1. SET STATISTICS IO ON. This is much more useful than setting time on imo. This will give you physical / logical reads per table as well as how specifically they were fetched. Usually I don't care if the query took 11 seconds, I want to know what took those 11 seconds.

2. The % numbers that everyone relies on when skimming execution plans are total BS. No one seems to realize this, but the percentage values are based off of the estimated query plan even when you're running the actual execution plan. Use the plan to determine which operators were chosen, disregard the % values. To get the true amount of work done, look at stats IO above. If your underlying issues is missing or stale stats for example, that incorrect data will pass through to your execution plan and that plan will lie to you.

3. Try to determine why a plan is getting generated. Don't just keep trying wacky code changes until you can get the correct plan (once..), find out what the optimizer is seeing and why it's doing what it's doing. You may know what operation is best in the current moment ("this was much faster as a nested loop") but instead of using a join hint, force the optimizer's hand by correcting whatever underlying issue is making it think that the merge/hash is a better route. This will save future-you hours of head-bashing when, inevitably, that join hint that was added three years ago causes a big production issue.

A lesser known hint I remember having heard. If you use an indexed column in your query, use the same character encoding in the query than in the column, otherwise you end up with a full scan (like varchar vs nvarchar).

This is also true for date types. Be careful when you use date trunc functions.

Wow! Never heard this one before. I will have to research this. Thanks!

I'm a junior DBA so take what follows with a grain of "he probably has no idea what he's talking about":

One of the most frustrating things coming from a world of say python or C is a function that implements an algorithm of say O(n) it will run in O(n) all-day-everyday but if a table's stats are out of date or there's a missing index or anything related goes awry a typically fine running query could perform terribly. And that's infuriating but it's the nature of the beast I guess.

Yup. As developers we're always told to trust the database and to use proper, normalized relational theory and how it's so much better that in the database we just say what we want instead of specifying exactly how to calculate it like we do in the client programming languages...

But that beautiful kernel of relational theory is wrapped up in a six-foot-ball of duck-tape, bubble-gum, and hot glue in the form of a zillion hacks and leaky abstractions.

At a certain point it really feels like databases are failing to live up to their promises. I expect things like sharding to be hard. I'm okay with that. But if I have one database server, I expect to be able to create tables, maybe indexes, and for it to Just Work given that information. That's what we're paying for.

Until there's an automated way for a database to tell a developer that their particular query is running terribly because of some defacto reason there'll always be a need for a DBA to interpret and investigate, though, I wish it weren't so. I wish we could just treat databases as "dumb" stores of information that could be queried in n number of ways without issue. Some or most DB-as-a-service offerings will handle the mundane tasks of backing up, upgrades, etc. but I know of none that offer this DBA-like level of service where an automated tool can tell you exactly what's going wrong, basically take the context of your query and match it with the current execution plan and the underlying structure of the tables and your data (data types included) and tell you what is wrong. That'd be a huge innovation if it exists. And I'd be out of a job.

Another issue with sql server which is very baffeling. It is MUCH faster to use sub selects than left join if you data allows it.

That is do not:

select a.bla, b.blabla from foo a left join bar b on (a.id = b.id)

Do:

select a.bla, (select blabla from bar where id = a.id) as blabla from foo a

I have reduced a big etl query with about 20 left joins from 2 hours running time down to 7 minutes by this.

And before you ask, yes the tables have been properly indexed, it is just that left join performance in sql server is very bad in some situations.

Did you compare the execution plans of both queries? Without doing that, it's hard to know why the planner did what it did. Maybe statistics were off and it was choosing a worse plan for the left join because it thought it'd be faster, where the sub-select it was forced into what should have been an inferior plan (if statistics were correct), which in reality was faster.

yes I, too, would like to see more on this. I am skeptical that doing sub queries is faster than joins

Technically the sub select is a semi join which could be optimized differently than left join (and could give different results). If bar.id had a unique constraint though the queries should generate identical plans.

Well, I simplified the example a bit, for the real query bar.id is not unique and has to be constrained by other means.

Think an application/case with a table with credit scoring variables where each variable has a case id and a variable type id, so i had to join the same table multiple times with different criteria. But replacing all of the left joins here with subselects did reduce the query time by an insane amount.

However I have seen this behaviour with unique indexed left joins too just not so extreme.

And for comparison (i have the same schema in my analytics postgresql database), the exact same query here takes about 1.5 minutes with left joins.

The two statements, as mentioned by gfody, are not equivalent. For the first statement, there can be any number of output rows based on the join condition. For the second statement, by definition, there can only be output row per input row in foo. That's a lot more information that the query planner can use to make optimizations.

The table _does_ have a unique index on the combination of caseid and type, but I'm not sure if that is relevant.

It's possible, as the other commenters mentioned, it would come down to reviewing the query plan. I was just trying to address the two parts of your original comment, which were "is very baffeling" and "left join performance in sql server is very bad in some situations.". Hopefully my answer helped to un-baffel a little.

You've got this backwards. Assuming the join brings back at most one row (i.e. The join key is a unique key) the left join is better because it can be optimized differently. The sub select must be done as a nested loop but the join can be done via a hash join. For a full data set (not just one of selected) it'd be more efficient. Simply count the net logical IOs.

That is why I think this is baffeling behaviour by sql server since in certain queries you can get an order of magnitude faster execution by dropping left join and using subselect. It's counter intuitive but try it in a real dataset.

This is true. I've done this in production and shaved a lot of times of some queries.

Is it just Firefox or is this page a pioneer of "staunchly unresponsive design?"

Edit: nope, Chrome too. I literally can't fit all the text on a small screen, no matter how feverishly I pinch, rotate or pan!

Same issue on my iPhone 5s. I changed to text mode and was able to view.

It may no longer be current, however, the query parser has an easier time if you use semicolons. After making a habit out of this for a few years, I find that the brain has an easier time too.

Some tips from a SQL Server DBA:

* DON'T USE USER DEFINED FUNCTIONS IN ANY PREDICATE OF ANY TYPE EVER, THIS IS DEATH.

* If you want things to go fast, consider that your query needs to be able to be converted into a "search argument" to use any of those precious indexes you made, so:

LIKE 'hdfhafsdh%' -- This works!

LIKE '%fafads%' -- Scanning all the things, so sad.

In the same vein, any function you use on your values in the tables mean that there are often no indexes you can use to help your operation (with a few technical exceptions around specific optimizations SQL Server has around datetime <-> date calculations )

Download SQL Sentry Plan Explorer - it just became free and with it you can shred apart your execution plans with ease and keep histories of the work you have done on a query, or watch a plan change over time as your tune it with ease.

https://sentryone.com/plan-explorer

Please don't use all-caps for emphasis. This is in the HN guidelines:

https://news.ycombinator.com/newsguidelines.html

in that vein inline table valued functions are preferred if you must use one:

http://stackoverflow.com/questions/2554333/multi-statement-t...

Reply on news.ycombinator.com