SQL知识点总结

--查看表所占的内存
SELECT 
    t.NAME AS '表名',
    s.Name AS '架构名称',
    p.rows AS '总行数',
    SUM(a.total_pages) * 8 AS '总行数所占空间内存（已KB为单位）', 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS '总行数所占空间内存（已MB为单位）',
    SUM(a.used_pages) * 8 AS '已使用空间大小（已KB为单位）', 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS '已使用空间大小（已MB为单位）', 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS '未使用空间大小（已KB为单位）',
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS '未使用空间大小（已MB为单位）'
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
	
-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量
-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？
select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 
from sys.allocation_units a, 
    sys.dm_os_buffer_descriptors b, 
    sys.partitions p 
where a.allocation_unit_id=b.allocation_unit_id 
    and a.container_id=p.hobt_id 
    and b.database_id=db_id()
group by p.object_id,p.index_id 
order by buffer_pages desc 



--查看SQL语句占用多大内存
SELECT s2.dbid, 
s1.sql_handle, 
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
( (CASE WHEN statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count, 
plan_generation_num, 
last_execution_time, 
total_worker_time, 
last_worker_time, 
min_worker_time, 
max_worker_time,
total_physical_reads, 
last_physical_reads, 
min_physical_reads, 
max_physical_reads, 
total_logical_writes, 
last_logical_writes, 
min_logical_writes, 
max_logical_writes 
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
WHERE s2.objectid is null 
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;



-- 查询SqlServer总体的内存使用情况
select      type
        , sum(virtual_memory_reserved_kb) VM_Reserved
        , sum(virtual_memory_committed_kb) VM_Commited
        , sum(awe_allocated_kb) AWE_Allocated
        , sum(shared_memory_reserved_kb) Shared_Reserved
        , sum(shared_memory_committed_kb) Shared_Commited
        --, sum(single_pages_kb)    --SQL2005、2008
        --, sum(multi_pages_kb)        --SQL2005、2008
from    sys.dm_os_memory_clerks
group by type
order by type、



-- 查询缓存的各类执行计划，及分别占了多少内存
-- 可以对比动态查询与参数化SQL（预定义语句）的缓存量
select    cacheobjtype
        , objtype
        , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb
        , count(bucketid) as cache_count
from    sys.dm_exec_cached_plans
group by cacheobjtype, objtype
order by cacheobjtype, objtype



-- 查询缓存中具体的执行计划，及对应的SQL
-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑
-- 查询结果会很大，注意将结果集输出到表或文件中
SELECT  usecounts ,
        refcounts ,
        size_in_bytes ,
        cacheobjtype ,
        objtype ,
        TEXT
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC ;
GO


---------用户连接数-----

SELECT login_name,
   Count(0) user_count
FROM   Sys.dm_exec_requests dr WITH(nolock)
   RIGHT OUTER JOIN Sys.dm_exec_sessions ds WITH(nolock)
 ON dr.session_id = ds.session_id
   RIGHT OUTER JOIN Sys.dm_exec_connections dc WITH(nolock)
 ON ds.session_id = dc.session_id
WHERE  ds.session_id > 50
GROUP  BY login_name
ORDER  BY user_count DESC


查询某个数据库用户的连接情况
sp_who 'sa'

--查看数据库允许的最大连接
select @@MAX_CONNECTIONS


--查询数据库基本信息
declare  @SessionID  nvarchar(100)

set @SessionID=null

SELECT
    SPID                = er.session_id 
    ,Status             = ses.status 
    ,[Login]            = ses.login_name 
    ,Host               = ses.host_name 
    ,BlkBy              = er.blocking_session_id 
    ,DBName             = DB_Name(er.database_id) 
    ,CommandType        = er.command 
    ,SQLStatement       = st.text 
    ,ObjectName         = OBJECT_NAME(st.objectid) 
    ,ElapsedMS          = er.total_elapsed_time 
    ,CPUTime            = er.cpu_time 
    ,IOReads            = er.logical_reads + er.reads 
    ,IOWrites           = er.writes 
    ,LastWaitType       = er.last_wait_type 
    ,StartTime          = er.start_time 
    ,Protocol           = con.net_transport 
    ,ConnectionWrites   = con.num_writes 
    ,ConnectionReads    = con.num_reads 
    ,ClientAddress      = con.client_net_address 
    ,Authentication     = con.auth_scheme 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id = er.session_id 
LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
    AND @SessionID IS NULL OR er.session_id = @SessionID 
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id 
	
	
	--查看锁表
	select request_session_id spid '锁表进程',OBJECT_NAME(resource_associated_entity_id) '被锁表名称'
	from sys.dm_tran_locks where resource_type='OBJECT'


	解锁：

	declare @spid int
	Set @spid = 92 –锁表进程
	declare @sql varchar(1000)
	set @sql=’kill ‘+cast(@spid as varchar)
	exec(@sql)
	
	--查看当前实例行锁等待情况、
	WITH CTE_SID ( BSID, SID, sql_handle )        
    AS ( SELECT [Blocking_Session_ID],
                        [Session_ID] ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    [Blocking_Session_ID] <> 0
               UNION ALL
               SELECT   A.[Blocking_Session_ID] ,
                        A.[Session_ID] ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.[Session_ID] = B.BSID
             )
    SELECT C.BSID,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.[Session_ID]
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid
	备注:如果出现行锁等待或者堵塞现象，则会在执行结果中罗列出来。
	BSID是指持有锁的sessionID。
	SID是指等待锁的sessionID。
	--查看sqlserver  实例 所分配的最大内存
	SELECT c.value, c.value_in_use
	FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'
	
	
	
	--设置服务器最大内存设置（4g）
	sp_configure 'show advanced options', 1;
	GO
	RECONFIGURE;
	GO
	sp_configure 'max server memory', 4096;
	GO
	RECONFIGURE;
	GO
	
	
	--当前数据库使用的内存
	SELECT  
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB
FROM sys.dm_os_process_memory