Can a DBA find performance issues just from reading the TSQL code?

Question

Can a DBA find performance issues just from reading TSQL code? Is a DBA expected to have that capability?

Answer 1

No. She would at least need to have the table schema and index definitions to be able to start guessing, and for an educated opinion, she would want to see performance profiling information from a live database.

There may be a few general "you just cannot do that" mistakes she could catch by just looking at the queries, but for most performance problems it all depends on configuration and context.

Answer 2

A good DBA can detect problems using nothing more than tea leaves and chicken entrails.

Seriously though, a hallmark of a good DBA is not so much to be able to detect the problems, but to be able to detect possible problems, based on this sort of static analysis.

By way of example, a good DBA can look at:

select * from tbl where col1 = 7 order by col2;

and immediately figure out that:

• the user of that query should either use every column they ask for, or they should ask for less.
• the table may need indexes on the col1 and col2 columns.

Now, those two potential problem areas aren't enough to decide whether there are problems. To find that out for sure, the DBA would need to examine the database schema and statistics. But static analysis should be a good start.

So, in answer to your actual question, no, they generally don't find problems from only reading TSQL (or any SQL), but they should be able to use that information to target their efforts.

Answer 3

A DBA should understand TSQL, and be able to identify issues with it.

Having said that, performance of a query is only partly caused by the TSQL itself.
It also depends hugely on the indexes, triggers, table structure, and architecture of the machine which is hosting the SQL Server.

So TSQL is just one factor which a DBA has to consider.