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.

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

THE ENVIRONMENT

environment before:

enter image description here

Basically 2 databases that access a shared database, via direct calls:

select * from SharedDB..Table1 where... (doing select, updates, inserts, delete etc)

environment now:

enter image description here

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 ...

THE PROBLEM

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

variables:

  • 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 ?

enter image description here enter image description here

Stats obtained from query described here DMV Queries as Perfmon Counters5

share|improve this question

closed as off-topic by Paul White, bluefeet, Mark Storey-Smith, Kin, Shawn Melton Dec 5 '13 at 3:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White, bluefeet, Mark Storey-Smith, Kin, Shawn Melton
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Have you checked wait stats during these slow periods? What does that show? –  Max Vernon Nov 29 '13 at 16:03
1  
Is DB1 running on the same SQL Server instance as SharedDB or is it running on another SQL Server instance? (Yes, it is possible to create a linked server that points back to itself.) Check for network throughput and latency between DB2 and SharedDB. You might check Paul Randal's posts starting here: sqlskills.com/blogs/paul/… –  RLF Nov 29 '13 at 16:17
3  
Shot in the dark would be that you have queries pulling a sizeable data volume from the remote calls. On the same box that data moved at 10GB/s, now its moving at 70MB/s across the network. Try Michael Swart's Top 20 scripts to identify the problem queries, grab execution plans for the likely candidates and add the output to your question. –  Mark Storey-Smith Nov 29 '13 at 18:41
2  
And my guess would be the same as Mark Storey-Smith's shot in the dark. –  RLF Nov 29 '13 at 18:50
1  
What server are those stats taken from? –  Shawn Melton Nov 29 '13 at 20:26