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; GO DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid); GO
–Check for any table or Index scans.
–Check for RID Lookups.
–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.