This subject is very deep subject but today we will see it very quickly and most important points. May be following up on few of the points of this point will help users to right away improve the performance of query. In this article I am not focusing on in depth analysis of database but simple tricks which DBA can apply to gain immediate performance gain.
- Table should have primary key
- Table should have minimum of one clustered index
- Table should have appropriate amount of non-clustered index
- Non-clustered index should be created on columns of table based on query which is running
- Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
- Do not to use Views or replace views with original source table
- Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
- Remove any adhoc queries and use Stored Procedure instead
- Check if there is atleast 30% HHD is empty – it improves the performance a bit
- If possible move the logic of UDF to SP as well
- Remove * from SELECT and use columns which are only necessary in code
- Remove any unnecessary joins from table
- If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
There are few hardware upgrades can be considered as well like separating index on different disk drive or moving tempdb to another drive. However, I am not suggesting them here as they are not quick way to improve the performance of query.
Reference : Pinal Dave (https://blog.sqlauthority.com)
42 Comments. Leave new
In simple terms views like any other sql statements need to be compiled then executed which comsumes time and space for large amount of data unlike stored procs which are already precompiled
Hi Pinal Dave
This is very usefull to my daily work as dba thanks alot
Jayant
Hi Hoapino and all,
Will d views occupy place in the Database?N can some one help me the meaning of MOVE D LOGIC OF UDF TO SP
Thanks,
Narenndra
Hello Pinal,
I wanted I wanted to know which kind of queries can be moved to stored procedures. Can you please guide me on Ad- Hoc, dynamic and parametrized queries in reference to SP. What will be beneficial for performance improvement?
Thanks for the Tips. It was useful.
Pinal,
Many time I am reading your blogs.
In this article you have mentioend very very basic optimization technique.
I would request you to separate your articale for expert, beginners etc.
-Nilesh
Hi
I am suresh. Thjs is my question.
I am inserting 8 lacks records from source table to multiple destination table with some ETL operation. First 10000 records getting inserted in 10 minutes and after that it takes 20 minutes to insert 2000 records it keeps on increasing the time. what would be the solution. plz asap
Surersh,
Create a text file of the table and use it as a source instead of directly using the table as the source.
You can use the cmd utility called bcp.exe to create the text file. Please reply as to how it works out.
Hello,
How to write a syntax for T-SQL,user defined functions and stored block procedure.
Hi Pinal,
Thanks for putting such helpful information on your blog. It always helped me to find solutions for queries. Thanks again.
Sandeep B
Hello sir,
\very Nice Blog of u.Thanks for putting such helpful information on your blog. It always helped me to find solutions for queries.
Hi Pinal,
Nice blog!
can any one plz tell me the tools used for query optimization for sql from the erspective of data warehouse
What about @table and #table… how it hurts performance.
Hi Pinal,
This was really helpful information for me. Can you optimize the query while using a ORDER BY clause for quick sorting of data records?
Hello Pinal,
Quick Question..
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Isn’t it a Join –> Where –> Order BY –> select..??
Can you please share example to justify your statement..?
Yes I applied this in Production Server seems to be good
Could you please tell me which option is best in database replication in small size around 200 mb between peer to peer connection
Thanks…
points to be remember…
Yes. :)