独立的应用程序：随着时间的推移保持数据库的健康/响应(Standalone app: keep the database healthy/responsive over time)

独立的应用程序：随着时间的推移保持数据库的健康/响应(Standalone app: keep the database healthy/responsive over time)

我有一个使用MS SQL Server Express数据库的WinForms C＃应用程序。 该应用程序部署在我们客户的个人电脑上，他们没有计算机相关知识。

应用程序定期更新数据库，并在索引文件中看到很多碎片。 如何随着时间的推移保持数据库的健康/响应？

我正在考虑编写一个存储过程来重组每个索引，但我缺乏t-sql技能; 有人能带领我走向正确的方向吗？

巴斯

I have a WinForms C# application using a MS SQL Server Express database. The application is deployed on the PCs of our customers and they don't have computer related knowledge.

The application updates the database regularly and I see a lot of fragmentation on the index files. How do I keep the database healthy/responsive over time?

I was thinking about programming a stored procedure which reorganizes every index, but I lack t-sql skills; can someone lead me in the right direction?

Bas

最满意答案

如果您能够在短时间内使表脱机，请使用DBCC REINDEX选项，或者使用DBCC INDEXDEFRAG。 但IndexDefrag选项已被贬低。 您还可以在SQL 2005/2008中使用ALTER INDEX语句。

I now use 2 sql scripts.

SELECT st.object_id AS objectid, st.index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, o.name, i.name FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') st join sys.objects o on o.object_id = st.object_id join sys.indexes i on st.object_id = i.object_id and i.index_id=st.index_id

I run this when starting my program and check if my main tables has an avg_fragmentation_in_percent of more then 70. If so I run the following script.

SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. if ( object_id( 'tempdb..#work_to_do' ) is not null ) DROP TABLE #work_to_do; -- Alleen indexen die meer dan x% gefragemteerd zijn SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do;

This script defrags all tables with a fragmantation of more then 5%