Hi Friends,
This is my first post here. Hope for a good response from the gurus ;-)
here it is...
We have a medium size database (upto 50 tables) out of which one table is a massive. It gets populated online at very high speed (> 20,000 records a day) so it grows quite large in month. Now the problem is when it grows to that extent in a month at the end of 2 months any query fired on it takes toooo long to execute (to the magnitude of 10 minutes ). We have following hardware configuration
1. 1.2 GHz Pentium 4
2. 40 GB HDD
3. 384 mb RAM.
What should be the server configuration to boost the performance of the database??
Regards,
Vishal.It may not be your hardware configuration. How often do you Reorg the database?|||Currently we only backup the table( The Big one) and trim it to achieve the speed. Is this what U R talking about?? Is there any thing else we need to do in order to reorg the db??|||after 2 months you should have something like 1200000 recrods yes?
what sort of data is in these records?
what indexes do you have on the table?
what are the searches being performed?
I imagine that if you applied some suitable indexes and possibly did some restructuring you should have not problems with this amount of data.
Yes, eventually it will slow as more data comes on board but no where near the 10 mins you are talking about....|||What rokslide mentioned will help greatly. But Reorg is a quick fix to boost performance for a db with large amount of reads and writes because it rearrange the data and index pages. To do a reorg just simply use Database Maintenance Plan Wizard and check the Reorganize Data and Index Page. Execute the plan once a month, it will greatly help the performance of your database.
Showing posts with label friends. Show all posts
Showing posts with label friends. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Hard Drive Space and Disk Caching
Dear friends
I have what may be a basic question to most folks here. Does SQL manage disk caching across partitions or hard drives for greatest efficiency
Basically I have a small C drive partition reserved for basic operating systems stuff. All program and data files, including SQL and databases are on the much larger D and E drives. Would there ever be an issue of the C drive not being large enough and causing SQL to slow down or not perform as well
Someone told me that all disk caching happens on the C drive, which doesn't make sense to me. It seems to me that SQL would be smart enough to use other physical drive resources as needed. They are encouraging us to use Partition Magic to expand the size of our C drive (which currently has about 1 Gig available, to be expanded to between 5 and 10 Gig)
Does this seem like good advice?I do not understand what they are saying - by definition caching is =holding data locally for fast access, SQL Server holds data in memory =having read it from disc. What drive letter is utterly irrelevant to it =(and from a data management perspective the whole concept of drive =letters is horrid!)
SQL Server caching is fine across any drive letter - it cares little =about such things. I have one system where C is getting really tight =(due to successive service packs and fixes) but all the SQL server =stuff, pagefile etc is elsewhere and we have noticed no performance =problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> > I have what may be a basic question to most folks here. Does SQL =manage disk caching across partitions or hard drives for greatest =efficiency?
> > Basically I have a small C drive partition reserved for basic =operating systems stuff. All program and data files, including SQL and =databases are on the much larger D and E drives. Would there ever be an =issue of the C drive not being large enough and causing SQL to slow down =or not perform as well?
> > Someone told me that all disk caching happens on the C drive, which =doesn't make sense to me. It seems to me that SQL would be smart enough =to use other physical drive resources as needed. They are encouraging =us to use Partition Magic to expand the size of our C drive (which =currently has about 1 Gig available, to be expanded to between 5 and 10 =Gig).
> > Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do not have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in that case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
I have what may be a basic question to most folks here. Does SQL manage disk caching across partitions or hard drives for greatest efficiency
Basically I have a small C drive partition reserved for basic operating systems stuff. All program and data files, including SQL and databases are on the much larger D and E drives. Would there ever be an issue of the C drive not being large enough and causing SQL to slow down or not perform as well
Someone told me that all disk caching happens on the C drive, which doesn't make sense to me. It seems to me that SQL would be smart enough to use other physical drive resources as needed. They are encouraging us to use Partition Magic to expand the size of our C drive (which currently has about 1 Gig available, to be expanded to between 5 and 10 Gig)
Does this seem like good advice?I do not understand what they are saying - by definition caching is =holding data locally for fast access, SQL Server holds data in memory =having read it from disc. What drive letter is utterly irrelevant to it =(and from a data management perspective the whole concept of drive =letters is horrid!)
SQL Server caching is fine across any drive letter - it cares little =about such things. I have one system where C is getting really tight =(due to successive service packs and fixes) but all the SQL server =stuff, pagefile etc is elsewhere and we have noticed no performance =problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> > I have what may be a basic question to most folks here. Does SQL =manage disk caching across partitions or hard drives for greatest =efficiency?
> > Basically I have a small C drive partition reserved for basic =operating systems stuff. All program and data files, including SQL and =databases are on the much larger D and E drives. Would there ever be an =issue of the C drive not being large enough and causing SQL to slow down =or not perform as well?
> > Someone told me that all disk caching happens on the C drive, which =doesn't make sense to me. It seems to me that SQL would be smart enough =to use other physical drive resources as needed. They are encouraging =us to use Partition Magic to expand the size of our C drive (which =currently has about 1 Gig available, to be expanded to between 5 and 10 =Gig).
> > Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do not have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in that case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
Hard Drive Space and Disk Caching
Dear friends,
I have what may be a basic question to most folks here. Does SQL manage dis
k caching across partitions or hard drives for greatest efficiency?
Basically I have a small C drive partition reserved for basic operating syst
ems stuff. All program and data files, including SQL and databases are on t
he much larger D and E drives. Would there ever be an issue of the C drive
not being large enough and
causing SQL to slow down or not perform as well?
Someone told me that all disk caching happens on the C drive, which doesn't
make sense to me. It seems to me that SQL would be smart enough to use othe
r physical drive resources as needed. They are encouraging us to use Partit
ion Magic to expand the siz
e of our C drive (which currently has about 1 Gig available, to be expanded
to between 5 and 10 Gig).
Does this seem like good advice?I do not understand what they are saying - by definition caching is =
holding data locally for fast access, SQL Server holds data in memory =
having read it from disc. What drive letter is utterly irrelevant to it =
(and from a data management perspective the whole concept of drive =
letters is horrid!)=20
SQL Server caching is fine across any drive letter - it cares little =
about such things. I have one system where C is getting really tight =
(due to successive service packs and fixes) but all the SQL server =
stuff, pagefile etc is elsewhere and we have noticed no performance =
problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
>=20
> I have what may be a basic question to most folks here. Does SQL =
manage disk caching across partitions or hard drives for greatest =
efficiency?
>=20
> Basically I have a small C drive partition reserved for basic =
operating systems stuff. All program and data files, including SQL and =
databases are on the much larger D and E drives. Would there ever be an =
issue of the C drive not being large enough and causing SQL to slow down =
or not perform as well?
>=20
> Someone told me that all disk caching happens on the C drive, which =
doesn't make sense to me. It seems to me that SQL would be smart enough =
to use other physical drive resources as needed. They are encouraging =
us to use Partition Magic to expand the size of our C drive (which =
currently has about 1 Gig available, to be expanded to between 5 and 10 =
Gig).
>=20
> Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do no
t have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in th
at case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
I have what may be a basic question to most folks here. Does SQL manage dis
k caching across partitions or hard drives for greatest efficiency?
Basically I have a small C drive partition reserved for basic operating syst
ems stuff. All program and data files, including SQL and databases are on t
he much larger D and E drives. Would there ever be an issue of the C drive
not being large enough and
causing SQL to slow down or not perform as well?
Someone told me that all disk caching happens on the C drive, which doesn't
make sense to me. It seems to me that SQL would be smart enough to use othe
r physical drive resources as needed. They are encouraging us to use Partit
ion Magic to expand the siz
e of our C drive (which currently has about 1 Gig available, to be expanded
to between 5 and 10 Gig).
Does this seem like good advice?I do not understand what they are saying - by definition caching is =
holding data locally for fast access, SQL Server holds data in memory =
having read it from disc. What drive letter is utterly irrelevant to it =
(and from a data management perspective the whole concept of drive =
letters is horrid!)=20
SQL Server caching is fine across any drive letter - it cares little =
about such things. I have one system where C is getting really tight =
(due to successive service packs and fixes) but all the SQL server =
stuff, pagefile etc is elsewhere and we have noticed no performance =
problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
>=20
> I have what may be a basic question to most folks here. Does SQL =
manage disk caching across partitions or hard drives for greatest =
efficiency?
>=20
> Basically I have a small C drive partition reserved for basic =
operating systems stuff. All program and data files, including SQL and =
databases are on the much larger D and E drives. Would there ever be an =
issue of the C drive not being large enough and causing SQL to slow down =
or not perform as well?
>=20
> Someone told me that all disk caching happens on the C drive, which =
doesn't make sense to me. It seems to me that SQL would be smart enough =
to use other physical drive resources as needed. They are encouraging =
us to use Partition Magic to expand the size of our C drive (which =
currently has about 1 Gig available, to be expanded to between 5 and 10 =
Gig).
>=20
> Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do no
t have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in th
at case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
Monday, February 27, 2012
Handling Transaction
hi friends,
I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.
When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.
Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.
I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...
JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,
thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.
But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?
Jake
Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.
I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.
When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.
Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.
I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...
JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,
thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.
But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?
Jake
Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.
Subscribe to:
Posts (Atom)