CPU on the roof with sqlserver.

SQL server is using 99% CPU

I right clicked the instance in Management studio, and did a CPU report and found the following SP was the issue

alter procedure [dbo].[sp_GetTrackerPointDataForAllDevices]	
	WITH CTE_RecentDevice (DeviceID, CommanTrackingID, GPSDateTime)
	AS (
		SELECT vpkDeviceID, MAX(ipkCommanTrackingID), MAX(dGPSDateTime)
		FROM vwCommonTrackingData
		GROUP BY vpkDeviceID
	 SELECT distinct
		isnull(vVehicleSpeed,0) as vVehicleSpeed,		
		isnull(bIsIgnitionOn,0) as bIsIgnitionOn,
		isnull(A.iTrackerType,0) as iTrackerType,
		isnull(ifkDriverID,0) as ifkDriverID,
		isnull(vOdometer, 0) as vOdometer,
		isnull(vReportID, 0) as vReportID,
		isnull(vSequenceID,0) as vSequenceID,
		isnull(Fuel,0) as Fuel,
		isnull(bIsGsmLocation, 0) as bIsGsmLocation,	
		C.vLogo 'DvLogo',
		C.vLogoName 'DvLogoName',
		isnull(iStartAngle,0) as iStartAngle,
		isnull(iStopAngle,0) as iStopAngle,
		isnull(iInRadius,0) as iInRadius,
		isnull(iOutRadius,0) as iOutRadius,
		isnull(iBatteryBackup,0) as iBatteryBackup,
		E.vDeviceName 'AssetType'
	  from vwCommonTrackingData A
		inner join CTE_RecentDevice R on R.DeviceID = A.vpkDeviceID and R.CommanTrackingID = A.ipkCommanTrackingID 
		inner join wlt_tblDevices B  on B.Id=A.ipkDeviceID
		inner join wlt_tblAssets D on D.Id=ifk_AssignedAssetId
		left outer join wlt_tblDevice_Master_SuperAdmin E on E.ipkDeviceID=B.ifk_DeviceTypeId
		left outer join wlt_tblAssets_Driver C on C.ipkDriverID=A.ifkDriverID
		left outer join wlt_tblTrackingData_Gsm on ifk_CommonTrackingId=ipkCommanTrackingID
		order by ipkCommanTrackingID desc


Open in new window

What tools can I use to see why this code is an issue?
Ora can anyone spot anything obvious?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Check for blocked sessions. Open a new query window a run the following command:
exec sp_who2

Open in new window

Will return a list of rows and each one representing a session. Check for Blocking column. If any has a value greater than zero means that process is being blocked by the process number referenced in the blocking column.
For solving the issue immediately you can add a locking hint in all tables in the SELECT:
from vwCommonTrackingData WITH (NOLOCK) A
            inner join CTE_RecentDevice WITH (NOLOCK) R on R.DeviceID = A.vpkDeviceID and R.CommanTrackingID = A.ipkCommanTrackingID
            inner join wlt_tblDevices WITH (NOLOCK) B  on B.Id=A.ipkDeviceID
            inner join wlt_tblAssets WITH (NOLOCK) D on D.Id=ifk_AssignedAssetId
            left outer join wlt_tblDevice_Master_SuperAdmin WITH (NOLOCK) E on E.ipkDeviceID=B.ifk_DeviceTypeId
            left outer join wlt_tblAssets_Driver WITH (NOLOCK) C on C.ipkDriverID=A.ifkDriverID
            left outer join wlt_tblTrackingData_Gsm WITH (NOLOCK) on

but better way is to review the indexes since you may need to create indexes to improve the query speed so it will avoid locks.
Barry CunneyCommented:
Hi Websss,
Another tip, to try to improve your actual  query, is to try and change your SQL so that it does not use SELECT DISTINCT.

Scott PletcherSenior DBACommented:
Would need to see the table definitions, esp. of any view(s) used.  Also, all index definitions and the query plan (estimated plan is OK in this case, since it's too much overhead to comfortably run the query to get the actual plan).

In general, use the actual tables rather than a view(s), or make sure the views are efficient if you really need to use views, such as for filtering rows or doing obscure joins.  Often an in-line table-valued function can perform much better than a traditional view.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

websssAuthor Commented:
Here is the  SPWHO results
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't have any blocking but I can see that SQL Server is using few memory.
How much RAM the server has? And how's the memory configured to that SQL Server instance (min and max memory)?
Also, which edition and version of SQL Server are you running? 32 or 64 bits?
websssAuthor Commented:
Its a dev server with 4gb ram, the database isn't that big on the dev server (1.94gb MDF and 24mb LDF)
Its a 64bit version is 2014 enterprise

Min and max mem have not been altered so will be system defaults
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry but I just missed your last comment.
I don't know if you still need help with this but here you go.

Being a DEV environment you must have many applications running in the same machine, right? 4GB would be ok for a dedicated DEV SQL Server instance with a single database but if SQL Server needs to share resources with other applications like SSRS, SSIS, SSAS or MS Visual Studio, it will be short of memory and CPU.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.