August 3, 2010 at 9:06 am
I have a 950 GB SQL Server 2005 database. I know that why my database has grown this big, because every weekend i run Rebiuld/Reorganise index and also run some update stats tasks. Since this is a very big database its takes almost 15+ hours to finish the task.
When i right click on the database--Task--shrink-database/files i can see that around 300 GB can be shrunk from the mdf and ndf files. But i do not shrink since that is a very bad practice and will cause massive fragmentation.
But now my question is everytime i run the above maintenance task in the weened atleast few GB's are increased and one day it will run out of disk space. and even if i think to shrink the database it takes so many hours to shrink. so these two processes are time consuming.
Now what i was looking for is a script that will show me fragmentation in the tables or inxexes that is specified in the script, so that i can rebuild/reorganise only those indexes that are specified above the shown percentage fragmentation level instead of running the whole task in the weekend.
So how does this idea seems to you all.
And also share with me what would you do if you have such a large database and how would you do maintenance to such database and maintain it so that it works smoothly.
Thanks
August 3, 2010 at 10:00 am
That is a good approach. You may use this query to check for the fragmentation of the tables.
select object_name ( object_id ) as [Table Name] , index_id , avg_fragmentation_in_percent from sys . dm_db_index_physical_stats ( db_id (), 0 , null, 0 , 'SAMPLED' )
order by object_name ( object_id )
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 3, 2010 at 11:22 am
Thanks Pradeep but how do you maintain such a massive amount of database?
August 3, 2010 at 11:27 am
You're eventually going to have to move things into separate filegroups on separate physical disks, and do separate filegroup backups, and reindex by filegroup on different windows. That's really the only practical way to maintain a large DB. Also would be helpful to segregate the tables and indexes into separate filegroups. You'll get better performance, and maybe it won't be so painful to do a reindex.
August 3, 2010 at 12:01 pm
I haven't heard about "reindex by filegroup". Please highlight how to do it. and yes i have 1 mdf, 1 ndf and 1 ldf. So how do i create a new filegroup and how do i move specific tables to a new filegroup?
August 3, 2010 at 12:18 pm
espanolanthony (8/3/2010)
I haven't heard about "reindex by filegroup". Please highlight how to do it. and yes i have 1 mdf, 1 ndf and 1 ldf. So how do i create a new filegroup and how do i move specific tables to a new filegroup?
Whoops. Allow me to clear up a misconception. In a nutshell, Filegroups have only a cursory acquaintance with .mdf's, .ndf's and .ldf's. One filegroup can be spread out across multiple files or you can have different filegroups on each different file. <----- EDIT: I mean each file can have it's own unique filegroup.
So, you have two data files and one log file. Right click your database, go to Properties and choose Files. Do both the .mdf and .ndf say "Primary" under Filegroup? If so, you can change the .ndf to "New Filegroup" and it will let you choose a name for that fileGroup. However, you have to be careful. SQL may not let you change filegroups without moving the data first.
Check out the following commands/topics in Books Online (BOL): "ALTER DATABASE", "files [SQL Server]", "filegroups [SQL Server], about filegroups", "Create Index" and "Alter Index", then drill down into those topics.
The DEFAULT filegroup is the one that gets new data input into it. The easiest way to move data is to recreate the index of a table onto a new filegroup.
August 3, 2010 at 12:35 pm
espanolanthony (8/3/2010)
I haven't heard about "reindex by filegroup". Please highlight how to do it. and yes i have 1 mdf, 1 ndf and 1 ldf. So how do i create a new filegroup and how do i move specific tables to a new filegroup?
To add to the poster above, to move data from one filegroup to another at the table level you have drop and recreate the clustered index and specify the filegroup that you want the index built on. Notice it's by filegroup, not by file. OS worries about files, SQL worries about filegroups, and you map them as he described above. Typically you could put the clustered index (i.e. the data) on one filegroup and the indexes on another filegroup, and if the files inside the two filegroups are on physically different disks (i.e. different spindles) you get parallel I/O between index lookup and data lookup, which can be huge. And reindexing on separate drives can speedup the time so it won't take 15 hours. You also can isolate indexes that need more care and feeding this way if needed. And you can do filegroup backups so that they don't take as long to perform and restores can be faster. If you have a DB of 1 TB or more, you may not have a good backup window to back up the entire database at once, and filegroup level backups are a decent alternative.
August 3, 2010 at 12:38 pm
do you guys have any links that i can refer that should guide me step-by-step to create filegroup and move the data between filegroups.
August 3, 2010 at 12:46 pm
Not all of these are for 2008 but the concepts will be similar and transferrable:
http://www.sqlservercentral.com/articles/Performance+Tuning/fileoptimtips/443/
http://www.sqlservercentral.com/articles/Files+and+Filegroups/65538/
http://www.sqlservercentral.com/articles/Administration/64275/
August 4, 2010 at 4:26 am
jeff.mason (8/3/2010)
Not all of these are for 2008 ...
Jeff, this is the 2005 forum, not the 2008 forum. @=)
August 4, 2010 at 5:54 am
Good point, I was looking at the wrong window when I typed that. But they are all 2005 articles or lower I think, so they will work. Besides, the basic structure of filegroups is the same across all three versions.
August 4, 2010 at 4:09 pm
espanolanthony (8/3/2010)
so that i can rebuild/reorganise only those indexes that are specified above the shown percentage fragmentation level instead of running the whole task in the weekend.
Here is the script that will create job to reindex all databases. You can control fragmentation thresholds.
--Vadim.
--Vadim R.
August 5, 2010 at 8:25 am
1) If you are tasked with maintaining a VLDB but don't have the skills/experience to do that, PLEASE have your company hire a professional to help mentor you and also get things set up right. A few days or a week of time from a good pro will do wonders for your system AND your abilities!!
2) you mentioned doing rebuilt/reorg on indexes. I sure hope you aren't doing BOTH, because that is completely redundant and wasteful. But I have seen numerous clients actually do just that.
3) Are you using a maintenance plan for this? If so STOP! go to http://ola.hallengren.com/ and get his maintenance stuff. WONDERFUL package that is FREE and I cannot recommend it highly enough. BTW, he just put out an update specifically for VLDBs that allows you to specify individual indexes for maintenance.
4) I repeat - GET SOME HELP initially to make sure you are doing things correctly!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2010 at 10:40 am
script to gather index frag data and dump to a database
-- Run system view to gather fragmentation data and insert into table
INSERT into index_stats.dbo.physical_stats ( database_id,
object_id,
index_id,
partition_number,
index_type_desc,
alloc_unit_type_desc,
index_depth,
index_level,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
page_count,
avg_page_space_used_in_percent,
record_count,
ghost_record_count,
version_ghost_record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes,
forwarded_record_count,
server_name,
date,
database_name
)
select database_id,
object_id,
index_id,
partition_number,
index_type_desc,
alloc_unit_type_desc,
index_depth,
index_level,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
page_count,
avg_page_space_used_in_percent,
record_count,
ghost_record_count,
version_ghost_record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes,
forwarded_record_count,
server_name = @@servername,
date = getdate(),
database_name = 'RA'
from sys.dm_db_index_physical_stats (7, null, null, null, 'detailed')
update physical_stats
set table_name = name from ra..sysobjects b join physical_stats a on a.object_id = b.id where table_name is null and database_id = 7
update physical_stats
set index_name = name FROM ra.sys.indexes b inner join physical_stats a on a.object_id = b.object_id AND a.index_id = b.index_id where database_id = 7 and index_name is null and datepart(d, date) = datepart(d, getdate())
August 5, 2010 at 10:44 am
maintenance script
still a bug or two in there since i'm making some changes. base code is from BOL and i've made changes over the years
USE [RA]
GO
/****** Object: StoredProcedure [dbo].[usp_alter_index_online] Script Date: 08/05/2010 12:41:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_alter_index_online]
AS
/* Declare Variables */
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);
/*
if exists(AdminDB..work_to_do_scs1)--sysobjects where id = OBJECT_ID(N'tblPivotData'))
drop table AdminDB..work_to_do_scs1
if exists(select * from AdminDB..work_to_do_scs2)--sysobjects where id = OBJECT_ID(N'tblPivotData'))
drop table AdminDB..work_to_do_scs2
if exists(select * from #work_to_do_scs1)--sysobjects where id = OBJECT_ID(N'tblPivotData'))
drop table #work_to_do_scs1
*/
declare @work1 table
(
objectid int,
indexid int,
partitionnum int,
frag float,
index_type nvarchar(255),
data_type nvarchar(255),
index_name varchar(255),
table_name varchar(255)
)
declare @work2 table
(
objectid int,
indexid int,
partitionnum int,
frag float,
index_type nvarchar(255),
data_type nvarchar(255),
index_name varchar(255),
table_name varchar(255)
)
/* Prepare list of tables to run maintenance on */
/* Get list of fragmented indexes*/
delete from @work1
delete from @work2
insert @work1
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag,
index_type_desc as index_type,
alloc_unit_type_desc as data_type,
index_name as index_name,
table_name
--INTO AdminDB..work_db1
FROM index_stats..physical_stats where
datepart(d, date) = datepart(d, getdate()) and
datepart(yy, date) = datepart(yy, getdate()) and
datepart(m, date) = datepart(m, getdate()) and
avg_fragmentation_in_percent > 20
AND index_id > 0
and index_level = 0
and database_id = 7
and index_level = 0
--and record_count > 1000000
--and not table_name like 'dispatch_%'
--and not table_name like 'aspnet_%'
order by index_name
/*Get list of indexes with large object data*/
insert @work2
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag,
index_type_desc as index_type,
alloc_unit_type_desc as data_type,
index_name as index_name,
table_name
--INTO AdminDB..work_scs2
FROM index_stats..physical_stats where
datepart(d, date) = datepart(d, getdate()) and
datepart(yy, date) = datepart(yy, getdate()) and
datepart(m, date) = datepart(m, getdate())
--avg_fragmentation_in_percent > 10.0
AND index_id > 0
and database_id = 7
and alloc_unit_type_desc = 'LOB_DATA'
and index_level = 0
--and not table_name like 'dispatch_%'
--and not table_name like 'aspnet_%'
order by index_name
/*delete list of indexes that cannot be maintained online*/
--delete from @work1 where objectid in (select objectid from @work2) and index_type = 'CLUSTERED INDEX' --and index_name = 'PK_tblI'
--delete from AdminDB..work_db1 where index_name = 'PK_tblI'
--delete from @work1 where index_name in (select index_name from AdminDB..physical_stats_offline)
/*Get data for cursor because i'm too lazy to add 2 more variables that won't be used in the cursor*/
/*
SELECT
objectid,
indexid,
partitionnum,
frag
--INTO #work_to_do_db1
from @work1
*/
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid, indexid, partitionnum, frag FROM @work1
where index_name is not null;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE ((select count(*) from @work1) > 0)
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;
-- 5 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 1
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag > 20.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' ;
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
insert index_stats..command (database_name, command, date, table_name, index_name) values ('RA', @command, getdate(),@objectname, @indexname);
EXEC (@command);
PRINT N'Executed: ' + @command;
delete @work1 where indexid = @indexid;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
--drop table AdminDB..work_to_do_scs1;
--drop table AdminDB..work_to_do_scs2;
--DROP TABLE #work_to_do_scs1;
GO
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply