latest enabled version of every document (was "help with sql please!")

Hi, I'm having trouble with some SQL. I have a document table which links to a document version table. I'd like to write a query to get the latest enabled version of every document - a resultset with one row per document. Is this possible with resorting to a cursor?

CREATE TABLE tblDoc (
DocId int
)
GO

CREATE TABLE tblDocVersion (
DocVersionId int,
DocId int,
DateCreated datetime,
IsEnabled bit
)
GO

INSERT INTO tblDoc (DocId) VALUES (1)
INSERT INTO tblDoc (DocId) VALUES (2)

INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '9/9/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 1, '10/10/2006' , 0)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '8/8/2006' , 1)
INSERT INTO tblDocVersion (DocVersionId, DocId, DateCreated, IsEnabled) VALUES (1, 2, '11/11/2006' , 1)

Desired output:

DocId DocVersionId DateCreated
============================
1 2 9/9/2006
2 5 11/11/2006

Thanks!
MrsMUse a subquery:

select tblDocVersion.DocId,
tblDoVersion.DocVersionID,
tblDocVersion.DateCreated
from tblDocVersion
inner join --LastVersions
(select DocId,
max(DateCreated) as DateCreated
from tblDocVersion
group by DocId) LastVersion
on tblDocVersion.DocId = LastVersion.DocID
and tblDocVersion.DateCreated = LastVersion.DateCreated

By the way...why do you prefix your table name with "tbl"? Are you afraid you are going to forget what it is? Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?|||ah nice, joining on a sub query - never thought of that, thanks a lot.

the tbl thing is part of the standards at my company. doesn't do anyone any harm!

thanks again|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!that's highly doubtful

not the "part of the standards at my company" part

the other part

:)|||Did your husbandBOB call and remind you to pick up some petfoodALPO for your dogFIDO today?

hah! :)

i always hated hungarian. intellisense seems to be slowly killing it off in the world of compiled code thankfully. from the bible of umaintainable code (http://mindprod.com/jgloss/unmain.html):

a_crszkvc30LastNameCol:

"It took a team of maintenance engineers nearly 3 days to figure out that this whopper variable name described a const, reference, function argument that was holding information from a database column of type Varchar[30] named "LastName" which was part of the table's primary key"|||the tbl thing is part of the standards at my company. doesn't do anyone any harm!Why don't you suggest a standard that all desktop PCs be painted schoolbus yellow, all laptops be candy-striped red, and all printers have blue polka-dots? Also harmless, and just as sensible.|||I knew one team at MS that had a convention of putting _sp at the end of every sproc.

Presumably this was because they knew there was some downside to putting sp_ at the front.

I'm sure it really helped them distingush procs from tables, which, btw, all had _tbl at the end. ;)

last read/write time of table

Is there a way to get the last read/write time of a table?

I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.

I think that unless you were to add a datetime column to each table, that information will be difficult to obtain.

For you purposes, it may be useful to add a datetime column, DEFAULT getdate(). If the application never provides a value, the server will always provide the current date/time on entry. UPDATEs would require a trigger.

You 'could' add a trigger to each table that updates a single field in a maintenance table. -Probably not too good of an idea though...

|||In addition to Arnies response and for the sake that you really need to know the last modification date, you could use a log reader to determine the last modification date. SQL Server does not provide an easy way to read the logs, therefore you would probably need to use a thrid party component.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Last Month-to-Last-Child

I am trying to write a calculation to give a Last Month reference, I use the following MDX and it works fine for the Day level

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month], 1,
[Time].[Date with Month].CurrentMember
)

However for Month level, I do not want it to reference to the last month value directly. What I want is, for example, today is "7th May 2007", I want the Last Month Reference for "May 2007 " to be the sum of "1st Apr 2007" to "7th Apr 2007" instead of the sum of the whole "Apr 2007".

Does anyone have any idea on how to archive this? Thanks in advance!

Assuming that all days in a month till the last day with data should be included, something like:

Aggregate(

CrossJoin({[Time].[Month Comparison].DefaultMember},
PeriodsToDate([Time].[Date with Month].CurrentMember.Level,

ParallelPeriod([Time].[Date with Month].[Month], 1,
Tail(NonEmpty(Descendants([Time].[Date with Month].CurrentMember,
[Time].[Date with Month].[Day])).Item(0).Item(0))))))

Last Day of Previous Month

I am trying to write a formula that will show all records where a date variable is in the month to date or the last day of the previous month. Any ideas?Hi Please Try this syntax

If {table.date} in LastFullMonth Then

{table.date}

Else

0

Its Returns the Value if the date falls on any date from the previous month.

if today is 07/21/2005

its returns 06/30/2005|||I think that will show all days in the previous month, I just need the last day of the previous month. Any ideas?|||Just in case anyone else is having a similar problem I figured out how to calculate the last day of the previous month. This formula will calculate the last day of the month before the current month:

Global datetimevar firstDate:= datetime(year(CurrentDateTime), month(CurrentDateTime),1,0,0,0) ;
Global datetimevar lastMonth:= dateadd("m",+0,firstDate);
lastMonth:=dateadd("d",-1, lastMonth);

Thanks for your help tks_muthu!

Large transaction log causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
> ****************************************
******************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
> ****************************************
******************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
other
this
was
the
again,
which
to 1
for a
help
>
>.
>

Large transaction log causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
>**********************************************************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
>**********************************************************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
>> Hi,
>> I really would appreciate any help on this. Can a very
>> large transaction log have a negative impact on write
>> performance' We were having a problem with VERY slow
>> writes (30 seconds each) to a database. Reads were very
>> fast (0 ms). The app is running on a Windows 2000/SQL
>> 2000 Active/Passive cluster. Each server has 2 G of ram
>> and disk space is absolutely not an issue. The only
other
>> applications that run on the server are Mcaffee (but
this
>> is disabled from the data/log directories).
>> We noticed that the log file for the affected database
was
>> EXTREMELY large (3G). TONS of leftover disk space for
the
>> log. There's not a whole lot of updates that occur in
>> this database. The database was in Full recovery mode.
>> We usually rely on nightly backups due to the sparse
>> update/inserts/deletes.
>> After encountering this problem, I failed over the node
>> (restarted the SQL services, and writes became fast
again,
>> but this was only temporary (lasted a couple hours),
which
>> led me to believe that it could be a memory issue.
>> Anyway, after that, I made the following changes: 1.
>> Backed up the database, then the log, shrank the log
>> (now .99MB) and temporarily put set the recovery mode to
>> simple. 2. Reduced the max SQL server memory setting
to 1
>> G.
>> Writes are again very fast. I have seen no problems
for a
>> day and a half under a normal load. It would really
help
>> to know if the large tlog could have actually been the
>> problem? Or the if the memory settings may have done
>> the trick and why? Then we can rest assured that the
>> issue will not crop
>> up again later!
>> Thanks,
>> Chris
>
>.
>

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?
On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:
>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et=
c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KS
KS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
Im not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until thats done we have to deal with the following problem.
Our site is busiest over the weekend. Its not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but itd be
nice to know where to start ?
Thank you for your time
KS
|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again
|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS
|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegr oups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>
|||Thank you both
|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:

> Hello,
> I’m not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until that’s done we have to deal with the following problem.
> Our site is busiest over the weekend. It’s not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but it’d be
> nice to know where to start ?
> Thank you for your time
> KS
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et= c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KSKS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I?m not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that?s done we have to deal with the following problem.
Our site is busiest over the weekend. It?s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it?d be
nice to know where to start ?
Thank you for your time
KS|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegroups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>|||Thank you both :)|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:
> Hello,
> Iâ'm not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until thatâ's done we have to deal with the following problem.
> Our site is busiest over the weekend. Itâ's not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but itâ'd be
> nice to know where to start ï'
> Thank you for your time
> KS
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et=
c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KSKS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
Im not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until thats done we have to deal with the following problem.
Our site is busiest over the weekend. Its not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but itd be
nice to know where to start ?
Thank you for your time
KS|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegroups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>|||Thank you both |||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and th
e
sp_blocker_pss80 script. If you are looking for a third party monitoring too
l
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:

> Hello,
> I’m not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until that’s done we have to deal with the following problem.
> Our site is busiest over the weekend. It’s not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but it’d be
> nice to know where to start ?
> Thank you for your time
> KS
>