February 19, 2014 at 3:17 am
In the last few days, one of my databases (SQLMonitorDB - the data repository for Red Gate's SQL Monitor) has been growing quickly and threatening to overwhelm the disk it sits on. The database is running on one instance of four on a SQL 2008 standard edition two-node cluster.
The database has grown to 110GB (it was more like half that not long ago – I added a new cluster with just one instance and one database back in December – the existing load was one cluster with 4 instances & one standalone server with one instance, around 80 databases in all)… and nearly all of that appears to be the table data.Cluster_SqlServer_SqlProcess_UnstableSamples
Running the query:
SELECT SCHEMA_NAME (t.[schema_id]) AS [Schema]
,t.NAME AS TableName
,i.name AS IndexName
,SUM (p.rows) AS RowCounts
,SUM (a.total_pages) AS TotalPages
,SUM (a.used_pages) AS UsedPages
,SUM (a.data_pages) AS DataPages
,100.0 * SUM (a.data_pages) / (SUM (a.used_pages) + 1) AS PercentData
, (SUM (a.total_pages) * 8) / 1024 AS TotalSpaceMB
, (SUM (a.used_pages) * 8) / 1024 AS UsedSpaceMB
, (SUM (a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
AND i.OBJECT_ID > 255
AND i.index_id <= 1
GROUP BY SCHEMA_NAME (t.[schema_id])
,t.NAME
,i.object_id
,i.index_id
,i.name
ORDER BY SUM (a.total_pages) DESC
…gives (these are the top few rows):
Schema TableName IndexName RowCounts TotalPages UsedPages DataPages PercentData TotalSpaceMB UsedSpaceMB DataSpaceMB
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- --------------------------------------- -------------------- -------------------- --------------------
data Cluster_SqlServer_SqlProcess_UnstableSamples Cluster_SqlServer_SqlProcess_UnstableSamples_Id_CollectionDate 870291 13061509 13055924 20128 0.154167552279903 102043 101999 157
data Cluster_Machine_Process_UnstableSamples Cluster_Machine_Process_UnstableSamples_Id_CollectionDate 17948360 164676 162311 161306 99.380206022968110 1286 1268 1260
data Cluster_SqlServer_TopQueries_UnstableSamples Cluster_SqlServer_TopQueries_UnstableSamples_Id_CollectionDate 7538925 81648 80980 80500 99.406033514034156 637 632 628
data Cluster_Machine_LogicalDisk_UnstableSamples Cluster_Machine_LogicalDisk_UnstableSamples_Id_CollectionDate 3348864 42092 41699 41504 99.529976019184652 328 325 324
data Cluster_SqlServer_Database_Performance_UnstableSamples Cluster_SqlServer_Database_Performance_UnstableSamples_Id_CollectionDate 3910564 35822 35358 35136 99.369326055601119 279 276 274
data Cluster_SqlServer_ServerWaits_UnstableSamples Cluster_SqlServer_ServerWaits_UnstableSamples_Id_CollectionDate 4552700 32728 31773 31575 99.373701768741738 255 248 246
data Cluster_SqlServer_TopQueries_Instances Cluster_SqlServer_TopQueries_Instances_Id 139756 25931 25508 25211 98.831784860245403 202 199 196
data Cluster_Machine_Network_UnstableSamples Cluster_Machine_Network_UnstableSamples_Id_CollectionDate 3941066 22973 22578 22450 99.428672660436688 179 176 175
data Cluster_SqlServer_Database_Storage_UnstableSamples Cluster_SqlServer_Database_Storage_UnstableSamples_Id_CollectionDate 3910564 19201 18520 18401 99.352086820366070 150 144 143
data Cluster_SqlServer_TopQueries_Sightings Cluster_SqlServer_TopQueries_Sightings_SightingDate_Id 5038012 18327 18278 18200 99.567810055254663 143 142 142
data Cluster_SqlServer_Database_File_UnstableSamples Cluster_SqlServer_Database_File_UnstableSamples_Id_CollectionDate 2340393 11631 11249 11176 99.342222222222222 90 87 87
data Cluster_SqlServer_Database_BackupType_Sightings Cluster_SqlServer_Database_BackupType_Sightings_SightingDate_Id 2544826 8997 8855 8818 99.570912375790424 70 69 68
(Sorry about the spacing, not sure how to post tabular data so this is the output from Results to Text in SSMS)
As you can see, that one table (the first row) is responsible for over 90% of the whole database size.
I reduced data retention from 2 weeks to 1 week, and the number of rows in the table fell from around 1,500,000 to 870,000, but the total pages, used pages and total space hardly flickered.
You can also see that for all other tables, the data pages make up 99% of the total size, but for this one, it is 0.15%!!
I’ve tried rebuilding the clustered index, DBCC CLEANTABLE, I tried shrinking the data file with reorganise (it shows almost no free space anyway, but worth a try), all to no avail – nothing seems to make any difference to this unreasonably massive table.
EXEC sp_spaceused @objname='data.Cluster_SqlServer_SqlProcess_UnstableSamples', @updateusage = N'TRUE'
Shows:
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
Cluster_SqlServer_SqlProcess_UnstableSamples 290016 104492008 KB 104446576 KB 704 KB 44728 KB
I've asked Red Gate support for help (yesterday) but heard nothing yet... and I'd like to find out what's going on and what I can do about it a.s.a.p. as I've had to set growth limits on the database to prevent it taking down my instance, and it's close to them already - and obviously I don't want to lose my monitoring.
If you have any insight, I'd be grateful...
Cheers,
Dave.
February 19, 2014 at 5:09 am
The table is a heap?
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 19, 2014 at 5:23 am
No, it has a clustered primary key on two columns (both bigint), no other indexes.
February 19, 2014 at 5:43 am
please post the schema for the table, any column types such as varbinary?
---------------------------------------------------------------------
February 19, 2014 at 5:48 am
CREATE TABLE [data].[Cluster_SqlServer_SqlProcess_UnstableSamples](
[Id] [bigint] NOT NULL,
[CollectionDate] [bigint] NOT NULL,
[_Blocked] [bigint] NULL,
[_BlockingLoginTime] [bigint] NULL,
[_Command] [nvarchar](max) NULL,
[_CumulativeProcessorTime] [bigint] NULL,
[_DatabaseName] [nvarchar](max) NULL,
[_FullBlockingCommand] [nvarchar](max) NULL,
[_FullCommand] [nvarchar](max) NULL,
[_LastBatch] [bigint] NULL,
[_LastWaitType] [nvarchar](max) NULL,
[_MemoryUsage] [bigint] NULL,
[_OpenTransactions] [bigint] NULL,
[_PhysicalIo] [bigint] NULL,
[_Status] [nvarchar](max) NULL,
[_WaitResource] [nvarchar](max) NULL,
[_WaitTime] [bigint] NULL,
[_WaitType] [nvarchar](max) NULL,
CONSTRAINT [Cluster_SqlServer_SqlProcess_UnstableSamples_Id_CollectionDate] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[CollectionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [data].[Cluster_SqlServer_SqlProcess_UnstableSamples] WITH CHECK ADD CONSTRAINT [Cluster_SqlServer_SqlProcess_UnstableSamples_Cluster_SqlServer_SqlProcess_Keys] FOREIGN KEY([Id])
REFERENCES [data].[Cluster_SqlServer_SqlProcess_Keys] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [data].[Cluster_SqlServer_SqlProcess_UnstableSamples] CHECK CONSTRAINT [Cluster_SqlServer_SqlProcess_UnstableSamples_Cluster_SqlServer_SqlProcess_Keys]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Contains the session ID of blocking process.' , @level0type=N'SCHEMA',@level0name=N'data', @level1type=N'TABLE',@level1name=N'Cluster_SqlServer_SqlProcess_UnstableSamples', @level2type=N'COLUMN',@level2name=N'_Blocked'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Login time of the blocking process' , @level0type=N'SCHEMA',@level0name=N'data', @level1type=N'TABLE',@level1name=N'Cluster_SqlServer_SqlProcess_UnstableSamples', @level2type=N'COLUMN',@level2name=N'_BlockingLoginTime'
GO
February 19, 2014 at 6:01 am
Ooh, that's a lot of LOB columns.
Maybe an index reorganise with LOB_Compaction on?
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 19, 2014 at 6:04 am
dear oh dear, strange choice of column types!
---------------------------------------------------------------------
February 19, 2014 at 6:50 am
GilaMonster (2/19/2014)
Ooh, that's a lot of LOB columns.Maybe an index reorganise with LOB_Compaction on?
I tried that originally, then rebuilt the index, neither helped.
February 19, 2014 at 7:00 am
Can you run a select on the table, get the DATA_LENGTH of each of the lob columns and order by the sum of all those columns data lengths desc? Let's see where that space is, it's definitely in the LOB data, in LOB pages (which is why the data pages are so low for this table)
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 19, 2014 at 7:35 am
Aha! Thanks Gail, I didn't know about DATALENGTH! That was the tool I needed to get to the bottom of it.
Nearly all the space is used by the _FullCommand column. Most records are quite reasonable (from a few bytes up to a few KB), but there are just over 100,000 records in there with more than 1MB in that column. When I cut the retention in half none of those big records were amongst the data to be deleted.
I can track down the statements (they are all of the same form, and I have a good idea where to look now) and determine what to do about them from this point on...
Thanks again, you've been a great help 🙂
Cheers,
Dave.
February 19, 2014 at 7:39 am
cough
---------------------------------------------------------------------
February 19, 2014 at 7:55 am
george sibbald (2/19/2014)
cough
Ha ha, sorry George, much appreciated too 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply