we have a 2 nodes win2k server cluster which installed sqlsrv 7.0, the
primary one failed, and the secondary take over the service as design.
we do not have system backup for the primary node, and we got a blue screen
for that one. it seams like that some registe records are crashed. we decide
to reinstall that node.
when we install sql server before cluster service, the cluster service will
complain the sql server's exsiting. when we install the cluster before sql
server 7.0, the sql server will complain we should undeploy the cluster.
i can not find any valueable resources for install sql server 7.0 in win2k
cluster. is there any one had this situation before?
Unfortunately, you will have to rebuild your SS70 cluster. This does not
mean you will have to rebuild your installation, just the cluster.
Uncluster the current node.
Add the new node to the Win2K cluster.
Rerun Comclust to cluster the MS DTC on the new node.
Install SS70 on the new node.
Recluster the SS70 installation.
Anthony Thomas
"linbiao" <linbiao@.discussions.microsoft.com> wrote in message
news:2133DDC4-EDDF-4023-B381-9AA778CDC567@.microsoft.com...
> we have a 2 nodes win2k server cluster which installed sqlsrv 7.0, the
> primary one failed, and the secondary take over the service as design.
> we do not have system backup for the primary node, and we got a blue
screen
> for that one. it seams like that some registe records are crashed. we
decide
> to reinstall that node.
> when we install sql server before cluster service, the cluster service
will
> complain the sql server's exsiting. when we install the cluster before sql
> server 7.0, the sql server will complain we should undeploy the cluster.
> i can not find any valueable resources for install sql server 7.0 in win2k
> cluster. is there any one had this situation before?
|||On Sun, 17 Dec 2006 15:21:50 -0800, Anthony Thomas <ALThomas@.kc.rr.com>
wrote:
> Unfortunately, you will have to rebuild your SS70 cluster. This does not
> mean you will have to rebuild your installation, just the cluster.
> Uncluster the current node.
> Add the new node to the Win2K cluster.
> Rerun Comclust to cluster the MS DTC on the new node.
> Install SS70 on the new node.
> Recluster the SS70 installation.
>
> Anthony Thomas
>
thank you very much. we will try it.
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Fact Tables and Clustered Indexes
A design question:
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com
"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.
|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com
"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.
|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.c om...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Fact Tables and Clustered Indexes
A design question:
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.com...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.com...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Say I have a medium-sized fact table w/ a handful of dimension key
columns (including a date) and a handful of measures.
I necessarily need a primary key on the composite dimension key
columns, and I don't know ahead of time which of my dimension key
column(s) will be the best candidate for a clustered index. I do plan
on putting non-clustered indexes on all my dimension key columns, and
the related dimension tables' key columns.
For the sake of argument, let's say we're not partitioning the fact
table.
Assume that new facts occur in time, the fact table grows with time,
and (nearly) all changes to the fact table occur as INSERTs.
Now, all things being equal, is there a benefit of adding a clustered
index to the fact table? Two options:
- Add an IDENTITY column, make it the primary key, and add the
clustered index to it.
- Add the clustered index on the date column, since it has a natural
order.
Basically, I'm after two answers in this scenario:
- Is there a benefit to having a clustered index on a table when the
application doesn't 'really' call for one?
- If so, is it better to add an IDENTITY column (adding size to the
table) or to pick an naturally ordered dimension key? A random key?
The fact's composite key?
Thanks much.
Steven D. Clark
stevec@.clarkdev.com"Steven Clark" <stevec@.clarkdev.com> wrote in message
news:d7740507.0407100710.2b1644b5@.posting.google.com...
> Basically, I'm after two answers in this scenario:
> - Is there a benefit to having a clustered index on a table when the
> application doesn't 'really' call for one?
In my opinion, there is never a reason NOT to have one; it's a freebie,
basically, unlike clustered indexes. It doesn't take up any extra disc
space, and you might as well order the data on the disc somehow, rather than
letting the server take care of it... So I always make sure that every table
has one.
> - If so, is it better to add an IDENTITY column (adding size to the
> table) or to pick an naturally ordered dimension key? A random key?
> The fact's composite key?
Some tips for clustered indexes:
- They assist with range queries and grouping. So try to use them for
columns that will be used for those kinds of operations (>, <, BETWEEN, etc,
or make it composite in the same order that you'll be grouping. If you do
composite, order the columns by selectivity, least selective first. This
will create a wider tree, which will result in somewhat quicker grouping.)
- Clustering on a random key is a very bad idea, because it will cause a
lot of page splits, leading to fragmentation. This will slow your data
loads. It will also give you no query benefits at all. So you'll actually
lose on this option.
- Clustering on an IDENTITY key or a DATETIME column that's
automatically set to the date the row is inserted will actually speed up
inserts as it will create a hotspot at the end of the table. So you'll
never have page splits when inserting new data. This can definitely help
speed your data load! Clustering on an IDENTITY will usually not help too
much with queries as, in my experience, most grouping and range operations
don't consider surrogates. Depending on your app, clustering on a DATETIME
as I described can help a lot, as a lot of queries will request data between
two dates, greater than a date, etc.
- Finally, clustering on the composite of your dimensions may be helpful
if you're grouping on them or requesting ranges. However, in the latter
case, remember that a composite index will only be used for searching if the
first column is part of the search criteria, so try to choose one of your
dimensions that will always be searched on (if that exists in your
warehouse).
I hope that answered your questions? Post back if you need some
clarification or further assistance.|||> It doesn't take up any extra disc space,
That's not true. The clustered index uses the datapages as the leaves, so
that is disk space you use already anyway, but the nodes of the index still
take up extra space on disk. That doesn't mean btw that it is not a good
idea to have a clustered index on every table. In almost all cases the
performance improvement that a clustered index provides more than offsets
the extra disk space used.
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23rDxElvZEHA.2388@.TK2MSFTNGP11.phx.gbl...
> "Steven Clark" <stevec@.clarkdev.com> wrote in message
> news:d7740507.0407100710.2b1644b5@.posting.google.com...
> In my opinion, there is never a reason NOT to have one; it's a
freebie,
> basically, unlike clustered indexes. It doesn't take up any extra disc
> space, and you might as well order the data on the disc somehow, rather
than
> letting the server take care of it... So I always make sure that every
table
> has one.
>
> Some tips for clustered indexes:
> - They assist with range queries and grouping. So try to use them for
> columns that will be used for those kinds of operations (>, <, BETWEEN,
etc,
> or make it composite in the same order that you'll be grouping. If you do
> composite, order the columns by selectivity, least selective first. This
> will create a wider tree, which will result in somewhat quicker grouping.)
> - Clustering on a random key is a very bad idea, because it will cause
a
> lot of page splits, leading to fragmentation. This will slow your data
> loads. It will also give you no query benefits at all. So you'll
actually
> lose on this option.
> - Clustering on an IDENTITY key or a DATETIME column that's
> automatically set to the date the row is inserted will actually speed up
> inserts as it will create a hotspot at the end of the table. So you'll
> never have page splits when inserting new data. This can definitely help
> speed your data load! Clustering on an IDENTITY will usually not help too
> much with queries as, in my experience, most grouping and range operations
> don't consider surrogates. Depending on your app, clustering on a
DATETIME
> as I described can help a lot, as a lot of queries will request data
between
> two dates, greater than a date, etc.
> - Finally, clustering on the composite of your dimensions may be
helpful
> if you're grouping on them or requesting ranges. However, in the latter
> case, remember that a composite index will only be used for searching if
the
> first column is part of the search criteria, so try to choose one of your
> dimensions that will always be searched on (if that exists in your
> warehouse).
> I hope that answered your questions? Post back if you need some
> clarification or further assistance.
>
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23cIz3GDaEHA.2544@.TK2MSFTNGP10.phx.gbl...
> That's not true. The clustered index uses the datapages as the leaves, so
> that is disk space you use already anyway, but the nodes of the index
still
> take up extra space on disk. That doesn't mean btw that it is not a good
> idea to have a clustered index on every table. In almost all cases the
> performance improvement that a clustered index provides more than offsets
> the extra disk space used.
Thanks for the clarification on that... I also didn't think about fill
factor, which could also create the impression of more disc space being
used.
Fact Table Optimal Design
I have a working model, but looking to optimize it. Particulary the fact table. Here is how fact table looks:
ProductKey ChargeAKey ChargeBKey ChargeCKey ChargeA$ ChargeB$ ChargeC$ TotalABC$
1 1 0 0 2$ 0 0 2$
1 2 0 0 3$ 0 0 3$
1 0 1 0 0 4$ 0 4$
1 0 0 1 0 0 2$ 2$
1 0 0 2 0 0 3$ 3$
1 0 0 3 0 0 3$ 3$
... now some explanation.. granuality of fact table is Product + ChargeType. each charge type (A, B, C) has it's own dimension table with its own unique list of attributes and they all come from different sources. The simplest loading would append each Product + ChargeX to the table. I was thinking of option b - settingup fact table as Product + ChargeType + ChargeKey + Chargeamount, but this design in my opinion is less friendly then the original because user would have to know what charges have to be filtered down first by type to get to specific key and its attributes... what I don't like about option a is that it takes a lot of space and there are fields populated with only meningless 0 vs having everything compressed into few records. e.g. above exmple could be designed to have only 3 records vs. 6 which translates in huge savings when we are talking millions of records.Would anyone have anyexperience with similiar challenge? I would be able to solve this with complex ETL procedures, but there has to be something much simplier. I am still not sure if this is design or ETL solution... any feedback greatly appreciated.
My general recommendation on dimensional design is to stay focused on your business users. What is the business process, activity, or event this table represents? How would your users describe the facts associated with this process/activity/event?
It appears you have incorporated a dimension (charge type) with three values into your model. If each charge type represents a different event/process/activity, then three fact tables are in order. If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension. (What happens if a fourth charge type is introduced?)
B.
|||Thank You. That's right I always keep end user in mind because it is for him that data is being presented... and you always think the performance and simplicity. You gave me the answer: "What happens if a fourth charge type is intorduced?".. I will be in a lot of work, if not trouble, to integrate everything into one fact table.. Thanks.
fact table design question
SSAS 2005 - I have the following 3 tables:
T1: dimA_id, dimB_id, dimC_id, prod_id, dollar_amt_1
T2: dimA_id, dimB_id, dimC_id, cat_id, dollar_amt_2
T3: cat_id, prod_id.
cat_id and prod_id has a parent to child relationship.
In this case, I have to build two fact tables, right? There is no way to combine T1 and T2 into one fact table because the dollar_amt 1 and 2 are at a different level of prod_id and cat_id. I just wanted to make sure that I am doing the right thing. It seems that there are repeated data (dimA_id, dimB_id, dimC_id) in both tables.
Thanks.
But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.|||Good point. I will give it a try. Would this be a better appoach than the two fact-table one?|||Should be more straightforward, at least - but large parent-child dimensions can cause performance problems. If this dimension is small, it may not be a concern.|||The cat table has more than 1/2 million records and the prod table is 2-3 times bigger than the cat table. Is this considered large?|||According to the AS 2005 Performance Guide, that is large, but you could try it and check:
Parent-child hierarchies
Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.
If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels.
fact table design question
I have created a factSales table with dimDate, dimCustomer, dimProduct, dimSalesPerson tables. The dimensions are all joined with surrogate integer identity PK fields which serve as the composite key in the fact table.
It is possible for the same customer to place multiple orders for the same product from the same sales person on the same date. When this happens it seems to me that only the last order will be stored in the fact table. I want to have a row for each order. How would one design a fact table to accomplish this. The only truly unique piece of data from the OLTP is the sales order number.
factSales:
PK DateKey, int
PK CustomerKey, int
PK ProductKey, int
PK SalesPersonKey, int
Amount
UnitCost
Weight
ShippingCost
dimDate
PK DateKey, int, identity
SalesDate
dimCustomer
PK CustomerKey, int, identity
CustomerName
Address
dimProduct
PK ProductKey, int, identity
Product#
ProductName
DimSalesPerson
PK SalesPersonKey, int, idnetity
SalesPersonName
Dept
StartDate
EndDate
Hi,
You'll need to stick another key on the fact to make each row unique. Even if it is something simple like a count
e.g.
FK DateKey, int
FK CustomerKey, int
FK ProductKey, int
FK SalesPersonKey, int
FK OrderNumberOfTheDay, int
measures ...
If possible a time stamp might be another way, I am assuming though that the different orders happen at different times of the day. But basically another key in your ETL process will fix that problem.
Hope that helps,
Matt
|||Thanks Matt. I was going to just add the sales order # as it is unique but I didn't want to violate and conventions that might cause problems down the line.
Thanks again!
|||Hi John
Matt is right in that you should simply bring through the Sales Order Number then your DSV design will take care of the aggregation
Alternatively, unless you need to analyse the data by the Sales Order Number, (which some might argue you should use your OLTP for) you could aggregate the facts in your fact table ETL load process. This can increase performance both in processing the cube and queries if you have a lot of data
If you want to use any drillthrough functionality then keep the Sales Order Number in.
HTH
Tim
|||Thanks Tim.
I had also considered that possibility. My actual OLTP/OLAP is more complex than I showed (I tried to keep it simple for this example). I would have to join several dozen records in order to aggregate the data and I may have a sales order record which does not yet have a ship date which will be the key date slicer. I can load my fact table with a pointer to a 0 date key or just skip SO records without a ship date (my next task to figure out); it would be very complex to aggregate them. Plus I was already storing the SO# in my fact table on the likely probability the users will want to drill back to the original table.
Fact Table Design Question
We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.
You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.sqlmonster.com
|||Hi,
It’s really depending how often you will be analyzing invoices and payments
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or would
> I separate them into two tables? If I do two tables, can I include two fact
> tables in an OLAP cube?
> Thanks in advance.
>
>
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.
You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.sqlmonster.com
|||Hi,
It’s really depending how often you will be analyzing invoices and payments
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or would
> I separate them into two tables? If I do two tables, can I include two fact
> tables in an OLAP cube?
> Thanks in advance.
>
>
Fact Table Design Question
We're putting together our data warehouse, and I had a questions regarding
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
design of fact tables for our situation. We have invoices and payments to
those invoices...would I include all information in one fact table, or would
I separate them into two tables? If I do two tables, can I include two fact
tables in an OLAP cube?
Thanks in advance.You can use a view...
it's even better to use a partitioned view...
Message posted via http://www.droptable.com|||Hi,
It’s really depending how often you will be analyzing invoices and payment
s
together.
Sometimes - you can built two cubes and join them into virtual cube.
Very often - you should include all information in your fact table design.
Tomasz B.
"T." wrote:
> We're putting together our data warehouse, and I had a questions regarding
> design of fact tables for our situation. We have invoices and payments to
> those invoices...would I include all information in one fact table, or wou
ld
> I separate them into two tables? If I do two tables, can I include two fa
ct
> tables in an OLAP cube?
> Thanks in advance.
>
>
Fact table design problem
Hello All,
I have a row for each employee, each month in my fact table.
I need to include bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. In some cases they have gotten the same bonus twice in the same month. For example:
EmployeeID DateKey Bonus Bonus Type
1 20060105 $5,000 Team
1 20060107 $500 GoodJob
1 20060110 $250 Incentive
How can I have multiple bonuses and their amounts in the fact table if I only have a single row for each employee?
Thank you for the help, this has been driving me crazy.
-Gumbatman
p.s. I've tried stuff with many-to-many dimensions and also Factless Fact tables to get this, but it is just not working for me. I read Mark Russo's very good article about many-to-many dimensions but I am not 100% that it applies to what I am doing.
Hello! I am not sure that I understand the problem fully but have you tried to add a bonus type dimension.
With a bonus type key, an employee key and a date key you should be able to do the analysis you are describing.
All these relations would be one-to-many from the dimension tables.
HTH
Thomas Ivarsson
|||Thomas,
Thank you for responding.
Are you saying keep the Bonus amounts (the facts) in the main Fact table or have a secondary fact table?
If it is in the main fact table, how do I connect the Bonus dimension to the fact table when there are multiple bonuses inside a month (which is the grain of that fact table)?
I tried using a Bonus fact table that connects to the main fact table via a DateKey and EmployeeKey. Then I connect the Bonus Dimension to the Bonus fact table. In order to get the Bonus Dimension to be "seen" by the main fact table, I am using a many-to-many connection.
The problem I am having there is I have a Geography Dimension connected only to the main fact table, not the Bonus fact table. I can't seem to get the Geography Dimension to work properly. What I mean by that is that the Bonus numbers show up for everyone, regardless of Geography.
Please let me know if I am not explaining this clearly. I really appreciate the help (and I really need the help). I've gotten to the point were I am ready to hire a consultant on this issue alone.
-Gumbatman
|||Perhaps you are talking about something not previously known in your first post.
I do not think that you will need two fact tables for this problem. I am thinking about one fact table with one measure like Amount.
The dimension tables will be time, employee and bonustype. If an employee receive the same type of bonus twice on a single day you can aggregate these recordss into one, with Integration Services.
HTH
Thomas Ivarsson
|||Sorry if I didn't mention stuff clearly in my first post. There is a lot to this issue that I may have forgotten to mention.
However, I am curious about your IS suggestion. I can certainly aggregate the amount into the single row. But how do I show that a person received multiple bonuses in that month?
For example, the Bonus Dimension looks like this:
BonusID BonusType BonusAmount
1 Thank You 50
2 Applause 100
3 Encore 250
On a single row, the employe received two Thank Yous and an Applause - a total of 200. Should my Dimension table look like it does? And this prompts another question, can I do a calculation from a Dimension table?
Thank you again for your help.
-Gumbatman
|||Hello. I would move the BonusAmount to the fact table and keep BonusId and BonusType in the dimension table.
You do all calculations in the fact table.
Your single fact table will look like this
EmployeeId
BonusId
BonusDate
BonusAmount
HTH
Thomas Ivarsson
|||Thomas,
Is the Fact table you are suggesting here, a secondary one to the "main" fact table? If so, what is the best way to link the two fact tables, since the data needs to be summarized by month?
If you are suggesting putting the bonus data in the main table, do I just repeat some of the other Dimensions? For example, here is what I am thinking would be my main fact table with bonus information for a single employee in one month:
Since it is additive by month, I have to zero-out the Salary for the additional rows.(At least that is my guess.)
Thank you.
-Gumbatman
|||No! Only one fact table.
I see that you have a salary measure as well and that it will repeat for all bonus records and that the granularity is actually on a month level?
If this is the case I suggest that you make each bonus type a separate measure and aggregate them to the same level as the salary(month?)
So if the same bonus appears several times during a month it will be aggregated into one single bonus type measure.
HTH
Regards
Thomas Ivarsson
|||OK. I think I got it now.
But if I add each bonus as a separate measure in the fact table, what happens if a new bonus type appears?
Thank you.
-Gumbatman
|||It will be a new measure.
HTH
Thomas Ivarsson
|||Sorry, I should have been more specific...
What is the best way to handle a new measure coming into the Fact table, through Integration Services? I guess I check and then append new columns when a new bonus measure is created?
Also, if I do it by measures, what happens to the Bonus Dimension? I will need to slice and dice by the Bonus types. I am unclear about how I can still show who got a what bonus type, especially if there was more than one in that monthly row.
Thank you again (and for your patience).
-Gumbatman
Fact Table Design for Hours Worked
Hi,
I am trying to design a cube with Date, Time and Employee dimension
which can answer following questions:
- how many employees were working at 10am on 15 June 2007? I
- how many man hours were pais between 10am -11am on 15 June 2007. If
somebody has punched in at 10:30 then he should be counted and .5 man
hours.
One solution i can think of is every minute of everyhour for every
employee entered as a row in fact table. In this case if a employees
works for 8 hours that will create 8*60= 480 records in fact table. So
this may not be a good solution.
Ideally it would be better if we can enter one record in fact table
with start time and stop time. But, how can we put corrective
transaction if we need to cancel or change the original transactions.
Any help will be appreciated.
Thanks
Maheshyou can use 2 hour dimension:
Start Time and End Time
to answer:
> - how many employees were working at 10am on 15 June 2007? I
you select the day and all the start times up to 10am and all the end times
after 10am and you have the result.
for the second one, you have to create some formula.
maybe you can create a new measure group which contains only the start time
/ end time , and, for each combination, the duration in minute of the
selected period (ie you select 10h30 and 11h45, the measure returns
75minutes) (call this measure NoMinutesInPeriod)
now, in the cube, you have to create a formula which apply a ratio if the
start time or the end time is between the selected period of time.
measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
the datediff formula will be more complicated then a simple datediff.
but maybe this idea is a good starting point for you.
or anybody else has a better solution... please share your ideas :-)
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
> Hi,
> I am trying to design a cube with Date, Time and Employee dimension
> which can answer following questions:
> - how many employees were working at 10am on 15 June 2007? I
> - how many man hours were pais between 10am -11am on 15 June 2007. If
> somebody has punched in at 10:30 then he should be counted and .5 man
> hours.
> One solution i can think of is every minute of everyhour for every
> employee entered as a row in fact table. In this case if a employees
> works for 8 hours that will create 8*60= 480 records in fact table. So
> this may not be a good solution.
> Ideally it would be better if we can enter one record in fact table
> with start time and stop time. But, how can we put corrective
> transaction if we need to cancel or change the original transactions.
> Any help will be appreciated.
> Thanks
> Mahesh
>|||Thanks Jeje. Your solution seems to be the best solution. The only
problem with this is the difficulty to add corrective transactions.
For example, if hours worked is entered as 8am - 5pm on June 5 for an
employee. On June 25 if it is changed to be 10am - 5pm, how can we
enter a new corrective transaction on June 25 without changing the
original transaction. We do not want to change the original
transaction to use Incrementa Processing on cubes.
Any response will be very much appreciated.
Mahesh
On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
> you can use 2 hour dimension:
> Start Time and End Time
> to answer:> - how many employees were working at 10am on 15 June 2007? I
> you select the day and all the start times up to 10am and all the end time
s
> after 10am and you have the result.
> for the second one, you have to create some formula.
> maybe you can create a new measure group which contains only the start tim
e
> / end time , and, for each combination, the duration in minute of the
> selected period (ie you select 10h30 and 11h45, the measure returns
> 75minutes) (call this measure NoMinutesInPeriod)
> now, in the cube, you have to create a formula which apply a ratio if the
> start time or the end time is between the selected period of time.
> measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
> the datediff formula will be more complicated then a simple datediff.
> but maybe this idea is a good starting point for you.
> or anybody else has a better solution... please share your ideas :-)
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||if you want to "cancel" a transaction already loaded in a cube.
you have to add another "transaction" with "negative values"
for example:
initially you load the cube using this:
June 25 / 8am - 5pm / +1 (+1 work)
you want to "cancel" this transaction, and replace by a new one:
June 25 / 8am - 5pm / -1 (-1 work)
June 25 / 10am - 5pm / +1 (+1 work)
so the total in the cube is:
June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
June 25 / 10am - 5pm / +1
but using this approach you can't use the "non empty" because the cell is
not empty, the cell contains a total of 0.
Also remember to do full processes at a regular basis because an incremental
loading reduce the cube performance.
good luck!
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
> Thanks Jeje. Your solution seems to be the best solution. The only
> problem with this is the difficulty to add corrective transactions.
> For example, if hours worked is entered as 8am - 5pm on June 5 for an
> employee. On June 25 if it is changed to be 10am - 5pm, how can we
> enter a new corrective transaction on June 25 without changing the
> original transaction. We do not want to change the original
> transaction to use Incrementa Processing on cubes.
> Any response will be very much appreciated.
> Mahesh
>
> On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
>|||Thanks you very much for your kind response, Jeje.
Mahesh
On Jun 25, 9:27 am, "Jeje" <willg...@.hotmail.com> wrote:
> if you want to "cancel" a transaction already loaded in a cube.
> you have to add another "transaction" with "negative values"
> for example:
> initially you load the cube using this:
> June 25 / 8am - 5pm / +1 (+1 work)
> you want to "cancel" this transaction, and replace by a new one:
> June 25 / 8am - 5pm / -1 (-1 work)
> June 25 / 10am - 5pm / +1 (+1 work)
> so the total in the cube is:
> June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
> June 25 / 10am - 5pm / +1
> but using this approach you can't use the "non empty" because the cell is
> not empty, the cell contains a total of 0.
> Also remember to do full processes at a regular basis because an increment
al
> loading reduce the cube performance.
> good luck!
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I am trying to design a cube with Date, Time and Employee dimension
which can answer following questions:
- how many employees were working at 10am on 15 June 2007? I
- how many man hours were pais between 10am -11am on 15 June 2007. If
somebody has punched in at 10:30 then he should be counted and .5 man
hours.
One solution i can think of is every minute of everyhour for every
employee entered as a row in fact table. In this case if a employees
works for 8 hours that will create 8*60= 480 records in fact table. So
this may not be a good solution.
Ideally it would be better if we can enter one record in fact table
with start time and stop time. But, how can we put corrective
transaction if we need to cancel or change the original transactions.
Any help will be appreciated.
Thanks
Maheshyou can use 2 hour dimension:
Start Time and End Time
to answer:
> - how many employees were working at 10am on 15 June 2007? I
you select the day and all the start times up to 10am and all the end times
after 10am and you have the result.
for the second one, you have to create some formula.
maybe you can create a new measure group which contains only the start time
/ end time , and, for each combination, the duration in minute of the
selected period (ie you select 10h30 and 11h45, the measure returns
75minutes) (call this measure NoMinutesInPeriod)
now, in the cube, you have to create a formula which apply a ratio if the
start time or the end time is between the selected period of time.
measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
the datediff formula will be more complicated then a simple datediff.
but maybe this idea is a good starting point for you.
or anybody else has a better solution... please share your ideas :-)
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
> Hi,
> I am trying to design a cube with Date, Time and Employee dimension
> which can answer following questions:
> - how many employees were working at 10am on 15 June 2007? I
> - how many man hours were pais between 10am -11am on 15 June 2007. If
> somebody has punched in at 10:30 then he should be counted and .5 man
> hours.
> One solution i can think of is every minute of everyhour for every
> employee entered as a row in fact table. In this case if a employees
> works for 8 hours that will create 8*60= 480 records in fact table. So
> this may not be a good solution.
> Ideally it would be better if we can enter one record in fact table
> with start time and stop time. But, how can we put corrective
> transaction if we need to cancel or change the original transactions.
> Any help will be appreciated.
> Thanks
> Mahesh
>|||Thanks Jeje. Your solution seems to be the best solution. The only
problem with this is the difficulty to add corrective transactions.
For example, if hours worked is entered as 8am - 5pm on June 5 for an
employee. On June 25 if it is changed to be 10am - 5pm, how can we
enter a new corrective transaction on June 25 without changing the
original transaction. We do not want to change the original
transaction to use Incrementa Processing on cubes.
Any response will be very much appreciated.
Mahesh
On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
> you can use 2 hour dimension:
> Start Time and End Time
> to answer:> - how many employees were working at 10am on 15 June 2007? I
> you select the day and all the start times up to 10am and all the end time
s
> after 10am and you have the result.
> for the second one, you have to create some formula.
> maybe you can create a new measure group which contains only the start tim
e
> / end time , and, for each combination, the duration in minute of the
> selected period (ie you select 10h30 and 11h45, the measure returns
> 75minutes) (call this measure NoMinutesInPeriod)
> now, in the cube, you have to create a formula which apply a ratio if the
> start time or the end time is between the selected period of time.
> measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
> the datediff formula will be more complicated then a simple datediff.
> but maybe this idea is a good starting point for you.
> or anybody else has a better solution... please share your ideas :-)
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||if you want to "cancel" a transaction already loaded in a cube.
you have to add another "transaction" with "negative values"
for example:
initially you load the cube using this:
June 25 / 8am - 5pm / +1 (+1 work)
you want to "cancel" this transaction, and replace by a new one:
June 25 / 8am - 5pm / -1 (-1 work)
June 25 / 10am - 5pm / +1 (+1 work)
so the total in the cube is:
June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
June 25 / 10am - 5pm / +1
but using this approach you can't use the "non empty" because the cell is
not empty, the cell contains a total of 0.
Also remember to do full processes at a regular basis because an incremental
loading reduce the cube performance.
good luck!
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
> Thanks Jeje. Your solution seems to be the best solution. The only
> problem with this is the difficulty to add corrective transactions.
> For example, if hours worked is entered as 8am - 5pm on June 5 for an
> employee. On June 25 if it is changed to be 10am - 5pm, how can we
> enter a new corrective transaction on June 25 without changing the
> original transaction. We do not want to change the original
> transaction to use Incrementa Processing on cubes.
> Any response will be very much appreciated.
> Mahesh
>
> On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
>|||Thanks you very much for your kind response, Jeje.
Mahesh
On Jun 25, 9:27 am, "Jeje" <willg...@.hotmail.com> wrote:
> if you want to "cancel" a transaction already loaded in a cube.
> you have to add another "transaction" with "negative values"
> for example:
> initially you load the cube using this:
> June 25 / 8am - 5pm / +1 (+1 work)
> you want to "cancel" this transaction, and replace by a new one:
> June 25 / 8am - 5pm / -1 (-1 work)
> June 25 / 10am - 5pm / +1 (+1 work)
> so the total in the cube is:
> June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
> June 25 / 10am - 5pm / +1
> but using this approach you can't use the "non empty" because the cell is
> not empty, the cell contains a total of 0.
> Also remember to do full processes at a regular basis because an increment
al
> loading reduce the cube performance.
> good luck!
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Fact Table Design - Help
Hi There,
I have to build a Fact table, and I have this situation.
My OLTP database, keeps always the last image of the
record.
Table
Incident(IncidentId, CreationTimme, StatusId, PriorityId,
rankId, Description, ..)
My Log Database, keeps a table per field and tracks all
the changes.
Incident_CreationTime(IncidentId, CreationTime, Timestamp)
Incident_StatusId(IncidentId, StatusId, Timestamp)
Incident_PriorityId(IncidentId, PriorityId, Timestamp)
Incident_RankId(IncidentId, RankId, Timestamp)
You got the Idea.
I have to be based on the Log database to create my Fact
Table of Incident. So what's the best way to create the
fact table in this case ? What about the dimension too ?
ThanksWhat are you trying to measure and how are you measuring it "by" ?
These are the inputs to your design, not the existing schema!
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
Statusid, PriorityId, per RankId.
it "by" ?
schema!
look something like:
DateID
StatusID
PriorityID
RankID
IncidentCount
And you would have four dimension tables, Date, Status, Priority, and Rank
each having the descriptive elements of those dimensions, like Date,
StatusDescription, PriorityNumber, RankNumber, or whatever is meaninful for
you for those dimensions.
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...[QUOTE]
> I'm trying to measure the number of incidents per
> Statusid, PriorityId, per RankId.
>
> it "by" ?
> schema!
> PriorityId,
> Timestamp)|||Hi Kevin,
Thanks for answering me back. I still have another
question for you.
It's not my first time that I develop a datawarehouse. My
only worry here, is the fact that these tables are related
together as many-to-many relationship in the OLTP system.
So If I do it like you suggested,
(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
IncidentCount)
I could end up for each Incident something like this in my
FACT table:
1, 20031208, 1, 0, 0, 1
1, 20031208, 0, 1, 1, 1
1, 20031208, 4, 0, 0, 1
1, 20031208, 5, 0, 0, 1
Because the StatusId, PriorityId, RankId there is a
possibility that they change so many times during the life
cycle of an incident.
So what I thought to do, is to create a fact table for
each Fact_IncidentStatus, Fact_IncidentPriority,
Fact_IncidentRank each of these fact tables is related to
its own dimension. and then all those fact tables are
related to FACT_Incident which contains only unique
Incident Numbers.
Do you think this is a good design?
Thanks for your time
fact table would
Priority, and Rank
like Date,
whatever is meaninful for
It seems to me that you need to have a separate dimension
for the incident numbers.
You need:
DIM_Date
DIM_Status
DIM_Priority
DIM_Rank
DIM_Incident
And:
FACT_IncidentCount
With a surrogate key from each of your dimensions and a
count field that would contain just the number "1". As
the cube aggregated on each dimension, it would add the
count field up.
Hope this helps,
Asa Monsey
related
my
life
I have to build a Fact table, and I have this situation.
My OLTP database, keeps always the last image of the
record.
Table
Incident(IncidentId, CreationTimme, StatusId, PriorityId,
rankId, Description, ..)
My Log Database, keeps a table per field and tracks all
the changes.
Incident_CreationTime(IncidentId, CreationTime, Timestamp)
Incident_StatusId(IncidentId, StatusId, Timestamp)
Incident_PriorityId(IncidentId, PriorityId, Timestamp)
Incident_RankId(IncidentId, RankId, Timestamp)
You got the Idea.
I have to be based on the Log database to create my Fact
Table of Incident. So what's the best way to create the
fact table in this case ? What about the dimension too ?
ThanksWhat are you trying to measure and how are you measuring it "by" ?
These are the inputs to your design, not the existing schema!
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
quote:|||I'm trying to measure the number of incidents per
> Hi There,
> I have to build a Fact table, and I have this situation.
> My OLTP database, keeps always the last image of the
> record.
> Table
> Incident(IncidentId, CreationTimme, StatusId, PriorityId,
> rankId, Description, ..)
> My Log Database, keeps a table per field and tracks all
> the changes.
> Incident_CreationTime(IncidentId, CreationTime, Timestamp)
> Incident_StatusId(IncidentId, StatusId, Timestamp)
> Incident_PriorityId(IncidentId, PriorityId, Timestamp)
> Incident_RankId(IncidentId, RankId, Timestamp)
> You got the Idea.
> I have to be based on the Log database to create my Fact
> Table of Incident. So what's the best way to create the
> fact table in this case ? What about the dimension too ?
> Thanks
>
Statusid, PriorityId, per RankId.
quote:
>--Original Message--
>What are you trying to measure and how are you measuring
it "by" ?
quote:
>These are the inputs to your design, not the existing
schema!
quote:|||You probably forgot about the date dimension. Then your fact table would
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:0cd301c3bb40$ec0fb7e0$a401280a@.phx.gbl...
PriorityId,[QUOTE]
Timestamp)[QUOTE]
>
>.
>
look something like:
DateID
StatusID
PriorityID
RankID
IncidentCount
And you would have four dimension tables, Date, Status, Priority, and Rank
each having the descriptive elements of those dimensions, like Date,
StatusDescription, PriorityNumber, RankNumber, or whatever is meaninful for
you for those dimensions.
"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...[QUOTE]
> I'm trying to measure the number of incidents per
> Statusid, PriorityId, per RankId.
>
> it "by" ?
> schema!
> PriorityId,
> Timestamp)|||Hi Kevin,
Thanks for answering me back. I still have another
question for you.
It's not my first time that I develop a datawarehouse. My
only worry here, is the fact that these tables are related
together as many-to-many relationship in the OLTP system.
So If I do it like you suggested,
(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
IncidentCount)
I could end up for each Incident something like this in my
FACT table:
1, 20031208, 1, 0, 0, 1
1, 20031208, 0, 1, 1, 1
1, 20031208, 4, 0, 0, 1
1, 20031208, 5, 0, 0, 1
Because the StatusId, PriorityId, RankId there is a
possibility that they change so many times during the life
cycle of an incident.
So what I thought to do, is to create a fact table for
each Fact_IncidentStatus, Fact_IncidentPriority,
Fact_IncidentRank each of these fact tables is related to
its own dimension. and then all those fact tables are
related to FACT_Incident which contains only unique
Incident Numbers.
Do you think this is a good design?
Thanks for your time
quote:
>--Original Message--
>You probably forgot about the date dimension. Then your
fact table would
quote:
>look something like:
>DateID
>StatusID
>PriorityID
>RankID
>IncidentCount
>And you would have four dimension tables, Date, Status,
Priority, and Rank
quote:
>each having the descriptive elements of those dimensions,
like Date,
quote:
>StatusDescription, PriorityNumber, RankNumber, or
whatever is meaninful for
quote:|||Elie,
>you for those dimensions.
>
>"Elie Khammar" <ekhammar@.positron.qc.ca> wrote in message
>news:067401c3bb4f$b6e6dee0$a001280a@.phx.gbl...
measuring[QUOTE]
message[QUOTE]
situation.[QUOTE]
all[QUOTE]
Timestamp)[QUOTE]
Fact[QUOTE]
the[QUOTE]
too ?[QUOTE]
>
>.
>
It seems to me that you need to have a separate dimension
for the incident numbers.
You need:
DIM_Date
DIM_Status
DIM_Priority
DIM_Rank
DIM_Incident
And:
FACT_IncidentCount
With a surrogate key from each of your dimensions and a
count field that would contain just the number "1". As
the cube aggregated on each dimension, it would add the
count field up.
Hope this helps,
Asa Monsey
quote:
>--Original Message--
>Hi Kevin,
>Thanks for answering me back. I still have another
>question for you.
>It's not my first time that I develop a datawarehouse. My
>only worry here, is the fact that these tables are
related
quote:
>together as many-to-many relationship in the OLTP system.
>So If I do it like you suggested,
>(IncidnetNumber, DateId, StatusId, PriorityId, RankId,
>IncidentCount)
>I could end up for each Incident something like this in
my
quote:
>FACT table:
>1, 20031208, 1, 0, 0, 1
>1, 20031208, 0, 1, 1, 1
>1, 20031208, 4, 0, 0, 1
>1, 20031208, 5, 0, 0, 1
>Because the StatusId, PriorityId, RankId there is a
>possibility that they change so many times during the
life
quote:
>cycle of an incident.
>So what I thought to do, is to create a fact table for
>each Fact_IncidentStatus, Fact_IncidentPriority,
>Fact_IncidentRank each of these fact tables is related to
>its own dimension. and then all those fact tables are
>related to FACT_Incident which contains only unique
>Incident Numbers.
>Do you think this is a good design?
>Thanks for your time
>
>
>fact table would
>Priority, and Rank
dimensions,[QUOTE]
>like Date,
>whatever is meaninful for
>measuring
>message
>situation.
>all
>Timestamp)
>Fact
>the
>too ?
>.
>
Fact table design
Hello.
I am working on an educational data warehouse. I have a semi-additive fact table that deals with student grades and averages. It's as such:
School, Acad Year, Student, Course, Term, Week, Week Grade, Term Average
where Week Grade and Term Average are the measures.
The term average is an average calculated based on a specific formula on top of week grade. It's supposed to appear when the user is standing on All weeks member of Week's dimension.
I have an average calculation problem in the term average...say i want to compare the student term averages.
i don't know if its correct to write an mdx formula to get it or to create a separate table for the Term Average containing the granularity by term rather than by week.
Thanks in advance for your advice
Christina
I think your design has some problems. The biggest one is that you are mixing grain levels in the fact table (terms and weeks) and both are dates. My advice is to first focus on creating the fact table at the most detailed grain level, which I think is probably days (complete day). So
FactGrades: SchoolKey, DateKey, StudentKey, CourseKey, Grade.
The date key should have some week attributes so you know to which week the current day is referring to, and the same with the term. Your Date dimension should look something like this:
DimDate: DateKey, FullDate, CalendarYear, CalendarSemester, CalendarMonth, CalendarDayInMonth, TermYear, TermSemester, TermMonth?, TermWeek, TermDayNumber, etc, etc.
From this you could create an aggregate fact table for the term averages or better yet, derive the term average with an MDX expression when you create your Olap Cube in Analysis Services.
|||Ok let's say i fix the date issue. i still have a concern. i want to precalculate the term average because the way to get it is based on many factors and i am trying to avoid its complexity. i prefer to add it to this fact table or to create a fact table just for it.
but this measure would be considered at a different granularity..is it correct to add it to this fact table or should i create a fact table just for it....i did the second option and want to make sure its correct.
and honestly that's why i separated the week from the term because i assumed that if i put it in a different fact table, i would need to use only the term key, and not the week.
in AS,i am using the scope function in the Calcuations to change the aggregated value of the Grade measure to TermAverage when the currentmember is at the Term level.
That's one of my concerns.
I have another issue resulting from this one... i would like to share it with you in case you could give me some advice.
1-assume i'm standing at class section level, which is a level in the student dimension (school, class, section, student). for a given course i want to compare the average between sections of the same class to see which section is performing better during this week.
2-i want to do the same but for a term instead of week
in case 1, the measure used should be the Grade.
in case 2, it should be the TermAverage.
when i added these measures to the cube, i set the aggregation property to average..however the avg is semi-additive and will only function on time dimension. i therefore created 2 calculated measures, one for the grade and another for the TermAverage as such:
Grade Average = Grade/count of records in the fact table
TermAverage Average = TermAverage/count of records in the other fact table containing only the TermAverage.
and then modified the scope calculation to put these measures in the aggergation instead of the original Grade and TermAverage because the other will give me Sum while standing at Class or Class Section while what i want is the average.
Am i doing right like that by creating both fact tables, doing the measures like that and then modifying the scope to do this? i would like to mention that the aggregation now is taking much more time to give results because of the calculated measures...
Thanks again for your help
Christina
|||I really think you should try to compute the term average with the information from the daily grades using MDX. How complex can it be? Keep it as simple as possible. If you still want to have it separately, you should create a different fact table for the term average. The only thing is that this fact table should no longer have a dateKey but a TermKey or put it as a "measure" in the fact table so you don't have to maintain a term dimension. This value is the same as the one defined for that term in the Date dimension. This is for the Data Warehouse. E.g.
DimDate.Term: 200509
FactTermAverage: SchoolKey, StudentKey, CourseKey, Term (200509), Average (19.45)
In this way you can link the term with your date dimension in Analysis Services. Just make sure to set the attribute hierarchical relationships properly for the Date dimension, and the scope for each measure (Checking the term average per calendar day, calendar month, day, etc. doesn't make sense so the term average should be displayed as "NA" in these cases).
In regards to your second question, you should have a single cube in AS for a DW (best practice). This means you would have a count measure in the cube for each fact table. However you should try to design a single calculated measure called "Section Average" which is calculated depending on where the user is in the cube:
Case
When user is doing week comparisons
Grade/count of records in the grade fact table
When user is doing term comparisons
Term Average/count of records in the term fact table
Else
"NA"
End
Thank you very much for your insight.
First i never knew i could link the Term to the Weekly dimension like that.. it saved me a lot.
I will also try to do the measure. The problem is that i'm not that good at MDX and i try to avoid using it..
You wondered how complex the MDX computation could be..well to me it's complex..i will explain it to you maybe you could help me again at this one..
in the time dimension i said i have Academic Year,Term and Week.
the term average=avg(Weeks 1-8)*0.60+avg(week 9)*0.40
The acad year average = Term 1*0.25 + Term 2 *0.25 + Term 3(week1-8)*0.20 + Term 3 (week9)*0.30
Thanks much
Christina
|||hi .. i am also working on university data warehouse but i am only working on two subjects i.e. Student record and Admission ..if some one can help me in designing of the model ..
thanx
Zeeshan Ali
Fact table design
Hello.
I am working on an educational data warehouse. I have a semi-additive fact table that deals with student grades and averages. It's as such:
School, Acad Year, Student, Course, Term, Week, Week Grade, Term Average
where Week Grade and Term Average are the measures.
The term average is an average calculated based on a specific formula on top of week grade. It's supposed to appear when the user is standing on All weeks member of Week's dimension.
I have an average calculation problem in the term average...say i want to compare the student term averages.
i don't know if its correct to write an mdx formula to get it or to create a separate table for the Term Average containing the granularity by term rather than by week.
Thanks in advance for your advice
Christina
I think your design has some problems. The biggest one is that you are mixing grain levels in the fact table (terms and weeks) and both are dates. My advice is to first focus on creating the fact table at the most detailed grain level, which I think is probably days (complete day). So
FactGrades: SchoolKey, DateKey, StudentKey, CourseKey, Grade.
The date key should have some week attributes so you know to which week the current day is referring to, and the same with the term. Your Date dimension should look something like this:
DimDate: DateKey, FullDate, CalendarYear, CalendarSemester, CalendarMonth, CalendarDayInMonth, TermYear, TermSemester, TermMonth?, TermWeek, TermDayNumber, etc, etc.
From this you could create an aggregate fact table for the term averages or better yet, derive the term average with an MDX expression when you create your Olap Cube in Analysis Services.
|||Ok let's say i fix the date issue. i still have a concern. i want to precalculate the term average because the way to get it is based on many factors and i am trying to avoid its complexity. i prefer to add it to this fact table or to create a fact table just for it.
but this measure would be considered at a different granularity..is it correct to add it to this fact table or should i create a fact table just for it....i did the second option and want to make sure its correct.
and honestly that's why i separated the week from the term because i assumed that if i put it in a different fact table, i would need to use only the term key, and not the week.
in AS,i am using the scope function in the Calcuations to change the aggregated value of the Grade measure to TermAverage when the currentmember is at the Term level.
That's one of my concerns.
I have another issue resulting from this one... i would like to share it with you in case you could give me some advice.
1-assume i'm standing at class section level, which is a level in the student dimension (school, class, section, student). for a given course i want to compare the average between sections of the same class to see which section is performing better during this week.
2-i want to do the same but for a term instead of week
in case 1, the measure used should be the Grade.
in case 2, it should be the TermAverage.
when i added these measures to the cube, i set the aggregation property to average..however the avg is semi-additive and will only function on time dimension. i therefore created 2 calculated measures, one for the grade and another for the TermAverage as such:
Grade Average = Grade/count of records in the fact table
TermAverage Average = TermAverage/count of records in the other fact table containing only the TermAverage.
and then modified the scope calculation to put these measures in the aggergation instead of the original Grade and TermAverage because the other will give me Sum while standing at Class or Class Section while what i want is the average.
Am i doing right like that by creating both fact tables, doing the measures like that and then modifying the scope to do this? i would like to mention that the aggregation now is taking much more time to give results because of the calculated measures...
Thanks again for your help
Christina
|||I really think you should try to compute the term average with the information from the daily grades using MDX. How complex can it be? Keep it as simple as possible. If you still want to have it separately, you should create a different fact table for the term average. The only thing is that this fact table should no longer have a dateKey but a TermKey or put it as a "measure" in the fact table so you don't have to maintain a term dimension. This value is the same as the one defined for that term in the Date dimension. This is for the Data Warehouse. E.g.
DimDate.Term: 200509
FactTermAverage: SchoolKey, StudentKey, CourseKey, Term (200509), Average (19.45)
In this way you can link the term with your date dimension in Analysis Services. Just make sure to set the attribute hierarchical relationships properly for the Date dimension, and the scope for each measure (Checking the term average per calendar day, calendar month, day, etc. doesn't make sense so the term average should be displayed as "NA" in these cases).
In regards to your second question, you should have a single cube in AS for a DW (best practice). This means you would have a count measure in the cube for each fact table. However you should try to design a single calculated measure called "Section Average" which is calculated depending on where the user is in the cube:
Case
When user is doing week comparisons
Grade/count of records in the grade fact table
When user is doing term comparisons
Term Average/count of records in the term fact table
Else
"NA"
End
Thank you very much for your insight.
First i never knew i could link the Term to the Weekly dimension like that.. it saved me a lot.
I will also try to do the measure. The problem is that i'm not that good at MDX and i try to avoid using it..
You wondered how complex the MDX computation could be..well to me it's complex..i will explain it to you maybe you could help me again at this one..
in the time dimension i said i have Academic Year,Term and Week.
the term average=avg(Weeks 1-8)*0.60+avg(week 9)*0.40
The acad year average = Term 1*0.25 + Term 2 *0.25 + Term 3(week1-8)*0.20 + Term 3 (week9)*0.30
Thanks much
Christina
|||hi .. i am also working on university data warehouse but i am only working on two subjects i.e. Student record and Admission ..if some one can help me in designing of the model ..
thanx
Zeeshan Ali
Fact table design
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
If I understand correctly you basically are talking about which users can have access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and permissions on the cube. I myself am in the learning process - as my name implies ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to the newsgroup.
By copy of this mail to the experts, I would like to ask a related question: Is it a good or bad design practice to have multiple fact tables?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Actually what I am looking for is more of a best practice, is it better to have one fact table that has columns that are null or have multiple fact tables.
|||If you include all measures related to all three types of claims, then your fact table can get huge depending on how much claims activity you process, amount of history, and record width.
If you put them in different fact tables, you can still join them by your degenerate key(primary key -ex. claims number), but at least you won't be wasting space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
|||in the fact table, there is no primary key because you have "degenerated' the primary key from the source system. It has ceased to become the primary key in the fact table even though the field still exists. So, in general, a claim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a surrogate key is created. The surrogate key is a meangless number. So a customerid was a primary key in your claims system, but now it is not a PK anymore in your dimension table
. You have just a number(surrogate key) to describe your product and that is your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as what is called the degenerate key in the fact table. The surrogate key is actually the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
If I understand correctly you basically are talking about which users can have access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and permissions on the cube. I myself am in the learning process - as my name implies ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to the newsgroup.
By copy of this mail to the experts, I would like to ask a related question: Is it a good or bad design practice to have multiple fact tables?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Actually what I am looking for is more of a best practice, is it better to have one fact table that has columns that are null or have multiple fact tables.
|||If you include all measures related to all three types of claims, then your fact table can get huge depending on how much claims activity you process, amount of history, and record width.
If you put them in different fact tables, you can still join them by your degenerate key(primary key -ex. claims number), but at least you won't be wasting space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have 3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 but others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables or should I combine them leaving the fields that only apply to one of the types null.
Thanks in advance for the help
|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
|||in the fact table, there is no primary key because you have "degenerated' the primary key from the source system. It has ceased to become the primary key in the fact table even though the field still exists. So, in general, a claim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a surrogate key is created. The surrogate key is a meangless number. So a customerid was a primary key in your claims system, but now it is not a PK anymore in your dimension table
. You have just a number(surrogate key) to describe your product and that is your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as what is called the degenerate key in the fact table. The surrogate key is actually the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.
Fact table design
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the helpIf I understand correctly you basically are talking about which users can ha
ve access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and perm
issions on the cube. I myself am in the learning process - as my name implie
s ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to th
e newsgroup.
By copy of this mail to the experts, I would like to ask a related question:
Is it a good or bad design practice to have multiple fact tables?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Actually what I am looking for is more of a best practice, is it better to h
ave one fact table that has columns that are null or have multiple fact tabl
es.|||If you include all measures related to all three types of claims, then your
fact table can get huge depending on how much claims activity you process, a
mount of history, and record width.
If you put them in different fact tables, you can still join them by your de
generate key(primary key -ex. claims number), but at least you won't be wast
ing space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the help|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||in the fact table, there is no primary key because you have "degenerated' th
e primary key from the source system. It has ceased to become the primary ke
y in the fact table even though the field still exists. So, in general, a cl
aim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a
surrogate key is created. The surrogate key is a meangless number. So a cus
tomerid was a primary key in your claims system, but now it is not a PK anym
ore in your dimension table
. You have just a number(surrogate key) to describe your product and that is
your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as wha
t is called the degenerate key in the fact table. The surrogate key is actua
lly the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||There are a number of different characteristics between medical, dental and
RX drugs that facilitate analysis meaningful for management, diagnosis and p
rocedure codes having the largest variation. I would treat all three as sepa
rate facts and use views an
d virtual cubes for those situations where combining the three makes sense.
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the helpIf I understand correctly you basically are talking about which users can ha
ve access to view which type of claims. Is this correct?
If this is your question then I think you can solve via using roles and perm
issions on the cube. I myself am in the learning process - as my name implie
s ;), but based on my so far understanding, this should do the trick.
Please let me know if this solves your problem? Kindly post your reply to th
e newsgroup.
By copy of this mail to the experts, I would like to ask a related question:
Is it a good or bad design practice to have multiple fact tables?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Actually what I am looking for is more of a best practice, is it better to h
ave one fact table that has columns that are null or have multiple fact tabl
es.|||If you include all measures related to all three types of claims, then your
fact table can get huge depending on how much claims activity you process, a
mount of history, and record width.
If you put them in different fact tables, you can still join them by your de
generate key(primary key -ex. claims number), but at least you won't be wast
ing space.
-- shache wrote: --
I am creating the data model for an insurance related datawarehouse, I have
3 types of Claims that we want to report on:
health, dental and drug. Some of the data the user needs will be in all 3 b
ut others will be in only one or two of the fact tables.
My question is should these 3 types of claims be divided into 3 fact tables
or should I combine them leaving the fields that only apply to one of the ty
pes null.
Thanks in advance for the help|||Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||in the fact table, there is no primary key because you have "degenerated' th
e primary key from the source system. It has ceased to become the primary ke
y in the fact table even though the field still exists. So, in general, a cl
aim number was the primary
key in your claims source system, but now it is just an ordinary field.
In the dimension table, the primary key is no longer valid anymore because a
surrogate key is created. The surrogate key is a meangless number. So a cus
tomerid was a primary key in your claims system, but now it is not a PK anym
ore in your dimension table
. You have just a number(surrogate key) to describe your product and that is
your new PK.
I'll understand if it is still confusing.
primary key is not the same as surrogate key. primary key is the same as wha
t is called the degenerate key in the fact table. The surrogate key is actua
lly the primary key in the dimension table.
-- Learner wrote: --
Hi,
A silly question from me:
Please explain what you mean by "(primary key -ex. claims number)"...the
reason for asking is that I'm new to this... is this the same thing as a
"surrogate key" (another term that I read in an article.
Regards.
> If you include all measures related to all three types of claims, then
> your fact table can get huge depending on how much claims activity you
> process, amount of history, and record width.
> If you put them in different fact tables, you can still join them by
> your degenerate key(primary key -ex. claims number), but at least you
> won't be wasting space.|||There are a number of different characteristics between medical, dental and
RX drugs that facilitate analysis meaningful for management, diagnosis and p
rocedure codes having the largest variation. I would treat all three as sepa
rate facts and use views an
d virtual cubes for those situations where combining the three makes sense.
Sunday, February 26, 2012
Extremely large db and blocking
Hi-
I'm a developer on a team with a 200GB db. We inherited it, it definitely
has design problems. About 3 weeks ago we started seeing a lot of blocking
on our largest table which has affected our systems. We made no changes at
the time the blocking started occurring. The db has been growing about 20
GB/month, and we are re-indexing every weekend. We have significant hardware
to support the database, and our DBAs do not believe it to be
hardware-related.
Is it possible that the SQL Server engine has problems when the db reaches
such a large size? Has anyone else experienced a similar situation or could
offer up ideas?
Thx,
JanHi
How are your indexes laid out compared to the database design? Is there a
clustered index on a very selectable column? Maybe posting the DDL of the
table would help us define a solution.
On large tables, a wrong clustered index can cuase havoc with blocking.
I am running multiple 5Tb databases, on 8Gb RAM servers, with no issues.
Regards
Mike
"Jan" wrote:
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of blocking
> on our largest table which has affected our systems. We made no changes at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or could
> offer up ideas?
> Thx,
> Jan|||Thanks for the reply. Wow- 5TB, very impressive!
We do have clustered indexes. Though not exactly the script (security
reasons), this might give you an idea:
CREATE TABLE [dbo].[PROBTBL] (
[RNO] [int] IDENTITY (1, 1) NOT NULL ,
[KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACUSTID] [int] NULL ,
[VID] [int] NULL ,
[VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OP] [datetime] NULL ,
[CLS] [datetime] NULL ,
[CLSCODE] [int] NULL ,
[PRN] [decimal](11, 2) NULL ,
[MNAMOUNT] [decimal](11, 2) NULL ,
[LTCHG] [decimal](11, 2) NULL ,
[LTPCT] [decimal](5, 2) NULL ,
[CANCL] [datetime] NULL ,
[TERM] [datetime] NULL ,
[STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[S1] [int] NULL ,
[S2] [int] NULL ,
[INTPRD] [decimal](7, 2) NULL ,
[SRVPRD] [decimal](7, 2) NULL ,
[ORIG] [decimal](11, 2) NULL ,
[LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IRTE] [decimal](6, 3) NULL ,
[DEF] [datetime] NULL ,
[RFL] [datetime] NULL ,
[INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IVPRCT] [decimal](7, 3) NULL ,
[INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSPCTT] [decimal](7, 3) NULL ,
[INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRCSSR] [int] NULL ,
[NXTCD] [int] NULL ,
[NXTDT] [datetime] NULL ,
[LASTACC] [datetime] NULL ,
[ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F] DEFAULT (0),
[IVFEE] [decimal](9, 2) NULL ,
[ISFEE] [decimal](9, 2) NULL ,
[ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
DEFAULT (0),
[ADFE] [decimal](9, 2) NULL ,
[IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301] DEFAULT
(0),
[CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A] DEFAULT
(0),
[VNDPRD] [int] NULL ,
[SVNXTCDE] [int] NULL ,
[SVNXTDT] [datetime] NULL ,
[DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73] DEFAULT
(0),
[XTCHNG] [datetime] NULL ,
[SCRWNL] [datetime] NULL ,
[BKNTFY] [datetime] NULL ,
[PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RGNT] [datetime] NULL ,
[MTRT] [datetime] NULL ,
[SRVCLS] [datetime] NULL ,
[NTS] [int] NULL ,
[NVSTD] [int] NULL ,
[PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSN] [int] NULL ,
[MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LstMdfd] [datetime] NULL ,
[LstMdfdDTTM] [datetime] NULL ,
[Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
(
[KNO]
) WITH FILLFACTOR = 40 ON [PRIMARY] ,
CONSTRAINT [FK_Lngnd] FOREIGN KEY
(
[Lngnd]
) REFERENCES [LngndDefinition] (
[Lngnd]
)
) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
GO
CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
[VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR = 90
ON [INDEXES2]
GO
CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH FILLFACTOR =90 ON [INDEXES]
GO
CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
[ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH FILLFACTOR
= 90 ON [INDEXES2]
GO
CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
[ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> How are your indexes laid out compared to the database design? Is there a
> clustered index on a very selectable column? Maybe posting the DDL of the
> table would help us define a solution.|||You should really find the source of the block. For example, is it a query
an update or an insert? Is the block caused by (say) small "X" locks, or an
"S" lock at the table level. One bad query plan can cause an index scan
which in turn can cause an "S" lock on the table; disaster!
There is some information on obtaining the "block chain" that you could find
using a Google search. I found a script which identifies the blocking
connection, all blocked connections, and query buffer contents for the
blocker (no always helpfull, but sometimes) and I have found this informatin
indespensible.
Without knowing the real cause and nature of the blocking, you might miss
the best solution.|||Jan wrote:
> Thanks for the reply. Wow- 5TB, very impressive!
> We do have clustered indexes. Though not exactly the script (security
> reasons), this might give you an idea:
> CREATE TABLE [dbo].[PROBTBL] (
> [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACUSTID] [int] NULL ,
> [VID] [int] NULL ,
> [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OP] [datetime] NULL ,
> [CLS] [datetime] NULL ,
> [CLSCODE] [int] NULL ,
> [PRN] [decimal](11, 2) NULL ,
> [MNAMOUNT] [decimal](11, 2) NULL ,
> [LTCHG] [decimal](11, 2) NULL ,
> [LTPCT] [decimal](5, 2) NULL ,
> [CANCL] [datetime] NULL ,
> [TERM] [datetime] NULL ,
> [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [S1] [int] NULL ,
> [S2] [int] NULL ,
> [INTPRD] [decimal](7, 2) NULL ,
> [SRVPRD] [decimal](7, 2) NULL ,
> [ORIG] [decimal](11, 2) NULL ,
> [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IRTE] [decimal](6, 3) NULL ,
> [DEF] [datetime] NULL ,
> [RFL] [datetime] NULL ,
> [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IVPRCT] [decimal](7, 3) NULL ,
> [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSPCTT] [decimal](7, 3) NULL ,
> [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRCSSR] [int] NULL ,
> [NXTCD] [int] NULL ,
> [NXTDT] [datetime] NULL ,
> [LASTACC] [datetime] NULL ,
> [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> [ISFEE] [decimal](9, 2) NULL ,
> [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> DEFAULT (0),
> [ADFE] [decimal](9, 2) NULL ,
> [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> DEFAULT (0),
> [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> DEFAULT (0),
> [VNDPRD] [int] NULL ,
> [SVNXTCDE] [int] NULL ,
> [SVNXTDT] [datetime] NULL ,
> [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> DEFAULT (0),
> [XTCHNG] [datetime] NULL ,
> [SCRWNL] [datetime] NULL ,
> [BKNTFY] [datetime] NULL ,
> [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RGNT] [datetime] NULL ,
> [MTRT] [datetime] NULL ,
> [SRVCLS] [datetime] NULL ,
> [NTS] [int] NULL ,
> [NVSTD] [int] NULL ,
> [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PSN] [int] NULL ,
> [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LstMdfd] [datetime] NULL ,
> [LstMdfdDTTM] [datetime] NULL ,
> [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> (
> [KNO]
> ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> CONSTRAINT [FK_Lngnd] FOREIGN KEY
> (
> [Lngnd]
> ) REFERENCES [LngndDefinition] (
> [Lngnd]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> GO
> CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> [INDEXES2] GO
> CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> = 90
> ON [INDEXES2]
> GO
> CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> = 90
> ON [INDEXES]
> GO
> CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> WITH FILLFACTOR = 90 ON [INDEXES2]
> GO
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> How are your indexes laid out compared to the database design? Is
>> there a clustered index on a very selectable column? Maybe posting
>> the DDL of the table would help us define a solution.
I would recommend some performance tuning on the server. There are a
number of products out there that may be able to point you to the
problem. You could also try using Profiler to see what SQL is taking a
lot of CPU or has long durations.
That looks like a large table with very wide rows. If you are reindexing
weekly, you may consider changing your fill factor so you don't leave
10% free space. That is, if you really don't require that much free
space for the inserts each week.
Plus your PK has a fill factor of 40!!!. That's means you are severly
degrading read performance. Probably more than 2X. You are in effect
increasing the size of the table by about 225%, causing all reads to
lock more than twice as many pages, causing updates to do the same, and
making your disks work so much harder to get at the data.
If you are reindexing every week, then you may want to leave the fill
factor at the default settings, or at least fill the pages 95% full.
Only highly transactional tables with clustered indexes that force page
breaks should really be using a lower fill factor.
I suspect that's a large portion of the problem.
David Gugick
Imceda Software
www.imceda.com|||To add to what David wrote.
You have a lot of indexes, the index maintenance overhead is big in such a
case. This can have horrible effects on blocking. Do you really need all
those indexes?
The databases we hit with such big volumes are fully normalized, with no
table exceeding 20 columns.
Your maximum row width would hit 600 bytes, without the TEXT datatype, so
having a fill factor of 40% on your clustered index really wastes space in
the cache and results in more I/O than necessary.
Also check that Auto update statistics is on for the DB (and especially on
big DB's like this, Torn Page Detection On)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OzZllPJ3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Jan wrote:
> > Thanks for the reply. Wow- 5TB, very impressive!
> >
> > We do have clustered indexes. Though not exactly the script (security
> > reasons), this might give you an idea:
> >
> > CREATE TABLE [dbo].[PROBTBL] (
> > [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> > [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ACUSTID] [int] NULL ,
> > [VID] [int] NULL ,
> > [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [OP] [datetime] NULL ,
> > [CLS] [datetime] NULL ,
> > [CLSCODE] [int] NULL ,
> > [PRN] [decimal](11, 2) NULL ,
> > [MNAMOUNT] [decimal](11, 2) NULL ,
> > [LTCHG] [decimal](11, 2) NULL ,
> > [LTPCT] [decimal](5, 2) NULL ,
> > [CANCL] [datetime] NULL ,
> > [TERM] [datetime] NULL ,
> > [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [S1] [int] NULL ,
> > [S2] [int] NULL ,
> > [INTPRD] [decimal](7, 2) NULL ,
> > [SRVPRD] [decimal](7, 2) NULL ,
> > [ORIG] [decimal](11, 2) NULL ,
> > [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IRTE] [decimal](6, 3) NULL ,
> > [DEF] [datetime] NULL ,
> > [RFL] [datetime] NULL ,
> > [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IVPRCT] [decimal](7, 3) NULL ,
> > [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSPCTT] [decimal](7, 3) NULL ,
> > [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRCSSR] [int] NULL ,
> > [NXTCD] [int] NULL ,
> > [NXTDT] [datetime] NULL ,
> > [LASTACC] [datetime] NULL ,
> > [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> > DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> > [ISFEE] [decimal](9, 2) NULL ,
> > [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> > DEFAULT (0),
> > [ADFE] [decimal](9, 2) NULL ,
> > [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> > DEFAULT (0),
> > [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> > DEFAULT (0),
> > [VNDPRD] [int] NULL ,
> > [SVNXTCDE] [int] NULL ,
> > [SVNXTDT] [datetime] NULL ,
> > [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> > DEFAULT (0),
> > [XTCHNG] [datetime] NULL ,
> > [SCRWNL] [datetime] NULL ,
> > [BKNTFY] [datetime] NULL ,
> > [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [RGNT] [datetime] NULL ,
> > [MTRT] [datetime] NULL ,
> > [SRVCLS] [datetime] NULL ,
> > [NTS] [int] NULL ,
> > [NVSTD] [int] NULL ,
> > [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PSN] [int] NULL ,
> > [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LstMdfd] [datetime] NULL ,
> > [LstMdfdDTTM] [datetime] NULL ,
> > [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> > (
> > [KNO]
> > ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> > CONSTRAINT [FK_Lngnd] FOREIGN KEY
> > (
> > [Lngnd]
> > ) REFERENCES [LngndDefinition] (
> > [Lngnd]
> > )
> > ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> > GO
> >
> > CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> > [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> > [INDEXES2] GO
> >
> > CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES]
> > GO
> >
> > CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> > [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> > WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> > [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> > WITH FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> >
> >
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> How are your indexes laid out compared to the database design? Is
> >> there a clustered index on a very selectable column? Maybe posting
> >> the DDL of the table would help us define a solution.
> I would recommend some performance tuning on the server. There are a
> number of products out there that may be able to point you to the
> problem. You could also try using Profiler to see what SQL is taking a
> lot of CPU or has long durations.
> That looks like a large table with very wide rows. If you are reindexing
> weekly, you may consider changing your fill factor so you don't leave
> 10% free space. That is, if you really don't require that much free
> space for the inserts each week.
> Plus your PK has a fill factor of 40!!!. That's means you are severly
> degrading read performance. Probably more than 2X. You are in effect
> increasing the size of the table by about 225%, causing all reads to
> lock more than twice as many pages, causing updates to do the same, and
> making your disks work so much harder to get at the data.
> If you are reindexing every week, then you may want to leave the fill
> factor at the default settings, or at least fill the pages 95% full.
> Only highly transactional tables with clustered indexes that force page
> breaks should really be using a lower fill factor.
> I suspect that's a large portion of the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||You have many DateTime fields. You can cut the size of these in half by
converting to SmallDateTime. See BOL.
Paul.
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:5C690F4A-5E37-4927-9FCC-827D7C412469@.microsoft.com...
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of
> blocking
> on our largest table which has affected our systems. We made no changes
> at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant
> hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or
> could
> offer up ideas?
> Thx,
> Jan
I'm a developer on a team with a 200GB db. We inherited it, it definitely
has design problems. About 3 weeks ago we started seeing a lot of blocking
on our largest table which has affected our systems. We made no changes at
the time the blocking started occurring. The db has been growing about 20
GB/month, and we are re-indexing every weekend. We have significant hardware
to support the database, and our DBAs do not believe it to be
hardware-related.
Is it possible that the SQL Server engine has problems when the db reaches
such a large size? Has anyone else experienced a similar situation or could
offer up ideas?
Thx,
JanHi
How are your indexes laid out compared to the database design? Is there a
clustered index on a very selectable column? Maybe posting the DDL of the
table would help us define a solution.
On large tables, a wrong clustered index can cuase havoc with blocking.
I am running multiple 5Tb databases, on 8Gb RAM servers, with no issues.
Regards
Mike
"Jan" wrote:
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of blocking
> on our largest table which has affected our systems. We made no changes at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or could
> offer up ideas?
> Thx,
> Jan|||Thanks for the reply. Wow- 5TB, very impressive!
We do have clustered indexes. Though not exactly the script (security
reasons), this might give you an idea:
CREATE TABLE [dbo].[PROBTBL] (
[RNO] [int] IDENTITY (1, 1) NOT NULL ,
[KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACUSTID] [int] NULL ,
[VID] [int] NULL ,
[VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OP] [datetime] NULL ,
[CLS] [datetime] NULL ,
[CLSCODE] [int] NULL ,
[PRN] [decimal](11, 2) NULL ,
[MNAMOUNT] [decimal](11, 2) NULL ,
[LTCHG] [decimal](11, 2) NULL ,
[LTPCT] [decimal](5, 2) NULL ,
[CANCL] [datetime] NULL ,
[TERM] [datetime] NULL ,
[STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[S1] [int] NULL ,
[S2] [int] NULL ,
[INTPRD] [decimal](7, 2) NULL ,
[SRVPRD] [decimal](7, 2) NULL ,
[ORIG] [decimal](11, 2) NULL ,
[LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IRTE] [decimal](6, 3) NULL ,
[DEF] [datetime] NULL ,
[RFL] [datetime] NULL ,
[INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IVPRCT] [decimal](7, 3) NULL ,
[INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSPCTT] [decimal](7, 3) NULL ,
[INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRCSSR] [int] NULL ,
[NXTCD] [int] NULL ,
[NXTDT] [datetime] NULL ,
[LASTACC] [datetime] NULL ,
[ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F] DEFAULT (0),
[IVFEE] [decimal](9, 2) NULL ,
[ISFEE] [decimal](9, 2) NULL ,
[ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
DEFAULT (0),
[ADFE] [decimal](9, 2) NULL ,
[IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301] DEFAULT
(0),
[CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A] DEFAULT
(0),
[VNDPRD] [int] NULL ,
[SVNXTCDE] [int] NULL ,
[SVNXTDT] [datetime] NULL ,
[DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73] DEFAULT
(0),
[XTCHNG] [datetime] NULL ,
[SCRWNL] [datetime] NULL ,
[BKNTFY] [datetime] NULL ,
[PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RGNT] [datetime] NULL ,
[MTRT] [datetime] NULL ,
[SRVCLS] [datetime] NULL ,
[NTS] [int] NULL ,
[NVSTD] [int] NULL ,
[PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PSN] [int] NULL ,
[MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LstMdfd] [datetime] NULL ,
[LstMdfdDTTM] [datetime] NULL ,
[Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
(
[KNO]
) WITH FILLFACTOR = 40 ON [PRIMARY] ,
CONSTRAINT [FK_Lngnd] FOREIGN KEY
(
[Lngnd]
) REFERENCES [LngndDefinition] (
[Lngnd]
)
) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
GO
CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
[VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR = 90
ON [INDEXES2]
GO
CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH FILLFACTOR =90 ON [INDEXES]
GO
CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH FILLFACTOR = 90
ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
[ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH FILLFACTOR
= 90 ON [INDEXES2]
GO
CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR = 90 ON
[INDEXES2]
GO
CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified]) WITH
FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
[ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
GO
CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS]) WITH
FILLFACTOR = 90 ON [INDEXES2]
GO
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> How are your indexes laid out compared to the database design? Is there a
> clustered index on a very selectable column? Maybe posting the DDL of the
> table would help us define a solution.|||You should really find the source of the block. For example, is it a query
an update or an insert? Is the block caused by (say) small "X" locks, or an
"S" lock at the table level. One bad query plan can cause an index scan
which in turn can cause an "S" lock on the table; disaster!
There is some information on obtaining the "block chain" that you could find
using a Google search. I found a script which identifies the blocking
connection, all blocked connections, and query buffer contents for the
blocker (no always helpfull, but sometimes) and I have found this informatin
indespensible.
Without knowing the real cause and nature of the blocking, you might miss
the best solution.|||Jan wrote:
> Thanks for the reply. Wow- 5TB, very impressive!
> We do have clustered indexes. Though not exactly the script (security
> reasons), this might give you an idea:
> CREATE TABLE [dbo].[PROBTBL] (
> [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ACUSTID] [int] NULL ,
> [VID] [int] NULL ,
> [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OP] [datetime] NULL ,
> [CLS] [datetime] NULL ,
> [CLSCODE] [int] NULL ,
> [PRN] [decimal](11, 2) NULL ,
> [MNAMOUNT] [decimal](11, 2) NULL ,
> [LTCHG] [decimal](11, 2) NULL ,
> [LTPCT] [decimal](5, 2) NULL ,
> [CANCL] [datetime] NULL ,
> [TERM] [datetime] NULL ,
> [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [S1] [int] NULL ,
> [S2] [int] NULL ,
> [INTPRD] [decimal](7, 2) NULL ,
> [SRVPRD] [decimal](7, 2) NULL ,
> [ORIG] [decimal](11, 2) NULL ,
> [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IRTE] [decimal](6, 3) NULL ,
> [DEF] [datetime] NULL ,
> [RFL] [datetime] NULL ,
> [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IVPRCT] [decimal](7, 3) NULL ,
> [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INSPCTT] [decimal](7, 3) NULL ,
> [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PRCSSR] [int] NULL ,
> [NXTCD] [int] NULL ,
> [NXTDT] [datetime] NULL ,
> [LASTACC] [datetime] NULL ,
> [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> [ISFEE] [decimal](9, 2) NULL ,
> [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> DEFAULT (0),
> [ADFE] [decimal](9, 2) NULL ,
> [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> DEFAULT (0),
> [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> DEFAULT (0),
> [VNDPRD] [int] NULL ,
> [SVNXTCDE] [int] NULL ,
> [SVNXTDT] [datetime] NULL ,
> [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> DEFAULT (0),
> [XTCHNG] [datetime] NULL ,
> [SCRWNL] [datetime] NULL ,
> [BKNTFY] [datetime] NULL ,
> [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RGNT] [datetime] NULL ,
> [MTRT] [datetime] NULL ,
> [SRVCLS] [datetime] NULL ,
> [NTS] [int] NULL ,
> [NVSTD] [int] NULL ,
> [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PSN] [int] NULL ,
> [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LstMdfd] [datetime] NULL ,
> [LstMdfdDTTM] [datetime] NULL ,
> [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> (
> [KNO]
> ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> CONSTRAINT [FK_Lngnd] FOREIGN KEY
> (
> [Lngnd]
> ) REFERENCES [LngndDefinition] (
> [Lngnd]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> GO
> CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> [INDEXES2] GO
> CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> = 90
> ON [INDEXES2]
> GO
> CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> = 90
> ON [INDEXES]
> GO
> CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> FILLFACTOR = 90 ON [INDEXES2]
> GO
> CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => 90 ON [INDEXES2]
> GO
> CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> GO
> CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> WITH FILLFACTOR = 90 ON [INDEXES2]
> GO
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> How are your indexes laid out compared to the database design? Is
>> there a clustered index on a very selectable column? Maybe posting
>> the DDL of the table would help us define a solution.
I would recommend some performance tuning on the server. There are a
number of products out there that may be able to point you to the
problem. You could also try using Profiler to see what SQL is taking a
lot of CPU or has long durations.
That looks like a large table with very wide rows. If you are reindexing
weekly, you may consider changing your fill factor so you don't leave
10% free space. That is, if you really don't require that much free
space for the inserts each week.
Plus your PK has a fill factor of 40!!!. That's means you are severly
degrading read performance. Probably more than 2X. You are in effect
increasing the size of the table by about 225%, causing all reads to
lock more than twice as many pages, causing updates to do the same, and
making your disks work so much harder to get at the data.
If you are reindexing every week, then you may want to leave the fill
factor at the default settings, or at least fill the pages 95% full.
Only highly transactional tables with clustered indexes that force page
breaks should really be using a lower fill factor.
I suspect that's a large portion of the problem.
David Gugick
Imceda Software
www.imceda.com|||To add to what David wrote.
You have a lot of indexes, the index maintenance overhead is big in such a
case. This can have horrible effects on blocking. Do you really need all
those indexes?
The databases we hit with such big volumes are fully normalized, with no
table exceeding 20 columns.
Your maximum row width would hit 600 bytes, without the TEXT datatype, so
having a fill factor of 40% on your clustered index really wastes space in
the cache and results in more I/O than necessary.
Also check that Auto update statistics is on for the DB (and especially on
big DB's like this, Torn Page Detection On)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OzZllPJ3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Jan wrote:
> > Thanks for the reply. Wow- 5TB, very impressive!
> >
> > We do have clustered indexes. Though not exactly the script (security
> > reasons), this might give you an idea:
> >
> > CREATE TABLE [dbo].[PROBTBL] (
> > [RNO] [int] IDENTITY (1, 1) NOT NULL ,
> > [KNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ANOTHKNO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ATYPEKIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LNITEMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ACUSTID] [int] NULL ,
> > [VID] [int] NULL ,
> > [VNUM] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTR] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [CNTY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LTYPE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [OP] [datetime] NULL ,
> > [CLS] [datetime] NULL ,
> > [CLSCODE] [int] NULL ,
> > [PRN] [decimal](11, 2) NULL ,
> > [MNAMOUNT] [decimal](11, 2) NULL ,
> > [LTCHG] [decimal](11, 2) NULL ,
> > [LTPCT] [decimal](5, 2) NULL ,
> > [CANCL] [datetime] NULL ,
> > [TERM] [datetime] NULL ,
> > [STS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [S1] [int] NULL ,
> > [S2] [int] NULL ,
> > [INTPRD] [decimal](7, 2) NULL ,
> > [SRVPRD] [decimal](7, 2) NULL ,
> > [ORIG] [decimal](11, 2) NULL ,
> > [LDESC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLADDRESS1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > , [MLADDRESS2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLCTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL , [MLST] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [MLZC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [BPHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IRTE] [decimal](6, 3) NULL ,
> > [DEF] [datetime] NULL ,
> > [RFL] [datetime] NULL ,
> > [INVID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INVNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [IVPRCT] [decimal](7, 3) NULL ,
> > [INRID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSNMNO] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [INSPCTT] [decimal](7, 3) NULL ,
> > [INSCRTT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRTY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PRCSSR] [int] NULL ,
> > [NXTCD] [int] NULL ,
> > [NXTDT] [datetime] NULL ,
> > [LASTACC] [datetime] NULL ,
> > [ONHLD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ONHLD__4301EA8F]
> > DEFAULT (0), [IVFEE] [decimal](9, 2) NULL ,
> > [ISFEE] [decimal](9, 2) NULL ,
> > [ADDFEEREQ] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADDFEE__43F60EC8]
> > DEFAULT (0),
> > [ADFE] [decimal](9, 2) NULL ,
> > [IVCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [ISCD] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [HLDFRBK] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__HOLDFO__44EA3301]
> > DEFAULT (0),
> > [CNTSTD] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__CONTES__45DE573A]
> > DEFAULT (0),
> > [VNDPRD] [int] NULL ,
> > [SVNXTCDE] [int] NULL ,
> > [SVNXTDT] [datetime] NULL ,
> > [DJSTBL] [bit] NOT NULL CONSTRAINT [DF__PROBTBL__ADJUST__46D27B73]
> > DEFAULT (0),
> > [XTCHNG] [datetime] NULL ,
> > [SCRWNL] [datetime] NULL ,
> > [BKNTFY] [datetime] NULL ,
> > [PRPTND] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [RGNT] [datetime] NULL ,
> > [MTRT] [datetime] NULL ,
> > [SRVCLS] [datetime] NULL ,
> > [NTS] [int] NULL ,
> > [NVSTD] [int] NULL ,
> > [PTYP] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [PSN] [int] NULL ,
> > [MRSN] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [LstMdfd] [datetime] NULL ,
> > [LstMdfdDTTM] [datetime] NULL ,
> > [Lngnd] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > CONSTRAINT [PK_PROBTBL] PRIMARY KEY CLUSTERED
> > (
> > [KNO]
> > ) WITH FILLFACTOR = 40 ON [PRIMARY] ,
> > CONSTRAINT [FK_Lngnd] FOREIGN KEY
> > (
> > [Lngnd]
> > ) REFERENCES [LngndDefinition] (
> > [Lngnd]
> > )
> > ) ON [PRIMARY] TEXTIMAGE_ON [TEXT3]
> > GO
> >
> > CREATE INDEX [IXC_PROBTBL] ON [dbo].[PROBTBL]([KNO], [VID]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [RNO_IDX] ON [dbo].[PROBTBL]([RNO]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [Clt_IDX] ON [dbo].[PROBTBL]([ACUSTID]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [LNITEMNO_IDX] ON [dbo].[PROBTBL]([LNITEMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [[tIDX_PROBTBL_COMPOSITE] ON [dbo].[PROBTBL]([ACUSTID],
> > [VNUM], [KNO], [ATYPEKIND], [LNITEMNO]) WITH FILLFACTOR = 90 ON
> > [INDEXES2] GO
> >
> > CREATE INDEX [Vdors_IDX] ON [dbo].[PROBTBL]([VID]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [SRVCLS_IDX] ON [dbo].[PROBTBL]([SRVCLS]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [VNUM_IDX] ON [dbo].[PROBTBL]([VNUM]) WITH FILLFACTOR
> > = 90
> > ON [INDEXES]
> > GO
> >
> > CREATE INDEX [ADDRESS1_IDX] ON [dbo].[PROBTBL]([ADDRESS1]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [NVSTD_IDX] ON [dbo].[PROBTBL]([NVSTD]) WITH
> > FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_KNO_ACUSTID] ON [dbo].[PROBTBL]([KNO],
> > [ACUSTID]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [INVNMNO_IDX] ON [dbo].[PROBTBL]([INVNMNO]) WITH
> > FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [CTR_IDX] ON [dbo].[PROBTBL]([CTR]) WITH FILLFACTOR => > 90 ON [INDEXES2]
> > GO
> >
> > CREATE INDEX [LastModified_IDX] ON [dbo].[PROBTBL]([LastModified])
> > WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [IDX_TSBodyAtt] ON [dbo].[PROBTBL]([VID], [NVSTD],
> > [ATYPEKIND]) WITH FILLFACTOR = 90 ON [INDEXES]
> > GO
> >
> > CREATE INDEX [tIDX_PROBTBL_ST_CLS] ON [dbo].[PROBTBL]([ST], [CLS])
> > WITH FILLFACTOR = 90 ON [INDEXES2]
> > GO
> >
> >
> >
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> How are your indexes laid out compared to the database design? Is
> >> there a clustered index on a very selectable column? Maybe posting
> >> the DDL of the table would help us define a solution.
> I would recommend some performance tuning on the server. There are a
> number of products out there that may be able to point you to the
> problem. You could also try using Profiler to see what SQL is taking a
> lot of CPU or has long durations.
> That looks like a large table with very wide rows. If you are reindexing
> weekly, you may consider changing your fill factor so you don't leave
> 10% free space. That is, if you really don't require that much free
> space for the inserts each week.
> Plus your PK has a fill factor of 40!!!. That's means you are severly
> degrading read performance. Probably more than 2X. You are in effect
> increasing the size of the table by about 225%, causing all reads to
> lock more than twice as many pages, causing updates to do the same, and
> making your disks work so much harder to get at the data.
> If you are reindexing every week, then you may want to leave the fill
> factor at the default settings, or at least fill the pages 95% full.
> Only highly transactional tables with clustered indexes that force page
> breaks should really be using a lower fill factor.
> I suspect that's a large portion of the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||You have many DateTime fields. You can cut the size of these in half by
converting to SmallDateTime. See BOL.
Paul.
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:5C690F4A-5E37-4927-9FCC-827D7C412469@.microsoft.com...
> Hi-
> I'm a developer on a team with a 200GB db. We inherited it, it definitely
> has design problems. About 3 weeks ago we started seeing a lot of
> blocking
> on our largest table which has affected our systems. We made no changes
> at
> the time the blocking started occurring. The db has been growing about 20
> GB/month, and we are re-indexing every weekend. We have significant
> hardware
> to support the database, and our DBAs do not believe it to be
> hardware-related.
> Is it possible that the SQL Server engine has problems when the db reaches
> such a large size? Has anyone else experienced a similar situation or
> could
> offer up ideas?
> Thx,
> Jan
Subscribe to:
Posts (Atom)