April 20, 2008 at 11:01 am
Hi All
I got a SQL server, that has been running normally, a few days back , i have made one job run 3 times more than it runs usual per minute, earlier the job runs once in every 3 minutes, and takes 30 secs to run, now it runs each minute taking the same time, but now the server seems to max its CPU and running out of resources, how do i check that how i can improve this server performance, what are the measures i have to take into account, how do i do it, i dont want to simply increase the server configuration, i want to do an analysis, how much the server can take, how can i do it?? please do drop a word
Cheers
Suji
April 21, 2008 at 6:52 am
Before you start focusing on the server, I'd focus on the code and the structure of your database. Is the code set based or row-by-row? Is it using indexes to get nice clean seeks or is it doing table scans or index scans. If it's doing scans, do you have good indexes in place. Are you maintaining your index fragmentation and statistics? Being a development dba, these are all my first places to hit. After that I start worrying about server configuration. More often than not, a simple, default install, will work very well assuming you've got a good set of code & structures running on it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2008 at 7:19 am
You may also want to take a good look at what you are actually doing. A job that runs every minute is probably not the most efficient process.
What are you actually trying to do? Is this process waiting for something to happen? Perhaps more of an event-driven model will be a better choice over constantly connecting, querying, and either doing something or doing nothing.
A job that has to run every minute is often suspicious of being a bad design. I would especially be concerned that you have something running once a minute that takes 30 seconds to complete.
April 22, 2008 at 5:46 am
Thanks Grant, i have looked into the fragmentaion level, it seems to be a bit high on certain tables, i am working on to defragament it, i also have looked into Free system cache to clear some un used system cache, with this all in place my CPU is maxing out to 90 % all the times, i have got a 6GB RAM and out of whicn 5 is allocated to SQL, its got 4 CPU's, is there any thing else i need to check to reduce the CPU usage, if i concentrate on the Query and fix it, will it reduce the CPU??, pleae advise
Cheers
Suji
April 22, 2008 at 6:25 am
The honest answer is, it depends. But I've found that most of the time, performance problems are in the code and the structure, not the system. That assumes the system isn't really tiny (yours isn't) and isn't sharing resources with five other services or something (is yours?). After that, it's almost always something that the code, whatever kind of code we're talking about, is doing to the system or it's bad database design or missing or inappropriate indexes. So, that's why I always recommend starting there.
Have you run Profiler during your heavier load times to collect information on which processes or procedures are putting the most load on the system? That'll give you more information and focus or it'll tell you that it's not your queries and then you can refocus on the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 8:06 am
Thats correct Grant, i have checked the system and it seems to be working fine, i have some Quries that take a large amount of CPU, in one application they are using select statement to retrive data, hope this affects the performance, will this performance be improved by changing this into a stored procedure?? then when a stored procedure is called its creating that stored procedure again, what shall i do to avoid this from happening??
Cheers
Suji
April 23, 2008 at 8:20 am
Turning a badly performing query into a stored procedure won't improve performance. You'll need to determine what the query is doing. Is the function being done in the best way? Should it be done at all? What's the execution plan? Maybe it just needs a different join or a better index. You just have to drill down now that you've identified a bottleneck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 8:44 am
Cheers Grant, i have also found one more problem with the SQL , once i used DBCC DropClearbuffer, the CPU usage drops a fair amount say from 100% to 20%, for a small time, and then it comes up again, is it advisible to run this as a job for a temp solution by the mean time i figure out the Query problem , and other thing is when a SP is called from the applicaiton , it creates a new SP with Create procedure , why is this happening, is this a good sign or is there any thing i can do to avoid this.
Cheers
Sujith
April 23, 2008 at 9:06 am
I don't know about the DBCC thing. Sorry.
It sounds like you've got a CREATE statement inside your procedure definition. That's a problem. You'll need to remove that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply