Query tuning

General

• Speed depends much on indexes than other things
• JOIN is quicker than IN

Index

• Index improves performance of SELECT queries
  • Index should be applied to column in which WHERE, JOIN conditions are applied
• But index slows down the INSERT/ UPDATE queries
• References
  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20020_1251/html/databases/X23074.htm

SQL Server

Use Execution plan

• Turn the Execution plan on

  [sql]SET SHOWPLAN_ALL ON
  GO
  exec [stored_proc]
  GO
  SET SHOWPLAN_ALL OFF[/sql]

• Use Actual Execution Plan to check the slow query and found that a Clustered Index Seek was taking up 90%. Is there any way to improve Index Seek performance?

Track heavy Stored Procedure from Profiler

Measure execution time

• Check SP:Completed from Stored procedures event class
• Run the stored procedure

Track the speed of a query

• Use the following query

  [sql]DECLARE @start DateTime
  DECLARE @end DateTime
  SET @start = getDate()

  — Do your stuff

  SET @end = getDate()
  SELECT DATEDIFF(ms, @start, @end) — Will show how many milliseconds your query used[/sql]

Create index

• When tuning SQL server and when showing execution plan for one of the queries at the top it reads:

  Missing Index (Impact 99.7782): CREATE NONCLUSTERED INDEX...
  

• Details of index missing

  [sql]
  /<em>
  Missing Index Details from ExecutionPlan1.sqlplan
  The Query Processor estimates that implementing the following index could improve the query cost by 99.7782%.
  </em>/

  /*
  USE [phsprod]
  GO
  CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
  ON [dbo].[address] ([userid])

  GO
  */
  [/sql]

•  Solution is to create index

  [sql]
  USE [phsprod]
  GO
  CREATE NONCLUSTERED INDEX IX_Address_UserId
  ON [dbo].[address] ([userid])
  [/sql]

How to improve UPDATE SQL speed

• Removing index on the column to be updated.
• Executing the update in smaller batches.
• Disabling Delete triggers.
• Replacing Update statement with a Bulk-Insert operation.

http://www.sqlservergeeks.com/sql-server-optimizing-update-queries-for-large-data-volumes/

Do not use variable table

[sql]
— Create a variable table
— Insert records in the variable table
— Run a final query which joins with variable table
–** Query: Easy to understand
–** Speed: Slow!!!

— Run a final query which directly joins with records
–** Query: Looks long and complicated
–** Speed: Faster!!

–It’s much more faster because there is no waste time of INSERT records
[/sql]

What is the difference between a clustered and a non-clustered index

Clustered Index

• Only one per table
• Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

• Can be used many times per table
• Quicker for insert and update operations than a clustered index

[html]
Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations. Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.
SQL Server will normally only use an index if its selectivity is above 95%.
[/html]

References

https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/