With that said, I will go ahead and use the disclaimer that you SHOULD NOT use hints. You need to know how to write code so that it will not NEED hints. It’s an all hands on deck situation that you need to figure out quick. But much like the 1950’s people keep their dirty laundry to themselves and outwardly show their best “non hint” selves, this applies as well. But before you go around adding hints everywhere in your system (which is a horrible idea). We’ve all had it happen, alerts start going off that the CPU on the SQL Server is now pegged.
Updating a certain column in a specific table and want to find all the procedures that are affected by that? ROUTINES system views have not in the past contained all of the stored procedure text. (If I’m wrong on that, don’t challenge me because I don’t care).The sub-query can refer to old values of the current row of the table being updated.you should ensure that the join produces at most one output row for each row to be modified.First Make sure it’s SQL Server Sometimes there can be other processes (even backups) that are causing the CPU to push over that final edge.If you don’t have access to log into the box, the quickest way to determine how much CPU SQL Server is using is to run the following DMV: [cc lang=”sql”] DECLARE @ts_now bigint SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info; SELECT record_id, dateadd(ms, -1 * (@ts_now – [timestamp]), Get Date()) as Event Time, SQLProcess Utilization, System Idle, 100 – System Idle – SQLProcess Utilization as Other Process Utilization FROM ( SELECT record.value(‘(./Record/@id)’, ‘int’) as record_id, record.value(‘(./Record/Scheduler Monitor Event/System Health/System Idle)’, ‘int’) as System Idle, record.value(‘(./Record/Scheduler Monitor Event/System Health/Process Utilization)’, ‘int’) as SQLProcess Utilization, TIMESTAMP FROM ( SELECT timestamp, CONVERT(xml, record) as record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ AND record LIKE ‘%%’) as x ) as y ORDER BY record_id DESC; [/cc] If the Other Process Utilization column has a high value then you know you need to log into the box, open task manager and figure out what is hosing your system.Here is a simple example: [cc lang=”sql”] SELECT CAST(12345 AS VARCHAR(11)) [/cc] And the output: Here we are casting the int to a varchar(11).