February 12, 2012 at 8:57 pm
As per topic :
--------------------------------------
SQL SERVER 2005 SP3 ENT 64BIT
WINDOWS SERVER 2003 ENT SP2
Drive total size Freespace
C:\ 129 GB 113 GB - BINARY
D:\ 272 GB 191 GB - SQL LOG FILE LOCATION
E:\ 1.7 TB 39.1 MB -SQL DATA FILE LOCATION
sp_spaceused
Name rows reserved data_sizeindex_sizeunused
FileList_T 5303873667971053408 KB960,138,384 KB10,818,096 KB96,928 KB
sp_helpdb 'FILELIST'
name fileidfilename Filegroupsize maxsizegrowth
FILELIST 1E:\MSSQL\Data\FILELIST.mdF PRIMARY1834926144 KBUnlimited10240 KB
FILELIST_Log 2D:\Databases\FILELIST.ldf NULL104985600 KBUnlimited102400KB
FILELIST
ALLOCATED SPACE : 1,623,174.88 MB
FREE SPACE : 546,475.69 MB (33%)
FILELIST_LOG
ALLOCATED SPACE : 59392.50 MB
FREE SPACE : 53831.14 MB (90%)
-------------------------------------------
Question : I am tyring to reindex the table FileList , based on the finding above, i failed for the first time when the drive E is failing up when runninng command DBCC DBREINDEX (FileList_T,fileidx1) with no_infomsgs.
1. I need to add additonal space into the mdf files , please advice how much space should be added ?
2. If i am using sort in tempDB , how much spaces hould be added to tempDB data and log files and the FIlelist data file ?
3. i know we should not allow such big files located in one mdf . If i would like to add another mdf file to same filegroup , run the dbcc reindex , when it is completed , dbcc shrinkfile with empty data file option . Will it re-create the fragmentation ?
4. If i can only have a temporarily additional 1 TB or 2 TB let say for 1 month , what option do you have ?
February 13, 2012 at 1:51 am
stanley wong-314201 (2/12/2012)
1. I need to add additonal space into the mdf files , please advice how much space should be added ?
You need roughly the size of the index free in the data file if you're using sort_in_tempDB, 120% of the size of the index if not. These are rough figures, not exact.
2. If i am using sort in tempDB , how much spaces hould be added to tempDB data and log files and the FIlelist data file ?
You need roughly 20% of the size of the index for sort space. This is a rough figure
3. i know we should not allow such big files located in one mdf . If i would like to add another mdf file to same filegroup , run the dbcc reindex , when it is completed , dbcc shrinkfile with empty data file option . Will it re-create the fragmentation ?
Yes, it will.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2012 at 2:12 am
The table have only one index which is a cluster index.
So, if i reindex these table using DBCC DBREINDEX, Is the size of the index equal the size of the data ?
reserved 971,053,408 KB
data_size 960,138,384 KB
index_size 10,818,096 KB
unused 96,928 KB
February 13, 2012 at 4:16 am
Yes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2012 at 9:20 am
I was curious what approach would be used to fix this particular situation.
Not trying to hijack, I don't have anything this large :-D.
The overall goal is shortest amount of down time and top performance.
At a high level,
Need to rebuild indexes, and plan regular maintenance approch.
Need to spread over multiple files possibly filegroups if supported (one per CPU equal size?)
SQL Standard vs. Enterprise limitations or different approch?
February 13, 2012 at 11:26 am
Maramor (2/13/2012)
Need to spread over multiple files possibly filegroups if supported (one per CPU equal size?)
The 1 per CPU, equal size is an old and outdated recommendation for TempDB only. More recently the recommendation is 1/4 to 1/2 the number of files as CPU cores, don't go above 8 without a good reason.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2012 at 11:32 am
Good to know.
I need to set up a few tests in my environment to see the difference 🙂 Thats why I put a ? mark as I was unsure about those recemendations.
February 13, 2012 at 8:18 pm
Does DBCC DBREINDEX (dbo.YourTableName, PK_YourTableName ) similar to CREATE UNIQUE CLUSTERED INDEX PK_YourTableName ON dbo.YourTableName(YourPKFields) WITH (DROP_EXISTING = ON) ON [NewFilegroup]
? Except it is rebuild the index and moved data into diffrent filegroup ?
Given the table details below, Currently TableA located in Primary filegroup , if i am going to move tableA with cluster index key from existing file group to ANOTHER new File group , what is the data size required for my new filegroup ?
Table Details -
rows5303873667
reserved 971,053,408 KB
data_size 960,138,384 KB
index_size 10,818,096 K
February 13, 2012 at 8:18 pm
rows5303873667
reserved 971,053,408 KB
data_size 960,138,384 KB
index_size 10,818,096 KB
February 13, 2012 at 9:34 pm
I just check on my MSSQL table and found out the cluster index is build with multiple column .
UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]
[Account] ASC,
[PathId] ASC,
[FileName] ASC,
[FileNameLength] ASC,
[Tdate] ASC
ON [PRIMARY]
So, If i run the drop_existing script , will it still move the entire table include the data and index to the secondary filegroup ?
CREATE UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]
([Account] ASC,
[PathId] ASC,
[FileName] ASC,
[FileNameLength] ASC,
[Tdate] ASC)
WITH (DROP_EXISTING = ON)
ON [SecondaryFG]
GO
February 13, 2012 at 9:42 pm
Maramor (2/13/2012)
I was curious what approach would be used to fix this particular situation.
If you have the Enterprise edition, partition the large tables by "row date" and "align the indexes" (See Books Online for more but that a fancy phrase for "partition the indexes, as well").
If you have the Standard edition, consider a "partitioned view".
The advantage of both is that you can reindex the much smaller partitions with a lot less overhead and a whole lot less time. If you have truly done the paritioning by some date having to do with the age of the row since it was inserted, then you may not have to reindex any but the "last" partition because only the last partition will have inserts and, more likely than not, the older data won't suffer many updates.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2012 at 1:17 am
stanley wong-314201 (2/13/2012)
So, If i run the drop_existing script , will it still move the entire table include the data and index to the secondary filegroup ?CREATE UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]
([Account] ASC,
[PathId] ASC,
[FileName] ASC,
[FileNameLength] ASC,
[Tdate] ASC)
WITH (DROP_EXISTING = ON)
ON [SecondaryFG]
GO
It'll move the clustered index, and the clustered index is the data, so the table moves to the new filegroup. Any LOB data will stay behind, as will any nonclustered indexes (you'd have to move them separately)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2012 at 2:22 am
I'm surprised Gail and Jeff haven't jumped all over this but that does not look like a good candidate for a clustered index, its certainly not narrow or ever-increasing.
Changing the cluster could save some of that 10GB down to indexes (you only have a clustered index?) and reduce the occurrence of fragmentation in the first place.
---------------------------------------------------------------------
February 14, 2012 at 2:25 am
george sibbald (2/14/2012)
I'm surprised Gail and Jeff haven't jumped all over this but that does not look like a good candidate for a clustered index, its certainly not narrow or ever-increasing.
Agreed, maybe take a read through this: http://www.sqlservercentral.com/articles/Indexing/68563/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2012 at 3:58 am
Thanks for the explanation .
Does below variable data type consider BOL data type in sql 2005 ? Will this column data transfered to the new filegroup when running create unique cluster index with option drop existing?
This is some of column exits in the table.
[FileKey] [binary](16) NOT NULL,
[Name] [binary](8) NOT NULL,
[FileAttribute] [varbinary](4096) NOT NULL,
[FileNameLength] AS (datalength([FileName
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply