Continuous slowness from last 2 week

Question

Continuous slowness from last 2 week

EasyBoy

SSCertifiable

Points: 5301
More actions
September 28, 2015 at 9:46 am

#303466

Hello,
We are supporting one of the client and they are facing slowness from last couple of weeks at a specific time. I have setup the sql profiler but not couldnt find anything unusual. I did check error-logs and it seems perfectly fine. No jobs running during that time.
Am i missing anything? Any input will be greatly appreciate.
Thanks a lot for your help in advance.
Edit: Usually there is no error message encountered by staff. It is just relatively slow, to the point where Application just freezes. They claim that switching tabs takes about 2-3 minutes or when they enter patient information.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 1

Hi EB,

I had been in a similar situation shortly ago. In a nutshell, our issue was an inadequate storage array. I would analyze disk performance using iPerf or similar tool.

One other possibility if you are virtualized is overloading the VM. We were somewhat in that situation as well. There are recommendations based on hardware of client to CPU ratios.

HTH.

Bill

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Bill-89778 (9/28/2015)
Hi EB,
I had been in a similar situation shortly ago. In a nutshell, our issue was an inadequate storage array. I would analyze disk performance using iPerf or similar tool.
One other possibility if you are virtualized is overloading the VM. We were somewhat in that situation as well. There are recommendations based on hardware of client to CPU ratios.
HTH.
Bill

Both of those caused slowness only at a certain time of day?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EasyBoy SSCertifiable Points: 5301 More actions · Answer 3

Yes. User facing the slowness at specific time. They claim that switching tabs takes about 2-3 minutes or when they enter patient information to the application.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

EasyBoy (9/28/2015)
Hello,
...
Edit: Usually there is no error message encountered by staff. It is just relatively slow, to the point where Application just freezes. They claim that switching tabs takes about 2-3 minutes or when they enter patient information.

Frozen applications and slowness when switching tabs or other forms of GUI interaction doesn't necessarily indicate an issue with the database server.

Have you used Activity Monitor or something to see if any performance metric is unusual during the time period that the application slowness occurs?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

EasyBoy (9/28/2015)
Yes. User facing the slowness at specific time. They claim that switching tabs takes about 2-3 minutes or when they enter patient information to the application.

Sorry... my comment was directed at Bill in the quote I included.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Also capture the data from sys.dm_os_wait_stats before and after the period of slowness to understand what the server is waiting 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

Eric M Russell SSC Guru Points: 125562 More actions · Answer 7

Is this topic a duplicate of the one posted yesterday under SQL Server 7,2000 Administration?

Continuous slowness from last 2 week

http://www.sqlservercentral.com/Forums/Topic1723583-5-1.aspx#bm1723614

Confirm if it's SQL Server 2000 or 2008 you have this issue with. The options available for analyzing performance metrics differs significantly between these two versions. For example, DMV queries are not available in v2000.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

EasyBoy SSCertifiable Points: 5301 More actions · Answer 8

My apology.Yes, it is duplicate post by mistake. Somehow, i am not able to delete that post. This issue persisting on SQL Server 2000.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

EasyBoy (9/29/2015)
My apology.Yes, it is duplicate post by mistake. Somehow, i am not able to delete that post. This issue persisting on SQL Server 2000.

For SQL Server 2000, you probably want to use System Monitor to capture trace.

https://msdn.microsoft.com/en-us/library/bb726968.aspx

This article describes how to baseline analysis and suggests a handful of I/O, cache, and CPU related counters relevant to diagnosing performance issues.

http://sqlmag.com/database-performance-tuning/performance-counters

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

EasyBoy (9/29/2015)
My apology.Yes, it is duplicate post by mistake. Somehow, i am not able to delete that post. This issue persisting on SQL Server 2000.

This is a SQL Server 2008 forum, so you may get answers that will absolutely not work with SQL Server 2000. For example, mine suggesting you use the DMVs. They're not available until SQL Server 2005.

"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

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 11

Hi Jeff,

For us, yes. We had backups and file transfers happening between locations.

Bill

EasyBoy SSCertifiable Points: 5301 More actions · Answer 12

Thanks Grant. I agree with you and will keep this in mind from now onward.