????????
?????????м????????????????????????????漰????????????????洢???????д??????μ????????????м???????????Щ?????????????????????Щ??????????????????????Щ??????????????
??????
????1. ????
????2. ????????
????2.1 ??????????м??
????2.2 SQL Server????????м??
????2.3 ???±?????м??
????3. ????
????3.1 ??м??????????
????3.2 ???????????м??
????3.3 ???????????????м???????
????3.4 ??????????ζ???м???????
????4. ???
????????????
????SQL Server ?????????洢??м???????????????????????????????м???????????????????????????????????????????洢??м???????????????檔
????SQL Server ??м??????????????????
??????????
??????м???????????????????????????????????????????????á????????????????????в??洢??????????????в??????????????????????????????????????е??????У?????????????е??????С????и??????????е??????У???????е???ж?????
?????????????
?????????????в????????????????????????????????????????????????????????????????????????????????????????????á?????????в???????е??????δ??????????????????????????3?????y???
??????????????м??
????SQL Server ???????Ч????????????????κ???? SQL ??????????м?????? SQL Server ??????κ? SQL ????????????潫????????????????????????? SQL ??????????м????SQL Server ???????????????κ????м?????????????±??? SQL ?????????????????????м????SQL Server ???????????μ???м????
????SQL Server????????м??
?????????????????м??
???????????????????????????￡??????????治????????SQL Server??????????????????????????????
???????л??????С?????max server memory???С??
?????????ж???????????м??
????????????治????????????????潫??????????????????????????????????Щ??м????????????????м????????????????????????е???м??SQL Server?????????????0?????????й?????м??SQL Server??????????????????????????????????????????????????м??????????????????????????м?????????????治?????????????????????м?????????????????????????????????????м????????????????????????????ü???????????м?????????????в?????ü??????????????潫?????????????????????
??????????????????????м???????????????????м??????????????????????????????治??????????м?????ζ??儷???????????????????治??????????????????????潫???????δ?????м??????????????????????м???????????????????У?????儷????????????
???????±?????м??
?????????????????????????????е??Щ????????????м??Ч????????Ч??SQL Server ????????м????Ч???????????????????Ч????????????в????????????????±????μ?????????????Ч???????????
???????????????????????и????ALTER TABLE ?? ALTER VIEW????
????????м?????????κ????????и????
????????м?????????????????и??￡???Щ??????????????? UPDATE STATISTICS????????????????????????????
?????????м??????????????
??????????? sp_recompile??
??????????????????????????????????????? INSERT ?? DELETE ?????????????????
????????????????????????????????????????????????
??????? WITH RECOMPILE ?????д洢?????
????????
??????м??????????
--1.?????????????????У???????????????????????????????????????????????????????????????
SELECT * FROM sys.syscacheobjects;
--2.????????????????????У???????м???????????????м?????С???????????м?????????????
SELECT * FROM sys.dm_exec_cached_plans;
GO
---3.??????????? sql_handle ????? SQL ??????????
/*????sql_handle????:
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_cursors
sys.dm_exec_xml_handles
sys.dm_exec_query_memory_grants
sys.dm_exec_connections
plan_handle????:sys.dm_exec_cached_plans
*/
SELECT * FROM sys.dm_exec_sql_text(sql_handle | plan_handle);
GO
--4.?? XML ???????????????????????????????????????????sys.dm_exec_cached_plans??plan_handle???
SELECT * FROM sys.dm_exec_query_plan(plan_handle);
GO
--5.???????????????У????????????sys.dm_exec_cached_plans??plan_handle???
SELECT * FROM sys.dm_exec_plan_attributes(plan_handle);
GO
--6.?????? Transact-SQL ??м????????????????? (CLR) ??м???????????????α?????У??????????????sys.dm_exec_cached_plans??plan_handle???
SELECT * FROM sys.dm_exec_cached_plan_dependent_objects(plan_handle);
--7.???????????????????????????????????е?????????????????ж????У??????е???????????????????????????????????????????????????????С?*/
--???????????????????е???м??????????????????????????????
SELECT * FROM sys.dm_exec_query_stats
???????????????м??
????--???????е???м??
????DBCC FREEPROCCACHE;
????-- ( plan_handle | sql_handle | pool_name )
????GO
????-- ??????????????м??
????DBCC FLUSHPROCINDB(<dbid>);
????GO
????---???????е?????
????DBCC DROPCLEANBUFFERS;
????---??????????洢???е???м??
????DBCC FREESYSTEMCACHE(<cachestore>)
????-- 'ALL'?? pool_name?? 'Object Plans'?? 'SQL Plans'?? 'Bound Trees'
????GO