Collecting SQL Server Performance and Host Performance Data - Professional SQL Server 2008 Administration with Windows PowerShell - page 405

SELECT @@SERVERNAME,
scheduler_id,current_tasks_count,runnable_tasks_count, GETDATE()
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
INSERT INTO AWEAllocated (servername, [AWE allocated, Mb],datestamp )
SELECT @@SERVERNAME,
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb], GETDATE()
FROM sys.dm_os_memory_clerks
INSERT INTO LogFileUsage (servername,instance_name,[Log File(s) Used Size
(KB)],datestamp)
SELECT @@SERVERNAME,
instance_name,cntr_value 'Log File(s) Used Size (KB)', GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'
order by instance_name desc
INSERT INTO TopMemConsumption (servername, type,[SPA Mem, Kb] ,datestamp)
SELECT TOP 10 @@SERVERNAME,
type, SUM(single_pages_kb) as [SPA Mem, Kb], GETDATE()
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC
INSERT INTO TopQueries (
Servername,creation_time,
last_execution_time,
row_no,
l1,
total_worker_time,
AvgCPUTime,
LogicalReads,
LogicalWrites,
execution_count,
AggIO,
AvgIO,
query_text,
db_name ,
object_id ,
datestamp)
select Top 10 @@SERVERNAME,
creation_time
, last_execution_time
,rank() over(order by (total_worker_time + 0.0)/
execution_count desc,
sql_handle,statement_start_offset ) as row_no
, (rank() over(order by (total_worker_time + 0.0)/
execution_count desc,
sql_handle,statement_start_offset ))%2 as l1
, (total_worker_time + 0.0)/1000 as total_worker_time
, (total_worker_time + 0.0)/(execution_count*1000)
as [AvgCPUTime]
, total_logical_reads as [LogicalReads]