SQL Server performance issue

Question

Having following sql server coenfiguration.

OS : Windows Server 2003
SQL Server 2005 Professional edition with SP3
Service Broker enabled
There are schedule jobs but for testing i have disabled agent.

When no user connected, its CPU uses goes upto 90%. I was wondering what is it doing internally.

Executed following statement

select session_id,wait_duration_ms,wait_type,blocking_session_id,resource_description  from sys.dm_os_waiting_tasks

It shows following data

0x0084A208  3   0   395797  LOGMGR_QUEUE    0x027E4BC0  NULL    NULL    NULL    NULL
0x0084A2F8  5   0   6157360 KSOURCE_WAKEUP  NULL    NULL    NULL    NULL    NULL
0x0084A3E8  10  0   6162063 ONDEMAND_TASK_QUEUE 0x027FD760  NULL    NULL    NULL    NULL
0x0084A7A8  12  0   16  PAGEIOLATCH_EX  0x033CFCF8  NULL    NULL    NULL    2:1:158017
0x0084A5C8  13  0   1375547 BROKER_TRANSMITTER  NULL    NULL    NULL    NULL    NULL
0x0084A6B8  14  0   94  PAGEIOLATCH_SH  0x033C96D8  NULL    NULL    NULL    7:1:1473515
0x0084AA78  21  0   94  BROKER_TASK_STOP    NULL    NULL    NULL    NULL    NULL
0x0084A898  56  0   1359813 LCK_M_X 0x03C3F0E0  0x0084A6B8  14  0   databaselock subresource=FULL dbid=7 id=lock3bd3640 mode=U
0x008D84D8  4   0   1719    REQUEST_FOR_DEADLOCK_SEARCH 0x628B00AC  NULL    NULL    NULL    NULL
0x008D85C8  11  0   6161172 CHECKPOINT_QUEUE    0x027FB718  NULL    NULL    NULL    NULL
0x008D87A8  7   0   297 SQLTRACE_BUFFER_FLUSH   NULL    NULL    NULL    NULL    NULL
0x008D8898  9   0   1366563 BROKER_TRANSMITTER  NULL    NULL    NULL    NULL    NULL
0x008D8C58  15  0   94  BROKER_TASK_STOP    NULL    NULL    NULL    NULL    NULL
0x008D8D48  20  0   94  BROKER_TASK_STOP    NULL    NULL    NULL    NULL    NULL
0x008D8E38  51  0   0   OLEDB   NULL    NULL    NULL    NULL    NULL

Here on line 8 It says databaselock subresource=FULL dbid=7 id=lock3bd3640 mode=U

Aaron Bertrand · Accepted Answer · 2018-11-12 17:13:13Z

1

I'm assuming that your sql server is the process that is consuming all of the cpu on your system.

Glen Berry has a bunch of diagnostic DMV queries that might help you with this issue: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!5538.entry

Additionaly Adam Machanic has an awesome stored procedure (who is active) that can give you a snapshot of running/blocking processes: http://whoisactive.com

edited Nov 12, 2018 at 17:13

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

answered Jun 30, 2010 at 19:32

etliens

1,3429 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server performance issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related