Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I don
t
understand what effect this might have overall. Any thoughts on this?
Message posted via http://www.droptable.comCharlesT via droptable.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and wh
at
> to do about it. The database contains about 24 billion records. It is spli
t
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I d
ont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via droptable.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and wha
t
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I do
nt
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
Hugo Kornelis, SQL Server MVP|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically o
f
the form how many calls with this attribute where made in a certain month. I
t
seems like date could be a candidate for an index then, but its not there. I
m
not sure if the person who designed the database knew that timestamp and
datetime are very different.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200602/1sql
Showing posts with label encountered. Show all posts
Showing posts with label encountered. Show all posts
Wednesday, March 21, 2012
Is this an ok design?
Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
Message posted via http://www.droptable.com
CharlesT via droptable.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via droptable.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
Hugo Kornelis, SQL Server MVP
|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
Message posted via http://www.droptable.com
CharlesT via droptable.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via droptable.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
Hugo Kornelis, SQL Server MVP
|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1
Is this an ok design?
Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
--
Message posted via http://www.sqlmonster.comCharlesT via SQLMonster.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via SQLMonster.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
--
Hugo Kornelis, SQL Server MVP|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
--
Message posted via http://www.sqlmonster.comCharlesT via SQLMonster.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via SQLMonster.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
--
Hugo Kornelis, SQL Server MVP|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1
Friday, March 9, 2012
Is there any way to use fields in a Header and/or Footer sections
I am New to reporting services and do not know most of it but the first
hurdle that i have encountered is of how to use a fields in a header/footer
section. the requirement that i have reports forces me to use fields but RS
does not allow it.
I was wondering if there is any workaround for this limitationYou can do this but you have to use either an aggrate (sum, count) or first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
>I am New to reporting services and do not know most of it but the first
> hurdle that i have encountered is of how to use a fields in a
> header/footer
> section. the requirement that i have reports forces me to use fields but
> RS
> does not allow it.
> I was wondering if there is any workaround for this limitation|||thanks i am trying to print a company logo as this is not a numeric datatype
how would i use a aggregate function....if you can give me an example that
will be a great help.
Also I was thinking more in lines of declaring a vriable and storing a field
value in it and then assigning it to an Image item......the only problem
hereis i do not know how to do declare a variable
"Bruce L-C [MVP]" wrote:
> You can do this but you have to use either an aggrate (sum, count) or first.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
> >I am New to reporting services and do not know most of it but the first
> > hurdle that i have encountered is of how to use a fields in a
> > header/footer
> > section. the requirement that i have reports forces me to use fields but
> > RS
> > does not allow it.
> > I was wondering if there is any workaround for this limitation
>
>|||Is the data you want in a field in the dataset? That is what I thought you
were looking for. Note I said aggragate OR use first. Use the expression
builder and it should suggest using first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
news:36C6B519-412D-4F4E-93F1-8AD781BA36AD@.microsoft.com...
> thanks i am trying to print a company logo as this is not a numeric
> datatype
> how would i use a aggregate function....if you can give me an example
> that
> will be a great help.
> Also I was thinking more in lines of declaring a vriable and storing a
> field
> value in it and then assigning it to an Image item......the only problem
> hereis i do not know how to do declare a variable
>
> "Bruce L-C [MVP]" wrote:
>> You can do this but you have to use either an aggrate (sum, count) or
>> first.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
>> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
>> >I am New to reporting services and do not know most of it but the first
>> > hurdle that i have encountered is of how to use a fields in a
>> > header/footer
>> > section. the requirement that i have reports forces me to use fields
>> > but
>> > RS
>> > does not allow it.
>> > I was wondering if there is any workaround for this limitation
>>|||Ok, i will expllain the whole situation. I i am storing Logos of different
companies in a MS SQL DB, now i want to assign this filed to an Image. The
Image item is in a Header section. Header Section does not allow to refer to
any fields, which is where my problem starts. I do not want to move header to
body as that will print logo at all pages. I have tried First but it errors
out. So again my question is is there any way i can work around this? I was
also thinking if there is any way that i can defined a variable (much like in
VB or C#) ans assign the logo field to this variable and then assign the
variable to the image field, but i do not know how to declare a variable in
Reporting Services and then use it in header section.
ANY HELP WILL BE REALLY APPRECIATED!!!|||What is the error message?
What is the expression you are using that doesn't work?
=First(Fields!fieldname.Value) should work, or you may need
=First(Fields!fieldname.Value, "datasetname") when working with
multiple datasets.|||I know the use of a FIRST and multiple datasets ...my Question is: How to
use a field in Page Header? Reporting Service does not allow to use Fields in
Page Header so, i was wondering if there is any workaround?
"timseal" wrote:
> What is the error message?
> What is the expression you are using that doesn't work?
> =First(Fields!fieldname.Value) should work, or you may need
> =First(Fields!fieldname.Value, "datasetname") when working with
> multiple datasets.
>|||In Bruce's first post, he said you can do this.
If you are having trouble, please be specific. 'Errors out' is not
helpful.|||I had the same problem. Please confirm that the only way to use Fields in the
Page Header is to use an aggregate function. So what you are telling us is
that Reporting Services cannot display a field from a dataset on every page
of the report. Read below.
I am trying to display a calculated field in the Page Header for every page.
I have tried the suggestions written, but the textbox only appears on the
Page Header on the last page of the report.
For example, textbox29 in the body of my report is:
=First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
In the Page Header, I just display the textbox:
=ReportItems!textbox29.value
The result only appears on the last page of the report. How can this result
appear on every page of the report? I tried using RepeatWith, but that did
nothing. Please help. Thanks.
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> You can do this but you have to use either an aggrate (sum, count) or first.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
> >I am New to reporting services and do not know most of it but the first
> > hurdle that i have encountered is of how to use a fields in a
> > header/footer
> > section. the requirement that i have reports forces me to use fields but
> > RS
> > does not allow it.
> > I was wondering if there is any workaround for this limitation
>
>
hurdle that i have encountered is of how to use a fields in a header/footer
section. the requirement that i have reports forces me to use fields but RS
does not allow it.
I was wondering if there is any workaround for this limitationYou can do this but you have to use either an aggrate (sum, count) or first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
>I am New to reporting services and do not know most of it but the first
> hurdle that i have encountered is of how to use a fields in a
> header/footer
> section. the requirement that i have reports forces me to use fields but
> RS
> does not allow it.
> I was wondering if there is any workaround for this limitation|||thanks i am trying to print a company logo as this is not a numeric datatype
how would i use a aggregate function....if you can give me an example that
will be a great help.
Also I was thinking more in lines of declaring a vriable and storing a field
value in it and then assigning it to an Image item......the only problem
hereis i do not know how to do declare a variable
"Bruce L-C [MVP]" wrote:
> You can do this but you have to use either an aggrate (sum, count) or first.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
> >I am New to reporting services and do not know most of it but the first
> > hurdle that i have encountered is of how to use a fields in a
> > header/footer
> > section. the requirement that i have reports forces me to use fields but
> > RS
> > does not allow it.
> > I was wondering if there is any workaround for this limitation
>
>|||Is the data you want in a field in the dataset? That is what I thought you
were looking for. Note I said aggragate OR use first. Use the expression
builder and it should suggest using first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
news:36C6B519-412D-4F4E-93F1-8AD781BA36AD@.microsoft.com...
> thanks i am trying to print a company logo as this is not a numeric
> datatype
> how would i use a aggregate function....if you can give me an example
> that
> will be a great help.
> Also I was thinking more in lines of declaring a vriable and storing a
> field
> value in it and then assigning it to an Image item......the only problem
> hereis i do not know how to do declare a variable
>
> "Bruce L-C [MVP]" wrote:
>> You can do this but you have to use either an aggrate (sum, count) or
>> first.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
>> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
>> >I am New to reporting services and do not know most of it but the first
>> > hurdle that i have encountered is of how to use a fields in a
>> > header/footer
>> > section. the requirement that i have reports forces me to use fields
>> > but
>> > RS
>> > does not allow it.
>> > I was wondering if there is any workaround for this limitation
>>|||Ok, i will expllain the whole situation. I i am storing Logos of different
companies in a MS SQL DB, now i want to assign this filed to an Image. The
Image item is in a Header section. Header Section does not allow to refer to
any fields, which is where my problem starts. I do not want to move header to
body as that will print logo at all pages. I have tried First but it errors
out. So again my question is is there any way i can work around this? I was
also thinking if there is any way that i can defined a variable (much like in
VB or C#) ans assign the logo field to this variable and then assign the
variable to the image field, but i do not know how to declare a variable in
Reporting Services and then use it in header section.
ANY HELP WILL BE REALLY APPRECIATED!!!|||What is the error message?
What is the expression you are using that doesn't work?
=First(Fields!fieldname.Value) should work, or you may need
=First(Fields!fieldname.Value, "datasetname") when working with
multiple datasets.|||I know the use of a FIRST and multiple datasets ...my Question is: How to
use a field in Page Header? Reporting Service does not allow to use Fields in
Page Header so, i was wondering if there is any workaround?
"timseal" wrote:
> What is the error message?
> What is the expression you are using that doesn't work?
> =First(Fields!fieldname.Value) should work, or you may need
> =First(Fields!fieldname.Value, "datasetname") when working with
> multiple datasets.
>|||In Bruce's first post, he said you can do this.
If you are having trouble, please be specific. 'Errors out' is not
helpful.|||I had the same problem. Please confirm that the only way to use Fields in the
Page Header is to use an aggregate function. So what you are telling us is
that Reporting Services cannot display a field from a dataset on every page
of the report. Read below.
I am trying to display a calculated field in the Page Header for every page.
I have tried the suggestions written, but the textbox only appears on the
Page Header on the last page of the report.
For example, textbox29 in the body of my report is:
=First(Fields!Next_2_Years.Value, "AnalysisCngEECSalary")
In the Page Header, I just display the textbox:
=ReportItems!textbox29.value
The result only appears on the last page of the report. How can this result
appear on every page of the report? I tried using RepeatWith, but that did
nothing. Please help. Thanks.
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> You can do this but you have to use either an aggrate (sum, count) or first.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sirish Saxena" <SirishSaxena@.discussions.microsoft.com> wrote in message
> news:6A79C35A-BD05-4E15-B491-938D6BDCD0B0@.microsoft.com...
> >I am New to reporting services and do not know most of it but the first
> > hurdle that i have encountered is of how to use a fields in a
> > header/footer
> > section. the requirement that i have reports forces me to use fields but
> > RS
> > does not allow it.
> > I was wondering if there is any workaround for this limitation
>
>
Is there any way to tell when a database file grew
We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.
Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>
|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a[vbcol=seagreen]
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David
|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the
> Quite possible, depending on the other IO activity at the time.
> David
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David
|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>
|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O[vbcol=seagreen]
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5
>
|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.
Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>
|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a[vbcol=seagreen]
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David
|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the
> Quite possible, depending on the other IO activity at the time.
> David
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David
|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>
|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O[vbcol=seagreen]
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5
>
|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>
Is there any way to tell when a database file grew
We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5[vbcol=seagreen]
>|||Growing in advance is much better. Grow doesn't block operations for which s
pace is available
(operations that uses current space (on same page, extent etc)) or space fre
e in the db file (as it
is when you grow in advance). Grow does block operations that requires new s
pace which isn't
available (which is the case when all extents are used and you do, say, an i
nsert on a table for
which all current extents are used and all pages on those extents are full,
and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...[vbco
l=seagreen]
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>[/vbcol]
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5[vbcol=seagreen]
>|||Growing in advance is much better. Grow doesn't block operations for which s
pace is available
(operations that uses current space (on same page, extent etc)) or space fre
e in the db file (as it
is when you grow in advance). Grow does block operations that requires new s
pace which isn't
available (which is the case when all extents are used and you do, say, an i
nsert on a table for
which all current extents are used and all pages on those extents are full,
and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...[vbco
l=seagreen]
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>[/vbcol]
Is there any way to tell when a database file grew
We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> > We have all the files in our database set to autogrow by 10%. During
the
> > day we encountered a 5 minute time period in which SQL activity seemed
to
> > come to a halt. I suspect that during that time period, one of the
> > database
> > files grew.
> >
> > Is there any way that I could tell this by looking at a catalog table?
> >
> > Thanks in advance.
> >
> >
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> > Thanks for the reply Andrew. The file is currently 70GB so therefore
the
> > growthsize is approximately 7GB.
> >
> > Would an autogrow extent of that size cause a "standstill" for about 5
> > minutes?
> >
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> > Would I experience the same problem if I tried to expand the file while
> > there is free space in the file? In other words - does this problem
only
> > occur when there is 0 free space available?
> >
> > Thanks,
> > Tom
> >
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> >>
> >> "TJT" <TJT@.nospam.com> wrote in message
> >> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> >> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> > the
> >> > growthsize is approximately 7GB.
> >> >
> >> > Would an autogrow extent of that size cause a "standstill" for about
5
> >> > minutes?
> >> >
> >>
> >> Quite possible, depending on the other IO activity at the time.
> >>
> >> David
> >>
> >>
> >
> >
>|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> > We have all the files in our database set to autogrow by 10%. During
the
> > day we encountered a 5 minute time period in which SQL activity seemed
to
> > come to a halt. I suspect that during that time period, one of the
> > database
> > files grew.
> >
> > Is there any way that I could tell this by looking at a catalog table?
> >
> > Thanks in advance.
> >
> >
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> > Thanks for the reply Andrew. The file is currently 70GB so therefore
the
> > growthsize is approximately 7GB.
> >
> > Would an autogrow extent of that size cause a "standstill" for about 5
> > minutes?
> >
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> > Would I experience the same problem if I tried to expand the file while
> > there is free space in the file? In other words - does this problem
only
> > occur when there is 0 free space available?
> >
> > Thanks,
> > Tom
> >
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> >>
> >> "TJT" <TJT@.nospam.com> wrote in message
> >> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> >> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> > the
> >> > growthsize is approximately 7GB.
> >> >
> >> > Would an autogrow extent of that size cause a "standstill" for about
5
> >> > minutes?
> >> >
> >>
> >> Quite possible, depending on the other IO activity at the time.
> >>
> >> David
> >>
> >>
> >
> >
>|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>
Subscribe to:
Posts (Atom)