Home Forums SQL Server 2008 SQL Server 2008 - General SQL Query causing CPU Spikes on SQL Server 2008 r2 RE: SQL Query causing CPU Spikes on SQL Server 2008 r2

  • January 14, 2013 at 1:28 am

    #1576779

    APA0876 (1/11/2013)

    anthony.green (1/11/2013)

    I would completly re-write it all, for one all the tables used are depreciated and are in the product for SQL 2000 compatability only, you really should be using the DMV's.

    Hi,

    Based of further investigation, it does not matter that I re-write the query using the DMV as I execute a select to the equivalent view SELECT * FROM sys.dm_tran_locks and I get the cpu spike too.

    The issue is related to the following views:syslockinfo, sys.dm_tran_locks apparently when the server have 12000 sessions which is our case, the query to those views is causing CPU spike as we have another server with similar specs but the difference is the number of sessions and does have the spike. We are talking here of a very powerfull machine , 80 CPUs, 256 GB RAM

    Can anyone offer some feedback regarding this, this is just happening with SQL Server 2008 R2 SP2,

    Thanks

    I didn't say it would improve the procedure, just that it will be supported in a newer version of SQL, the tables you are using will be removed from SQL in a newer version so if your monitoring is not up with the times and you migrate, your monitoring wont work.