Maintaing 10GB Database?

Hi Experts,

I'm trying to query data from a database of size 10GB. But it is taking too much time to get the data.

I'm uing SQL Server 2005, Xeon 4 processor Server.

The database sits on a single machine.

I don't have clear Idea about scaling.

Is it need to scale-out My database.>>>?

Please could you provide more information to help track down the problem? It will be difficult to track down the cause of the poor performance you are seeing without more information.

It would be useful if you could provide the definitions of your tables and indexes, examples of poor-performing queries and sample data. Also, an indication of the number of rows in your tables would be helpful.

Chris

|||

You need to give us some more details so we can give you some useful answers. More details about the hardware, about what symptoms you are seeing, what type of workload that you have, etc.

If you read my blog post (below), it shows some easy steps for gathering some basic information about your hardware and your environment, that will serve as a starting point.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!424.entry

|||

Thanks for the reply

More details means,

I need to maintain data of around 500 tables. some tables will have 500000 records,

Maintaining data of an enterprise , like Customers,orders,warehouse etc.

How to maintain such type of database? Any Idea plz?

Maintaing 10GB Database

Hi Experts,

I'm trying to query data from a database of size 10GB. But it is taking too much time to get the data.

I'm uing SQL Server 2005, Xeon 4 processor Server.

The database sits on a single machine.

I don't have clear Idea about scaling.

Is it need to scale-out My database.>>>?

Please could you provide more information to help track down the problem? It will be difficult to track down the cause of the poor performance you are seeing without more information.

It would be useful if you could provide the definitions of your tables and indexes, examples of poor-performing queries and sample data. Also, an indication of the number of rows in your tables would be helpful.

Chris

|||

You need to give us some more details so we can give you some useful answers. More details about the hardware, about what symptoms you are seeing, what type of workload that you have, etc.

If you read my blog post (below), it shows some easy steps for gathering some basic information about your hardware and your environment, that will serve as a starting point.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!424.entry

|||

Thanks for the reply

More details means,

I need to maintain data of around 500 tables. some tables will have 500000 records,

Maintaining data of an enterprise , like Customers,orders,warehouse etc.

How to maintain such type of database? Any Idea plz?

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.
Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system."
Excuse me if this is not necessary, but have you tested the mail agent in SQL Server (SQL Mail) to determine if it is configured correctly and can send mail?
Another option is to cut and paste the sendmail in the stored procedure to find out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails and for other reasons. I haven't encountered this particular error message, but maybe my suggestions will help?
Jon
Message posted via http://www.sqlmonster.com

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that al
low SQL Server to operate as a workgroup post office for a MAPI-enabled e-ma
il system."
Excuse me if this is not necessary, but have you tested the mail agent in SQ
L Server (SQL Mail) to determine if it is configured correctly and can send
mail?
Another option is to cut and paste the sendmail in the stored procedure to f
ind out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails an
d for other reasons. I haven't encountered this particular error message, b
ut maybe my suggestions will help?
Jon
Message posted via http://www.droptable.com

mail stored procedure- message -2147220975

i'm using a stored procedure that sends mail using the OA extended
procedures. it works fine on a number of servers.
Using Query analyzer, when executed the above message number shows up on the
messages tab. What's this? I've verified the stored procedure compiled and
the syntax check passes.Using "xp_sendmail"
"Microsoft? SQL Server? provides a set of extended stored procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system."
Excuse me if this is not necessary, but have you tested the mail agent in SQL Server (SQL Mail) to determine if it is configured correctly and can send mail?
Another option is to cut and paste the sendmail in the stored procedure to find out if it for any syntac errors.
I use xp_sendmail in several stored procedures to alert me if a job fails and for other reasons. I haven't encountered this particular error message, but maybe my suggestions will help?
Jon
--
Message posted via http://www.sqlmonster.com

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
Additional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com
|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
--
Message posted via http://www.sqlmonster.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything to
do with AV scan! Does it? What kind of configuration does the AV needs so it
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A7C2ADFB58F@.SQLMonster.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.sqlmonster.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. So
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1

Machine hangs after SQL QA query

I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast 2GB)
.
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.comAdditional Info:
This host has AntiVirus enabled. But I dont think the MDB etc. are excluded
from scanning. But I dont think the QA query mentioned would have anything t
o
do with AV scan! Does it? What kind of configuration does the AV needs so i
t
both does its work properly, but at the same time does not hinder my SQL
server work?
-philipus
Philipus wrote:
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast 2GB
).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Can you open the SQL Server error log from the windows explorer? If the
database is still recovering you will see messages related to that in the
error log. I think you should just let the database recover.
Do you happen to have a backup that is good enough to replace the current
database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Philipus via droptable.com" <forum@.droptable.com> wrote in message
news:51A7C2ADFB58F@.droptable.com...
I tried to delete some records from a table that has 10 million records
(delete <table> where xxx= 'yyy'). This does not have any matching index. So
it used up all the memory (500 MB) ,and the swap(virtual mem), and the whole
machine hanged.
Then today, I stopped the query, and waited for hours...
When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CPU
and huge memory (both). I believe its still rolling back the 'delete'
transactions.
Is there any way to clean up the resource, and start using the machine in a
normal way? (Im also in the process of increasing the memory to atleast
2GB).
Pl. note that I may not be able to open the EM or QA due to the underlying
heavy load/recovery process(?)...
Thanks a lot
Philipus
Message posted via http://www.droptable.com|||Since I was not able to do anything in the machine, I just left it for
another day, and it came up after clearing the rollback. Now its normal. The
error log has a huge list of ' roll back...recovering...'
Thanks reddy!
philipus
Narayana Vyas Kondreddi wrote:
>Can you open the SQL Server error log from the windows explorer? If the
>database is still recovering you will see messages related to that in the
>error log. I think you should just let the database recover.
>Do you happen to have a backup that is good enough to replace the current
>database?
>I tried to delete some records from a table that has 10 million records
>(delete <table> where xxx= 'yyy'). This does not have any matching index. S
o
>it used up all the memory (500 MB) ,and the swap(virtual mem), and the whol
e
>machine hanged.
>Then today, I stopped the query, and waited for hours...
>When I rebooted, it took hours to come up, and the sqlsrvr runs with 50% CP
U
>and huge memory (both). I believe its still rolling back the 'delete'
>transactions.
>Is there any way to clean up the resource, and start using the machine in a
>normal way? (Im also in the process of increasing the memory to atleast
>2GB).
>Pl. note that I may not be able to open the EM or QA due to the underlying
>heavy load/recovery process(?)...
>Thanks a lot
>Philipus
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1