October 19, 2007 at 10:56 am
My questions are:
Are my indexes health?
Can the performance be improved with index maintenance?
Does the PK matter in this instance if the query plan is only accessing the index?
Here is the background:
I have a Dimension table, DimUsers, that has close to 200 million records.
When loading up Fact tables, I do the following:
INSERT INTO Fact(dateKey, countryKey, userKey, userPerCountryPerDay)
SELECT d1.dateKey, d2.countryKey, d3.userKey, count(*)
FROM NewData as ND
JOIN DimDates as d1 on d1.[date] = ND.[date]
JOIN DimCountries as d2 on d2.[countryId] = ND.[countryId]
JOIN DimUsers as d3 on d3.[cookieId] = ND.[cookieId]
The performance of this query suffers enormously when joined to DimUsers (the 200million record table).
To frame it properly, looking up around 60,000 cookieId takes close to 1 minute.
DimUsers has the following columns:
CREATE TABLE [DimUsers] (
[UserKey] [int] IDENTITY (1, 1) NOT NULL ,
[CookieId] [int] NULL ,
[GroupId] [tinyint] NULL ,
[GroupName] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoadDate] [smalldatetime] NULL CONSTRAINT [DF__DimUsers__LoadDa__1DE57479] DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[UserKey]
) ON [Dimensions] ,
CONSTRAINT [UQ_DimUsers_Cookie] UNIQUE NONCLUSTERED
(
[CookieId]
) WITH FILLFACTOR = 99 ON [DimensionIndexes]
) ON [Dimensions]
GO
There is also an index on [cookieId, userKey] as such:
CREATE INDEX IX_DimUsers_CookieId_UserKey on DimUsers (cookieId, userKey)
Here is the showcontig info for the PK and index IX_DimUsers_CookieId_UserKey
DBCC SHOWCONTIG scanning 'DimUsers' table...
Table: 'DimUsers' (469576711); index ID: 1, database ID: 12
TABLE level scan performed.
- Pages Scanned................................: 1456061
- Extents Scanned..............................: 182604
- Extent Switches..............................: 337395
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 53.94% [182008:337396]
- Logical Scan Fragmentation ..................: 6.11%
- Extent Scan Fragmentation ...................: 13.27%
- Avg. Bytes Free per Page.....................: 468.0
- Avg. Page Density (full).....................: 94.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'DimUsers' table...
Table: 'DimUsers' (469576711); index ID: 3, database ID: 12
LEAF level scan performed.
- Pages Scanned................................: 620221
- Extents Scanned..............................: 78208
- Extent Switches..............................: 86255
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 89.88% [77528:86256]
- Logical Scan Fragmentation ..................: 0.75%
- Extent Scan Fragmentation ...................: 63.55%
- Avg. Bytes Free per Page.....................: 108.4
- Avg. Page Density (full).....................: 98.66%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TIA,
jmr
October 23, 2007 at 2:18 pm
First of all, yes, you do have some fragmentation in your tables and I would look to reindex/defrag these.
Also, look at your distribution statistics. How up to date are these? If yo do a defrag, update the statistics as well.
What does your execution plan show during the insert? Can you see anything which could point to a particular problem?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply