Return to Question

As part of another question (http://dba.stackexchange.com/questions/54171/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.

2 servers, one server accesses a database on the first via a linked server, for nice diagram, please see http://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

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 http://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

As part of another question (https://dba.stackexchange.com/questions/54171/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.

2 servers, one server accesses a database on the first via a linked server, for nice diagram, please see https://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

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 https://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

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

Threads during the CPU spike

As part of another question (http://dba.stackexchange.com/questions/54171/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 http://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

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!

As part of another question (http://dba.stackexchange.com/questions/54171/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 http://dba.stackexchange.com/questions/54171/sql-server-2012-performance-issues-maybe-due-to-linked-server

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:

I get this when there is no CPU spike:

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 http://dba.stackexchange.com/questions/54171/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..