What are the top issues and in which order of importance to look into while optimizing (performance tuning, troubleshooting) an existing (but unknown to you) database?
Which actions/measures in your previous optimizations gave the most effect (with possibly the minimum of work) ?
I’d like to partition this question into following categories (in order of interest to me):
- one needs to show the performance boost (improvements) in the shortest time. i.e. most cost-effective methods/actions;
- non-intrusive or least-troublesome most effective methods (without changing existing schemas, etc.)
- intrusive methods
Suppose I have a copy of a database on dev machine without access to production environment to observe stats, most used queries, performance counters, etc. in real use.
This is development-related but not DBA-related question.
Suppose the database was developed by others and was given to me for optimization (review) before it was delivered to production.
It is quite usual to have outsourced development detached from end-users.
Besides, there is a database design paradigm that a database, in contrast to application data storage, should be a value in itself independently on specific applications that use it or on context of its use.
Update3: Thanks to all answerers! You all pushed me to open subquestion
How do you stress load dev database (server) locally?
If you’re not interested in the runtime behavior of the database, e.g. what are the most frequently executed queries and those that consume the most time, you can only do a “static” analysis of the database structure itself. That has a lot less value, really, since you can only check for a number of key indicators of bad design – but you cannot really tell much about the “dynamics” of the system being used.
Things I would check for in a database that I get as a
.bak file – without the ability to collect live and actual runtime performance statistics – would be:
normalization – is the table structure normalized to third normal form? (at least most of the time – there might be some exceptions)
do all tables have a primary key? (“if it doesn’t have a primary key, it’s not a table”, after all)
For SQL Server: do all the tables have a good clustering index? A unique, narrow, static, and preferably ever-increasing clustered key – ideally an INT IDENTITY, and most definitely not a large compound index of many fields, no GUID’s and no large VARCHAR fields (see Kimberly Tripp’s excellent blog posts on the topics for details)
are there any check and default constraints on the database tables?
are all the foreign key fields backed up by a non-clustered index to speed up JOIN queries?
are there any other, obvious “deadly sins” in the database, e.g. overly complicated views, or really badly designed tables etc.
But again: without actual runtime statistics, you’re quite limited in what you can do from a “static analysis” point of view. The real optimization can only really happen when you have a workload from a regular day of operation, to see what queries are used frequently and put the most stress on your database –> use Mitch’s checklist to check those points.
Answered By – marc_s
Answer Checked By – Senaida (BugsFixing Volunteer)