What exactly are you looking for?

The CPU usage by SQL server in general or the CPU usage for a query?

You can find some information about the general CPU usage in sys.dm_os_ring_buffers. But be aware that you only get info about the last 4 hours or so.

Here's an example.

DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT record_id,

DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

SQLProcessUtilization,

SystemIdle,

100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization

FROM (

SELECT

record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,

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

For CPU usage by query you can query sys.dm_exec_query_stats. But again you will only get info about query plans which are in cache.

SELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,*

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle)