SQL Server ·|«ÊÂê¦]½sĶÂê©wªº´y­z

• ¦b°õ¦æ¹w¦sµ{§Çªº¨Ï¥ÎªÌ¤£¬Oµ{§Çªº¾Ö¦³ªÌ¡C
• ¹w¦sµ{§Ç¦WºÙ¤£¬O§¹¾ãªºª«¥ó¾Ö¦³ªÌªº¦WºÙ¡C

• lastwaittypefor the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) andwaitresourceis of the form "TAB: dbid:object_id[[COMPILE]]," where "object_id" is the object ID of the stored procedure.
• Blockers havewaittype0x0000, status runnable. Blockees havewaittype0x000e (exclusive lock), status sleeping.
• Although the duration of the blocking incident may be long, there is no single SPID that is blocking the other SPIDs for a long time. There is rolling blocking. As soon as one compilation is complete, another SPID takes over the role of head blocker for a?several seconds or less, and so on.

   spid  blocked  waittype  waittime  lastwaittype  waitresource
   ----  -------  --------  --------  ------------  -------------------------
   
   221    29      0x000e    2141      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   228    29      0x000e    2235      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    29   214      0x000e    3937      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    13   214      0x000e    1094      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    68   214      0x000e    1968      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   214     0      0x0000       0      LCK_M_X       TAB: 6:834102 [[COMPILE]]

• If you are using SQL Server 2005, many of the system tables from SQL Server 2000 are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. For more information about mapping between the SQL Server 2000 system tables and the SQL Server 2005 system views, see the "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" topic in SQL Server 2005 Books Online.

• If your stored procedure name starts with the "sp_" prefix and is not in the master database, you seeSP:CacheMissbefore the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules. (The "preferred" location is in the master database.) The names of user-created stored procedures should not start with "sp_".

• If an owner-qualified procedure is executed with a different case than the owner-qualified procedure was created as, the owner-qualified procedure can obtain aCacheMissor request a COMPILE lock but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations, the request for a COMPILE lock can cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is being used to find the procedure in cache is based on hash values (for performance reasons), which can change if the case is different.
  
  The workaround is to drop and create the procedure with the same case as?the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.

• If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine that the query is trying to execute the particular procedure, and then try to find a plan in cache for that procedure. To avoid this situation in which SQL Server must parse and compile the language event, make sure that the query is sent to SQL as an RPC.
  
  For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."

¦^¦¹­¶³Ì¤W¤è

Known issues

• You use BLOB variables as a Stored Procedure parameter. ¦p»Ý¸Ô²Ó¸ê°T¡A½Ð«ö¤@¤U¤U¦Cªº¤å¥ó½s¸¹¡AÀ˵ø ¡u Microsoft ª¾ÃÑ®w ¡v ¤¤ªº¤å¥ó¡G¡G
  2380435? (http://support.microsoft.com/kb/2380435/ ) FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
• You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
  
  http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx (http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx)

¦^¦¹­¶³Ì¤W¤è