To determine what plans are in the cache and how often they''re used we can use sys.dm_os_memory_cache_counters dm view .

SELECT  TOP 6
 LEFT([name], 20) as [name],
 LEFT([type], 20) as [type],
 [single_pages_kb] + [multi_pages_kb] AS cache_kb,
 [entries_count]
FROM sys.dm_os_memory_cache_counters 
order by single_pages_kb + multi_pages_kb DESC

 here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers. 
CACHESTORE_SQLCP are cached SQL statements or batches that aren''t in stored procedures, functions and triggers.  This includes any dynamic SQL or raw SELECT statements sent to the server. 
CACHESTORE_PHDR  These are algebrizer trees for views, constraints and defaults.  An algebrizer tree is the parsed SQL text that resolves the table and column names.

(you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.
That is the reason the sal statements are going in to  Plan cache.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.  There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

After this to know the querry we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries

select  TOP 100
 objtype,
    usecounts, 
 p.size_in_bytes/1024 ''IN KB'',
 LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

 Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache). 

We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.

Further , the query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count
 ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

 After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

SELECT LEFT(CASE database_id 
   WHEN 32767 THEN ''ResourceDb'' 
   ELSE db_name(database_id) 
        END, 20) AS Database_Name,
 count(*)AS Buffered_Page_Count, 
 count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

 And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25 
 obj.[name],
 i.[name],
 i.[type_desc],
 count(*)AS Buffered_Page_Count ,
 count(*) * 8192 / (1024 * 1024) as Buffer_MB
    -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

zappjones commented on Jan 30 2015

首先两款软件都可以用来管理数据库链接 MySQL 和 MariaDB

相对于新手或者前端工程师使用 Navicat for MySQL 就够了，功能相对于 Navicat Premium 比较少
Navicat for MySQL 是一套管理和开发 MySQL 或 MariaDB 的理想解决方案，支持单一程序，可同时连接到 MySQL 和 MariaDB。这个功能齐备的前端软件为数据库管理、开发和维护提供了直观而强大的图形界面，给 MySQL 或 MariaDB 新手以及专业人士提供了一组全面的工具。
而 Navicat premium 是一款数据库管理工具，是一个可多重连线资料库的管理工具，它可以让你以单一程式同时连线到 MySQL、SQLite、Oracle 及 PostgreSQL 资料库，让管理不同类型的资料库更加的方便。
在这里插入图片描述

Navicat Premium 功能多较 Navicat for MySQL 强大，熟悉之后建议使用 Navicat Premium
下载地址：http://www.navicat.com.cn/download/navicat-premium

原文：https://blog.csdn.net/weixin_43888402/article/details/86544201