August 17, 2014 at 8:21 pm
Recently one of customer was complaining about performance of his application. and he definitely said that there was problem on my database side.
and the database is about 2GB. and 700 MB in use. So I started looking into it. and what I found:
SQL Server version: SQL Server 2008 R2
database compatibility level is SQL 2000
1.Performance monitor
1) buffer cache hit ratio is 100%
2) Page life Expectancy is 71954s
3) User Connections is less than 100
4) Transactions is less than 10
2. SQL Trace
5) Total Server memory is 6338MB
6) Working set is 466MB ( I don't why it's lower than the max memory I configured)
7) Most of queries SQL Trace captured were finished within 7s
What shall I do else to diagnose the problem?
August 17, 2014 at 11:17 pm
Check wait statistics. Is SQL the only software installed on the server?
For the SQL trace, what sort of logical IO are you seeing?
7 second queries are quite slow for an OLTP application, everything should be sub-1 second, with most queries finishing sub-100 ms. However reports can run much slower.
If you run through Glen Berry's performance queries, that will give you much more insight as to where the issue is.
http://sqlserverperformance.wordpress.com/
Mainly check wait stats and which queries are performing the most IO.
August 18, 2014 at 5:39 am
This was removed by the editor as SPAM
August 18, 2014 at 5:57 am
All those are server resource measurements, which are interesting and can suggest if you have a problem. But, to quickly determine if things are running long (whether they're maxing out the server resources or not), I'd suggest running a query against sys.dm_exec_query_stats. That will allow you to see, for the queries currently in cache, how long they run on average, their longest run, the amount of resources they use, etc. You can combine this with sys.dm_exec_sql_text to see the query itself. These are called Dynamic Management Objects (DMO, views and functions are DMV & DMF, so you can look them up later). It's a great resource for quick and simple troubleshooting.
"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
August 18, 2014 at 3:22 pm
I appreciate all input. Below is my update
1) Wait stats
WaitType Wait_SResource_S Signal_S WaitCountPercentage
WRITELOG 15414.3515104.01 310.34 329591927.03
BROKER_RECEIVE_WAITFOR9600.169600.12 0.03 32 16.84
ASYNC_IO_COMPLETION5881.595880.64 0.95 1113 10.31
BACKUPIO 5742.515721.43 21.08 130227510.07
BACKUPBUFFER 5634.85547.3 87.5 7794499.88
2) In total, it has 4 SQL Server Instances on this server.
3) I run the sys.dm_exec_query_stats and added indexes to improve the performance of top 5 queries which consuming most CPU/IO.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply