Is this use of shrinkfile safe?

Hi all,
I've been hunting around, but haven't found a satisfactory answer
yet, so am hoping one of you can help me.
On a production database on a SQL 2K server with Full recovery mode,
we do a daily differential backup on the database. Then, weekly, we do
a full backup on both the database and log (without truncateonly).
I understand that these themselves don't shrink the physical file, so
am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
the log after the weekly full backup, or if the potential exists to
lose data if we need to do a restore.
Thanks,
-Phil M
You can do a shrinkfile if you wish, but you have to ask yourself if its
worth the performance impact of doing so, bearing in mind that your files
have grown to a certain size, and probably will continue to do so each week.
When sql server performs an automatic growth, it is inherently slow and much
slower than performing a manual grow of a file. Even better for performance
is just to leave the files as they are, knowing the space will get used up by
the end of the week anyway.
To answer your question: yes it is safe. you wont lose data unless something
catastrophic occurs.
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"fuiru2000@.yahoo.com" wrote:

> Hi all,
> I've been hunting around, but haven't found a satisfactory answer
> yet, so am hoping one of you can help me.
> On a production database on a SQL 2K server with Full recovery mode,
> we do a daily differential backup on the database. Then, weekly, we do
> a full backup on both the database and log (without truncateonly).
> I understand that these themselves don't shrink the physical file, so
> am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
> the log after the weekly full backup, or if the potential exists to
> lose data if we need to do a restore.
> Thanks,
> -Phil M
>
|||fuiru2000@.yahoo.com wrote:
> Hi all,
> I've been hunting around, but haven't found a satisfactory answer
> yet, so am hoping one of you can help me.
> On a production database on a SQL 2K server with Full recovery mode,
> we do a daily differential backup on the database. Then, weekly, we do
> a full backup on both the database and log (without truncateonly).
> I understand that these themselves don't shrink the physical file, so
> am wondering if it is safe to (occasionally) do a dbcc shrinkfile on
> the log after the weekly full backup, or if the potential exists to
> lose data if we need to do a restore.
> Thanks,
> -Phil M
Why do you do both a database backup and log backup weekly? That sounds
pointless to me. If you don't know the difference between log and full
backups then read about them in Books Online. If you don't require log
backups (it sounds like you don't) then select the Simple Recovery
model. In any case shrinking regularly in a production system is a very
bad idea and it isn't necessary.
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
|||I'm not a DBA, and didn't design the backup software, so can't speak as
to why it's done that way; I'm simply looking at a complaint someone
has about disk usage.
I'm not sure how I gave the impression that we don't need log backups.
The database is part of an archive of rather important information, so
I'm assuming that, if there's any redundancy in doing the incremental
and full backups this way, there's a good reason. Or, perhaps there was
in some previous incarnation of SQL or another DBMS. I'll ask the guy
responsible, though.
The thing I think the person who is reporting the disk usage problem is
worried about is the fact that, after doing a shrinkfile yesterday, the
log was 1.8 GB (at ~12% use, if I remember what SQLPERF reported) to 8
GB today after an incremental, with about 96% usage. I think it's
gotten up as high as 30 GB.
I may end up telling them to live with it, if I can determine that that
log file won't grow beyond a certain size. That, or he'll need to move
the log file. If he presses the issue, though, I just want to know if
throwing in the shrinkfile every week or two is safe, which it sounds
like it is.
In any case, it appears I have a few options at this point, so I'll see
where it goes from here. Thank you both for your help.
-Phil
|||No. I don't think you get the difference between a DB backup, a
differential backup and a transaction log backup (it may take a bit of
reading, but Books Online has this all pretty clearly documented). In
your scenario the tlog backups are essentially useless since you're
doing differential backups daily, so there's no point (as David said) in
doing the tlog backups weekly (when restoring, tlog backups get restored
AFTER the latest differential). Differential backups do not truncate
the transaction log. This is why it continues to grow until the tlog
backup that occurs once a week.
The answer is, rather than doing differentials daily, do transaction log
backups daily and forget about the differentials (this will keep the
tlog much smaller since you're truncating it every day rather than once
a week). Alternately, if you're doing weekly DB backups and daily
differentials, then do tlog backups more frequently than daily (ie. the
differential backup frequency), for example hourly or 4-hourly or
6-hourly (depending on how small you wish to keep your tlog).
With the way it currently is, if you ever have to restore, you'll
restore your latest DB backup, then the most recent differential backup
(that was taken after the DB backup), then every tlog backup that has
occurred since that differential backup, up to the point in time you
wish to restore to (presumedly as most recent as possible to minimise
data loss). If your tlog backups are only done once a week (right after
the DB backup) then they'll essentially be useless as they won't contain
any transactions that the DB backup doesn't contain and they're not even
as current as your subsequent differentials so you won't be able to use
them at all when you restore.
Basically, the tlog backups are what keep you transaction log trim (they
automatically truncate the transaction log so the space can be reused by
future transactions). If it's growing too fat then do tlog backups more
often or don't keep the transaction log at all (ie. simple recovery
mode). My recommendation would be weekly DB backups, daily diff backups
and hourly tlog backups, OR weekly DB backups and daily tlog backups.
(The production DBs where I worked are on a daily DB backup/15 min tlog
backup schedule to minimise data loss to, at most, 15 minutes and keep
the tlog files nice and small.)
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
fuiru2000@.yahoo.com wrote:

>I'm not a DBA, and didn't design the backup software, so can't speak as
>to why it's done that way; I'm simply looking at a complaint someone
>has about disk usage.
>I'm not sure how I gave the impression that we don't need log backups.
>The database is part of an archive of rather important information, so
>I'm assuming that, if there's any redundancy in doing the incremental
>and full backups this way, there's a good reason. Or, perhaps there was
>in some previous incarnation of SQL or another DBMS. I'll ask the guy
>responsible, though.
>The thing I think the person who is reporting the disk usage problem is
>worried about is the fact that, after doing a shrinkfile yesterday, the
>log was 1.8 GB (at ~12% use, if I remember what SQLPERF reported) to 8
>GB today after an incremental, with about 96% usage. I think it's
>gotten up as high as 30 GB.
>I may end up telling them to live with it, if I can determine that that
>log file won't grow beyond a certain size. That, or he'll need to move
>the log file. If he presses the issue, though, I just want to know if
>throwing in the shrinkfile every week or two is safe, which it sounds
>like it is.
>In any case, it appears I have a few options at this point, so I'll see
>where it goes from here. Thank you both for your help.
>-Phil
>
>

Is this collation correct?

Hi Everyone,
I've taken over this database and asked to optimize performance. I noticed
that we have 'SQL_Latin1_General_CP1.CI_AS' set for out database collation.
We collect data from two points in the USA and in Hong Kong.
Question: Is this the best collation to use for this environment?
Thanks in advance.
Larry
That is the default collation for SQL Server and probably will meet your
needs fine.
Your life will probably be easier if all the servers are using the same
collation.
You won't get a performance boost from using a different collation.
Brian
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:4E8914BF-B1C8-4CC7-9994-5BDB1656E664@.microsoft.com...
> Hi Everyone,
> I've taken over this database and asked to optimize performance. I
noticed
> that we have 'SQL_Latin1_General_CP1.CI_AS' set for out database
collation.
> We collect data from two points in the USA and in Hong Kong.
> Question: Is this the best collation to use for this environment?
> Thanks in advance.
> Larry
|||Note that that collation can only store Chinese text in Unicode fields
(nvarchar, nchar). You should use Unicode data types if you have an app
with international users. If your schema currently does not use Unicode
data types, though, you should probably be using a Chinese collation so
that you can store Chinese non-Unicode text safely. This recommendation
doesn't have anything to do with performance. As Brian mentioned,
collation isn't the right place to start if you're looking to optimize
performance.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Brian Moran" <brian@.solidqualitylearning.com>
| References: <4E8914BF-B1C8-4CC7-9994-5BDB1656E664@.microsoft.com>
| Subject: Re: Is this collation correct?
| Date: Fri, 17 Sep 2004 09:47:44 -0400
| Lines: 30
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <OxXUrzLnEHA.132@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: pcp02581462pcs.reston01.va.comcast.net 68.50.27.82
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:360329
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| That is the default collation for SQL Server and probably will meet your
| needs fine.
|
| Your life will probably be easier if all the servers are using the same
| collation.
|
| You won't get a performance boost from using a different collation.
|
| --
|
| Brian
|
|
| "Larry" <Larry@.discussions.microsoft.com> wrote in message
| news:4E8914BF-B1C8-4CC7-9994-5BDB1656E664@.microsoft.com...
| > Hi Everyone,
| >
| > I've taken over this database and asked to optimize performance. I
| noticed
| > that we have 'SQL_Latin1_General_CP1.CI_AS' set for out database
| collation.
| > We collect data from two points in the USA and in Hong Kong.
| >
| > Question: Is this the best collation to use for this environment?
| >
| > Thanks in advance.
| >
| > Larry
|
|
|

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 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

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

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

is there no way ?

is there no way i could retrieve value from a trigger variable to my
dataset
i mean ive been on it like a hound for two days and not a single
article or post that really helped me.
heres what i have understood till now.
ALTER TRIGGER lastserial
ON dbo.master
FOR INSERT
AS
begin
select serialcode = @.@.IDENTITY
end
now how do i retrieve this serialcode value to my application in vb.netHere is one way:
=====
CREATE TABLE Foo
(
colA VARCHAR(10)
)
GO
CREATE TRIGGER FooTrigger ON Foo
FOR INSERT AS
BEGIN
SELECT 'Srinivas'
END
GO
=====
The above creates a SQL Server table and a trigger on the same. Now, here is
the C#.NET code that puts a record into the table and reads the output of
the trigger into the application code and prints it out. The example uses
.NET 1.1 version
=====
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
using (SqlConnection oConn = new
SqlConnection("Server=tl- devdb\\matrix;Database=pubs;Uid=sa;Pwd=p
@.ssw0rd"))
{
SqlCommand oCmd = new SqlCommand();
SqlDataReader rdr;
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = "INSERT INTO Foo VALUES ('Sampath')";
oConn.Open();
try
{
rdr = oCmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine("{0}", rdr.GetString(0));
rdr.Close();
}
catch (SqlException e)
{
Console.WriteLine (e.Message);
}
finally
{
oConn.Close();
}
Console.Read();
}
}
}
}
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<prabodhtiwari@.gmail.com> wrote in message
news:1140425436.598510.175750@.f14g2000cwb.googlegroups.com...
> is there no way i could retrieve value from a trigger variable to my
> dataset
> i mean ive been on it like a hound for two days and not a single
> article or post that really helped me.
> heres what i have understood till now.
> ALTER TRIGGER lastserial
> ON dbo.master
> FOR INSERT
> AS
> begin
> select serialcode = @.@.IDENTITY
> end
> now how do i retrieve this serialcode value to my application in vb.net
>|||but how do you retrieve the value 'sampath' only using the datareader|||I thought your question was to retrieve what the trigger was SELECTing into,
which is what this example shows.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23m7Nc4fNGHA.1460@.TK2MSFTNGP10.phx.gbl...
> Here is one way:
> =====
> CREATE TABLE Foo
> (
> colA VARCHAR(10)
> )
> GO
> CREATE TRIGGER FooTrigger ON Foo
> FOR INSERT AS
> BEGIN
> SELECT 'Srinivas'
> END
> GO
> =====
> The above creates a SQL Server table and a trigger on the same. Now, here
> is the C#.NET code that puts a record into the table and reads the output
> of the trigger into the application code and prints it out. The example
> uses .NET 1.1 version
> =====
> using System;
> using System.Data;
> using System.Data.SqlClient;
> namespace ConsoleApplication1
> {
> class Class1
> {
> [STAThread]
> static void Main(string[] args)
> {
> using (SqlConnection oConn = new
> SqlConnection("Server=tl- devdb\\matrix;Database=pubs;Uid=sa;Pwd=p
@.ssw0rd")
)
> {
> SqlCommand oCmd = new SqlCommand();
> SqlDataReader rdr;
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.Text;
> oCmd.CommandText = "INSERT INTO Foo VALUES ('Sampath')";
> oConn.Open();
> try
> {
> rdr = oCmd.ExecuteReader();
> while (rdr.Read())
> Console.WriteLine("{0}", rdr.GetString(0));
> rdr.Close();
> }
> catch (SqlException e)
> {
> Console.WriteLine (e.Message);
> }
> finally
> {
> oConn.Close();
> }
> Console.Read();
> }
> }
> }
> }
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> <prabodhtiwari@.gmail.com> wrote in message
> news:1140425436.598510.175750@.f14g2000cwb.googlegroups.com...
>