SQL Server's query planner gets better and better with every release, but it still screws up on a regular basis. To have efficient queries on SQL Server the programmer (in the absence of a full-time DBA) needs to know far more about the internals of the server than they really should. Some of the common issues for slowly running queries I see include:
- Scalar UDFs (User-Defined Functions) are pure evil. Query Planner always estimates their cost at 0, no matter how complex their implementation. Scalar UDFs used in queries are run RBAR (Row By Agonizing Row) so their real world costs add up very, very quickly. If you absolutely must have Scalar UDFs in your system ensure that they perform no table accesses whatsoever and only operate on the provided parameters.
- Closely related to Scalar UDFs, and for the same reason, Table Valued Functions can be evil as well. Prefer to use Inline Table Valued Functions (that do not contain BEGIN and END keywords) which behave more like Views. Query Planner can inline the code of ITVFs into the current statement and even optimize out whole chunks of code if it detects that they're unnecessary to complete the query.
- Bad indexes. Indexes should be chosen based on cardinality so that the least number of B-Tree traversals are required to get to data, e.g.: prefer (InvoiceID, BusinessID) over (BusinessID, InvoiceID) as there'll be far more invoices than businesses in your system.
- Out of date index statistics. Left to its own devices, SQL Server doesn't update indexes statistics anywhere near as frequently as it should. If you have an indexed table that's regularly having rows inserted and deleted (especially if you run a "soft deleting" system) you should consider updating statistics yourselves on a recurring schedule or after X rows have changed.
- Every foreign key should be indexed, and, in the case of Composite Foreign Keys, the order of the FK items should match the column order and the asc/desc order of the referenced Primary Key indexes.
- Also related to index statistics is something that bites new programmers all the time: Did you know that statistics for #Temp tables and ##Temp tables are cached between uses? i.e.: if you put indexes on #Temp and ##Temp tables in your queries you should immediately run update statistics...with fullscan on them before using the temp tables, otherwise Query Planner makes decisions based on what the same-named temp tables had last execution!
SQL Server's a mine field of bad performance for the unaware. I doubt it's much different in Oracle and MySQL/MariaDB.