I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.
Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
Reference : Pinal Dave (https://blog.sqlauthority.com) , Original Article
22 Comments. Leave new
What do you mean by Keep your clustered index small?
Hi pinal ,
How to use ‘Use APPLY’?
It is APPLY operators such as cross apply
Hi Pinal ,
What is archive your old data?
Hi pinal ,
what’s the difference between Covering Indexes and Clustered Indexes ?
how to use clustered index small ?
why to use veiws ?and how to use it.and why veiws used
what are indexes ?and why indexes are used
for question#1 correct me if i am wrong Pinal.
is this the same thing our cluster value shuld be small rather than long (sting values)?
HI Pinal,
could you please reply on the followings:
1. Keep your clustered index small.
2. Use Apply
3. Avoid cursors – When we have a situation that we can not avoid the use of cursor than what is the alternate solution? is there anything we can use instead of cursor to perform the desired task? which optiomize the peroformance too.
Thanks in advance.
Regards,
Rupesh
Hi! try using WHILE LOOP. It works the same way with cursor but is more efficient.
Hi Brixton,
How did you come to this conclusion to you WHILE loops instead of cursors?
-Manoj
Hi, could you explain detail of these tips?
Could you please tell me the difference between Hash join,Merge Join and Nested Join in Execution plan often seen.
Which scenario they come to the picture.And the performance
order of the three.
Hi Pinal,
I am not sure whether this is right or not. As i am giving link of other website to your website reader. My intention is only to help them. If you think it is not right then let me know so that i can stop.
Thanks,
Vijay
Sir ,
i studied that indexes will increase the performance
by decreasing table scans and i/o cost……..
SO
could you please explain
what is table scan?
what is i/o cost?
Dear Mr. Dave,
I knew this 12 tips before I read your articles, but I’ve a question that just raised in my head..
Sometimes we must use cursors for some reasons, how can anyone avoid using it. Do you have any alternative of CURSORS?
If yes, then please let me know..
rgds,
Sidd
sidd,
You can use correlated subqueries,temporary tables and common table expessions as an alternative to cursors.
suggest me if iam wrong.
Thanks
lakshmi
Even the subqueriew will drag your query performence down ‘
a simple join can answer your degraded performence
with subquries
usage of table variables and CTE will best replace the
cursors
This is for LAKSHMI,
Lakshmi u r absolutely Correct…
1. Outer joins vs Sub query :which is best considering the performance .
2. @tables vs #tables :which is best considering the performance .
if the data to store in table is less then use @tables as it is stored in memory. for large records use #tables
Hi pinal,
tell me what is USE APPLY
regards,
Hari
APPLY allows to invoke a function (tabled-valued) for each row of a query’s result set:
SELECT c.NAME, tf.PHONE_NUMBER
FROM dbo.CONTRACTOR AS c
CROSS /*OUTER*/ APPLY
dbo.ufnGetContractorsDefaultPhoneNumber(c.CONTRACTORS_ID) AS tf;
Wouldn’t that mean that decreasing a query performance?
Even in a the training kit for 70-433 there is a hint that its a long running query, for a result set containing 20k rows.