映射SQL Server命令

Question

我是SQL Server的新手(虽然已经在Linux上使用过MySQL )。我有一个数据库，它始终使用Windows服务器上的最大限制。我已经限制了最大内存量，但我的应用程序很慢。如何找出SQL命令/字符串是否在使用我的内存？

Answer 1

实时内存使用情况跟踪可以通过在Perfmon中设置计数器来完成。设置起来有点麻烦，所以如果您可以运行查询来查看某个固定的时间点，那么Glenn Berry有一套非常全面的内存诊断查询，我可以在我们的SQL Server 2012数据库上使用这些查询。

https://sqlserverperformance.wordpress.com/2010/10/08/sql-server-memory-related-queries/

在您的情况下使用的特定查询取决于您正在查找的是查询还是存储过程。

查找查询：

-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype = N'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

查找存储过程：

-- Top Cached SPs By Total Logical Reads (SQL 2008 and 2008 R2). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS                [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS     [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS     [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;