Hi
I have recently reinstalled XP Professional as well as the MSSQL client tool
s.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so f
or quite a while, no changes where done to the server while I reinstalled XP
on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting v
ia the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored procedu
res on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs
in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have
used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>
Showing posts with label ram. Show all posts
Showing posts with label ram. Show all posts
Sunday, February 26, 2012
Extremely Slow connection time
Hi
I have recently reinstalled XP Professional as well as the MSSQL client tools.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so for quite a while, no changes where done to the server while I reinstalled XP on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting via the Query Analyzer takes up to 2 minutes, and so do any other subsequent actions, for example opening the list of databases, tables or stored procedures on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!
Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>
I have recently reinstalled XP Professional as well as the MSSQL client tools.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so for quite a while, no changes where done to the server while I reinstalled XP on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting via the Query Analyzer takes up to 2 minutes, and so do any other subsequent actions, for example opening the list of databases, tables or stored procedures on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!
Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>
Friday, February 24, 2012
extremely bad performance running on huge box ?
hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
extremely bad performance running on huge box ?
hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.are there 4 physcial processors or is this a Xeon box with
Hyper-Threading,
if HT is enabled, then regardless of the number of actual
processors, try OPTION (MAXDOP 1) or 2
suppose your average row size is 100Bytes, then 20M rows
means the table is 2GB in size
a select count(*) requires a full table scan.
if your data is on a single disk, you can probably achieve
a sequential transfer rate of 50MB/sec, meaning your query
should take 40sec,
if you have 2 disks striped, you ought to be closer to
100MB/sec or 20sec.
if the entire table is in memory, the scan rate should be
800MB/sec,
so something is not right.
on the select *, i need to know the index and the full
query,
also keep in mind the time it QA to receive a large number
of rows
>--Original Message--
>hi all !!!
>I have a SQL Server 2000 running on a 4 processor 2 GB
ram DELL box running
>Windows 2003 Server. no other apps run in this machine.
>a table witn 20 million records is truncated and
recreated once every week.
>records are quite simple: (key,value). Table in indexes
on key field.
>the problem is thar running a "select count(*) from
table" takes more than 2
>minutes.
>even worst, doing a "select * from table where (condition
on indexed key
>field)" also takes more than 2 minutes.
>during these queries, which I do from the Query Analyzer,
the overall CPUs
>usage is less than 3%.
>what could be wrong ? any help would be greatly
appreciated.
>thanks in advance,
> ant.
>
>.
>|||Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.are there 4 physcial processors or is this a Xeon box with
Hyper-Threading,
if HT is enabled, then regardless of the number of actual
processors, try OPTION (MAXDOP 1) or 2
suppose your average row size is 100Bytes, then 20M rows
means the table is 2GB in size
a select count(*) requires a full table scan.
if your data is on a single disk, you can probably achieve
a sequential transfer rate of 50MB/sec, meaning your query
should take 40sec,
if you have 2 disks striped, you ought to be closer to
100MB/sec or 20sec.
if the entire table is in memory, the scan rate should be
800MB/sec,
so something is not right.
on the select *, i need to know the index and the full
query,
also keep in mind the time it QA to receive a large number
of rows
>--Original Message--
>hi all !!!
>I have a SQL Server 2000 running on a 4 processor 2 GB
ram DELL box running
>Windows 2003 Server. no other apps run in this machine.
>a table witn 20 million records is truncated and
recreated once every week.
>records are quite simple: (key,value). Table in indexes
on key field.
>the problem is thar running a "select count(*) from
table" takes more than 2
>minutes.
>even worst, doing a "select * from table where (condition
on indexed key
>field)" also takes more than 2 minutes.
>during these queries, which I do from the Query Analyzer,
the overall CPUs
>usage is less than 3%.
>what could be wrong ? any help would be greatly
appreciated.
>thanks in advance,
> ant.
>
>.
>|||Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>
Subscribe to:
Posts (Atom)