Hello, I have an ASP.NET 2.0 application that is running under Windows 2003
with SQL 2005/SP2. The server is fully patched with all updates, Windows,
..NET, etc.

I have this ASP.NET application that I host that basically has a webform for
the user to plug in a search criteria, hit submit, and one or more SP are
run against the SQL database and then it returns a gridview of data.

The problem is that periodically, users experience a huge slowness with the
server, however the server CPU and memory look fine, best I can tell. There
are typically no more than 1-3 users hitting the application concurrently.

I have ELMAH installed and I typically will get a SQL Timeout Exception for
ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
minutes, but this doesn't really appear to be the issue.

The kicker is that doing an IIS and/or SQL restart doesn't fix the problem.
Only have a restart the server does the query actually work.

What makes this even more interesting is that the result set only returns 2
rows by running a query for the past 24 hours, however if I run the SP
manually, it only takes a few seconds to run in SQL Management Studio.

This problem seems to surface every so often out of no where. Sometimes the
application runs weeks without any issues. Other times I get 5-8 phone
calls a day at different times that its slow (timing out) and, again,
nothing is going on.

*** RANT MODE ON ***
I'm really getting tired of this issue and I'm getting tired of
non-technical folks always saying, "Can't you just restart the server" as if
its an on/off switch and not a server. It is very frustrating and having
users that run XP/Vista, etc, that remote into other servers for running
non-enterprise applications (I.E., applications that are written in VB6 or
something else that run as a console application) and require full admin
access to the server to use the program. Then every time there is any
performance problem (or perceived problem) they call me and I'm suppose to
miraculous fix their issues; knowing good and well that they do what the
please on the box and I can't stop them... Yes, they use Explorer all the
freakin' time and accidently drag/drop folders to other folders and treat
the server environment like its a glorified desktop workstation. ARRGHHHHH

Microsoft really did everyone a disservice by merging their workstation and
server code and GUI. At the very least, they should have made the GUI less
intutive to make folks that have a laptop and XP/Vista not think they know
how to operate a server! I'm sick of it! I'd use Ubuntu in a heartbeat
if I could run the applications on it and make it more difficult for normal
users to maneuver around the system and force them to use command line!
**** RANT MODE OFF ****

Any hep regarding my issue would be greatly appreciated and if you feel like
commenting on my rant, please do so! ;-)

The first think I would do was to use the SQL profiler to see by instance
what other processes are locking the connections.

Cor

"dm1608" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello, I have an ASP.NET 2.0 application that is running under Windows
> 2003
> with SQL 2005/SP2. The server is fully patched with all updates, Windows,
> .NET, etc.
>
> I have this ASP.NET application that I host that basically has a webform
> for
> the user to plug in a search criteria, hit submit, and one or more SP are
> run against the SQL database and then it returns a gridview of data.
>
> The problem is that periodically, users experience a huge slowness with
> the
> server, however the server CPU and memory look fine, best I can tell.
> There
> are typically no more than 1-3 users hitting the application concurrently.
>
> I have ELMAH installed and I typically will get a SQL Timeout Exception
> for
> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
> minutes, but this doesn't really appear to be the issue.
>
> The kicker is that doing an IIS and/or SQL restart doesn't fix the
> problem.
> Only have a restart the server does the query actually work.
>
> What makes this even more interesting is that the result set only returns
> 2
> rows by running a query for the past 24 hours, however if I run the SP
> manually, it only takes a few seconds to run in SQL Management Studio.
>
> This problem seems to surface every so often out of no where. Sometimes
> the
> application runs weeks without any issues. Other times I get 5-8 phone
> calls a day at different times that its slow (timing out) and, again,
> nothing is going on.
>
> *** RANT MODE ON ***
> I'm really getting tired of this issue and I'm getting tired of
> non-technical folks always saying, "Can't you just restart the server" as
> if
> its an on/off switch and not a server. It is very frustrating and having
> users that run XP/Vista, etc, that remote into other servers for running
> non-enterprise applications (I.E., applications that are written in VB6 or
> something else that run as a console application) and require full admin
> access to the server to use the program. Then every time there is any
> performance problem (or perceived problem) they call me and I'm suppose to
> miraculous fix their issues; knowing good and well that they do what the
> please on the box and I can't stop them... Yes, they use Explorer all the
> freakin' time and accidently drag/drop folders to other folders and treat
> the server environment like its a glorified desktop workstation.
> ARRGHHHHH
>
> Microsoft really did everyone a disservice by merging their workstation
> and
> server code and GUI. At the very least, they should have made the GUI
> less
> intutive to make folks that have a laptop and XP/Vista not think they know
> how to operate a server! I'm sick of it! I'd use Ubuntu in a heartbeat
> if I could run the applications on it and make it more difficult for
> normal
> users to maneuver around the system and force them to use command line!
> **** RANT MODE OFF ****
>
> Any hep regarding my issue would be greatly appreciated and if you feel
> like
> commenting on my rant, please do so! ;-)
>
>
>
>

Hi mate,

Sorry I feel your frustration, however If anyone is to help you we need far
more infromation. SQL Server performance is an art and a vast subject as is
perfromance of any DB.

Try to replicate the issue and do a query plan for the SP's causing an
issue, and post the query plan here. You should be able to get the queries
by profiling the server and identifying the queries when the problem
occurs. Best to do in a dev environment but push come to shove in the live.
Lets be honest will it really impact the users more than the current state
of affairs?

I have used Oracle and SQL Server and any DB will have its problems
depending on design, so this is most likely not an MS problem so don't get
frustrated with them.

There are many SQLServerPerfomance websites so do a search, some of the
information can be a bit intimidating tho.

Finally another area of contention to look at is your network. Is there a
mass file transfer occuring at the time it occurs?

Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing machine.
If someone took a (true, off the dealer's car lot) stock car and put it on
the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like correct
indexes), remove things weighing down the car (like bad indexes) are going
to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly, then
its nothing but a (off the car lot) "stock" car running in a race. And it
won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts them
in a nice GUI format.

.....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known issue
about performance.


"dm1608" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello, I have an ASP.NET 2.0 application that is running under Windows
> 2003
> with SQL 2005/SP2. The server is fully patched with all updates, Windows,
> .NET, etc.
>
> I have this ASP.NET application that I host that basically has a webform
> for
> the user to plug in a search criteria, hit submit, and one or more SP are
> run against the SQL database and then it returns a gridview of data.
>
> The problem is that periodically, users experience a huge slowness with
> the
> server, however the server CPU and memory look fine, best I can tell.
> There
> are typically no more than 1-3 users hitting the application concurrently.
>
> I have ELMAH installed and I typically will get a SQL Timeout Exception
> for
> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
> minutes, but this doesn't really appear to be the issue.
>
> The kicker is that doing an IIS and/or SQL restart doesn't fix the
> problem.
> Only have a restart the server does the query actually work.
>
> What makes this even more interesting is that the result set only returns
> 2
> rows by running a query for the past 24 hours, however if I run the SP
> manually, it only takes a few seconds to run in SQL Management Studio.
>
> This problem seems to surface every so often out of no where. Sometimes
> the
> application runs weeks without any issues. Other times I get 5-8 phone
> calls a day at different times that its slow (timing out) and, again,
> nothing is going on.
>
> *** RANT MODE ON ***
> I'm really getting tired of this issue and I'm getting tired of
> non-technical folks always saying, "Can't you just restart the server" as
> if
> its an on/off switch and not a server. It is very frustrating and having
> users that run XP/Vista, etc, that remote into other servers for running
> non-enterprise applications (I.E., applications that are written in VB6 or
> something else that run as a console application) and require full admin
> access to the server to use the program. Then every time there is any
> performance problem (or perceived problem) they call me and I'm suppose to
> miraculous fix their issues; knowing good and well that they do what the
> please on the box and I can't stop them... Yes, they use Explorer all the
> freakin' time and accidently drag/drop folders to other folders and treat
> the server environment like its a glorified desktop workstation.
> ARRGHHHHH
>
> Microsoft really did everyone a disservice by merging their workstation
> and
> server code and GUI. At the very least, they should have made the GUI
> less
> intutive to make folks that have a laptop and XP/Vista not think they know
> how to operate a server! I'm sick of it! I'd use Ubuntu in a heartbeat
> if I could run the applications on it and make it more difficult for
> normal
> users to maneuver around the system and force them to use command line!
> **** RANT MODE OFF ****
>
> Any hep regarding my issue would be greatly appreciated and if you feel
> like
> commenting on my rant, please do so! ;-)
>
>
>
>

//> What makes this even more interesting is that the result set only
returns
> 2
> rows by running a query for the past 24 hours, however if I run the SP
> manually, it only takes a few seconds to run in SQL Management Studio.//


That would suggest the Parameter Sniffing issue. Make sure you investigate
that (google search it).



"sloan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Geeze dude. Don't blame Microsoft for the world's ills.
>
> Take a look here:
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry
>
> You can find missing indexes and also bad indexes.
> You can also look at some I/O stats.
>
> Here is one metaphor for Sql Server db tuning.
> Think about stock car racing.
>
> Every team is allowed to take a stock car and turn it into a racing
> machine.
> If someone took a (true, off the dealer's car lot) stock car and put it on
> the track......they'd get blown away.
>
> The people who can tweak the car, put improvements on the cars (like
> correct indexes), remove things weighing down the car (like bad indexes)
> are going to have a chance to win the race.
>
> Sql Server is like that. If you don't learn how to tweak it correctly,
> then its nothing but a (off the car lot) "stock" car running in a race.
> And it won't perform well.
>
> Glenn's articles above are good ones.
>
>
> You can also get the "Sql Server Dashboard Reports" up and running.
> Google it, they take alot of the information glenn persents, and puts them
> in a nice GUI format.
>
> ....................
>
> PS
> You might google "Parameter Sniffing" "Sql Server" for another known issue
> about performance.
>
>
> "dm1608" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hello, I have an ASP.NET 2.0 application that is running under Windows
>> 2003
>> with SQL 2005/SP2. The server is fully patched with all updates,
>> Windows,
>> .NET, etc.
>>
>> I have this ASP.NET application that I host that basically has a webform
>> for
>> the user to plug in a search criteria, hit submit, and one or more SP are
>> run against the SQL database and then it returns a gridview of data.
>>
>> The problem is that periodically, users experience a huge slowness with
>> the
>> server, however the server CPU and memory look fine, best I can tell.
>> There
>> are typically no more than 1-3 users hitting the application
>> concurrently.
>>
>> I have ELMAH installed and I typically will get a SQL Timeout Exception
>> for
>> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
>> minutes, but this doesn't really appear to be the issue.
>>
>> The kicker is that doing an IIS and/or SQL restart doesn't fix the
>> problem.
>> Only have a restart the server does the query actually work.
>>
>> What makes this even more interesting is that the result set only returns
>> 2
>> rows by running a query for the past 24 hours, however if I run the SP
>> manually, it only takes a few seconds to run in SQL Management Studio.
>>
>> This problem seems to surface every so often out of no where. Sometimes
>> the
>> application runs weeks without any issues. Other times I get 5-8 phone
>> calls a day at different times that its slow (timing out) and, again,
>> nothing is going on.
>>
>> *** RANT MODE ON ***
>> I'm really getting tired of this issue and I'm getting tired of
>> non-technical folks always saying, "Can't you just restart the server" as
>> if
>> its an on/off switch and not a server. It is very frustrating and having
>> users that run XP/Vista, etc, that remote into other servers for running
>> non-enterprise applications (I.E., applications that are written in VB6
>> or
>> something else that run as a console application) and require full admin
>> access to the server to use the program. Then every time there is any
>> performance problem (or perceived problem) they call me and I'm suppose
>> to
>> miraculous fix their issues; knowing good and well that they do what the
>> please on the box and I can't stop them... Yes, they use Explorer all
>> the
>> freakin' time and accidently drag/drop folders to other folders and treat
>> the server environment like its a glorified desktop workstation.
>> ARRGHHHHH
>>
>> Microsoft really did everyone a disservice by merging their workstation
>> and
>> server code and GUI. At the very least, they should have made the GUI
>> less
>> intutive to make folks that have a laptop and XP/Vista not think they
>> know
>> how to operate a server! I'm sick of it! I'd use Ubuntu in a
>> heartbeat
>> if I could run the applications on it and make it more difficult for
>> normal
>> users to maneuver around the system and force them to use command line!
>> **** RANT MODE OFF ****
>>
>> Any hep regarding my issue would be greatly appreciated and if you feel
>> like
>> commenting on my rant, please do so! ;-)
>>
>>
>>
>>
>
>

In another post, I read that someone was having performance problems calling
stored procedures from .Net. Running the stored procedure using SQL Server
Management studio returned results much faster. One respondent suggested
using local variables in the stored procedure assigned with the incoming
parameter values. Apparently, it impacts the way the query analyzer works
and yields better results when calling from .Net.

Just a thought


"dm1608" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hello, I have an ASP.NET 2.0 application that is running under Windows
> 2003
> with SQL 2005/SP2. The server is fully patched with all updates, Windows,
> .NET, etc.
>
> I have this ASP.NET application that I host that basically has a webform
> for
> the user to plug in a search criteria, hit submit, and one or more SP are
> run against the SQL database and then it returns a gridview of data.
>
> The problem is that periodically, users experience a huge slowness with
> the
> server, however the server CPU and memory look fine, best I can tell.
> There
> are typically no more than 1-3 users hitting the application concurrently.
>
> I have ELMAH installed and I typically will get a SQL Timeout Exception
> for
> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
> minutes, but this doesn't really appear to be the issue.
>
> The kicker is that doing an IIS and/or SQL restart doesn't fix the
> problem.
> Only have a restart the server does the query actually work.
>
> What makes this even more interesting is that the result set only returns
> 2
> rows by running a query for the past 24 hours, however if I run the SP
> manually, it only takes a few seconds to run in SQL Management Studio.
>
> This problem seems to surface every so often out of no where. Sometimes
> the
> application runs weeks without any issues. Other times I get 5-8 phone
> calls a day at different times that its slow (timing out) and, again,
> nothing is going on.

Hi -- I have already done this.

There is nothing blocking any connections. I've also used sp_who2 to
verify.

Typically, SQL Profiler doesn't show where the ASP.NET page is executing the
SQL statement until the timeout exception has occurred. Which makes me
think that ADO.NET is not able to communicate with SQL for some reason.




"Cor Ligthert[MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> The first think I would do was to use the SQL profiler to see by instance
> what other processes are locking the connections.
>
> Cor
>
> "dm1608" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hello, I have an ASP.NET 2.0 application that is running under Windows
>> 2003
>> with SQL 2005/SP2. The server is fully patched with all updates,
>> Windows,
>> .NET, etc.
>>
>> I have this ASP.NET application that I host that basically has a webform
>> for
>> the user to plug in a search criteria, hit submit, and one or more SP are
>> run against the SQL database and then it returns a gridview of data.
>>
>> The problem is that periodically, users experience a huge slowness with
>> the
>> server, however the server CPU and memory look fine, best I can tell.
>> There
>> are typically no more than 1-3 users hitting the application
>> concurrently.
>>
>> I have ELMAH installed and I typically will get a SQL Timeout Exception
>> for
>> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
>> minutes, but this doesn't really appear to be the issue.
>>
>> The kicker is that doing an IIS and/or SQL restart doesn't fix the
>> problem.
>> Only have a restart the server does the query actually work.
>>
>> What makes this even more interesting is that the result set only returns
>> 2
>> rows by running a query for the past 24 hours, however if I run the SP
>> manually, it only takes a few seconds to run in SQL Management Studio.
>>
>> This problem seems to surface every so often out of no where. Sometimes
>> the
>> application runs weeks without any issues. Other times I get 5-8 phone
>> calls a day at different times that its slow (timing out) and, again,
>> nothing is going on.
>>
>> *** RANT MODE ON ***
>> I'm really getting tired of this issue and I'm getting tired of
>> non-technical folks always saying, "Can't you just restart the server" as
>> if
>> its an on/off switch and not a server. It is very frustrating and having
>> users that run XP/Vista, etc, that remote into other servers for running
>> non-enterprise applications (I.E., applications that are written in VB6
>> or
>> something else that run as a console application) and require full admin
>> access to the server to use the program. Then every time there is any
>> performance problem (or perceived problem) they call me and I'm suppose
>> to
>> miraculous fix their issues; knowing good and well that they do what the
>> please on the box and I can't stop them... Yes, they use Explorer all
>> the
>> freakin' time and accidently drag/drop folders to other folders and treat
>> the server environment like its a glorified desktop workstation.
>> ARRGHHHHH
>>
>> Microsoft really did everyone a disservice by merging their workstation
>> and
>> server code and GUI. At the very least, they should have made the GUI
>> less
>> intutive to make folks that have a laptop and XP/Vista not think they
>> know
>> how to operate a server! I'm sick of it! I'd use Ubuntu in a
>> heartbeat
>> if I could run the applications on it and make it more difficult for
>> normal
>> users to maneuver around the system and force them to use command line!
>> **** RANT MODE OFF ****
>>
>> Any hep regarding my issue would be greatly appreciated and if you feel
>> like
>> commenting on my rant, please do so! ;-)
>>
>>
>>
>>
>

I seriously do not think its a query plan or anything that is taking
excessive time. If so, why would me running the same SP that I copied from
SQL Profiler with SQL Management Studio run and only take a couple secs?

SQL is installed on the same box as the ASP.NET application.

Since this is a website that I inherited and simply install, I have no
access to the source code to see what they're doing or how. I can simply
reverse engineer it by looking at SQL Trace and unenrypting their 2005 SP.

It almost seems like ASP.NET/ADO.NET cannot communicate to SQL Server for
some strange reason. But other, smaller, queries work.




"Paul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi mate,
>
> Sorry I feel your frustration, however If anyone is to help you we need
> far more infromation. SQL Server performance is an art and a vast subject
> as is perfromance of any DB.
>
> Try to replicate the issue and do a query plan for the SP's causing an
> issue, and post the query plan here. You should be able to get the queries
> by profiling the server and identifying the queries when the problem
> occurs. Best to do in a dev environment but push come to shove in the
> live. Lets be honest will it really impact the users more than the current
> state of affairs?
>
> I have used Oracle and SQL Server and any DB will have its problems
> depending on design, so this is most likely not an MS problem so don't get
> frustrated with them.
>
> There are many SQLServerPerfomance websites so do a search, some of the
> information can be a bit intimidating tho.
>
> Finally another area of contention to look at is your network. Is there a
> mass file transfer occuring at the time it occurs?
>
>

Thanks for the links. I'll look at them and see if they help.

I don't blame Microsoft for anything, however they do give us all enough
rope to hang ourselves.... Plus they make everything so darn easy and
drag-and-drop that anyone that knows how to use a mouse thinks they're an
expert.

I really do not think this is an index problem or blocking issue since I can
run the same query while the ASP.NET page is waiting for the SQL timeout
error without issue.

It's almost like ADO.NET cannot communicate to SQL Server for this
particular query.




"sloan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Geeze dude. Don't blame Microsoft for the world's ills.
>
> Take a look here:
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry
>
> You can find missing indexes and also bad indexes.
> You can also look at some I/O stats.
>
> Here is one metaphor for Sql Server db tuning.
> Think about stock car racing.
>
> Every team is allowed to take a stock car and turn it into a racing
> machine.
> If someone took a (true, off the dealer's car lot) stock car and put it on
> the track......they'd get blown away.
>
> The people who can tweak the car, put improvements on the cars (like
> correct indexes), remove things weighing down the car (like bad indexes)
> are going to have a chance to win the race.
>
> Sql Server is like that. If you don't learn how to tweak it correctly,
> then its nothing but a (off the car lot) "stock" car running in a race.
> And it won't perform well.
>
> Glenn's articles above are good ones.
>
>
> You can also get the "Sql Server Dashboard Reports" up and running.
> Google it, they take alot of the information glenn persents, and puts them
> in a nice GUI format.
>
> ....................
>
> PS
> You might google "Parameter Sniffing" "Sql Server" for another known issue
> about performance.
>
>
> "dm1608" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Hello, I have an ASP.NET 2.0 application that is running under Windows
>> 2003
>> with SQL 2005/SP2. The server is fully patched with all updates,
>> Windows,
>> .NET, etc.
>>
>> I have this ASP.NET application that I host that basically has a webform
>> for
>> the user to plug in a search criteria, hit submit, and one or more SP are
>> run against the SQL database and then it returns a gridview of data.
>>
>> The problem is that periodically, users experience a huge slowness with
>> the
>> server, however the server CPU and memory look fine, best I can tell.
>> There
>> are typically no more than 1-3 users hitting the application
>> concurrently.
>>
>> I have ELMAH installed and I typically will get a SQL Timeout Exception
>> for
>> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
>> minutes, but this doesn't really appear to be the issue.
>>
>> The kicker is that doing an IIS and/or SQL restart doesn't fix the
>> problem.
>> Only have a restart the server does the query actually work.
>>
>> What makes this even more interesting is that the result set only returns
>> 2
>> rows by running a query for the past 24 hours, however if I run the SP
>> manually, it only takes a few seconds to run in SQL Management Studio.
>>
>> This problem seems to surface every so often out of no where. Sometimes
>> the
>> application runs weeks without any issues. Other times I get 5-8 phone
>> calls a day at different times that its slow (timing out) and, again,
>> nothing is going on.
>>
>> *** RANT MODE ON ***
>> I'm really getting tired of this issue and I'm getting tired of
>> non-technical folks always saying, "Can't you just restart the server" as
>> if
>> its an on/off switch and not a server. It is very frustrating and having
>> users that run XP/Vista, etc, that remote into other servers for running
>> non-enterprise applications (I.E., applications that are written in VB6
>> or
>> something else that run as a console application) and require full admin
>> access to the server to use the program. Then every time there is any
>> performance problem (or perceived problem) they call me and I'm suppose
>> to
>> miraculous fix their issues; knowing good and well that they do what the
>> please on the box and I can't stop them... Yes, they use Explorer all
>> the
>> freakin' time and accidently drag/drop folders to other folders and treat
>> the server environment like its a glorified desktop workstation.
>> ARRGHHHHH
>>
>> Microsoft really did everyone a disservice by merging their workstation
>> and
>> server code and GUI. At the very least, they should have made the GUI
>> less
>> intutive to make folks that have a laptop and XP/Vista not think they
>> know
>> how to operate a server! I'm sick of it! I'd use Ubuntu in a
>> heartbeat
>> if I could run the applications on it and make it more difficult for
>> normal
>> users to maneuver around the system and force them to use command line!
>> **** RANT MODE OFF ****
>>
>> Any hep regarding my issue would be greatly appreciated and if you feel
>> like
>> commenting on my rant, please do so! ;-)
>>
>>
>>
>>
>
>

I never heard of this, but will look into it.

I will unencrypt the SQL 2005 SP and see how they're running the query. I
woudl still think that if this is the issue, by me bouncing SQL, that the
query plan would be lost and a new one would have to be created for the
first query.






"sloan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> //> What makes this even more interesting is that the result set only
> returns
>> 2
>> rows by running a query for the past 24 hours, however if I run the SP
>> manually, it only takes a few seconds to run in SQL Management Studio.//
>
>
> That would suggest the Parameter Sniffing issue. Make sure you
> investigate that (google search it).
>
>
>
> "sloan" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> Geeze dude. Don't blame Microsoft for the world's ills.
>>
>> Take a look here:
>> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
>> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
>> http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry
>>
>> You can find missing indexes and also bad indexes.
>> You can also look at some I/O stats.
>>
>> Here is one metaphor for Sql Server db tuning.
>> Think about stock car racing.
>>
>> Every team is allowed to take a stock car and turn it into a racing
>> machine.
>> If someone took a (true, off the dealer's car lot) stock car and put it
>> on the track......they'd get blown away.
>>
>> The people who can tweak the car, put improvements on the cars (like
>> correct indexes), remove things weighing down the car (like bad indexes)
>> are going to have a chance to win the race.
>>
>> Sql Server is like that. If you don't learn how to tweak it correctly,
>> then its nothing but a (off the car lot) "stock" car running in a race.
>> And it won't perform well.
>>
>> Glenn's articles above are good ones.
>>
>>
>> You can also get the "Sql Server Dashboard Reports" up and running.
>> Google it, they take alot of the information glenn persents, and puts
>> them in a nice GUI format.
>>
>> ....................
>>
>> PS
>> You might google "Parameter Sniffing" "Sql Server" for another known
>> issue about performance.
>>
>>
>> "dm1608" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Hello, I have an ASP.NET 2.0 application that is running under Windows
>>> 2003
>>> with SQL 2005/SP2. The server is fully patched with all updates,
>>> Windows,
>>> .NET, etc.
>>>
>>> I have this ASP.NET application that I host that basically has a webform
>>> for
>>> the user to plug in a search criteria, hit submit, and one or more SP
>>> are
>>> run against the SQL database and then it returns a gridview of data.
>>>
>>> The problem is that periodically, users experience a huge slowness with
>>> the
>>> server, however the server CPU and memory look fine, best I can tell.
>>> There
>>> are typically no more than 1-3 users hitting the application
>>> concurrently.
>>>
>>> I have ELMAH installed and I typically will get a SQL Timeout Exception
>>> for
>>> ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
>>> minutes, but this doesn't really appear to be the issue.
>>>
>>> The kicker is that doing an IIS and/or SQL restart doesn't fix the
>>> problem.
>>> Only have a restart the server does the query actually work.
>>>
>>> What makes this even more interesting is that the result set only
>>> returns 2
>>> rows by running a query for the past 24 hours, however if I run the SP
>>> manually, it only takes a few seconds to run in SQL Management Studio.
>>>
>>> This problem seems to surface every so often out of no where. Sometimes
>>> the
>>> application runs weeks without any issues. Other times I get 5-8 phone
>>> calls a day at different times that its slow (timing out) and, again,
>>> nothing is going on.
>>>
>>> *** RANT MODE ON ***
>>> I'm really getting tired of this issue and I'm getting tired of
>>> non-technical folks always saying, "Can't you just restart the server"
>>> as if
>>> its an on/off switch and not a server. It is very frustrating and
>>> having
>>> users that run XP/Vista, etc, that remote into other servers for running
>>> non-enterprise applications (I.E., applications that are written in VB6
>>> or
>>> something else that run as a console application) and require full admin
>>> access to the server to use the program. Then every time there is any
>>> performance problem (or perceived problem) they call me and I'm suppose
>>> to
>>> miraculous fix their issues; knowing good and well that they do what the
>>> please on the box and I can't stop them... Yes, they use Explorer all
>>> the
>>> freakin' time and accidently drag/drop folders to other folders and
>>> treat
>>> the server environment like its a glorified desktop workstation.
>>> ARRGHHHHH
>>>
>>> Microsoft really did everyone a disservice by merging their workstation
>>> and
>>> server code and GUI. At the very least, they should have made the GUI
>>> less
>>> intutive to make folks that have a laptop and XP/Vista not think they
>>> know
>>> how to operate a server! I'm sick of it! I'd use Ubuntu in a
>>> heartbeat
>>> if I could run the applications on it and make it more difficult for
>>> normal
>>> users to maneuver around the system and force them to use command line!
>>> **** RANT MODE OFF ****
>>>
>>> Any hep regarding my issue would be greatly appreciated and if you feel
>>> like
>>> commenting on my rant, please do so! ;-)
>>>
>>>
>>>
>>>
>>
>>
>
>