That's my question. Being someone who's more of a developer than a DBA (which doesn't matter, I'm going to get asked DBA questions anyway, right?), in my original post, I mention that there are dozens of things that affect query performance, dozens of tools that can be used to check things out, etc. So which two are the most important? That's why I come to this forum, to get help from the opinion of experts such as yourself. 🙂

I did spend 3-5 minutes on it during the interviews, but more picking random topics from the lists above and citing examples of times I've seen them. I'll give one example: I'd talk about checking out the query design for SARGeable errors using the Query Execution Plan. I'd try to verbally describe queries like these:

-- 96% of the batch cost...

SELECT c.name AS 'IdentityColumn'

FROM sys.columns c

INNER JOIN sys.objects o ON o.object_id = c.object_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE s.name + '.' + o.name = 'HumanResources.Department'

AND c.is_identity = 1

-- 4% of the batch cost...

SELECT c.name AS 'IdentityColumn'

FROM sys.columns c

INNER JOIN sys.objects o ON o.object_id = c.object_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE o.name = 'Department' AND s.name = 'HumanResources'

AND c.is_identity = 1;

GO

...since I know putting a calculation or concatenation on the left side of the WHERE clause comparison forces a table scan, even if there is an index, because the concatenation has to be applied to each row first.

Then, I would talk about other things, like checking indexes, to see if adding one more column can create a covering index, thus improving performance, and checking the column and sort order of the index and such.

Although I know about these and other issues that cause slow query performance, I don't think citing individual examples is what the interviewers are looking for.