Summary of the problem
We've spit our one database server environment into two servers.
We are now having intermittent performance issues
I am hoping someone can offer clues - perhaps direction how to investigate further
Basically 2 databases that access a shared database, via direct calls:
select * from SharedDB..Table1 where...
(doing select, updates, inserts, delete etc)
In order to maintain the same exact code in both databases, both databases access the shared DB via a LINKED SERVER:
select * from LinkedServer.SharedDB.dbo.Table1 where ...
On the new server (2012), we are seeing intermittent performance issues:
- CPU spikes to 85% (normally 15%), for about 5-15 min
- run time of all queries increases 10x
- spike occurs sometimes twice an hour, sometimes once every 3 hours - very intermittent
WHAT WE KNOW / TRIED
- we are aware of issues in general with linked server, and our first guess was bad linked server queries but we have optimized those
- we could not correlate the performance issues with any maintenance jobs, or queries that run from time to time in general
- both sql and windows servers have the latest patches (sql server 2012 has SP1 with CU7)
- network connection between the server is very reliable, local 1GB connection
- both DB1 and DB2 have the exact same TSQL code. both access the SharedDB via linked server
- both DB1 and DB2 get very high, and uniform utilization so if the issue was due to some one query, the issue should be so intermittent. Also, studying the performance of all queries during the slow down and normally, does not reveal one query running particularly slow
- both DB1 and DB2 access the linked server constantly - not only when the cpu spike occurs (although still 99% of the work on the databases happens without accessing the sharesDB)
- the system is not new and had been in production without significant change for years
- both physical server are configured as similarly as possible, and are not virtualized
- different OS and SQL Server versions
- DB1 still on the same box as SharedDB, DB2 on a different physical box
UPDATE DEC 4 Due to the comments I got (thank you all btw!), I have found a correlation between Bytes Received and CPU spikes (ie, the lag) - see as you can see in the diagram below.
QUESTION - is there an easy way to track down the stored procedures that could be causing this? some kind of a select to see top network usage or something ?