Slow query spikes CPU

Khy2shy 2015-11-20 20:26:01

This query make lots of read and increases CPU when accessed on concurrently. Can you help tune it?

SELECT ah.[RSSAppHistoryKey]
,ah.[RSSAppKey]
,ah.[RSSPartyKey]
,[RSSAppType]
,[RSSAppStatus]
,[CreateBy]
,[CreateDt]
,[UpdateBy]
,[UpdateDt]
,[ActiveFlag]
,[RSSSourceSystem]
,[RSSSourceSystemRefKey]
,[RSSClonedFromAppKey]
,[RSSInitiatorGroup]
,[RSSHardstopReason]
,[RSSSupportFBO]
,[PartyUnregulated]
,[FundsFromUSCo]
,[AssignedTo]
,[RSSStatusExplanation]
,[SiteInspection]
,[SiteInspectionUpdateDt]
,[RSSEDDOutcome]
,[RSSEDDComments]
,ah.[RSSFunctionalGroup]
,[RoleOwnerCreateDt]
,[RoleOwnerProcessID]
,[RoleOwnerStatus]
,[RoleJointCreateDt]
,[RoleJointProcessID]
,[RoleJointStatus]
,[RoleAuthCreateDt]
,[RoleAuthProcessID]
,[RoleAuthStatus]
,[RolePoACreateDt]
,[RolePoAProcessID]
,[RolePoAStatus]
,[RoleBenOCreateDt]
,[RoleBenOProcessID]
,[RoleBenOStatus]
,[RoleBeneCreateDt]
,[RoleBeneProcessID]
,[RoleBeneStatus]
,[RoleGuarCreateDt]
,[RoleGuarProcessID]
,[RoleGuarStatus]
,[RSSCompleteCDD]
,[RSSCompletePEA]
,[RSSCompleteAD]
,[RoleOwnerUpdateBy]
,[RoleOwnerUpdateDt]
,[RoleJointUpdateBy]
,[RoleJointUpdateDt]
,[RoleAuthUpdateBy]
,[RoleAuthUpdateDt]
,[RolePoAUpdateBy]
,[RolePoAUpdateDt]
,[RoleBenOUpdateBy]
,[RoleBenOUpdateDt]
,[RoleBeneUpdateBy]
,[RoleBeneUpdateDt]
,[RoleGuarUpdateBy]
,[RoleGuarUpdateDt]
FROM [dbo].[RSSAppHistory] ah
INNER JOIN (
SELECT RSSFunctionalGroup, RSSPartyKey, MaxKey = MAX(RSSAppHistoryKey)
FROM [dbo].[RSSAppHistory]
WHERE ISNULL(ActiveFlag, ") = "
GROUP BY RSSFunctionalGroup, RSSPartyKey
) mk
ON ah.RSSAppHistoryKey = mk.MaxKey

Aaron Bertrand 2015-11-23 16:14:31
Need a lot more information – can you show an actual query plan generated from within Plan Explorer? Can you tell us about any indexes on RSSAppHistory? Can you explain why so many of these columns (and every single row in the table) are needed for output? Why is ActiveFlag a nullable string instead of a non-nullable bit column? What version of SQL Server are you using?