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.
TableNameIndexIdFrag % - 8/27/2012Frag % - 8/28/2012Frag % - 8/29/2012
S_ASGN_DYN_COMP215.673981191222619.762845849802419.7628458498024
S_ASGN_DYN_COMP315.789473684210519.823788546255519.8237885462555
S_AUDIT_ITEM19.0382759729816710.518003790271611.1842105263158
S_AUDIT_ITEM1010.518003790271611.1842105263158
S_AUDIT_ITEM19.03827597298167011.1842105263158
S_AUDIT_ITEM10011.1842105263158
S_AUDIT_ITEM197.2438162544169613.682432432432415.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