In my company, we have a central database and 4 remote databases. the
central database replicate same database into 4 remote database in parallel.
the replication is transaction replication and the data is input in the
central database and replicated into remote databases. there are some
triggers in the remote database that retrieve data that was replicated from
central database into other table. the design is like this. it is wierd that
one of remote database is very slow and replication locking the table and
client application is blocked out. the client application is hang. the
replication is very slow, 4 rows/minute. other 3 remote db are ok. I don't
know why only one remote db is ofen happened such thing. The other remote
databases were no problem, and the replication doesn't lock the table. I know
the triggers are bad, especially work with replication. But why other remote
db were ok. I don't know if there are some other problem. can dbcc dbreindex
be help? Thanks.
I would make sure indexes on the keys exist. Replication is not magic
either, you can monitor it just like normal database activity. I would
suggest running the profiler. Use the duration template, but filter
for say >2000ms.
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
perfmon to remote server
I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
Josh
Local Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>
Monday, February 20, 2012
Perf Mon creating large temp files
I am using a remote connection to connect to a specific server (13B) and I a
m
running a trace from there against another server (138). I have a template t
o
track performance points and am saving the results to a table. My problem is
that when I run this trace, I also am creating a very large set of tmp files
on the local C drive of 13B. The files are named nst1.tmp,prf3.tmp,prf4.tmp.
The C drive space is not very large so I max out the space quickly. I am
looking for a way to point the tmp files to another drive or elimate them
altogether.
ThanksNever go directly to a table. Place the files on a local hard drive first
and then import them later. Otherwise you will incur more overhead than you
need to. If you want to import Perfmon files take a look at the Logman.exe
utility.
Andrew J. Kelly SQL MVP
"MNDiver" <MNDiver@.discussions.microsoft.com> wrote in message
news:1F3F9BF1-5D66-4882-83A2-19C28F1522B1@.microsoft.com...
>I am using a remote connection to connect to a specific server (13B) and I
>am
> running a trace from there against another server (138). I have a template
> to
> track performance points and am saving the results to a table. My problem
> is
> that when I run this trace, I also am creating a very large set of tmp
> files
> on the local C drive of 13B. The files are named
> nst1.tmp,prf3.tmp,prf4.tmp.
> The C drive space is not very large so I max out the space quickly. I am
> looking for a way to point the tmp files to another drive or elimate them
> altogether.
> Thanks
m
running a trace from there against another server (138). I have a template t
o
track performance points and am saving the results to a table. My problem is
that when I run this trace, I also am creating a very large set of tmp files
on the local C drive of 13B. The files are named nst1.tmp,prf3.tmp,prf4.tmp.
The C drive space is not very large so I max out the space quickly. I am
looking for a way to point the tmp files to another drive or elimate them
altogether.
ThanksNever go directly to a table. Place the files on a local hard drive first
and then import them later. Otherwise you will incur more overhead than you
need to. If you want to import Perfmon files take a look at the Logman.exe
utility.
Andrew J. Kelly SQL MVP
"MNDiver" <MNDiver@.discussions.microsoft.com> wrote in message
news:1F3F9BF1-5D66-4882-83A2-19C28F1522B1@.microsoft.com...
>I am using a remote connection to connect to a specific server (13B) and I
>am
> running a trace from there against another server (138). I have a template
> to
> track performance points and am saving the results to a table. My problem
> is
> that when I run this trace, I also am creating a very large set of tmp
> files
> on the local C drive of 13B. The files are named
> nst1.tmp,prf3.tmp,prf4.tmp.
> The C drive space is not very large so I max out the space quickly. I am
> looking for a way to point the tmp files to another drive or elimate them
> altogether.
> Thanks
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)