查找数据库占用大量内存的SQL语句

chinaaxia 5月前 38

监控占用内存的sql
 SELECT SS.SUM_EXECUTION_COUNT,

                T.TEXT,
                SS.SUM_TOTAL_ELAPSED_TIME,
                SS.SUM_TOTAL_WORKER_TIME,
                SS.SUM_TOTAL_LOGICAL_READS,
                SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
                         SUM(S.EXECUTION_COUNT) SUM_EXECUTION_COUNT,
                         SUM(S.TOTAL_ELAPSED_TIME) SUM_TOTAL_ELAPSED_TIME,
                         SUM(S.TOTAL_WORKER_TIME) SUM_TOTAL_WORKER_TIME,
                         SUM(S.TOTAL_LOGICAL_READS) SUM_TOTAL_LOGICAL_READS,
                         SUM(S.TOTAL_LOGICAL_WRITES) SUM_TOTAL_LOGICAL_WRITES
          FROM SYS.DM_EXEC_QUERY_STATS S
          GROUP BY S.PLAN_HANDLE
          ) AS SS
          CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SS.PLAN_HANDLE) T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC



下面的语句可以查看每个数据库Buffer Pool内存使用情况: 
SELECT 

   (CASE WHEN ([database_id] = 32767)

       THEN  'Resource Database'

       ELSE  DB_NAME([database_id]) END)  AS [DatabaseName],

   COUNT(*) * 8 / 1024  AS [MBUsed],

   SUM(CAST([free_space_in_bytes] AS BIGINT)) /(1024  * 1024) AS [MBEmpty]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id];

GO  

mss sql   清空日志

 1  修改简单模式    
ALTER DATABASE 数据库名称 SET RECOVERY SIMPLE  
--Shrink the truncated log file to 2M
 
2. 手动收缩
GO

 -- Reset the database recovery model. 
3. 还原完整模式
 ALTER DATABASE 
数据库名称  SET RECOVERY FULL 




-- 索引碎片查找

SELECT object_name(dt.object_id) Tablename,si.name
IndexName,dt.avg_fragmentation_in_percent AS
ExternalFragmentation,dt.avg_page_space_used_in_percent AS
InternalFragmentation
FROM
(
SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id('数据库名字'),null,null,null,'DETAILED'
)
WHERE index_id <>0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

 


最新回复 (0)
返回
发新帖