SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cluster index fragementation


Cluster index fragementation

Author
Message
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12607 Visits: 8560
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 on googles mail service
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39810 Visits: 38563
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.

Cool
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)
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12607 Visits: 8560
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 on googles mail service
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39810 Visits: 38563
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.

Smile

Cool
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)
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12607 Visits: 8560
Yeah - that was the main point included in the distraction plea, although not the only one!! Hehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87186 Visits: 41113
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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
vkundar
vkundar
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 869
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12607 Visits: 8560
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 on googles mail service
vkundar
vkundar
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 869
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
SQL Guy 1
SQL Guy  1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1360 Visits: 2515
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search