Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
--
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>