slow performance

Question

slow performance

Grace09

SSCommitted

Points: 1532
More actions
July 9, 2015 at 2:41 pm

#305676

I have a sql server 2008 r2 on windows server 2008 r2. Users have been complaining about the slow performance. from the task manager, the memory utilize at about 90%, CPU spikes to 80% sometimes, but went down in a few seconds. I do see the CPU goes up when running some parallel queries. Can you help me identify if my server has cpu or memory bottleneck? Thanks in advance.
wait_type wait_time_s pct running_pct
CXPACKET 62482.93 42.0742.07
PAGEIOLATCH_SH 35695.04 24.0466.11
SOS_SCHEDULER_YIELD 16345.95 11.0177.12
BACKUPBUFFER 7864.70 5.30 82.41
ASYNC_IO_COMPLETION7846.66 5.2887.70
BACKUPIO 4159.832.80 90.50
WRITELOG 3290.542.22 92.71
PAGEIOLATCH_EX 2203.061.48 94.20
ASYNC_NETWORK_IO 1724.761.16 95.36
OLEDB 1696.091.14 96.50
IO_COMPLETION 1047.590.71 97.21
LCK_M_SCH_S 974.830.66 97.86
LATCH_EX 591.440.40 98.26
SQLTRACE_FILE_BUFFER427.430.29 98.55
LCK_M_IX 400.850.27 98.82
LCK_M_X 362.40 0.24 99.06

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Grace09 (7/9/2015)
I have a sql server 2008 r2 on windows server 2008 r2. Users have been complaining about the slow performance. from the task manager, the memory utilize at about 90%, CPU spikes to 80% sometimes, but went down in a few seconds. I do see the CPU goes up when running some parallel queries. Can you help me identify if my server has cpu or memory bottleneck? Thanks in advance.
wait_type wait_time_s pct running_pct
CXPACKET 62482.93 42.0742.07
PAGEIOLATCH_SH 35695.04 24.0466.11
SOS_SCHEDULER_YIELD 16345.95 11.0177.12
BACKUPBUFFER 7864.70 5.30 82.41
ASYNC_IO_COMPLETION7846.66 5.2887.70
BACKUPIO 4159.832.80 90.50
WRITELOG 3290.542.22 92.71
PAGEIOLATCH_EX 2203.061.48 94.20
ASYNC_NETWORK_IO 1724.761.16 95.36
OLEDB 1696.091.14 96.50
IO_COMPLETION 1047.590.71 97.21
LCK_M_SCH_S 974.830.66 97.86
LATCH_EX 591.440.40 98.26
SQLTRACE_FILE_BUFFER427.430.29 98.55
LCK_M_IX 400.850.27 98.82
LCK_M_X 362.40 0.24 99.06

Without even looking at the numbers above, if such a bottleneck exists, it's not likely due to hardware. It's usually a code problem. What is the code that is the problem? Start opening SSMS, pressing the {f8} key to open the "Object Explorer" (if it's not already open), right click on the instance name, select {Reports} and then {Standard Reports}. After that, chose your poison for either of the two "Performance - Top Queries By" to get a list of the top 10 consumers of CPU or IO. Keep in mind that the queries are only for those that are in cache and it is possible that they might have been in cache since the last time the SQL Service was restarted or the last time the cache was cleared by displacement by other objects.

My recommendation after that is to fix the top 5 queries in both categories and then do it all again.

--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)

Grace09 SSCommitted Points: 1532 More actions · Answer 2

Jeff, should I run the report of Top queries by total or average cpu/IO?

Thanks.

Grace09 SSCommitted Points: 1532 More actions · Answer 3

One of the queries comes in on the top in both reports (top queries by total IO and CPU).

It has a total of 1,135,492.95 ms of total CPU time. The avg. CPU time is 5.63 ms only, but execute for 201,682 times.

It has a total of 672,046,743 for the Total Logical IO #, of which # Avg. Logical IO is 3,332.21.

The query text is

fetch next from Cursor1 into @nMpp_factor, @nRez_id, @nMpl_id, @nMpp_pos,@nMpp_portion,@nArt_id

Thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

In the report itself, you should see a little [+] sign next to the query text. Click that to expand and tell me what the full "Compile Time" is.

This type of "cursor" code is usually generated as an artifact of one provider or another in response to some front-end or "managed code". It's going to be a bit difficult to isolate this code well enough to figure out where it's coming from but it is possible to do.

Before we start all that, though, we need to make sure we're not chasing ghosts from code that has been compiled and running for a month or something like that. That's why I need the "Compile Time" that I mentioned above.

I'll also tell you that 5.63 ms average is an awfully long time for such a short call especially since it's only pulling 1 row tiny row into variables. Although I'll likely get some disagreement from some folks, the number of reads for this don't seem right, either. That's a hell of a lot of reads for a "Fetch Next". Remember that a "read" is 8,192 bytes. It would also be interesting to see what else is going on because, by themselves, these numbers look like the tip of the proverbial iceberg.

So... what's the "Compile Time" and how many users to you typically have using the system?

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

And the first several chapters of my book on query tuning should help.

"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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Grant Fritchey (7/10/2015)
And the first several chapters of my book on query tuning should help.

I know Gail's fine articles on performance tuning don't cover the ODBC cursors that are created by the front end but it's been a good while since I've read your book on the subject (you should attach a link, as well. Great book that everyone should read) and I don't remember if you cover how to find the actual code that they represent.

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

@Grace09,

Do you have that "Compile Time" that I asked for?

--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)

Grace09 SSCommitted Points: 1532 More actions · Answer 9

Jeff, The Compilation Time is from the last time the sql server were rebooted. Thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Grant Fritchey (7/10/2015)
And the first several chapters of my book on query tuning should help.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Grace09 (7/9/2015)
I have a sql server 2008 r2 on windows server 2008 r2. Users have been complaining about the slow performance. from the task manager, the memory utilize at about 90%, CPU spikes to 80% sometimes, but went down in a few seconds. I do see the CPU goes up when running some parallel queries. Can you help me identify if my server has cpu or memory bottleneck? Thanks in advance.

What is your max mem set to?

How much memory is on the server?

A spike that goes back to normal after a few seconds is not an indicator of a cpu bottleneck. If you had a sustained spike, that would be more indicative of a cpu bottleneck.

It seems more apparent that you have either some code that could be optimized for that occasional spike or potentially a missing index.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

Grace09 (7/10/2015)
Jeff, The Compilation Time is from the last time the sql server were rebooted. Thanks.

Ok... but I need to know what that date and time was, please.

--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)

Grace09 SSCommitted Points: 1532 More actions · Answer 13

Jeff, the compilation Time is on 6/13. Thank you.

Grace09 SSCommitted Points: 1532 More actions · Answer 14

The CPU doesn't stay at high utilization. It spikes when queries are running. I set the Max Memory as 12 GB out of 16 GB. The task manager shows it is 92%. I wonder if it is memory pressure.