My db is supporting a high traffic website. It seems that
every 2-3 days, the total cpu utilization suddenly shoots
up to 100% and stays there until I restart sql server.
I've used perf monitor to log total cpu, sql cpu, sql
threads, etc over a period of days and it is relatively
stable with plenty of resources until this sudden spike
occurs. This is not gradual, and I do not see any sudden
spike in memory. Spike is only in cpu, and also # of sql
threads (goes up to around 100). Average total cpu
normally is around 50%.
I've run the blocker script, but there do not seem to be
any blocks going on. I've run profiler to try to catch any
never-ending sp calls or sp hogs, but haven't been able to
catch it either.
It may be that we just need to add more resources, but the
fact that this occurs on a semi-regular schedule is what
is troubling. We don't appear to be getting a sudden
increase in traffic during the problm. Could this be
symptom of an attack? It doesn't seem to be a slow leak-
type, since the graphs show a sudden spike, not a ramp up.
What is unusual, is that the sp that shows up in profiler
as a the most predominant hog during the abnormal high cpu
utlization condition, doesn't normally show up under
normal conditions.What is your system setup (Win Vers, SQL Vers, Service Packs)|||some thoughts...
* cpu spikes like this are very common. I do a lot of tuning and see this
regulalry...
* you would not expect to see blocking during a CPU spike you would expect
to see lower CPU if there are significant waits due to blocking...
* you point out that a particular proc seems to be run during the spike?
Have you looked at the cpu utilization for this proc?
* many times... when I tune things like this... it's not a single big,
expensive proc that causes the spike... but a piece of app code that is
causing a large number of relatively inexpensive procs to be run VERY
frequently. Don't rule that option out by looking just for procs that are
consuming a lot of CPU...
* all the symptoms you're outlined are consistent with a pretty run of the
mill application induced spike. Nothing that seems to strongly suggest an
attack of any kind. Don't rule that out of course... but I'd focus on
profiler to see what sql is using most of the CPU...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||Turn off autogrow for dbs, log files and especially temp db.....manage
growth manually.
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||I agree that you should manage growth by doing it manually (or pre arranged
scheduled task) but you should leave auto grow on just in case. Hopefully
you won't ever use it if you manage it correctly but it is a good fail safe.
--
Andrew J. Kelly
SQL Server MVP
"Tiffany" <tiffany.edwards@.vodafone.net> wrote in message
news:evJbGui9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Turn off autogrow for dbs, log files and especially temp db.....manage
> growth manually.
>
> "Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
> news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> > My db is supporting a high traffic website. It seems that
> > every 2-3 days, the total cpu utilization suddenly shoots
> > up to 100% and stays there until I restart sql server.
> >
> > I've used perf monitor to log total cpu, sql cpu, sql
> > threads, etc over a period of days and it is relatively
> > stable with plenty of resources until this sudden spike
> > occurs. This is not gradual, and I do not see any sudden
> > spike in memory. Spike is only in cpu, and also # of sql
> > threads (goes up to around 100). Average total cpu
> > normally is around 50%.
> >
> > I've run the blocker script, but there do not seem to be
> > any blocks going on. I've run profiler to try to catch any
> > never-ending sp calls or sp hogs, but haven't been able to
> > catch it either.
> >
> > It may be that we just need to add more resources, but the
> > fact that this occurs on a semi-regular schedule is what
> > is troubling. We don't appear to be getting a sudden
> > increase in traffic during the problm. Could this be
> > symptom of an attack? It doesn't seem to be a slow leak-
> > type, since the graphs show a sudden spike, not a ramp up.
> >
> > What is unusual, is that the sp that shows up in profiler
> > as a the most predominant hog during the abnormal high cpu
> > utlization condition, doesn't normally show up under
> > normal conditions.
> >
>|||the bad thing about profiler is that it won't catch infinite loop
problems.
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
try checking the current activity in enterprise manager (or check
sysprocesses) to find the spid(s) that are using the high cpu and go
from there.
i've had the exact same problem as you (although it was a random
event). i simply killed the problematic spid and everything returned to
normal.
Cynthia wrote:
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.|||<<
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
depends on how you define an infinite loop. Loops just mean you're doing the
same thing over and over. Of course there will be stmt ending events...
you will not see the end event if there is a bug in sql and the command
truly never ends, but that is quite rare. A command may run for a while...
but it will eventually end... and if something is running THAT long...
it's VERY easy to find the culprit in sysprocesses as you point out.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ch" <ch@.dontemailme.com> wrote in message
news:40337E84.50912EDB@.dontemailme.com...
> the bad thing about profiler is that it won't catch infinite loop
> problems.
> you can get the sqlstatement started event (which doesn't show you how
> much it cpu it is using), and you'll never get the sqlstatement ended
> event because it's in an infinite loop. the ended event is the one that
> will show you how much cpu the statement or sp used.
> try checking the current activity in enterprise manager (or check
> sysprocesses) to find the spid(s) that are using the high cpu and go
> from there.
> i've had the exact same problem as you (although it was a random
> event). i simply killed the problematic spid and everything returned to
> normal.
>
> Cynthia wrote:
> > My db is supporting a high traffic website. It seems that
> > every 2-3 days, the total cpu utilization suddenly shoots
> > up to 100% and stays there until I restart sql server.
> >
> > I've used perf monitor to log total cpu, sql cpu, sql
> > threads, etc over a period of days and it is relatively
> > stable with plenty of resources until this sudden spike
> > occurs. This is not gradual, and I do not see any sudden
> > spike in memory. Spike is only in cpu, and also # of sql
> > threads (goes up to around 100). Average total cpu
> > normally is around 50%.
> >
> > I've run the blocker script, but there do not seem to be
> > any blocks going on. I've run profiler to try to catch any
> > never-ending sp calls or sp hogs, but haven't been able to
> > catch it either.
> >
> > It may be that we just need to add more resources, but the
> > fact that this occurs on a semi-regular schedule is what
> > is troubling. We don't appear to be getting a sudden
> > increase in traffic during the problm. Could this be
> > symptom of an attack? It doesn't seem to be a slow leak-
> > type, since the graphs show a sudden spike, not a ramp up.
> >
> > What is unusual, is that the sp that shows up in profiler
> > as a the most predominant hog during the abnormal high cpu
> > utlization condition, doesn't normally show up under
> > normal conditions.
>|||Here's an update for anyone interested.
I believe the problem was just a combination of high
traffic and the need for some sp optimization. There were
no looping or blocking sps.
Here's how I troubleshot the problem:
I used sql profiler over a period of time to see which sps
were cpu hogs during the normal periods and the high cpu
periods. The profiler brought to my attention one sp
during the normal periods, and another sp during the peak
periods. Both of these sps were each doing both a select
and insert or update on tables that contained many rows.
I then used query analyzer's execution plan together with
profiler to monitor the performance of those sps on an
offline database copy.
I then measured the performance with and without indexes
on the tables (the tables originally did not have indexes
on columns being searched) I saw a tremendous select
performance improvment with the indexes, with negligible
impact to insertion performance.
I added the indexes to the live servers and got fantastic
results. Now, the cpus are probably underutilized as the
total and sql cpu % is well below 10%. It has been running
like this for many days now and the response time of the
website is excellent.
Thanks to all of those of provided feedback.
>--Original Message--
>My db is supporting a high traffic website. It seems that
>every 2-3 days, the total cpu utilization suddenly shoots
>up to 100% and stays there until I restart sql server.
>I've used perf monitor to log total cpu, sql cpu, sql
>threads, etc over a period of days and it is relatively
>stable with plenty of resources until this sudden spike
>occurs. This is not gradual, and I do not see any sudden
>spike in memory. Spike is only in cpu, and also # of sql
>threads (goes up to around 100). Average total cpu
>normally is around 50%.
>I've run the blocker script, but there do not seem to be
>any blocks going on. I've run profiler to try to catch
any
>never-ending sp calls or sp hogs, but haven't been able
to
>catch it either.
>It may be that we just need to add more resources, but
the
>fact that this occurs on a semi-regular schedule is what
>is troubling. We don't appear to be getting a sudden
>increase in traffic during the problm. Could this be
>symptom of an attack? It doesn't seem to be a slow leak-
>type, since the graphs show a sudden spike, not a ramp up.
>What is unusual, is that the sp that shows up in profiler
>as a the most predominant hog during the abnormal high
cpu
>utlization condition, doesn't normally show up under
>normal conditions.
>.
>
Showing posts with label basis. Show all posts
Showing posts with label basis. Show all posts
Monday, March 19, 2012
High CPU Utilization on Periodic basis
My db is supporting a high traffic website. It seems that
every 2-3 days, the total cpu utilization suddenly shoots
up to 100% and stays there until I restart sql server.
I've used perf monitor to log total cpu, sql cpu, sql
threads, etc over a period of days and it is relatively
stable with plenty of resources until this sudden spike
occurs. This is not gradual, and I do not see any sudden
spike in memory. Spike is only in cpu, and also # of sql
threads (goes up to around 100). Average total cpu
normally is around 50%.
I've run the blocker script, but there do not seem to be
any blocks going on. I've run profiler to try to catch any
never-ending sp calls or sp hogs, but haven't been able to
catch it either.
It may be that we just need to add more resources, but the
fact that this occurs on a semi-regular schedule is what
is troubling. We don't appear to be getting a sudden
increase in traffic during the problm. Could this be
symptom of an attack? It doesn't seem to be a slow leak-
type, since the graphs show a sudden spike, not a ramp up.
What is unusual, is that the sp that shows up in profiler
as a the most predominant hog during the abnormal high cpu
utlization condition, doesn't normally show up under
normal conditions.What is your system setup (Win Vers, SQL Vers, Service Packs)|||some thoughts...
* cpu spikes like this are very common. I do a lot of tuning and see this
regulalry...
* you would not expect to see blocking during a CPU spike you would expect
to see lower CPU if there are significant waits due to blocking...
* you point out that a particular proc seems to be run during the spike?
Have you looked at the cpu utilization for this proc?
* many times... when I tune things like this... it's not a single big,
expensive proc that causes the spike... but a piece of app code that is
causing a large number of relatively inexpensive procs to be run VERY
frequently. Don't rule that option out by looking just for procs that are
consuming a lot of CPU...
* all the symptoms you're outlined are consistent with a pretty run of the
mill application induced spike. Nothing that seems to strongly suggest an
attack of any kind. Don't rule that out of course... but I'd focus on
profiler to see what sql is using most of the CPU...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||Turn off autogrow for dbs, log files and especially temp db.....manage
growth manually.
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||I agree that you should manage growth by doing it manually (or pre arranged
scheduled task) but you should leave auto grow on just in case. Hopefully
you won't ever use it if you manage it correctly but it is a good fail safe.
Andrew J. Kelly
SQL Server MVP
"Tiffany" <tiffany.edwards@.vodafone.net> wrote in message
news:evJbGui9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Turn off autogrow for dbs, log files and especially temp db.....manage
> growth manually.
>
> "Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
> news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
>|||the bad thing about profiler is that it won't catch infinite loop
problems.
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
try checking the current activity in enterprise manager (or check
sysprocesses) to find the spid(s) that are using the high cpu and go
from there.
i've had the exact same problem as you (although it was a random
event). i simply killed the problematic spid and everything returned to
normal.
Cynthia wrote:
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.|||<<
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
depends on how you define an infinite loop. Loops just mean you're doing the
same thing over and over. Of course there will be stmt ending events...
you will not see the end event if there is a bug in sql and the command
truly never ends, but that is quite rare. A command may run for a while...
but it will eventually end... and if something is running THAT long...
it's VERY easy to find the culprit in sysprocesses as you point out.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ch" <ch@.dontemailme.com> wrote in message
news:40337E84.50912EDB@.dontemailme.com...
> the bad thing about profiler is that it won't catch infinite loop
> problems.
> you can get the sqlstatement started event (which doesn't show you how
> much it cpu it is using), and you'll never get the sqlstatement ended
> event because it's in an infinite loop. the ended event is the one that
> will show you how much cpu the statement or sp used.
> try checking the current activity in enterprise manager (or check
> sysprocesses) to find the spid(s) that are using the high cpu and go
> from there.
> i've had the exact same problem as you (although it was a random
> event). i simply killed the problematic spid and everything returned to
> normal.
>
> Cynthia wrote:
>
>
every 2-3 days, the total cpu utilization suddenly shoots
up to 100% and stays there until I restart sql server.
I've used perf monitor to log total cpu, sql cpu, sql
threads, etc over a period of days and it is relatively
stable with plenty of resources until this sudden spike
occurs. This is not gradual, and I do not see any sudden
spike in memory. Spike is only in cpu, and also # of sql
threads (goes up to around 100). Average total cpu
normally is around 50%.
I've run the blocker script, but there do not seem to be
any blocks going on. I've run profiler to try to catch any
never-ending sp calls or sp hogs, but haven't been able to
catch it either.
It may be that we just need to add more resources, but the
fact that this occurs on a semi-regular schedule is what
is troubling. We don't appear to be getting a sudden
increase in traffic during the problm. Could this be
symptom of an attack? It doesn't seem to be a slow leak-
type, since the graphs show a sudden spike, not a ramp up.
What is unusual, is that the sp that shows up in profiler
as a the most predominant hog during the abnormal high cpu
utlization condition, doesn't normally show up under
normal conditions.What is your system setup (Win Vers, SQL Vers, Service Packs)|||some thoughts...
* cpu spikes like this are very common. I do a lot of tuning and see this
regulalry...
* you would not expect to see blocking during a CPU spike you would expect
to see lower CPU if there are significant waits due to blocking...
* you point out that a particular proc seems to be run during the spike?
Have you looked at the cpu utilization for this proc?
* many times... when I tune things like this... it's not a single big,
expensive proc that causes the spike... but a piece of app code that is
causing a large number of relatively inexpensive procs to be run VERY
frequently. Don't rule that option out by looking just for procs that are
consuming a lot of CPU...
* all the symptoms you're outlined are consistent with a pretty run of the
mill application induced spike. Nothing that seems to strongly suggest an
attack of any kind. Don't rule that out of course... but I'd focus on
profiler to see what sql is using most of the CPU...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||Turn off autogrow for dbs, log files and especially temp db.....manage
growth manually.
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.
>|||I agree that you should manage growth by doing it manually (or pre arranged
scheduled task) but you should leave auto grow on just in case. Hopefully
you won't ever use it if you manage it correctly but it is a good fail safe.
Andrew J. Kelly
SQL Server MVP
"Tiffany" <tiffany.edwards@.vodafone.net> wrote in message
news:evJbGui9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Turn off autogrow for dbs, log files and especially temp db.....manage
> growth manually.
>
> "Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
> news:0cae01c3f592$333ef460$3a01280a@.phx.gbl...
>|||the bad thing about profiler is that it won't catch infinite loop
problems.
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
try checking the current activity in enterprise manager (or check
sysprocesses) to find the spid(s) that are using the high cpu and go
from there.
i've had the exact same problem as you (although it was a random
event). i simply killed the problematic spid and everything returned to
normal.
Cynthia wrote:
> My db is supporting a high traffic website. It seems that
> every 2-3 days, the total cpu utilization suddenly shoots
> up to 100% and stays there until I restart sql server.
> I've used perf monitor to log total cpu, sql cpu, sql
> threads, etc over a period of days and it is relatively
> stable with plenty of resources until this sudden spike
> occurs. This is not gradual, and I do not see any sudden
> spike in memory. Spike is only in cpu, and also # of sql
> threads (goes up to around 100). Average total cpu
> normally is around 50%.
> I've run the blocker script, but there do not seem to be
> any blocks going on. I've run profiler to try to catch any
> never-ending sp calls or sp hogs, but haven't been able to
> catch it either.
> It may be that we just need to add more resources, but the
> fact that this occurs on a semi-regular schedule is what
> is troubling. We don't appear to be getting a sudden
> increase in traffic during the problm. Could this be
> symptom of an attack? It doesn't seem to be a slow leak-
> type, since the graphs show a sudden spike, not a ramp up.
> What is unusual, is that the sp that shows up in profiler
> as a the most predominant hog during the abnormal high cpu
> utlization condition, doesn't normally show up under
> normal conditions.|||<<
you can get the sqlstatement started event (which doesn't show you how
much it cpu it is using), and you'll never get the sqlstatement ended
event because it's in an infinite loop. the ended event is the one that
will show you how much cpu the statement or sp used.
depends on how you define an infinite loop. Loops just mean you're doing the
same thing over and over. Of course there will be stmt ending events...
you will not see the end event if there is a bug in sql and the command
truly never ends, but that is quite rare. A command may run for a while...
but it will eventually end... and if something is running THAT long...
it's VERY easy to find the culprit in sysprocesses as you point out.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"ch" <ch@.dontemailme.com> wrote in message
news:40337E84.50912EDB@.dontemailme.com...
> the bad thing about profiler is that it won't catch infinite loop
> problems.
> you can get the sqlstatement started event (which doesn't show you how
> much it cpu it is using), and you'll never get the sqlstatement ended
> event because it's in an infinite loop. the ended event is the one that
> will show you how much cpu the statement or sp used.
> try checking the current activity in enterprise manager (or check
> sysprocesses) to find the spid(s) that are using the high cpu and go
> from there.
> i've had the exact same problem as you (although it was a random
> event). i simply killed the problematic spid and everything returned to
> normal.
>
> Cynthia wrote:
>
>
Friday, March 9, 2012
hierarchy in SSRS 2005?
is it possible to use a hierarchy (e.g: CountryCode, PostCode, CityName) as basis for a report parameter? I would like the user to have the possibility to drill down from e.g.: "Country = 'Austria' - PostCode '1010' - CityName 'Vienna'" and after that view the report with the selected parameters.
possible?
yes - no - maybe?
regards
pamike
Don't think so... At least using the standart issues!|||is it hten possible to build the hierarchie in an Analysis Services Olap Cube and use parameters to pass througt to a predefined report? I also tried this but without any success;-(
thx
pamike
|||Can't answer that... i'm not very experienced in Analisys Services...
Sorry...
|||Are you looking to do this with one parameter or can it use multiple parameters? You can set up cascading parameters to do something like this. For example,
Parameter1 Datasource => SELECT countryid, country FROMCountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = Paramenters!Parameter1.Value;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = Paramenters!Parameter2.Value;
I am not sure if you can change the visibilty of each parameter after you select it (or if you need to).
R
|||Small error. Those should read
Parameter1 Datasource => SELECT countryid, country FROM CountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = @.Parameter1 ;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = @.Parameter2;
R
|||This is very possible using cascading parameters...
Parameter 1 (country code):
SELECT CountryCode FROM AccountGeography
Use something like the above as the dataset for the first parameter.
Multi value Parameter 2 (postal code):
SELECT PostalCode FROM AccountGeography WHERE CountryCode = @.CountryCode
Assuming you named the first parameter "CountryCode", the above statement will generate a list of postal codes that correspond to the CountryCode value selected in the first parameter.If the first parameter is a multi-valued parameter where you can select more than one CountryCode at a time then it gets a little trickier.To avoid brain numbing dynamic SQL you can use a user defined function to pass the comma delimited list of values from the CountryCode parameter to an IN statement in the child parameters.Example:
SELECT PostalCode FROM AccountGeography WHERE CountryCode IN (SELECT Param FROM ufn_MVParam(@.CountryCode,','))
Multi value Parameter 3 (city name):
SELECT CityName FROM AccountGeography WHERE PostalCode IN (SELECT Param FROM ufn_MVParam(@.PostalCode,','))
The third parameter is the same as the second except for that where parameter 2 uses the value of parameter 1 to filter its values, parameter 3 uses the value of parameter 2.
...Don't forget that when you're using reporting services parameters in your data set queries, the parameter name is case sensitive! WHERE CountryCode = @.CountryCode is not the same as WHERE CountryCode = @.Countrycode.
...SSRS will take care of generating the dynamic SQL for you!I'm not sure if that statement is technically correct, but it's at least operationally true.You will understand how wonderful this is if you have used SSRS 2000 x_x
...Here is the code to create the function which pareses the comma delimited string.I got this on the web somewhere but can't remember where:
CREATE FUNCTION [dbo].[ufn_MVParam](@.RepParam nvarchar(4000), @.Delim char(1)= ',')
RETURNS @.VALUES TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @.chrind INT
DECLARE @.Piece nvarchar(4000)
SELECT @.chrind = 1
WHILE @.chrind > 0
BEGIN
SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)
IF @.chrind > 0
SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)
ELSE
SELECT @.Piece = @.RepParam
INSERT @.VALUES(Param) VALUES(@.Piece)
SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)
IF LEN(@.RepParam) = 0 BREAK
END
RETURN
END
Good luck!!
|||My bad... you only need that crazy text parsing function when trying to pass multi value parameters to a stored procedure. If your result set is not generated using a stored procedure, all you need is the SSRS parameter name like this:
SELECT CityName FROM AccountGeography WHERE PostalCode IN (@.PostalCode)
with SSRS 2005 you would only need the additional user function when using a stored procedure as the data set in your report... and in that situation you wouldn't call the function from within SSRS, you would call it from within the sproc itself because SSRS passes the multi value parameter to SQL server as a comma delimted value string ("CountryCode1,CountryCode2,CountryCode3" etc.)
Subscribe to:
Posts (Atom)