Brent Ozar

For Microsoft SQL server, I recommend reading all things what Brent Ozar [1] has to say There is also another blog from Dr. DMV on SQL Server performance [2] [1] https://www.brentozar.com/ [2] http://www.sqlskills.com/blogs/glenn/category/dmv-queries/

Awesome, thank you. If you are on Microsoft SQL Server I additionally recommend to take a look at Brent Ozars sp_blitz[1] script collection and the according youtube series. Many optimizations apply for other databases, too. [1]: https://www.brentozar.com/blitz/

> Read this to see more from the small company side and how much of a mess it is: > https://www.brentozar.com/archive/2017/12/gdpr-stopped-selling-stuff-e… I read his post, and wonder if the 'attorneys' he consulted are specialists in EU law, or were his standard ones. There's a lot of FUD around GDPR but the guidance is getting better, and people assume it's being 'policed' when the UK's body (the ICO) is more about guiding into best practices than punishment. The comments (esp around medical records) suggest ignorance of how it's working. Source: reading the GDPR and accompanying notes from the ICO; discussions with a large company's GDPR advisor, who was involved in drafting the legislation. Disclaimer: this is not legal advice, I am not a lawyer, and you are an idiot if you act on anything I say.

> Any insight from HN insiders on how seriously companies big and small are taking this? We stopped selling online training in the EU because of it: https://www.brentozar.com/archive/2017/12/gdpr-stopped-selling-stuff-e… I actually love the idea behind GDPR, but as a small company, the cost of compliance (and heaven forbid, responding to an EU inquiry) is just too high relative to the low amount of revenue we get from EU citizens. I'm looking forward to revisiting that in 2019 as WordPress, WooCommerce, Gravity Forms, etc make it easier to be compliant, but right now there's just no way.

Yes, but StackOverflow does have a DBA, and very good one at that: http://www.brentozar.com/ I guess he doesn't deal w/ this level of optimization, or it never hit his radar.

SRE who deals with some .Net stuff that uses MSSQL but is converting to MySQL. so I feel somewhat qualified to talk about MSSQL. TL;DR: Nothing interesting going on. There is nothing to talk about here. It's boring database engine that powers boring business applications. It's pretty efficient and can scale vertically pretty well. With state of modern hardware, that vertical limit is high enough most people won't encounter it. It's also going the way of Windows Server which is to say, it's being sold but not a ton of work is being done on it. Companies that are still invested in it are likely because they don't care about cost ultimately or cost of switching is too high to greenlight the switch. Anyone who does care about cost like my current company has switched to OSS solutions like PostGres/MySQL/$RandomNoSQLOSSOption. My company switched away when turned into SaaS business and those MSSQL server costs ate into bottom line. This has been happening throughout the ecosystem. Proget which is THE solution for .Net Artifacts is switching to PostGres: https://blog.inedo.com/inedo/so-long-sql-server-thanks-for-all-the-fet… Also, I saw this article from Brent Ozar, who I see as MSSQL smart person, which basically said if you have the option, just go with PostGres: https://www.brentozar.com/archive/2023/11/the-real-problem-with-sql-se… It's also worth noting that Microsoft even bought PostGres scaling solution called Citus so they read the writing on…

The case of Wikipedia shows that efforts like that can be scaled but it's hard to find more examples. A counter example is the old DMOZ directory which was a continuation of the tree-style Yahoo web directory. That was subdivided into sections that were managed by "experts" (admins appointed to the sections) but the admins were lazy, corrupt or both. Even if they had been doing their jobs they would have been overwhelmed by hardly relevant "content marketing" submissions for web pages that are "200 OK", hot keywords, and a scam. You have to draw from a spam-free pool. For instance there is a data dump for stackoverflow https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-ov… personally i think stackoverflow is a junk web site full of more wrong answers than right answers and wouldn't it be nice to pick just the best answer to the question and not see the code sample at the top that didn't work that the original poster was asking about? If you are a real programmer, I mean you are shipping, you write a unit test, if it doesn't work they are gonna send it back to you, you may learn the hard way that answer #1 is not right (the compiler said so!) and scroll through a lot of half-baked discussion before you find that answer #7 passed the unit test, the acid test, all the other tests -- you are an expert and your opinion is worth much more than the "crowd". If "Road Scholars" like us were getting our experience fed back into…

For API gateways, I recommend APISIX: https://github.com/apache/apisix/ Claim: I am the core developer of APISIX, and I am also the core developer of OpenResty (known as Nginx + Lua). I have written Go for several years and I have contributed to github.com/golang/go. We have done some benchmarks around APISIX, Kong, Tyk. APISIX is the fastest (APISIX > Kong > Tyk). All of them have rich features and fantasy GUI. If people really care about the performance, it would be good to consider APISIX. Remember to benchmark every candidates in your own environment, even if you do not benchmark them correctly (every vendor complains others can't do benchmark correctly, some even don't allow others to do benchmark[1][2]), it is the way you use them in the production. Advertisement time is over. Let's talk something irrelevant to my employer. If you care about performance, forget about writing a plugin with a guest language. A guest language is a language doesn't supported by the gateway natively, like Go in Kong and Lua in Tyk. The performance waste in ctx serialization and IPC are huge. I have seen these complains for more than one. > Imagine how many crazy long tail problems nginx has already solved There is a problem I believe that API Gateways based on Go can't solve it unless Go have made its GC as good as Java's. Some people have consulted with me about replacing their Go implementation to a Nginx or Envoy one because this problem. [1]:…

The GDPR isn't as good as you think it is. It's going to turn into one of those laws where small software startups / or normal small businesses are just going to be in constant violation, because the amount of resources required to do it properly requires a team of 5 or 10 expensive software engineers. It's going to be a great way to nip small companies in the bud and consolidate this kind of stuff into bigger companies. It gets even worse with it's extraterritoriality, because you can still be under it if your dealing with a person who lies that they are an EU citizen and they are using your service in your own non EU country as a resident of the non EU country. It's like data FATCA. And there are other loopy catch 22 ambiguities, like if you want to delete someone from your audit log, do you delete their personal info (which is fairly expansive definition under GDPR) from the audit log too? Then how can you show you deleted the person's info if they are also deleted from your audit log? Read this to see more from the small company side and how much of a mess it is: https://www.brentozar.com/archive/2017/12/gdpr-stopped-selling-stuff-e… Think of this theoretical situation. If your an EU citizen vacationing in a developing nation who has a medical emergency, could that hospital just decide to reject you because the hassle & cost of dealing with GDPR is too great? Remember, it's a developing nation, they can choose to just refuse service to you. Kind of like…

So here is my take: I'm currently working on a SaaS product built on .NET running on Windows Server, IIS, and SQL Server. We're not really a "startup" anymore, but we're still very small. I wasn't here when the first version of the product was built so I wasn't part of that decision. If I were building this product from scratch today as a money-poor startup, I would probably go with tech stack included Java or Python and PostgreSQL. The primary motivation being cost (as in money). Each developer is technically required to have an MSDN/VS Pro license (renews for just under $1000 per year). This is not very expensive if you're generating revenue, but if bootstrapping it is not cheap. You could cheat and just share a single license; you definitely won't get caught. Windows Server licenses, compared to other datacenter costs, are relatively inexpensive. Windows Server has a few advantages; particularly in it's easy to administer and there are plenty of sysadmins out there than know it well. The elephant in the room, at least for a SaaS product, is SQL Server. The features of Enterprise Edition are so compelling for SaaS (HADR, unlimited RAM, online index operations, compression, and encryption) that it's practically a requirement. But this will quickly become one of the most expensive costs in your datacenter. But in my experience, SQL Server has some real advantages that I haven't yet discovered for PostgreSQL. The "community" around SQL Server is huge and has a ton of…

The single biggest thing that helped me speed up my queries and lower resource usage on the server was focusing on making my queries more sargable. https://en.wikipedia.org/wiki/Sargable https://www.brentozar.com/blitzcache/non-sargable-predicates/

Reminds me of this old post about deleting large amounts of data efficiently at MySpace. Page has now gone but was archived. https://web.archive.org/web/20090525233504/http://blogs.msdn.com/sqlca… Brent Ozar talked about this back in 2008 in reference to working with large tables in MSSQL Server: https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows…

Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. What does this mean? Running SELECT column1, ( SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id ) FROM table_a Results in "subquery must return only one column" as I expected. You mean returning the multiple columns as a record / composite type? Keep in mind GROUP BY clause usually dictates index use. The reason for this wasn't immediately apparent to me. For those who were curious, this blog post walks through it step by step: https://www.brentozar.com/archive/2015/06/indexing-for-group-by/

At least for the time-being, you can still do full downloads/extracts of stack for your needs, e.g.: https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-ov…

How very Oracle of them. https://stackoverflow.com/questions/12115397/is-it-against-license-to-… https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra… https://news.ycombinator.com/item?id=14232976 (2017)

We are absolutely talking about billions of records. But when you say "return excess data to the application" I'm not sure what you mean. Not doing lots of complex JOINs doesn't mean not filtering the queries at the DB at all. Nobody is pulling back a billion records at a time. Here's an example of what I'm talking about - read the first comment on https://www.brentozar.com/archive/2015/05/do-foreign-keys-matter-for-i… another venue for this same debate.

Some analysis of the Azure / Sql Server 2017+ automatic index tuning: https://www.brentozar.com/archive/2019/02/what-does-azure-sql-db-autom…

The Stackoverflow database is available for download, no need to scrape the site. https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-ov…

The “DeWitt Clause” or sometimes also known as the “Oracle Clause” has been a fixture of ToS for years. The fact that it features in Cloudflare’s docs only serves to remind us that entrenched practices die hard. https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

> It's such a high end niche that there's only a handful of companies that can even run the benchmark competitively Alternatively, you don’t see benchmarks because Oracle’s licensing bans posting benchmarks - https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

That is my understanding. [0] [0] https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra… https://dwheeler.com/essays/dewitt-clause.html

> There's basically 0 chance this would be legal if you're a private company. In the USA it would absolutely be legal and you would be bound by a binding arbitration clause and a non-disclosure + non-disparagement clause. You might even be bound by a license / ToS which prevents you from comparing the capabilities of that system against a better system[1]. [1] https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

remember that the database to SO is released under CC. https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-ov…

Microsoft SQL Server has the same clause in their license, unfortunately. https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra… So, you wouldn't be considering any commercial SQL offering, basically.

If it's in the contract not a lot you can do ... https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

The GDPR is pretty bad for under 5 engineer startups. They can be very privacy respecting small businesses that charge for their product, like sql training courseware[0], and wouldn't mind clearing what little personal data they had on you (user server logs, billing info, etc). But being privacy respecting and properly complying with GDPR with audit log systems, etc are 2 different things that require hiring 1 or 2 extra engineers or causing them to pause the companies roadmap as 1 or more people implement government bureaucracy infrastructure for half a year. If they say it's easy and cheap, then they haven't actually tried to do it properly and are exposing themselves to multi-million dollar penalties. [0] https://www.brentozar.com/archive/2017/12/gdpr-stopped-selling-stuff-e…

The sources in the wikipedia article are a bit older but apparently this is still a thing and SQL Server does it too. https://www.brentozar.com/archive/2018/05/the-dewitt-clause-why-you-ra…

> Mind sharing a link to your blog (if it's still active)? Thanks :) It seems to be there: https://www.brentozar.com/blog/ Bam. Inbound marketing seems to go well, a HNer just linked to the blog by inspecting the HN profile the consultant put on HN ... ;)

I have read it, as have our lawyers, which is why we have no choice but to block all EU traffic from our services. All fans of the law think it will only be used to go after "bad" guys. But that just isn't how laws like this are used in practice. They are designed to be easy to violate for a reason - that reason being to generate massive revenue from fines. You should take a look at this well reasoned blog post about why someone stopped selling their products in the EU - https://www.brentozar.com/archive/2017/12/gdpr-stopped-selling-stuff-e… There are just far more issues here than you're acknowledging.