mssql sqlserver存储过程第一次运行时编译的案例分析


摘要:
存储过程第一次运行,通常会比后几次运行速度慢,这其中有一个原因:
sql存储过程第一次运行时,需进行编译操作。
—————————————-
存储过程是一个预编译的脚本,但需要在第一次执行时,进行预编译操作,我们可以通过下面的例子来观察sql预编译的情况


例:

USE AdventureWorks
GO
--清楚所有缓存
DBCC FREEPROCCACHE
GO
-----创建新的测试存储过程
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[pr_test]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr_test]
GO
-- 创建一个新的存储过程
CREATE PROCEDURE pr_test
AS
SELECT *
FROM HumanResources.Department
GO
-- 查看当前缓存的执行计划,未发现存储过程pr_test
SELECT a.objtype AS [计划类型],
OBJECT_NAME(c.objectid,c.dbid) AS [对象名称],
a.refcounts,
a.usecounts,
c.TEXT AS [sql内容],
b.query_plan AS [执行计划]
FROM sys.dm_exec_cached_plans AS a
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS b
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) AS c;
GO
/* 执行存储过程 */
EXEC pr_test
GO
-- 查看当前缓存的执行计划,未发现存储过程pr_test
SELECT a.objtype AS [计划类型],
OBJECT_NAME(c.objectid,c.dbid) AS [对象名称],
a.refcounts,
a.usecounts,
c.TEXT AS [sql内容],
b.query_plan AS [执行计划]
FROM sys.dm_exec_cached_plans AS a
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS b
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) AS c;
GO