T-SQL

Re: Script to REBUILD/REORGANIZE all database indexes

Diana — Fri, 12 Feb 2010 10:15:37 GMT

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

Re: Script to REBUILD/REORGANIZE all database indexes

blc2001ro — Fri, 12 Feb 2010 01:09:45 GMT

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

Re: Script to REBUILD/REORGANIZE all database indexes

cardasim — Wed, 09 Sep 2009 06:21:57 GMT

Multumesc, Calin.

Re: Script to REBUILD/REORGANIZE all database indexes

sageata2002 — Mon, 07 Sep 2009 18:28:17 GMT

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

Re: Script to REBUILD/REORGANIZE all database indexes

cardasim — Mon, 07 Sep 2009 06:43:37 GMT

Multumesc

Re: Script to REBUILD/REORGANIZE all database indexes

Diana — Mon, 31 Aug 2009 11:45:19 GMT

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"...

Re: Script to REBUILD/REORGANIZE all database indexes

xmldeveloper — Mon, 31 Aug 2009 11:34:06 GMT

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).

Re: Script to REBUILD/REORGANIZE all database indexes

rremus — Sat, 29 Aug 2009 01:47:40 GMT

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

Script to REBUILD/REORGANIZE all database indexes

cardasim — Fri, 28 Aug 2009 08:20:11 GMT

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.