November 3, 2009 at 5:39 am
Hello All,
My SQL Server database was fine until we upgraded one of our applications on the server and now we have noticed poor performance on the application side, high memory usage, and high CPU usage.
To try to resolve this issue the following was done:
-Installed 4GB additional memory totaling 16GB on the server
-Added reorganize index, rebuild index, and update statistics to the maintenance plan
still problem exists. I ran SQL server profiler and the Performance monitoring at the same time to try to figure out what is causing the sluggish performance. The following counters were high to my knowledge:
Page Faults/sec
SQL Recompilations/sec
Lock Timeout/sec
Lock wait time (ms)
Lock waits/sec
Buffer cache hit ratio
Database pages
checkpoint pages/sec
page life expectancy
% Processor Time
Pool Nonpages Bytes
Transition Faults/sec
Free system Page Table Entries
Pool Pages bytes
Available Mbytes
The following SQL Server trace was performed on the server:
Errors and warnings
Locks
Stored procedures
TSQL
I was able to import the performance data into SQL Server Profiler and now, what should I be looking for? By looking at the profiler with the performance monitor, how would I figure out what is wrong with the system/SQL Server?
Please advice.
November 3, 2009 at 7:41 am
Hi,
In my opinion, your not going to get a lot of definitive answers when it comes to performance questions such as this. The reason for this is the very nature of the beast. Performance is influenced by many different things. If i were you i would post the numbers your getting as well as the counter. You'll probably get more people interested in trying to help you.
Brian
Think great, be great!
November 3, 2009 at 8:05 am
What I'd look for is items in Profiler that are either
a) executed a large number of times (group by and count the SQL command/proc call)
b) things that take a long duration to complete.
That should get you some low hanging fruit to examine and try to tune.
I do agree with the post above in that knowing some of the counters you think are high, or how they deviate from a baseline would help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply