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?
>
Showing posts with label slowdowns. Show all posts
Showing posts with label slowdowns. Show all posts
Friday, March 30, 2012
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?
>
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?
>
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?
>
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?
>
Wednesday, March 28, 2012
Intermittent query slowdowns and corresponding high CPU utilization
Hi. We are experiencing an issue on SQL Server that sounds very simlar to
the hotifx detailed here: http://support.microsoft.com/kb/835864
Only trouble is we are running on a single CPU server when the article
suggests it is only applicable to multple CPU servers. Has anyone had
experience with this issue and resolving it?
--
McGeeky
http://mcgeeky.blogspot.comOn Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>the hotifx detailed here: http://support.microsoft.com/kb/835864
>Only trouble is we are running on a single CPU server when the article
>suggests it is only applicable to multple CPU servers. Has anyone had
>experience with this issue and resolving it?
I may be seeing it myself on a small, old two-processor box running on
only 512mb. Thanks for pointing out the article.
Microsoft is infamous for admitting to one special-case of bugs that
turn up in about a million other environments.
Doesn't guarantee that what you or I see is the same bug, since even
Microsoft may not have verified all the situations.
Josh|||Do you see it happen to any query? We are experiencing it only on one
query - it generally takes only 5-10 seconds but occassionally SQL Server
goes nuts and it takes 20 minutes with CPU on 100%.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:t5n452t4bn1h2r4f7iv4cpaqbutsch2skv@.4ax.com...
> On Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>>the hotifx detailed here: http://support.microsoft.com/kb/835864
>>Only trouble is we are running on a single CPU server when the article
>>suggests it is only applicable to multple CPU servers. Has anyone had
>>experience with this issue and resolving it?
>
> I may be seeing it myself on a small, old two-processor box running on
> only 512mb. Thanks for pointing out the article.
> Microsoft is infamous for admitting to one special-case of bugs that
> turn up in about a million other environments.
> Doesn't guarantee that what you or I see is the same bug, since even
> Microsoft may not have verified all the situations.
> Josh|||On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>Do you see it happen to any query? We are experiencing it only on one
>query - it generally takes only 5-10 seconds but occassionally SQL Server
>goes nuts and it takes 20 minutes with CPU on 100%.
Not at my current location.
Saw something that might have been this at a previous shop, where an
SP in a particular test (load) script goes nuts, but attempts to rerun
with even the same data via query analyzer see no problem.
There were a lot of executions of prepared plans involved, we might
have mismapped the numbers to the source, should have run a test with
SP statement start and plan capture set in profiler, so I can't be
ENTIRELY certain. And even if it was what it seemed, the cause might
not have been the memory allocation issue mentioned in the MS
document, since this wasn't really a high-transaction system.
Josh|||Your scenario sounds exactly like mine: its one query in particular but
attempts to run it in query analyzer with the exact same parameters fail to
reproduce the problem.
I like your idea of setting up a trace on the stored procedure plan. A
possibility is that SQL Server gets mixed up occasionally and creates a
really bad access plan.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh|||Can you capture plans in SQL Server 2000?
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh
the hotifx detailed here: http://support.microsoft.com/kb/835864
Only trouble is we are running on a single CPU server when the article
suggests it is only applicable to multple CPU servers. Has anyone had
experience with this issue and resolving it?
--
McGeeky
http://mcgeeky.blogspot.comOn Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>the hotifx detailed here: http://support.microsoft.com/kb/835864
>Only trouble is we are running on a single CPU server when the article
>suggests it is only applicable to multple CPU servers. Has anyone had
>experience with this issue and resolving it?
I may be seeing it myself on a small, old two-processor box running on
only 512mb. Thanks for pointing out the article.
Microsoft is infamous for admitting to one special-case of bugs that
turn up in about a million other environments.
Doesn't guarantee that what you or I see is the same bug, since even
Microsoft may not have verified all the situations.
Josh|||Do you see it happen to any query? We are experiencing it only on one
query - it generally takes only 5-10 seconds but occassionally SQL Server
goes nuts and it takes 20 minutes with CPU on 100%.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:t5n452t4bn1h2r4f7iv4cpaqbutsch2skv@.4ax.com...
> On Fri, 28 Apr 2006 14:55:11 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Hi. We are experiencing an issue on SQL Server that sounds very simlar to
>>the hotifx detailed here: http://support.microsoft.com/kb/835864
>>Only trouble is we are running on a single CPU server when the article
>>suggests it is only applicable to multple CPU servers. Has anyone had
>>experience with this issue and resolving it?
>
> I may be seeing it myself on a small, old two-processor box running on
> only 512mb. Thanks for pointing out the article.
> Microsoft is infamous for admitting to one special-case of bugs that
> turn up in about a million other environments.
> Doesn't guarantee that what you or I see is the same bug, since even
> Microsoft may not have verified all the situations.
> Josh|||On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>Do you see it happen to any query? We are experiencing it only on one
>query - it generally takes only 5-10 seconds but occassionally SQL Server
>goes nuts and it takes 20 minutes with CPU on 100%.
Not at my current location.
Saw something that might have been this at a previous shop, where an
SP in a particular test (load) script goes nuts, but attempts to rerun
with even the same data via query analyzer see no problem.
There were a lot of executions of prepared plans involved, we might
have mismapped the numbers to the source, should have run a test with
SP statement start and plan capture set in profiler, so I can't be
ENTIRELY certain. And even if it was what it seemed, the cause might
not have been the memory allocation issue mentioned in the MS
document, since this wasn't really a high-transaction system.
Josh|||Your scenario sounds exactly like mine: its one query in particular but
attempts to run it in query analyzer with the exact same parameters fail to
reproduce the problem.
I like your idea of setting up a trace on the stored procedure plan. A
possibility is that SQL Server gets mixed up occasionally and creates a
really bad access plan.
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh|||Can you capture plans in SQL Server 2000?
--
McGeeky
http://mcgeeky.blogspot.com
"jxstern" <jxstern@.wherever.com> wrote in message
news:chsf521jm3l6e86tamq0c6t0covng5eg4m@.4ax.com...
> On Tue, 2 May 2006 12:25:06 +0100, "McGeeky" <anon@.anon.com> wrote:
>>Do you see it happen to any query? We are experiencing it only on one
>>query - it generally takes only 5-10 seconds but occassionally SQL Server
>>goes nuts and it takes 20 minutes with CPU on 100%.
> Not at my current location.
> Saw something that might have been this at a previous shop, where an
> SP in a particular test (load) script goes nuts, but attempts to rerun
> with even the same data via query analyzer see no problem.
> There were a lot of executions of prepared plans involved, we might
> have mismapped the numbers to the source, should have run a test with
> SP statement start and plan capture set in profiler, so I can't be
> ENTIRELY certain. And even if it was what it seemed, the cause might
> not have been the memory allocation issue mentioned in the MS
> document, since this wasn't really a high-transaction system.
> Josh
Subscribe to:
Posts (Atom)