Post reply

Application Slowness issue

  • May 29, 2013 at 5:32 am

    #293761

    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

    #1619256

    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

    #1619263

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 29, 2013 at 8:14 am

    #1619306

    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_SLEEP 1193793959 1367

    LATCH_EX 89361955 3223305

    CXPACKET 41858655 1684455

    BACKUPIO 10339306 341072

    BACKUPBUFFER 9273282 203284

    ASYNC_IO_COMPLETION 8343013 54

    LCK_M_S 8037900 18372

    LCK_M_U 6707010 1635

    LCK_M_IU 3575857 1652

    WRITELOG 3188065 414686

    PAGEIOLATCH_SH 2587725 13881

    ASYNC_NETWORK_IO 1465321 7457

    SOS_SCHEDULER_YIELD 574212 536542

    BACKUPTHREAD 302396 588

    LCK_M_IX 293313 189

    Thanks

  • May 29, 2013 at 8:21 am

    #1619312

    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

    #1619318

    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

    #1619324

    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

    #1619331

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 29, 2013 at 8:59 am

    #1619345

    Thank you Grant...this will help me a lot

  • May 29, 2013 at 10:07 am

    #1619379

    BTW, if you haven't read Grant's book he referenced earlier, I'd recommend it. It's good.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply