May 20, 2008 at 7:05 am
From last couple of days our production DB is running at 100% CPU usage, after going through profiler we are able to trace using Audit Login option that every fraction of second some login activity is happening on DB. My first question is, Is this normal?
And, if this is not normal, how I can find out this activity is initiated from which web server/client/machine/application. We use same SQL login for each application which has got very limited access to DB but each developer logins using windows authentication.
Query time is too high even for small SQLs though they were running okay till last week, may be because CPU is running at 90 - 100% most of the times.
Any ideas will be great help!!
Thanks in Advance!!
May 20, 2008 at 7:53 am
From last couple of days our production DB is running at 100% CPU usage, after going through profiler we are able to trace using Audit Login option that every fraction of second some login activity is happening on DB. My first question is, Is this normal?
While it might be normal for some systems I don't think that the case here.
And, if this is not normal, how I can find out this activity is initiated from which web server/client/machine/application. We use same SQL login for each application which has got very limited access to DB but each developer logins using windows authentication.
Either add the Hostname as a column in your Profiler trace or even more simple check Activity Monitor in SSMS.
[font="Verdana"]Markus Bohse[/font]
May 20, 2008 at 7:19 pm
Thanks for the reply Markus!
Though now I can see which web server is initiating this login, I stopped that web server and now I can see same activity from other web server. The common thing between these 2 servers is; they are hosting .Net 2.0 applications and the activity is also initiated from .Net Client Data Adapter. Now, is it normal for .net applications querying the database constantly every second, and if yes, I think thats not good as that's putting extra load on DB server as well as on network.
Any thoughts??
May 20, 2008 at 9:13 pm
As far as I know, a web server uses an application account to connect to a database server. If you kill its spid, it will connect the database right away. We have to use some special approaches to break the connection.
A connection from a web server to a database unnecessarily means there is any activity. To check whether or not there is any activity, we can use,
sp_who2 active
May 20, 2008 at 9:16 pm
It depends on what the app is doing. I've got a web app that hits the database server about 2600 times an hour (user initiated). And that's still a baby compared to things like Amazon. The hit on the database server will depend on what is being run each time the DB is hit. Just logging in to it shouldn't create massive spikes in CPU usage.
Check out this thread for help tracking CPU spikes:
http://www.sqlservercentral.com/Forums/Topic493141-146-1.aspx
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 20, 2008 at 11:42 pm
thanks for this tip Scott!!
I did exactly what you suggested in above thread, there's no single thread which goes to above 90% and stay there for long. most of them just jump to 80 - 90% and then flat back to 0.
We've done the rebuilding/reorganizing of indexes yesterday and statistics update too, what else we can look into?
The performance is really very slow and most of the options on web sites are not able to finish and come back as DB timed out!!
May 20, 2008 at 11:54 pm
do you have anything else on your production server.
"Keep Trying"
May 21, 2008 at 2:09 am
Work with your application developers to find out the exact reason for frequent logins. Check for things such as connection pool etc.
May 26, 2008 at 4:04 pm
Check to see that your queries are using the indexes that are there.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 27, 2008 at 9:01 am
Did you inclue any jobs recently or did any changes, this might affect as well, i faced the same problem and when i looked into it, it was a SP that runs every 5 minutes that has been changed by one of my mate, that made the CPU spike high, once the Query is fixed then it was fine,
Check for Query that consumes most resources,
which version of SQL are u using, its more easy to check on 2005
May 27, 2008 at 9:05 am
1. Check the index fill factor .
Ideally it should be 70% for highly updating tables and 100% for rest DML opearations.
2. Check whether any anti-virus is installed on SQL server and SQL server activities are not eliminated from scanning of anti-virus software.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply