Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As part of another question (SQL Server 2012 performance issues (maybe due to linked server)) I've identified a CPU spike to be directly related to a spike in Bytes Received, which leads me to believe it is likely due to the link server.

THE ENVIRONMENT

2 servers, one server accesses a database on the first via a linked server, for nice diagram, please see SQL Server 2012 performance issues (maybe due to linked server)

enter image description here enter image description here CPU spike directly related to Bytes Received Spike

QUESTION How to find which queries could be causing it??

TYPES OF QUERIES WE RUN

I've went through all our stored procedures, and they are optimized. Here are the types of linked server access that we do:

select one value from remote table

select @userid = userid from FBGC.FBGCommon.dbo.players where playerid = @playerid

execute stored procedure that returns result set of max few hundred rows

exec FBGC.Fbgcommon.dbo.qPlayersFriends @PlayerID

execute stored procedure that returns one value

exec @HasEnoughCredits = FBGC.FBGCommon.dbo.qDoesPlayerHaveEnoughCredits @PlayerID, @Cost

delete a few rows from remove table

delete from FBGC.FBGCommon.dbo.InactivePlayersToBeWarned where PlayerID = @AccountOwnerPlayerID

Update one row in remote table, or add one row to remote table

update FBGC.FBGCommon.dbo.users set XP = XP + @XPForTitle where userid = @userid
insert into FBGC.FBGCommon.dbo.UserXPHistory (userid, time, XPReceived, XPFromTypeID, XPFromRealmID) values (@UserID, GETDATE(), @XPForTitle, 1, @ThisRealmID)

I believe all of these are optimized, yet, it appears we are joining tables over the linked server!


Some interesting wait stats that I do not know how to interpret. Ran a query from http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I get this DURING the CPU spike: enter image description here

I get this when there is no CPU spike: enter image description here

These wait times are on the server with DB2, the SQL 2012 server which accesses a remote database on another server running sql server 2008 (for diagram, see SQL Server 2012 performance issues (maybe due to linked server)


Restarting sql server help - no CPU spike for significant amount of time.

We've also found that, restarting SQL Server helps - we could be getting intermittent spikes for hours, many times an hour, then after restart, no spikes for an hour or more..


Process explorer

As suggested by @EdwardDortland, I used Process explorer to see what thread is consuming most CPU during the slow down

unfortunately, no single thread shows up as the main culprit - during CPU spike, simply all threads consume more CPU. although I am seeing a lot more threads highlighted in red (meaning deleted) during the CPU spike - not sure how to interpret this.

Threads before the CPU spike enter image description here

Threads during the CPU spike enter image description here

share|improve this question
1  
Start with sys.dm_exec_query_stats. High xxx_worker_time values mean high CPU consumption. –  Remus Rusanu Dec 4 '13 at 21:54
    
See this answer stackoverflow.com/a/10320158/847990 –  Shawn Melton Dec 5 '13 at 3:36
add comment

2 Answers

To answer the question: How to find the offending query:

Since the spikes in the graph you posted last for several minutes you have plenty of time to use the following method:

Download sysinternals process explorer

  1. start process explorer and find the SQL Server process.
  2. right click and select properties
  3. look at the thread tab.
  4. Sort on the CPU column and note the thread id (TID) that is consuming the most CPU.

Use this query and lookup the query that is currently being executed by that thread:

SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <thread id>
share|improve this answer
    
Thanks Edward, I am trying that! –  Greg Bala Dec 5 '13 at 13:40
    
Thank again Edward! I did as you suggested, and added the out put to the question. unfortunately, no single thread seems to be consuming most CPU - it just seems like all threads just simply slow down :( I am starting to think it is not my queries that are causing this ... –  Greg Bala Dec 5 '13 at 15:03
add comment
up vote 0 down vote accepted

Since I found the reason for the issues, I would like to share it incase someone else may be facing a similar situation.

The reason for my problem, was not the linked server, but simply resources contention - simply not enough memory for the SQL Server with these databases!

SO - if you have similar symptoms, it could be simply memory.

Reason why this was not obvious for us, is because there was plenty of memory left on the server, because wrong (32bit) version of SQL Server was installed - it was simply not using more memory because it could not

For a great way of checking for memory pressure, see comments in this question SQL Server 2012 SP1 (CU7) MUCH slower than SQL Server 2008 - thanks Paul White and others for their insight!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.