SQL server 表信息查询,cpu利用率查询

一、查看所有表的行数
select a.name as '表名',b.rows as '表数据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where a.type = 'u'
and b.indid in (0,1)
--and a.name not like 't%'
order by b.rows desc

 

 

二、sql查看表名以及所占用空间信息
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tabName') and xtype='u')
drop table #tabName
go
create table #tabName(
tabname varchar(100),
rowsNum varchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size varchar(100)
)

declare @name varchar(100)
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #tabName
exec sp_spaceused @name
--print @name

fetch next from cur into @name
end
close cur
deallocate cur

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName
--where tabName not like 't%'
order by cast(rowsNum as int) desc

 

 三、查看cpu使用过高的语句

SELECT TOP 10

total_worker_time/execution_count AS avg_cpu_cost, plan_handle,

execution_count,

(SELECT SUBSTRING(text, statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offset

END - statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY [avg_cpu_cost] DESC

四、查询阻塞

SELECT
es.session_id,
database_name=DB_NAME(er.database_id),
er.cpu_time,
er.reads,
er.writes,
er.logical_reads,
login_name,
er.status,
blocking_session_id,
wait_type,
wait_resource,
wait_time,
individual_query=SUBSTRING(qt.text,(er.statement_start_offset/2)+1,((CASE WHEN er.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE er.statement_end_offset END-er.statement_start_offset)/2)+1),
parent_query=qt.text,
program_name,
host_name,
nt_domain,
start_time,
DATEDIFF(MS,er.start_time,GETDATE())as duration,
(SELECT query_plan FROM sys.dm_exec_query_plan (er.plan_handle))AS query_plan
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id=es.session_id
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle)AS qt
WHERE
es.session_id> 50
AND es.session_Id NOT IN(@@SPID)
ORDER BY
1, 2

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'
ORDER BY [cpu_time] DESC