May 29, 2013 at 5:32 am
Hi,
My database is working fine, but application is getting slow. According to the client,If they login with one or two user application is fast. If all the user login at the same time the software became very slow.
Is this a Database problem or Network issue.
Can anyone please tell me the solution?
Thanks in advance.
May 29, 2013 at 6:23 am
My immediate thought would be to check the wait statistics on the server - It's not the most definitive way of checking performance issues but could possibly point you in the right direction.
Note that the values returned are cumulative since SQL's last restart
SELECT TOP 15
wait_type ,
wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
May 29, 2013 at 6:36 am
To know if it's the app or the database, you need to capture performance metrics from the database as it's running. There's a lot to that. I wrote about a series of server & query metrics in the first two chapters of my book on query tuning. It's that involved.
But, for a quick hit, use the dynamic management object (DMO) sys.dm_exec_requests to see what is currently running on the system. The main thing you'd be looking for here would be any processes that are blocked or blocking others. That will give you a hint at how things are behaving on the system, but it doesn't even start to document what's going on.
"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
May 29, 2013 at 8:14 am
Thanks for the reply...
I run the query, but still I am not able to get, what is the issue. Please take a look the results when i executed the query.
SQLTRACE_INCREMENTAL_FLUSH_SLEEP11937939591367
LATCH_EX893619553223305
CXPACKET418586551684455
BACKUPIO10339306341072
BACKUPBUFFER9273282203284
ASYNC_IO_COMPLETION834301354
LCK_M_S803790018372
LCK_M_U67070101635
LCK_M_IU35758571652
WRITELOG3188065414686
PAGEIOLATCH_SH258772513881
ASYNC_NETWORK_IO14653217457
SOS_SCHEDULER_YIELD574212536542
BACKUPTHREAD302396588
LCK_M_IX293313189
Thanks
May 29, 2013 at 8:21 am
arooj300 (5/29/2013)
Hi,My database is working fine, but application is getting slow. According to the client,If they login with one or two user application is fast. If all the user login at the same time the software became very slow.
Is this a Database problem or Network issue.
Can anyone please tell me the solution?
Thanks in advance.
Software is slow is so very generic. You really need to get them to be more specific about the problems they are having. What is slow and what do they mean by slow. Are they getting time outs while doing specific functions, what?
May 29, 2013 at 8:26 am
Hi lynn,
when they are updating or selecting any data that time the performance will be slow.
Thanks
May 29, 2013 at 8:32 am
arooj300 (5/29/2013)
Hi lynn,when they are updating or selecting any data that time the performance will be slow.
Thanks
Define slow.
May 29, 2013 at 8:44 am
Getting the wait statistics gives you some idea of what is causing the server to run slow, on average. To understand what is causing the server to run slow over a period of time, you'd need to capture the wait statistics more than once. Say, now and 1/2 hour from now, then compare the two values to see what has caused slowness of the time period in question.
But, that still doesn't give you an accurate measure of what "slow" is on the system. And it doesn't tell you which queries are causing the problems. What you need is a measure of the page load of the app and a measure of any and all queries run by that page load. See which is taking up the most time.
Since you're posting in a 2008 forum, I'd suggest reading up on extended events as a way to measure query performance.
Here's the deal. You're asking us to give you a neat, packaged answer. Click this button and it will tell you all you need to know. Problem is, it doesn't work like that. There's a lot of work in setting up methods to gather metrics in order to understand how things are behaving on your system.
How about this. As a starting point, you might want to check out this book (free to download) Troubleshooting SQL Server[/url]. It can help to get you going on this.
"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
May 29, 2013 at 8:59 am
Thank you Grant...this will help me a lot
May 29, 2013 at 10:07 am
BTW, if you haven't read Grant's book he referenced earlier, I'd recommend it. It's good.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply