Search This Blog & Web

Wednesday, August 5, 2009

Optimize a query using Indexes (Index Options) Part-2

To get this query i find help from msdn and got following result.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
  ON (column [ ASC | DESC ] ) 
   INCLUDE ( column_name )  
   WITH ( [ Columnlist] )  
   ON  partition_scheme_name ( column_name ) 
   filegroup_name 
   default 
 
  PAD_INDEX = [ ON | OFF ] 
   SORT_IN_TEMPDB = [ ON | OFF ] 
   IGNORE_DUP_KEY = [ ON | OFF ] 
   STATISTICS_NORECOMPUTE = [ ON | OFF ] 
   DROP_EXISTING = [ ON | OFF ] 
   ONLINE = [ ON | OFF ] 
   ALLOW_ROW_LOCKS = [ ON | OFF ] 
   ALLOW_PAGE_LOCKS = [ ON | OFF ] 
   MAXDOP = max_degree_of_parallelism


Step 6 : After building index following query used to find the index on my table.

select * from sys.indexes where name like '_dta_index_users_bitActive_%'

Step 7 : After a little search i find a rule to handle index after creation

         1. Reorganize index when its defregmentation is less then 40%  

         2. Rebuild index when its defregmentation is greater 40%

and this query returns the fregmention percent

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and ps.index_id = 248440009
ORDER BY ps.OBJECT_ID
GO

Step 8: Now i need a query that update my new created index and i use

ALTER INDEX Index Name 
ON dbo.TABLE REBUILD REORGANIZE
WITH ( PAD_INDEX = OFF, 
  STATISTICS_NORECOMPUTE = OFF, 
  ALLOW_ROW_LOCKS = OFF, 
  ALLOW_PAGE_LOCKS = ON, 
  SORT_IN_TEMPDB = OFF, 
  ONLINE = ON )
GO

Step9: At last i need some job who check fregmentation and reorganize or rebuild that index.

Step10 : After all query result is dramatically fast but job might not be the best idea every time.

Step11: I am searching more and will share when got result........ 

No comments: