4 ms·

The other half of this is looking sql performance. Not being a DBA I'm less proficient in this area and can't recommend a good tool :(

by 6DM 10y ago

Thr0waway65 10y ago
There is the excellent Mini Profiler (http://miniprofiler.com/ http://miniprofiler.com/) by Sam Saffron. It originated while he was at Stack Overflow, I believe. There is also Glimpse (http://getglimpse.com/ http://getglimpse.com/) which includes a few more things but has similar goals.
- 6DM 10y ago
  miniprofiler seems like it's only good when you want to target a very specific area and try to report while in production. I don't like the idea of mixing profiling in with the code. Glimpse looks awesome though and I'm surprised they're not charging for it.
partycoder 10y ago
MS SQL Server for instance comes with its own profiler. You can look for slow queries, then try to understand why by getting the query execution plan. You can alternatively also look for queries with a high standard deviation, which can hint of queries that get slower as there are more rows associated with a user. Once you have the execution plan, you can either optimize the query, optimize the schema (add indices), or simply optimize the application (e.g: narrow the scope of the query, reduce call count). That however, won't allow you to horizontally scale. The only realistic way to horizontally scale is sharding. Replication will slow down your writes as more machines are added and that simply does not scale. Using the same database for everything and everyone is a lousy way to go. Eventually you will need to keep things on different machines at some point. Then, don't put large blobs into a database and store just the blob IDs in your database. Use another data store for that. Analytics, logging, etc doesn't belong into an OLTP database. Use another database for that not your OLTP one.
- 6DM 10y ago
  I tried analyzing execution plans for a fairly large stored procedure. I only needed to do it a few times. Everytime resulting in the DBA informing me that they've already analyzed that before and it was understandable why I couldn't make any real headway. Side note I found a free pdf for analyzing execution plans in this stackoverflow answer (toward the bottom): http://stackoverflow.com/a/7359705/834579 http://stackoverflow.com/a/7359705/834579 Would you consider logging user actions, for reporting in website, as something that should be stored else where? In one case I can think of the table had about 7 million records, but nobody seemed concerned about it.
  - partycoder 10y ago
    If it's just a log then you can "merge on write" (nosql) instead of "merge on read" (join),
daigoba66 10y ago
One tricky thing about databases is that the performance characteristics of a database running on local dev hardware versus production are quite different. Therefore it's important to capture and analyze performance metrics _from production_. A really good commercial tool is SQL Sentry (http://www.sqlsentry.com/ http://www.sqlsentry.com/). You can, of course, patch together your own tooling from open source and homegrown solutions.

The other half of this is looking sql performance. Not being a DBA I'm less proficient in this area and can't recommend a good tool :(

by 6DM 10y ago

The other half of this is looking sql performance. Not being a DBA I'm less proficient in this area and can't recommend a good tool :(

Thr0waway65 10y ago

There is the excellent Mini Profiler (http://miniprofiler.com/ http://miniprofiler.com/) by Sam Saffron. It originated while he was at Stack Overflow, I believe. There is also Glimpse (http://getglimpse.com/ http://getglimpse.com/) which includes a few more things but has similar goals.

6DM 10y ago
miniprofiler seems like it's only good when you want to target a very specific area and try to report while in production. I don't like the idea of mixing profiling in with the code. Glimpse looks awesome though and I'm surprised they're not charging for it.

partycoder 10y ago

MS SQL Server for instance comes with its own profiler. You can look for slow queries, then try to understand why by getting the query execution plan. You can alternatively also look for queries with a high standard deviation, which can hint of queries that get slower as there are more rows associated with a user. Once you have the execution plan, you can either optimize the query, optimize the schema (add indices), or simply optimize the application (e.g: narrow the scope of the query, reduce call count). That however, won't allow you to horizontally scale. The only realistic way to horizontally scale is sharding. Replication will slow down your writes as more machines are added and that simply does not scale. Using the same database for everything and everyone is a lousy way to go. Eventually you will need to keep things on different machines at some point. Then, don't put large blobs into a database and store just the blob IDs in your database. Use another data store for that. Analytics, logging, etc doesn't belong into an OLTP database. Use another database for that not your OLTP one.

6DM 10y ago
I tried analyzing execution plans for a fairly large stored procedure. I only needed to do it a few times. Everytime resulting in the DBA informing me that they've already analyzed that before and it was understandable why I couldn't make any real headway. Side note I found a free pdf for analyzing execution plans in this stackoverflow answer (toward the bottom): http://stackoverflow.com/a/7359705/834579 http://stackoverflow.com/a/7359705/834579 Would you consider logging user actions, for reporting in website, as something that should be stored else where? In one case I can think of the table had about 7 million records, but nobody seemed concerned about it.
- partycoder 10y ago
  If it's just a log then you can "merge on write" (nosql) instead of "merge on read" (join),

daigoba66 10y ago

One tricky thing about databases is that the performance characteristics of a database running on local dev hardware versus production are quite different. Therefore it's important to capture and analyze performance metrics _from production_. A really good commercial tool is SQL Sentry (http://www.sqlsentry.com/ http://www.sqlsentry.com/). You can, of course, patch together your own tooling from open source and homegrown solutions.