代码之家 › 专栏 › 技术社区 › Ali

将多个Select语句的结果插入到各自列中的一个表中

variables tsql

Ali · 技术社区 · 6 年前

我正在收集一些用于升级计划的SQLServer统计数据。我找到了一些很好的查询并修改了它以满足我的要求。我想将每个查询的结果添加到一个表(SQLSTATS)中,结果将进入相应的列。我计划每隔N分钟通过一个SQL代理作业运行这些查询来收集数据。我将展示这一发现,以决定我们是否需要转向磁盘优化、CPU优化、内存优化或网络优化的服务器。任何帮助或指导将不胜感激。

查询如下:

 --Disk I/O
    SELECT
        wait_time_ms/waiting_tasks_count AS 'PAGEIOLATCH_SH'
    FROM
       sys.dm_os_wait_stats 
    WHERE
       waiting_tasks_count > 0
   and wait_type = 'PAGEIOLATCH_SH'
ORDER BY
   wait_time_ms DESC

SELECT
    wait_time_ms/waiting_tasks_count AS 'PAGEIOLATCH_SH'
FROM
   sys.dm_os_wait_stats 
WHERE
   waiting_tasks_count > 0
   and wait_type = 'PAGEIOLATCH_EX'
ORDER BY
   wait_time_ms DESC


SELECT
    wait_time_ms/waiting_tasks_count AS 'PAGEIOLATCH_UP'
FROM
   sys.dm_os_wait_stats 
WHERE
   waiting_tasks_count > 0
   and wait_type = 'PAGEIOLATCH_UP'
ORDER BY
   wait_time_ms DESC


--Network
SELECT st.text AS [SQL_Text],
w.wait_type AS [Wait_Type]
FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
AS st WHERE w.session_id > 50
AND w.wait_duration_ms > 0
AND WAIT_Type='ASYNC_NETWORK_IO'


--CPU
DECLARE @ts BIGINT;
DECLARE @lastNmin TINYINT;
SET @lastNmin = 1;
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); 
SELECT TOP(@lastNmin)
        SQLProcessUtilization AS [SQLServer_CPU_Utilization]    
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;

我在网上做了一些广泛的搜索,但没有找到任何与我的问题相关的答案。我尝试将查询结果插入变量,然后从变量插入表->列。它没有起作用。参见以下代码:

Declare @PAGEIOLATCH_SH Nvarchar(50)
--IO Disk 
SET @PAGEIOLATCH_SH = (SELECT 
wait_time_ms/waiting_tasks_count AS 'PAGEIOLATCH_SH'
FROM
   sys.dm_os_wait_stats 
WHERE
   waiting_tasks_count > 0
   and wait_type = 'PAGEIOLATCH_SH')
PRINT @PAGEIOLATCH_SH

Select @PAGEIOLATCH_SH INTO SQLSTATS (pageiolatch_sh))

Here's the query result set: