Есть 3 группы неоптимальных запросов:

1) Когда кверя возвращает очень много записей, причем с сервера - т.е. весь курсор данных путешествует через сеть. У меня в практике был случай когда число полей в таблице исчислялось сотнями, и у меня при перечислении штук 70 полей в квере типа "select f1, f2, f3 ... f70 From..." (дух захватывало - кверя на несколько страниц) возникал вопрос не лучше ли послать "Select * From", экспериментальным путем выяснилось - нет не лучше, несмотря на ужасающий размер самого запроса квери проходила намного быстрее. Не желательно выкверять и очень много записей, в 99% случаев они вам не нужны, надо ставить более четкие и жесткие условия отбора записей, лучше у вас в части Where будет на несколько условий больше, а результат запроса получится меньше. Например не надо писать квери "Select * From Table" чтоб подсчитать количество записей в таблице, для этого гораздо лучше подойдет "Select count(*) From Table", или применять "Select * From Table" если вам надо выбрать только одну самую первую запись после сортировки - в этом случае надо писать "Select top 1 * From Table".

2) Классика  жанра - декартово произведение - это таким образом составленный запрос к нескольким таблицам, который возвращает все возможные комбинации из нескольких таблиц. Например вы имеете 2 небольшие таблицы по 1000 записей, декартово произведение вернет вам 1000000 записей и наверняка выведет вам из строя или клиент или в худшем случае сервер...

3) Любая кверя которая заставляет читать таблицу "record by record". Классический пример - если у вас нет индексов по полям, которые упоминаются в части Where. Подлость в том, что сервер все равно такую кверю выполняет... и до поры до времени вы отмечаете вовсе неплохую работу, но таблицы растут. Приведу пример из своей практики - есть не очень большая база данных (Примерно 100Gb), к ней идут запросы с частотой примерно 10-50 квери в секунду. Сервер вполне хороший - 2х процессорная система на 1GHz  + 4Gb RAM+ немерянно дискового пространства. Вижу - загрузка процессоров на сервере 100%, примерно 10% квери не проходят - их выбивает - одни по time out, другие по Dead lock... Разбираемся в чем дело, выясняется что несколько посылаемых кверей идут на не индексированных полях. Добавляем 3 индекса (к уже 3м десяткам существующих), результат- квери проходят 100%, загрузка процессоров на сервере около 30%... 

Автор ответа Vit