As close as I could get to the correct forum..

So our SQL 2008 R2 64 bit server started consuming close 100% of the CPU during various times of the days and when refreshing the patch management dashboard reports.

We had 2 processors and then added 2 more - didn't help much.

So I analyzed what was going on in SQL and defined 2 new non-clustered indexes - now when these processes run we are well under 10% of CPU usage and the processes finish much faster!

So I thought I would share this in case anyone is having issues with there SQL Servers - might help your performance issues...

CREATE NONCLUSTERED INDEX [SDZG_SigIDStatus] ON [dbo].[PATCHDEVICESTATUS]
(
[SIGNATURE_ID] ASC,
[status] ASC
)
INCLUDE ( [deviceId])

and this one

CREATE NONCLUSTERED INDEX [SDZG_SigIDStatusCreated] ON [dbo].[PatchScanAuditLog]
(
[signatureId] ASC,
[status] ASC,
[createdDate] ASC
)
INCLUDE ( [deviceId])