Daily problems and issues that are hard to resolve about SSMS,SSRS,SSIS,SSAS,DTS,Agent, Optimization, Administration, Always On, Clustering, Point in Time recovery and more...
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
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
No comments:
Post a Comment