August 30, 2012 at 10:44 am
Hello,
I have a process that collects and maintains the output of sys.dm_db_index_physical_stats in two tables. I use a historical table and a current run table. I've been running my process on a test server daily in order to anaylize the fragmentation percentage change per day. I'd like to have a query that shows the tablename, index_id and fragmentation %'s over the course of the week, therefore having to join the current table and the historical table(several times). I'm having difficulty writing the query to obtain what I'm looking for. I've listed the table formats and what I've written so far and an example of where the output isn't correct. Any help would be appreciated. The FragmentationHist table is the same structure as the current table.
CREATE TABLE [MAINT].[Fragmentation](
[FragmentationId] [bigint] IDENTITY(1,1) NOT NULL,
[SampleDate] [smalldatetime] NOT NULL,
[DatabaseName] [varchar](100) NOT NULL,
[DatabaseId] [smallint] NOT NULL,
[ObjectId] [int] NOT NULL,
[IndexId] [int] NOT NULL,
[PartitionNumber] [int] NOT NULL,
[IndexType] [varchar](60) NOT NULL,
[AllocUnitType] [varchar](60) NOT NULL,
[IndexDepth] [tinyint] NOT NULL,
[IndexLevel] [tinyint] NOT NULL,
[AvgFragmentationPercent] [float] NULL,
[FragmentCount] [bigint] NULL,
[AvgFragmentSizePages] [float] NULL,
[PageCount] [bigint] NOT NULL,
[AvgPageSpaceUsedPercent] [float] NULL,
[RecordCount] [bigint] NULL,
CONSTRAINT [PK_Fragmentation] PRIMARY KEY CLUSTERED
(
[FragmentationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [MAINT].[Fragmentation] ADD DEFAULT (getdate()) FOR [SampleDate]
GO
USE MyDatabaseName;
select OBJECT_NAME(C.ObjectId) as 'TableName', C.IndexId,
(H2.AvgFragmentationPercent) as 'Frag % - 8/27/2012',
(H1.AvgFragmentationPercent) as 'Frag % - 8/28/2012',
(C.AvgFragmentationPercent) as 'Frag % - 8/29/2012'
from DBA.MAINT.Fragmentation C
JOIN DBA.MAINT.FragmentationHist H1 on C.ObjectId = H1.ObjectId and C.IndexId = H1.IndexId
JOIN DBA.MAINT.FragmentationHist H2 on H1.ObjectId = H2.ObjectId and H1.IndexId = H2.IndexId
where C.DatabaseName = 'MyDatabaseName' and H1.SampleDate = '2012-08-28 18:00:00' and H2.SampleDate = '2012-08-27 18:00:00'
and C.IndexType <> 'HEAP'
and C.AvgFragmentationPercent > 9.999
and C.PageCount > 99
--GROUP by OBJECT_NAME(C.ObjectId), C.IndexId
ORDER BY OBJECT_NAME(C.ObjectId), C.IndexId
Output of correct and incorrect data. The incorrect stems from an index not appearing in each date criteria.
TableName IndexId Frag % - 8/27/2012 Frag % - 8/28/2012 Frag % - 8/29/2012
S_ASGN_DYN_COMP 2 15.6739811912226 19.7628458498024 19.7628458498024
S_ASGN_DYN_COMP 3 15.7894736842105 19.8237885462555 19.8237885462555
S_AUDIT_ITEM 1 9.03827597298167 10.5180037902716 11.1842105263158
S_AUDIT_ITEM 1 0 10.5180037902716 11.1842105263158
S_AUDIT_ITEM 1 9.03827597298167 0 11.1842105263158
S_AUDIT_ITEM 1 0 0 11.1842105263158
S_AUDIT_ITEM 19 7.24381625441696 13.6824324324324 15.78073089701
August 30, 2012 at 12:46 pm
When doing queries like these, I like to gather all the rows I'm going to report first, then left join the data rows to them. For me it makes this sort of thing much less complicated, but you won't hurt my feelings if you don't like this method 😉
For instance, a simplified proc would look something like this (I eliminate the 'current' table and report entirely out of the history table, because the current look is just the latest set in the history table). I wasn't able to test this but with some test data it shouldn't be hard to fix if its broken. Could you post your queries to populate the tables? I'd be interested in them as I need to be watching some indexes too!
DECLARE @DATE1 DATETIME
DECLARE @DATE2 DATETIME
DECLARE @DATE3 DATETIME
SET @DATE1 = '2012-08-29 18:00:00'
SET @DATE2 = '2012-08-28 18:00:00'
SET @DATE3 = '2012-08-27 18:00:00'
SELECT ids.TableName,
ids.IndexID,
H2.AvgFragmentationPercent,
H1.AvgFragmentationPercent,
C.AvgFragmentationPercent
FROM
( -- GATHER ALL UR IDS FIRST, THEN LEFT JOIN TO THIS LIST OF IDS. 'UNION' MAKES THE ENTIRE LIST DISTINCT
SELECT OBJECT_NAME(ObjectId) as TableName, IndexId
FROM FragmentationHist WHERE DatabaseName = 'MyDatabaseName' AND SampleDate = '2012-08-29 18:00:00'
UNION
SELECT OBJECT_NAME(ObjectId) as TableName, IndexId
FROM FragmentationHist WHERE DatabaseName = 'MyDatabaseName' AND SampleDate = '2012-08-28 18:00:00'
UNION
SELECT OBJECT_NAME(ObjectId) as TableName, IndexId
FROM FragmentationHist WHERE DatabaseName = 'MyDatabaseName' AND SampleDate = '2012-08-27 18:00:00'
) ids
LEFT JOIN FragmentationHist C
ON
ids.TableName = OBJECT_NAME(C.ObjectId) AND
ids.IndexID = C.IndexID AND
C.SampleDate = @DATE1
LEFT JOIN FragmentationHist H2
ON
ids.TableName = OBJECT_NAME(H2.ObjectId) AND
ids.IndexID = H2.IndexID AND
H2.SampleDate = @DATE2
LEFT JOIN FragmentationHist H1
ON
ids.TableName = OBJECT_NAME(H1.ObjectId) AND
ids.IndexID = H1.IndexID AND
H1.SampleDate = @DATE3
-- I didn't apply any filtering on indextype, avgfragmentationpercent, pagecount, etc
edit: changed tablenames to match original post
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply