Utilizing SQL server resources

Question

Hi

I have a job that runs for 8 hrs. during this job is running, my TFS (team foundation Server) is down. The job and TFS access the same database engine. IS there a chance that my job is utilizing SQL resource completely and hence SQl server do not respond for TFS.? I got timeout error for TFS. Once job is over TFS is up.

How to solve this?

---

Ravi.S Chennai, Tamil Nadu, India

Answer 1

Hi Ravi,

There might be chance that your doubt is rite..

To find out CPU state of SQL SERVER

SELECT 
scheduler_id,current_tasks_count,runnable_tasks_count 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255

The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.

To find out how much memory SQL Server has allocated through AWE.

SELECT 
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
FROM sys.dm_os_memory_clerks

to get the memory consumption by internal components of SQL Server

SELECT TOP 10 type, 
SUM(single_pages_kb) as [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY SUM(single_pages_kb) DESC

to get the memory consumption by internal components of SQL Server

SELECT TOP 10 type, 
SUM(single_pages_kb) as [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY SUM(single_pages_kb) DESC

Or whether check is it a IO problem

SELECT * FROM sys.dm_io_pending_io_requests

Answer 2

Hi

Right now the job is running and TFS hangs.

The resault of above queries as follows.

"SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255" - Gives me runnable_tasks_count = 0 for all rows.

--------------------------------------------------------------

"SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks" - Gives me 0 for AWE allocated

------------------------------------------------------------------------------

Result for "

SELECT TOP 10 type, 
SUM(single_pages_kb) as [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY type 
ORDER BY SUM(single_pages_kb) DESC

is

OBJECTSTORE_LOCK_MANAGER 75952
CACHESTORE_OBJCP 50016
CACHESTORE_SQLCP 44088
CACHESTORE_PHDR 35944
USERSTORE_SCHEMAMGR 17680
MEMORYCLERK_SOSNODE 13288
USERSTORE_DBMETADATA 8288
MEMORYCLERK_SQLGENERAL 5656
MEMORYCLERK_SQLSTORENG 5504
CACHESTORE_SYSTEMROWSET 3704

-----------------------------------------------------------

And i see 1 IO pendion for

SELECT * FROM sys.dm_io_pending_io_requests
So what can i conclude from above results and what could be the solution?

---

Ravi.S Chennai, Tamil Nadu, India

Answer 3

use perfmon and see the counters for system-> processor queue lenght-->

if it is more than 8 , then your server is running over processor bottle neck

which job is running in the server, i mean  what your job does

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

Could you please let us know your configured MAX SERVER MEMORY value?

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 5

MAX SERVER MEMORY value = 2000 MB and use AWE to allocate memory is unchecked.

Minimum memory per query = 1024 KB

---

Ravi.S Chennai, Tamil Nadu, India

Answer 6

Whats the total server memory?

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 7

You mean the RAM in Server? it is 8GB RAM

---

Ravi.S Chennai, Tamil Nadu, India