3 ms·

> Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system? Uhh, yep, that's exactly how a

> Surely you wouldn't run analytics directly on your prod serving database, and risk a bad query taking down your whole system? Uhh, yep, that's exactly how a lot of businesses work. There are defenses at the database layer. For example, in Microsoft SQL Server, we've got Resource Governor which lets you cap how much CPU/memory/etc that any one department or user can get.

speleding 7y ago
That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with complex joins, production has many parallel short queries. If you start mixing the two, you can no longer reliably tune your database. For example you would want the alert for slow queries set set to a different time out on production and on reporting. Also, you complicate locking down access to the database. A reporting database can typically contain less sensitive info, reporting would not have password (hashes) for user accounts for example.
- mwigdahl 7y ago
  I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...
  - jsjohnst 7y ago
    > I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.
    - BrentOzar 7y ago
      > Reading other comments Brent’s made here, I’m not so sure. No no, it's not a good idea, but it's just the reality that I usually have to deal with. I wish I could lay down rules like "no analyst ever gets the rights to query the database directly," but all it takes is one analyst to be buddy-buddy with the company owner, and produce a few reports that have really high business value, and then next thing you know, that analyst has sysadmin rights to every database, and anybody who tries to be a barrier to that analyst's success is "slowing the business down."
- SigmundA 7y ago
  Pretty trival to setup read replicas using log shipping in MSSQL and postgresql to offload analytical loads to secondary servers. I work on a monolith that does this, but its usually not even necessary a single db server on modern hardware with proper resource governing can handle quite a bit.
jsjohnst 7y ago
> Uhh, yep, that's exactly how a lot of businesses work. Just because a lot of businesses do it, doesn’t mean it’s a good idea. A lot of businesses don’t do source control of any kind, so should we all do that too?

speleding 7y ago

That's not a good idea. The usage patterns for a production database and one that runs reporting are very different. Reporting has long running queries with complex joins, production has many parallel short queries. If you start mixing the two, you can no longer reliably tune your database. For example you would want the alert for slow queries set set to a different time out on production and on reporting. Also, you complicate locking down access to the database. A reporting database can typically contain less sensitive info, reporting would not have password (hashes) for user accounts for example.

mwigdahl 7y ago
I don't think Brent was necessarily saying it was a good idea, just something that is commonly seen. At my own company we try hard to get customers to run reports against replicas/extracts rather than production, but some customers insist that they absolutely _need_ to pull in live data from production. So they run complex reports against a production OLTP schema and wonder why the system is running slow...
- jsjohnst 7y ago
  > I don't think Brent was necessarily saying it was a good idea Reading other comments Brent’s made here, I’m not so sure.
  - BrentOzar 7y ago
    > Reading other comments Brent’s made here, I’m not so sure. No no, it's not a good idea, but it's just the reality that I usually have to deal with. I wish I could lay down rules like "no analyst ever gets the rights to query the database directly," but all it takes is one analyst to be buddy-buddy with the company owner, and produce a few reports that have really high business value, and then next thing you know, that analyst has sysadmin rights to every database, and anybody who tries to be a barrier to that analyst's success is "slowing the business down."
SigmundA 7y ago
Pretty trival to setup read replicas using log shipping in MSSQL and postgresql to offload analytical loads to secondary servers. I work on a monolith that does this, but its usually not even necessary a single db server on modern hardware with proper resource governing can handle quite a bit.

jsjohnst 7y ago

> Uhh, yep, that's exactly how a lot of businesses work. Just because a lot of businesses do it, doesn’t mean it’s a good idea. A lot of businesses don’t do source control of any kind, so should we all do that too?