May 13, 2014 at 3:51 am
Hi,
We have an installation of Microsoft CRM 2011 on SQL 2008 R2. There is a table which only has 47,204 records but this is equating to 40Gb disk space.
When I do a sp_spaceused on the table the results are
name AsyncOperationBase
rows 47204
reserved 40399752 KB
data 40143824 KB
index_size 19048 KB
unused 236880 KB
I have tried rebuilding indexes, clean table etc.
Can anyone advise how I can find out where all the disk space is being eaten up?
thanks in advance
May 13, 2014 at 3:59 am
May 13, 2014 at 4:02 am
Thanks David, create table script ....
USE [MSCRM]
GO
/****** Object: Table [dbo].[AsyncOperationBase] Script Date: 05/13/2014 11:00:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AsyncOperationBase](
[MessageName] [nvarchar](160) NULL,
[Depth] [int] NOT NULL,
[PrimaryEntityType] [int] NULL,
[Data] [nvarchar](max) NULL,
[RegardingObjectId] [uniqueidentifier] NULL,
[WorkflowStageName] [nvarchar](256) NULL,
[OperationType] [int] NULL,
[DependencyToken] [nvarchar](256) NULL,
[RecurrencePattern] [nvarchar](256) NULL,
[Name] [nvarchar](256) NULL,
[PostponeUntil] [datetime] NULL,
[WorkflowState] [varchar](max) NULL,
[TimeZoneRuleVersionNumber] [int] NULL,
[OwningBusinessUnit] [uniqueidentifier] NOT NULL,
[IsWaitingForEvent] [bit] NULL,
[CreatedBy] [uniqueidentifier] NOT NULL,
[ErrorCode] [int] NULL,
[ModifiedBy] [uniqueidentifier] NOT NULL,
[CorrelationId] [uniqueidentifier] NOT NULL,
[RecurrenceStartTime] [datetime] NULL,
[StatusCode] [int] NULL,
[AsyncOperationId] [uniqueidentifier] NOT NULL,
[Sequence] [bigint] IDENTITY(1,1) NOT NULL,
[RequestId] [uniqueidentifier] NULL,
[WorkflowIsBlocked] [bit] NULL,
[ModifiedOn] [datetime] NULL,
[Message] [nvarchar](max) NULL,
[StartedOn] [datetime] NULL,
[HostId] [nvarchar](256) NULL,
[StateCode] [int] NOT NULL,
[WorkflowActivationId] [uniqueidentifier] NULL,
[CompletedOn] [datetime] NULL,
[CorrelationUpdatedTime] [datetime] NOT NULL,
[UTCConversionTimeZoneCode] [int] NULL,
[RetryCount] [int] NULL,
[CreatedOn] [datetime] NULL,
[RegardingObjectIdName] [nvarchar](4000) NULL,
[RegardingObjectTypeCode] [int] NULL,
[RegardingObjectIdYomiName] [nvarchar](4000) NULL,
[FriendlyMessage] [nvarchar](max) NULL,
[ExecutionTimeSpan] [float] NOT NULL,
[ModifiedOnBehalfBy] [uniqueidentifier] NULL,
[CreatedOnBehalfBy] [uniqueidentifier] NULL,
[OwningExtensionId] [uniqueidentifier] NULL,
[OwnerId] [uniqueidentifier] NOT NULL,
[OwningExtensionIdName] [nvarchar](4000) NULL,
[OwningExtensionTypeCode] [int] NULL,
[OwnerIdType] [int] NOT NULL,
CONSTRAINT [cndx_PrimaryKey_AsyncOperation] PRIMARY KEY CLUSTERED
(
[AsyncOperationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[AsyncOperationBase] WITH NOCHECK ADD CONSTRAINT [business_unit_asyncoperation] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[AsyncOperationBase] CHECK CONSTRAINT [business_unit_asyncoperation]
GO
ALTER TABLE [dbo].[AsyncOperationBase] WITH NOCHECK ADD CONSTRAINT [lk_asyncoperation_workflowactivationid] FOREIGN KEY([WorkflowActivationId])
REFERENCES [dbo].[WorkflowBaseIds] ([WorkflowId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[AsyncOperationBase] CHECK CONSTRAINT [lk_asyncoperation_workflowactivationid]
GO
ALTER TABLE [dbo].[AsyncOperationBase] WITH NOCHECK ADD CONSTRAINT [owner_asyncoperations] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[OwnerBase] ([OwnerId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[AsyncOperationBase] CHECK CONSTRAINT [owner_asyncoperations]
GO
ALTER TABLE [dbo].[AsyncOperationBase] ADD CONSTRAINT [DF_AsyncOperationBase_CorrelationUpdatedTime] DEFAULT (getutcdate()) FOR [CorrelationUpdatedTime]
GO
ALTER TABLE [dbo].[AsyncOperationBase] ADD CONSTRAINT [DF_AsyncOperationBase_ExecutionTimeSpan] DEFAULT ((0)) FOR [ExecutionTimeSpan]
GO
ALTER TABLE [dbo].[AsyncOperationBase] ADD CONSTRAINT [DF_AsyncOperationBase_OwnerId] DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [OwnerId]
GO
ALTER TABLE [dbo].[AsyncOperationBase] ADD CONSTRAINT [DF_AsyncOperationBase_OwnerIdType] DEFAULT ((8)) FOR [OwnerIdType]
GO
May 13, 2014 at 4:18 am
Is it a heap (no clustered index)?
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
May 13, 2014 at 4:21 am
No, it has a clustered index on [AsyncOperationId] which is type [uniqueidentifier]
May 13, 2014 at 4:29 am
There are three blob columns in that table (varchar(max)/nvarchar(max)), they can be up to 2GB each. Gut feel, that's where the space is going.
Check the Datalength() of each of those columns for each row, see how big they are.
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
May 13, 2014 at 4:35 am
Hi,
A quick google of the table name throws up alsorts of posts about this table growing hugely, so much so that there is a microsoft kb article about clearing it down.
Have a look at:
It looks at the issue from an application point of view.
This is a history table with a lot of very wide columns so the datasize is going to grow.
This will show the row size
DBCC SHOWCONTIG WITH TABLERESULTS;
May 13, 2014 at 4:48 am
Thank you both for taking the time to look.
I've also googled and found various articles based on this table. They all tend to say performance degrades when this table has a lot (millions) of records.
I've run all the purge scripts etc that they recommend which has reduced the table from 200k records to 47k but the disk space taken up still remains circa 40Gb.
When I look at the datalength of the blob columns, the largest is the 'data' column and is 95529736.
thanks,
Jonathan.
May 13, 2014 at 4:56 am
When I run DBCC SHOWCONTIG, I get the following results
ObjectName:AsyncOperationBase
ObjectId:1724585232
IndexName: cndx_PrimaryKey_AsyncOperation
IndexId: 1
Level:0
Pages:23923
Rows:47204
MinimumRecordSize: 459
MaximumRecordSize: 8051
AverageRecordSize: 3012.731
ForwardedRecords: 0
Extents: 2991
ExtentSwitches: 2990
AverageFreeBytes: 2147.44
AveragePageDensity: 73.4687422782308
ScanDensity: 100
BestCount: 2991
ActualCount: 2991
LogicalFragmentation: 0
ExtentFragmentation: 0.200601805416249
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply