Is anyone aware of performance degradation writing to a SAN over fibre optic network? We r using SQL Server 2000 (Enterprise version) with Compaq Proliant DL580 G2, writing to Compaq SAN. Is any network tuning needed? A gigabit ethernet card pushes the data over to the SAN.
Also, we r taking about 37 hours building a cube for a database barely 15GB. Database is shipped by Microsoft and is used by their product Microsoft Proect Server. So, database level tuning is out of question.
Any help would greatly be appreciated.
Thanx in advance,
BhushanYou should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...
*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?
basically it comes down to finding the bottleneck and then fixing it.|||Originally posted by Pigeon
You should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...
*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?
basically it comes down to finding the bottleneck and then fixing it.
Thanx a lot, Pigeon. I did check the Queue length originally; but was told by Compaq that the Queue length reported by Windows 2000 (for Compaq hardware) is not reliable for various reasons.
Anway! I personally think NIC card is slow and thats why I am seeing NETWORKIOWAIT within SQL Server. Just wanted to confirm, if I am not missing anything...
Thanx again.
- Bhushan|||The NIC can be a big bottleneck. WHat protocol does it use to send the data across the network? UDP, TCP, is it using a SCSI miniport driver (Encapsulating scsi calls into a udp or tcp packet) Many times, if this driver or protocol is not working, it will cause an increase in processor utilization, or the disk queue will be high. No matter what compaq tells you, if your queue is high, something is stopping the traffic from getting to the disk. Good Luck with it. I know these types of problems can sometimes be a real pain to find.
Showing posts with label network. Show all posts
Showing posts with label network. Show all posts
Monday, March 26, 2012
Monday, February 20, 2012
Perdormance Issues Querying a table over the network
Greetings,
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
Sagi Shkedy
http://blog.shkedy.com
I've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
Sagi Shkedy
http://blog.shkedy.com
I've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
Perdormance Issues Querying a table over the network
Greetings,
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
Sagi Shkedy
http://blog.shkedy.comI've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
Sagi Shkedy
http://blog.shkedy.comI've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
Perdormance Issues Querying a table over the network
Greetings,
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
--
Sagi Shkedy
http://blog.shkedy.comI've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
--
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
When I remote in to the server with the DB installed on it and run a query
it runs in less than a second.
When I run the same query from my remote machine it takes anywhere from 45
seconds to a minute and a half.
The table has less than 10 records in it and the query is a simple select
query (SELECT * FROM [aspnet_Profile])
I can run another query on the same database from the remote machine that
will return much more data (100s of heavier rows) on the same database and
the same connection and it will run much much faster.
I have no idea where to even start troubleshooting this.
Any help would be appreciated.
--
Sagi Shkedy
http://blog.shkedy.comI've seen this a lot when your workstation (or the machine that's doing
the querying) has a lot of CPU load. Take a look at Task Manager and
make sure there's nothing taking up your CPU.
--
-Dave Markle
http://www.markleconsulting.com/blog
Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>|||Hi
First of all , don't use '*' with SELECT statement , it may hurt perfomance.
Is it stored procedure?
SET NOCOUNT ON
SELECT * FROM [aspnet_Profile]
I'd also run SQL Server Profile to see what is going on
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>|||Hello Shkedy,
To understand the issue better, I'd like to know if you run the query by
using Query Analyzer? As Shkedy mentioned, you may want to use profiler to
trace the queries for both situaitons. If profiler trace does not show any
differences, it shall be a server side issue. You may check if there is
differentce on session options such as "ANSI_PADDING" etc.
Also, please check if the issue occurs on different client machines. Try to
run query analyzer on different clients and try to run the query to test
the result.
If we isolate to the client side issue specific to this client machine, you
may want to use task manager or perfmon Dave mentioned to see if client has
performance problem.
I'd also like to know if the clients are in the same net segement on the
server? It might be network/name resolution issues if they are in different
sites or network segment.
Please let's know if you have any update on the issue. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Shkedy wrote:
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
>
What is the structure of the table aspnet_Profile? There may only be 10
rows in that table, but how much data do those 10 rows represent? You
could be pulling back 10 rows with TEXT columns that each have a gig of
data in them! Pulling that across the network would obviously be slower.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I identified the problem -
the built in serilaizer in the .net framework was creating bloated objects
when it was serializing an business object which made the serilized value be
really big and hence slow in moving over the network.
"Shkedy" <shkedy@.newsgroups.nospam> wrote in message
news:%23qfBZZOPHHA.4260@.TK2MSFTNGP02.phx.gbl...
> Greetings,
> I am trying to query one of the ASP.Net tables in SQL 2000 sp4.
> When I remote in to the server with the DB installed on it and run a query
> it runs in less than a second.
> When I run the same query from my remote machine it takes anywhere from 45
> seconds to a minute and a half.
> The table has less than 10 records in it and the query is a simple select
> query (SELECT * FROM [aspnet_Profile])
> I can run another query on the same database from the remote machine that
> will return much more data (100s of heavier rows) on the same database and
> the same connection and it will run much much faster.
> I have no idea where to even start troubleshooting this.
> Any help would be appreciated.
> --
> Sagi Shkedy
> http://blog.shkedy.com
>
Subscribe to:
Posts (Atom)