What was it about this forum that made you think your question could be answered here?
Without wanting to sound too rude, go somewhere else! I doubt anyone on this forum will be able to help you.
-Jamie
Showing posts with label website. Show all posts
Showing posts with label website. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
Historical tables, partitioning or what?
I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?
I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.
I think you should use the archive table and partition it with 2005s new capabilities. This will allow you to easily administer the table(s). Heres a good link on 2005 partitioning. http://msdn2.microsoft.com/en-us/library/ms345146.aspx If you use a seperate archive table, you can create good indexes that will really speed queries. The only bad thing is that if you insert new records into it often, it could be slow. So, you might want to only insert new archive records during off-peak time if possible.Tim|||I would caution to have the absolute minimal indexes on the archive table. In some situations, it may be efficient to create an index just for a report, and then remove the index as soon as the report is complete.|||Thanks for your comments. I read about SQL Server 2005's partitioning feature and find it very interesting (and useful, I think), but the application in question runs on the Express Edition currently with possible switch to a higher version, but doubtfuly the Enterprise one, so partitioning is out of the question, unfortunately.
I will hopefuly use it when I will work on some enterprise level application in the future.
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...
> > 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.
>.
>
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.
>.
>
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
High Availability and Scalability ...
We have a multi-lingual website (English, Spanish and German). We have a table called Posts that is potentially getting really big.
We are in the initial design phase of the database and would like to know what the experts are suggesting to keep our database mean and lean in the long run.
We have been talking about splitting the database up into 3 separate databases, one for English, one for Spanish and one for German. The language specific databases would also be hosted in countries where the language is spoken eg. the German database would be hosted in Germany.
Or maybe database partitioning by language?
Making changes to 3 databases once launched seems like a nightmare. It would be nice to have one main database and maybe 2 (Spanish and German) “satellite” databases or something like that – any ideas?.
Any suggestions of how to deal with this problem the best way would be greatly appreciated. We are using SQL Server 2005.
Newbie!
moving out of replication forum and into TSQL forum.
What is your business requirement exactly? It's not clear what you mean by "lean and mean", nor is it clear exactly what problem you think you're going to have. Disk space? Performance?
|||i think you need to design an application that service these different languages.
there are two approach that you can use. one is to have a central database that holds all the data. the other is to partition this into different site and database.
for the first approach say you are to design an inventory database. for your product table you will a design such as these:
product_id
barcode
productname_english
productname_spanish
productname_german
quantity
the advantage of this design is that
1. you have a live count of your inventory without querying so many database.
2. you use less machine
3. administered by less people
the disavantage of this design is
1. its difficult to implement.
2. there is no site autonomy
3. if the system fails every site fails.
4. difficult to maintain
the second approach is to break the database into a per site basis. that is you have an english database, spanish database and german database. if this approach is taken i suggest also that there must be a separate system for each language.
the advantage of this approach
1. there is site autonomy
2. easy to implement
3. faster site query due to less data.
the downside
slow query for comprehensive and overall reports
need lot of people
need several machine and server.
after some consideretion i would suggest that the second approach be taken due to the following reason. machine and people are cheap if it is to be compared with the confusion between people and the importance of autonomy of each site.
furthermore after implementation of OLTPs on different site i suggest a datawarehouse that shall consolidate data from different sites
regards
|||
We are worried that our posts table (blog entires, forum entries and pictures, everything is a post in our system) is getting too big with all the posts from the different languages. We are thinking of ways that we could avoid our performance going down when a German speaking user is coming to our site and we only need to show content in German. If all the languages are together in the same table we worry that the queries would take a long time to find the language specific content.
So we are debating if we should have a separate database for each language or if we could somehow have one main database in the United States for example and 2 satellite databases in different countries. The satellite databases which would hold only the records for a specific language for example German in Germany would once a day synchronize their data with the main database.
I hope this makes it a little clearer what our dilemma is.
Subscribe to:
Posts (Atom)