Can you optimize my ms sql or mysql database?

Can you optimize my ms sql or mysql database for best performance?

Probably the most common request a DBA in general can get is to optimize a database or to perform a performance audit, which is really just another way of saying the same thing. The idea behind this request seems simple enough – you're the expert on databases. I have a database, please tell me if my DB is running as it's supposed to.

It should be no different from going to a mechanic and asking him/her to check if your car is working well or not. Right?

Well, the true answer is maybe. A database is very different from a car and can't really be compared. One of the biggest differences relevant to this discussion is the purpose. All cars have the same purpose – to get us from point A to point B. True, some of them are better suited for paved roads, and others for off-road driving, and yet others may be amphibian, but at the end of the day a car is a transport.

Not so a database. While it could be said that the ultimate purpose of a database is to store data and to enable users to access it, there is a lot more to it:

1. First of all, a database has a schema which defines what can be stored and what can't. It has to be built in accordance with business needs. In some cases, malformed schema can prevent a business from being able to capture critical information, in milder cases of dysfunction it can cause developers to write code that has no chance of performing well. The code optimization for your database such as: as ms sql or my sql could be mission impossible.
2. Even if the database design is 100% perfect there are still ways to write ugly queries. Using an ORM? It's almost assured that your queries will not perform and/or scale. Some companies aren't even conscious of the fact that data in their database is only accessibly via an API and that API needs to be defined and fine-tuned.
3. Only after the above two issues are taken care of does a “traditional” DBA work begin. Once the logical layer is properly defined and tuned, we can deal with the physical issues such as type of storage for tables (including partitioning), memory usage including cache management, etc.

What does this mean in practical terms? Simple. If you're considering a database performance review start from the logical layer.

1. Make sure that your entities are defined in accordance to all business needs. Which means keeping things as specific as possible.
   1. Many people with OOP background tend to create “generic” tables. That's a sure way to kill your database performance.
   2. Keep your data types as small as possible. Some people think that it's not an issue because storage is cheap. True, storage isn't as expensive as it used to be, but that's not the only reason to avoid using varchar(max) all over the place. One of the more obvious reasons is that well defined types make optimizing queries so much easier.
   3. Speaking of optimizing queries, watch your collations. Rarely is there a reason to have more than one collation in the same database. So, think twice before you decide that a particular field needs to be case sensitive in only one table. And while we're on the subject. Please make sure that you have a data dictionary for your model. Because most of these problems happen because someone just wasn't paying attention, or didn't notice, etc. Having a data dictionary eliminates most of these accidental differences.
      
      As a side note, a data dictionary is also known as metadata which is the data about your data. At minimum you should have list of tables and columns with their data types. But it is much more than that and a whole subject on its own.
      
      
2. Create your API in the database programmable layer. Whatever you do don't use an ORM. Many developers have a fear of stored procedures. I don't fully get it. Maybe your company can cover therapy sessions to help. Whatever it takes, keep your API close to your data.
3. Design your database with the right tools. A data modelling tool will provide you a global view on the entire database schema and will warren you of any mistakes done within the database design. Our favorite tool for the design of database is xCase. But there are many other tools that will work just as well. Check our article about the Reverse Database Engineering and Database Design, and how we maximize our productivity of the data modelling tool we use.

An experienced Remote DBA can guide you through this process and help you create a solution that is both powerful and flexible.