indexing help tools - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

indexing help tools

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 02:30:02

Dear All,
in my current databases, indexing is very poor. same columns are having clustered index and non clustered indexes. is there any tool to help me out?
i'm thinking in this way...please correct me if i'm thinking wrong...

1) i'm planning to drop all the indexes first.
2) i'm planning to create clustered index on ID column.
3) i'm planning to create non clustered index on some columns which are using where conditions.(many procedures and functions, as well as report queries).
4)planning to run the index rebuild script everday at non-peak time
5)planning to run the index defragmentation script every week at non-peak time
6) planning to run shrink database command every week.

please correct me and add flavour with your great experience.

thank you very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-06-12 : 02:51:13

Several comments.

If you rebuild your indexes daily, there's no need to run the defrag. An index rebuild does all that a defrag does, and more.

Do not shrink your databases. Amoung other things it really, really fragments indexes and is uaually a waste of time, as the database will just grow again, causing file level fragmentation as well.
For more details see this blog post. [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]

As for where to create the indexes, there's no single rule. Clustered on the pk and nonclustered on other columns is a common method. It's not necessarily the best. To find the best config for indexes you are going to have to evaluate the queries you have, see what they do and experiment with indexes until you find a good config.

See this blog for lots of indexing info - [url]http://www.sqlskills.com/blogs/kimberly[/url]

--
Gail Shaw

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 03:12:54

Great help Gail Shaw. thank you very much.
which one is best doing...
index rebuild? or index defragmentation?

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-06-12 : 06:41:57

Rebuild. It's also the one that takes longer and on SQL 2000 is an offline operation (table is not accessible during the rebuild)

An index rebuild completely recreates the index, removing fragmentation at all levels of the index. It also updates the statistics of that index.

A defragment shuffles the leaf levels of the index into order, potentially leaving fragmentation at higher levels of the index. It dos not update statistics.

The recommendation I've often seen is if an index has a fragmentation between 10 and 30%, do a defrag. If it's over 30%, do a rebuild

--
Gail Shaw

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-06-12 : 07:11:55

This should help you identify which indexes need rebuilding as opposed to reorganizing.


use adventureworks
GO
 
SET NOCOUNT ON; 
	 
SELECT Object_Name(dt.Object_id) AS ObjectName
	, si.Name AS IndexName
	,dt.avg_fragmentation_in_Percent
	,dt.avg_page_Space_Used_in_Percent
	,CASE WHEN dt.avg_page_Space_Used_in_Percent < 75 THEN 'Yes' 
	 ELSE 'No' END  AS Has_Internal_Frag_Occured
	,CASE WHEN dt.avg_fragmentation_in_percent > 10 THEN 'Yes' 
	  ELSE 'No' END  AS Has_External_Frag_Occured
	,CASE WHEN (dt.avg_page_Space_Used_in_Percent <75 AND dt.avg_page_Space_Used_in_Percent >60)
		 OR (dt.avg_fragmentation_in_Percent > 10 AND dt.avg_fragmentation_in_Percent < 15 )THEN 'REORGANIZE' 
		 WHEN (dt.avg_page_Space_Used_in_Percent < 60) 
         OR (avg_fragmentation_in_Percent >15) THEN 'REBUILD'
	END AS  Execution_Type
		
FROM
	  (SELECT  object_ID
			  ,index_id 
			  ,avg_fragmentation_in_percent
			  ,avg_page_Space_Used_in_Percent
	   FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')
	   WHERE INDEX_ID <> 0) AS DT
	INNER JOIN sys.indexes SI 
		ON SI.Object_ID = DT.Object_ID AND SI.index_ID = DT.Index_ID
WHERE CASE WHEN dt.avg_fragmentation_in_percent > 10 THEN 'Yes' 
	  ELSE 'No' END = 'Yes'
	  OR
	CASE WHEN dt.avg_page_Space_Used_in_Percent < 75 THEN 'Yes' 
	 ELSE 'No' END = 'Yes';

SET NOCOUNT OFF;

Dallr

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-12 : 07:40:10

Thank you very much Dallr....
it is a good help

Arnav
Even you learn 1%, Learn it with 100% confidence.

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-06-12 : 07:55:21

No problem Glad to help!

Dallr

Ola Hallengren
Starting Member

33 Posts

Posted - 2008-06-12 : 15:45:14

I have a stored procedure that you could use to dynamically rebuild or reorganize your indexes. It's using sys.dm_db_index_physical_stats.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Microsoft also has a script in Books Online that you could use.
http://msdn.microsoft.com/en-us/library/ms188917.aspx

Ola Hallengren
http://ola.hallengren.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources