Salut,

1. Are cineva un script care face REBUILD pe toti indecsii dintr-o baza de date?
2. Eventual cu optiunea de a face REORGANIZE in functie de gradul de fragmentare

Multumesc.

---

Florin Cardasim

http://sqlfool.com/2009/06/index-defrag-script-v30/

---

http://rusanu.com

Pe langa scriptul sugerat de Remus, as mentiona si exemplul dat de Books Online http://msdn.microsoft.com/en-us/library/ms175008.aspx (inlocuind DBCC SHOWCONTIG cu sys.dm_db_index_physical_stats).

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

Te poti inspira si dupa:
http://www.sqlservercentral.com/scripts/Rebuild+Index/65933/
La http://www.sqlservercentral.com/Scripts/ gasesti script-uri pentru diverse "situatiuni"...

Multumesc

---

Florin Cardasim

Salut.

Din pacate nu stiu daca sunt autorizat sa iti furnizez un script de pe SQLMag.com (unde am subscription), dar http://ola.hallengren.com este ceea ce iti recomand cu caldura, nu numai pentru index defrag.

De asemenea mai am un script de la Microsoft, dar ceea ce ti-am recomandat mi se pare superior, mai ales ca merge si pe 2005, si pe 2008.

toate cele bune,
calin

Multumesc, Calin.

---

Florin Cardasim

Buun.
Incercarea nr.1 (cu modificarea numai a bazei de date la care se lucreaza):
http://sqlfool.com/2009/06/index-defrag-script-v30/
Incercarea nr.2 (cu modificarea numai a  bazei de date cu care se lucreaza):
http://ola.hallengren.com/
De ce la o verificare a starii fragmentarii indecsilor, datele raman neschimbate?

Scriptul de verificare:

SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 30 
ORDER BY ps.avg_fragmentation_in_percent desc
GO

Ce dimensiune au (cate pagini) indecsii?
http://www.sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx