Lazy CPUs ?? 7 Idle, 1 maxed out

Question

Lazy CPUs ?? 7 Idle, 1 maxed out

homebrew01

SSC Guru

Points: 55548
More actions
March 2, 2014 at 5:04 pm

#284178

I've got a couple of long running SQL jobs going, and am getting "Lock request timeout period exceeded" and "SQL is busy" messages when using Management Studio. I opened Windows Task Manager and see that processor #4 is steady at around 80% - 90 %, while the other 7 processors are showing almost no activity on the graph .... bouncing along the bottom barely above 0%.
Do I have some settings wrong on the server or SQL ?
Windows Server 2003 R2 Standard Edition
SQL 2008 R0 Standard SP1
EDIT 3-3-2014: To clarify, I do see the other processors working sometimes, but was surprised this time to see one doing all the work.

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

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

homebrew01 (3/2/2014)
I've got a couple of long running SQL jobs going, and am getting "Lock request timeout period exceeded" and "SQL is busy" messages when using Management Studio. I opened Windows Task Manager and see that processor #4 is steady at around 80% - 90 %, while the other 7 processors are showing almost no activity on the graph .... bouncing along the bottom barely above 0%.
Do I have some settings wrong on the server or SQL ?
Windows Server 2003 R2 Standard Edition
SQL 2008 R0 Standard SP1

Do you see any blocking in SQL Server ? And also did you check the Wait Stats ..

What are your MAXDOP and Cost threshold for Parallelism settings and CPU related settings ? Do you see any long running queries ? What are your Query Timeout settings ?

And what does the memory & disk counters say ?

Memory: Available MBytes

SQLServer:Memory Manager: Total Server Memory (KB)

SQLServer:Memory Manager: Target Server Memory (KB)

Page Life Expectancy

Paging File % Usage

Avg DiskSec\Read

Avg DiskSec\Write

--

SQLBuddy

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I can only answer a couple of those right now.

MAXDOP is 0, and Cost Threshold for Parallelism is 5. There was no blocking, but I know there was a long running query that updates many rows in a table.

From task manager, total memory is 3405672 k. At the time, available was 470632 k.

All I captured at the time was a task manager screenshot. Profiler was not running. I have a SQL server side trace running 24/7.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

homebrew01 (3/2/2014)
I can only answer a couple of those right now.
MAXDOP is 0, and Cost Threshold for Parallelism is 5. There was no blocking, but I know there was a long running query that updates many rows in a table.
From task manager, total memory is 3405672 k. At the time, available was 470632 k.
All I captured at the time was a task manager screenshot. Profiler was not running. I have a SQL server side trace running 24/7.

Am I reading that correctly? You have an 8 CPU box with only 3.4GB total memory?

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Jeff Moden (3/2/2014)
Am I reading that correctly? You have an 8 CPU box with only 3.4GB total memory?

Bad News: Not much memory

Good News: Approval to upgrade all our servers & go to SQL 2012

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

homebrew01 (3/2/2014)
I can only answer a couple of those right now.
MAXDOP is 0, and Cost Threshold for Parallelism is 5. There was no blocking, but I know there was a long running query that updates many rows in a table.
From task manager, total memory is 3405672 k. At the time, available was 470632 k.
All I captured at the time was a task manager screenshot. Profiler was not running. I have a SQL server side trace running 24/7.

Try to capture those metrics which will give us more insight into the problem. It looks like the system is 32-bit.

Did you enable \

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

sqlbuddy123 (3/2/2014)
homebrew01 (3/2/2014)
I can only answer a couple of those right now.
MAXDOP is 0, and Cost Threshold for Parallelism is 5. There was no blocking, but I know there was a long running query that updates many rows in a table.
From task manager, total memory is 3405672 k. At the time, available was 470632 k.
All I captured at the time was a task manager screenshot. Profiler was not running. I have a SQL server side trace running 24/7.
Try to capture those metrics which will give us more insight into the problem. It looks like the system is 32-bit.
Did you enable \

Sorry for the incomplete post .. Did you enable \3GB Switch and Lock Pages in Memory.

Also check for any CX_Packet Waits that indicates parallelism check the daily maintenance jobs if they ran successfully.

Also check for Index fragmentation and outdated statistics. In addition, check if SQL Server is looking for any missing indexes ..

--

SQLBuddy

dbalmf SSCrazy Points: 2063 More actions · Answer 7

Another shot in the dark.. 🙂

How many Tempdb data files do you have? Using current wisdom on this you should have eight. I have seen your symptoms on a server with only one Tempdb datafile.

See this link for more details

http://www.sqlskills.com/blogs/paul/tempdb-configuration-survey-results-and-advice/

Ta

David

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Just 1 tempdb file. I recall reading conflicting opinions, so I left it at 1.

To clarify, I do see the other processors working sometimes, but was surprised this time to see one doing all the work.

dbalmf SSCrazy Points: 2063 More actions · Answer 9

I would start by adding tempdb data files inline with the current wisdom from SQL Product Support which is to have 1 file per core up to 8 cores, over 8 cores have 8 files but monitor and see, if you still have contention issues add more files in groups of 4 files at a time.

As a good starting point for other server setup points the guys and girls over at Brent Ozar Unlimited have a good article:

http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/

Ta

David

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

I don't believe this is a TempDB issue even if the symptoms say it is. I think it's either a resource intensive query that might need some tweeking because it's not using parallelism (or has an accidental many-to-many join in it) or there's something wrong with the server allowing the use of only 1 CPU.

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 11

homebrew01 (3/3/2014)
Just 1 tempdb file. I recall reading conflicting opinions, so I left it at 1.
To clarify, I do see the other processors working sometimes, but was surprised this time to see one doing all the work.

I don't think it's tempDB issue too .. Try to check the things that I outlined in the previous reply ..

--

SQLBuddy