Tips : Optimize your SQL Statements

We all write lot of SQL Statements during our course of developing a project. There is a huge difference between in just writing an SQL statement which works and one which works really well and performs correctly. Sometimes we would be too focused on just writing the SQL to perform the required task, without taking into consideration its performance and most importantly its impact on the SQL Server Instance, i.e. the amount of CPU, IO, and memory resources their SQL is consuming.

Thus, many times they starve other SQL Server processes during the SQL statement execution bringing the whole instance to its knees.

There are certain check points which you could keep in mind while writing an SQL Query or during DB administration.  Some of them are:

– Check your table joins

This can easily happen and the output will be a Cartesian join, i.e. the product of the tables, something absolutely not desired.

Check if you’re missing any required WHERE clause

A missing WHERE clause will return more data than needed. The same repercussions for Step 1 apply here.

Check if statistics are being created & updated automatically

By default on creating a new database, the Auto Create Statistics and Auto Update Statistics are ON. Statistics are used by the optimizer to determine the best execution plan for the submitted query.

These settings can be checked by right clicking on the database, select Properties and Options.

Check if the statistics are up-to-date

Although statistics are automatically created, it is important that they are updated to reflect changes in data.

USE AdventureWorks;
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);

Check for any table or Index scans.

Using the execution plan, it’s easy to identify if the SQL statement is performing any table or index scans. Check out msdn articles here and here  to understand more on this.

Check for RID Lookups.

Again, using the execution plan, it’s easy to identify RID Lookups. These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced. Refer a tecnet article here.

Check for any sort operator.

If in the execution plan sort operators are identified carrying a large % of the total query cost, then it might make sense to further investigate on that matter.

Check for excessive index fragmentation

Index fragmentation can be easily viewed using the following DMV – sys.dm_db_index_physical_stats. If the fragmentation is greater then 30%, an index rebuilt is recommended, while an index reorganize is enough for fragmentation less then 30%

Check table locks.

If the source tables are locked due to DML statements, the query engine may spend time waiting for the locks to get cleared. There are a number of steps to perform to overcome locking problems. Keep transactions as short as possible. This is the first approach which could  be taken.



About mytechlifedays

Its been a wonderful learning period over the last 6 years in the IT industry , getting exposed to whole lot of technologies and ideas. The hurdles and the crisis that came along have been wonderful experience ... And now its time to pen them down so that let some others execute faster and easily with these information .....
This entry was posted in Database, General and tagged . Bookmark the permalink.

Leave a Reply ! It would be always appreciated ! :)

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s