Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Cluster index fragementation Expand / Collapse
Author
Message
Posted Monday, June 2, 2014 10:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 4,343, Visits: 6,151
CREATE TABLE XXXX_XX(
[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] [int] IDENTITY(1,1) NOT NULL,
[POL_TYPE_ID] [int] NULL,
[GENDER] [varchar](1) NULL,
[DRIVER_AGE_RANGE_ID] [int] NULL,
[CARRIER_ID] [varchar](11) NULL,
[C_ST_ALPH] [nvarchar](10) NULL,
[LIMIT_ID] [int] NULL,
[BIZ_MONTHYEAR] [int] NOT NULL,
[BIZ_YEAR] [varchar](4) NULL,
[BIZ_MONTH] [varchar](2) NULL,
[TOT_POLICY_COUNT] [int] NULL,
[TOT_VEHICLE_COUNT] [int] NULL,
[TOT_DRIVER_COUNT] [int] NULL,
[START_DT] [int] NULL,
[END_DT] [int] NULL,
[LOAD_DT] [datetime] NOT NULL,
CONSTRAINT [PK_DM_CV_AGGR_Fact_Policy_Coverage] PRIMARY KEY CLUSTERED
(
[BIZ_MONTHYEAR] ASC,
[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
END
GO

Wow - hard to know where to start with all the issues I see here.

1) I will assume you have biz_monthyear as leading column for some particular reason (report query performance probably). With it first you will get splits as you insert new data and really bad splitting if you load them out of order.

2) Your PK allows you to insert the EXACT SAME RECORD multiple times. Hopefully you guarantee that cannot happen via some other control.

3) Do you need 4.2Billion values for POL_TYPE_ID? Doubtful. If not you have wasted space over small or tiny int.

4) Gender being varchar(1) was already covered as bad practice.

5) DRIVER_AGE_RANGE_ID DEFINITELY doesn't need 4.2B values. tinyint gives 255 values for 1 byte vs 4 for int.

6) BIZ_MONTHYEAR doesn't need 4.2B values. A smallint gives you sufficient years for 2 bytes.

7) BIZ_MONTH as varchar(2) is like Gender.

8) I wonder how many of your (n)varchars are actually stuffed with valid values sufficient to make their average length at or very close to the variable length allowed. If they are then variable length fields are quite inefficient from storage and processing perspective.

9) Why do you need 2 YEAR fields?

10) START_DT/END_DT again have the int/smallint problem.

Bytes REALLY do add up and are REALLY important!! You could easily have 20 bytes PER ROW in this table that are useless.



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1576657
Posted Monday, June 2, 2014 10:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
TheSQLGuru (6/2/2014)
CREATE TABLE XXXX_XX(
[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] [int] IDENTITY(1,1) NOT NULL,
[POL_TYPE_ID] [int] NULL,
[GENDER] [varchar](1) NULL,
[DRIVER_AGE_RANGE_ID] [int] NULL,
[CARRIER_ID] [varchar](11) NULL,
[C_ST_ALPH] [nvarchar](10) NULL,
[LIMIT_ID] [int] NULL,
[BIZ_MONTHYEAR] [int] NOT NULL,
[BIZ_YEAR] [varchar](4) NULL,
[BIZ_MONTH] [varchar](2) NULL,
[TOT_POLICY_COUNT] [int] NULL,
[TOT_VEHICLE_COUNT] [int] NULL,
[TOT_DRIVER_COUNT] [int] NULL,
[START_DT] [int] NULL,
[END_DT] [int] NULL,
[LOAD_DT] [datetime] NOT NULL,
CONSTRAINT [PK_DM_CV_AGGR_Fact_Policy_Coverage] PRIMARY KEY CLUSTERED
(
[BIZ_MONTHYEAR] ASC,
[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
END
GO

Wow - hard to know where to start with all the issues I see here.

1) I will assume you have biz_monthyear as leading column for some particular reason (report query performance probably). With it first you will get splits as you insert new data and really bad splitting if you load them out of order.

2) Your PK allows you to insert the EXACT SAME RECORD multiple times. Hopefully you guarantee that cannot happen via some other control.

3) Do you need 4.2Billion values for POL_TYPE_ID? Doubtful. If not you have wasted space over small or tiny int.

4) Gender being varchar(1) was already covered as bad practice.

5) DRIVER_AGE_RANGE_ID DEFINITELY doesn't need 4.2B values. tinyint gives 255 values for 1 byte vs 4 for int.

6) BIZ_MONTHYEAR doesn't need 4.2B values. A smallint gives you sufficient years for 2 bytes.

7) BIZ_MONTH as varchar(2) is like Gender.

8) I wonder how many of your (n)varchars are actually stuffed with valid values sufficient to make their average length at or very close to the variable length allowed. If they are then variable length fields are quite inefficient from storage and processing perspective.

9) Why do you need 2 YEAR fields?

10) START_DT/END_DT again have the int/smallint problem.

Bytes REALLY do add up and are REALLY important!! You could easily have 20 bytes PER ROW in this table that are useless.



Actually, if START_DT and END_DT are dates, they should be stored as dates, not integers.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1576664
Posted Monday, June 2, 2014 12:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 4,343, Visits: 6,151
I call "daughter distraction" on that one Lynn! Actually I have seen, and used myself often, int dates stored as CCYYMMDD in data warehouses. I think a lot of the reasoning for that went away with the DATE data type though. I would love to see a good study done of the plusses and minuses of each on modern systems.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1576712
Posted Monday, June 2, 2014 12:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
TheSQLGuru (6/2/2014)
I call "daughter distraction" on that one Lynn! Actually I have seen, and used myself often, int dates stored as CCYYMMDD in data warehouses. I think a lot of the reasoning for that went away with the DATE data type though. I would love to see a good study done of the plusses and minuses of each on modern systems.


Well, if you store a date as an integer in the format CCYYMMDD you have to use the INT data type.

:)



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1576728
Posted Monday, June 2, 2014 1:10 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 4,343, Visits: 6,151
Yeah - that was the main point included in the distraction plea, although not the only one!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1576738
Posted Monday, June 2, 2014 3:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
I just "Love" INT dates and times and the "new" datetime datatypes especially when someone wants to know the duration in decimal hours between a start and end date with dates and times.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576786
Posted Wednesday, June 4, 2014 8:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:01 AM
Points: 104, Visits: 517
Thank you all for your valuable inputs. It was great learning.

I rebuild all the indexes of these tables. could still see fragementaion more then 20 - 25 % for all these tables which was rebuild. We have not done any insert oe update / deleteactivities , why is this so?

Is this something related to filesystem fragmentation?


Cheers,

Got an idea..share it !!

DBA_Vishal
Post #1577357
Posted Wednesday, June 4, 2014 8:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 4,343, Visits: 6,151
What is the growth factor for the data file(s)? What is the size of the indexes that are still fragmented?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1577368
Posted Thursday, June 5, 2014 9:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:01 AM
Points: 104, Visits: 517
As this DW environemnt , we have created a BIG FAT datafile of 20 TB. The DB is only 40% full. Autogrowth is none now.

Cheers,

Got an idea..share it !!

DBA_Vishal
Post #1577862
Posted Thursday, June 5, 2014 2:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 439, Visits: 1,381
DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] [int] IDENTITY(1,1) NOT NULL -- is ever increasing.

But place it on the 1 st place in your clustered index. It still can be partitioned by BIZ_MONTHYEAR even if it is on the 2 nd place.
Post #1578061
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse