SQL Server 2005/2008でのパラメータースニッフィング（例としてProject Server 2007を使用）
読み込まれたProject Server 2007で興味深いエラーに直面しました。さらに、アクティブな使用（製品内での読み取り）で現れます。 開発、デバッグ、およびテスト中に、このエラーは一度も表示されませんでした。 さらに悪いことに、エラーが発生すると、SQLは「くさび」をかけ、外部からの介入なしではエラーは消えません。 まあ、最後に-ログから、その発生の理由を理解することは非常に困難です。 残念ながら、Googleは助けにならなかったので、実際、この問題について書くようになりました。
プロジェクトの詳細ページで予期しないエラーが発生し、エラー7888（SQLタイムアウト）がログに記録されている場合は、catにようこそ。 SQL Serverの内部エゴに興味がある場合は、それもお読みください。
このエラーは、Project Server 2007 Web Access（PWA）でプロジェクト情報（http：//server/pwa/ProjectDrillDown.aspx）を表示するときに発生し、PWA> 1人（特定の条件が設定されていない）で作業するときに発生します。 異なるWindows Server（2003 / 2008R2）および異なるSQL Server（2005 / 2008R2）に登場しました。
ページが表示されます：
OSアプリケーションのイベントログ：
SharePointのULSログ（\ Program Files \ Common Files \ Microsoft Shared \ Web Server Extensions \ 12 \ LOGS \）には、誤って新しい情報は見つかりませんでした。 ちなみに、ログの読み取りにはSharePoint ULS Log Viewerをお勧めします 。
私が見つけた合計：
1.ファイル「ProjectDrillDown.aspx」でエラーが発生し、一度表示されると、それ自体は処理されません。
2. ViewGetViewReportsByTypeメソッドが呼び出され、SqlException：Timeout expiredで終了するとエラーが発生します。
3. SharePointファームレベル（stsadm -o setproperty -pn database-connection-timeout -pv 60）でタイムアウト時間を変更しても効果はありません。
4. SQLのインスタンスレベルでタイムアウト時間を変更すると、「エラーがリセットされます」が、非常に迅速に再表示されます。 一時的な小屋として、SQLはJobに固定され、数分ごとにインスタンスパラメーターが変更されます。
1.ディープグーグル、定期的な手段の選択、エラーを人為的に再現し、発生の原因を理解しようとする試みは失敗しました。
2. SQL Profilerによるキャッチも失敗しました。ロードされたシステムでは非現実的な数のクエリ（フィルタリング時でも）、およびロードされていないクエリでは-エラーは発生しません。
3. GACから「Microsoft.Office.Project.Server.WebService.PWA.dll」ライブラリを削除し、 .Net Reflectorを使用してガット化する必要がありました。 必要なViewGetViewReportsByTypeメソッドがさらにいくつかを呼び出した結果、バグのあるSQLクエリが見つかりました。 ProjectServer_Publishedデータベースのテーブル関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoへの（他のいくつかのストレージ機能を介した）要求であることが判明しました。
4.ここで私はタンバリンを手に入れなければなりませんでした。UDFは問題なく書かれており、インターネットに関する情報はありません。 しかし奇跡：この関数を呼び出すか、SQL Management Studioで実行計画を作成すると、すべてが機能し始めます！
合計：問題は表関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoにあります。
長い間、何が問題なのか理解できませんでした...
しかし、問題はMSSQL ServerのunningなキャッシュとProject Server 2007の（ActiveXに接続された）Webパーツの作者の曲がった手にありました。
実際、クエリ（ストレージを含む）を実行すると、SQL Serverはクエリの実行プランを作成し、それをキャッシュに入れます。 次のリクエストでは、コンパイルされませんが、キャッシュから取得されます（常にではありません）。 その結果、Project Serverからのクエリ（何が明確ではない）を使用して、SQLがassを介して関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoの実行プランをコンパイルし、タイムアウトが発生しました。 そして、もちろん、キャッシュをリセットするまで（データベースパラメーターを変更するか、関数を再コンパイルすることにより）-Project Serverは機能しません。
内部の指定された関数には、カーソルと頻繁に変化する5つのテーブルの呼び出しが含まれ、さまざまなサイズのテーブルを（入力パラメーターに応じて）返し、さまざまな実行プランでクエリを構築します。 そして、後者は、この関数の通常の操作では、入力パラメーターに依存するべきではありません（いわゆるパラメータースニッフィング）。 治療オプション：
-要求の前に毎回関数をコンパイルします。
-自動更新せずに特定の「正しい」実行計画を修正します。
-外部変数を内部変数に書き換えて、パラメータスニッフィングを無効にします。
最初の2つのオプションは（OPTIONまたはWITHパラメーターを介した）手順にのみ適しているため、患者に松葉杖が処方されました（3番目のオプション）。 関数の変更された部分を持ち、_parameter接尾辞で変数を追加しました：
SP / UDFを使用するすべてのプログラマー、およびすべてのDBAは、次の記事を読むことを強くお勧めします。
1. パラメータスニッフィングとストアドプロシージャの実行計画 。 2006年までさかのぼる「パラメータスニッフィング」に関する有能な記事であり、依然として関連しています。
2. SQL Server 2005のバッチコンパイル、再コンパイル、およびプランキャッシュの問題 。 SQL Serverの内部動作の公式説明。
3.この問題に関するハブに関する記事がありましたが、興味のある人はほとんどいませんでした。MSSQL 2005、パラメータスニッフィング、ストアドプロシージャの速度を低下させます 。
このエラーは、最新のSP WS2008R2 / SQL2008R2 / MOSS2007SP2 / MOPS2007SP2を備えたプラットフォームでも発生しました。
UPD 02/25/2011：エラーテキストが修正されました。
プロジェクトの詳細ページで予期しないエラーが発生し、エラー7888（SQLタイムアウト）がログに記録されている場合は、catにようこそ。 SQL Serverの内部エゴに興味がある場合は、それもお読みください。
既往歴
このエラーは、Project Server 2007 Web Access（PWA）でプロジェクト情報（http：//server/pwa/ProjectDrillDown.aspx）を表示するときに発生し、PWA> 1人（特定の条件が設定されていない）で作業するときに発生します。 異なるWindows Server（2003 / 2008R2）および異なるSQL Server（2005 / 2008R2）に登場しました。
ページが表示されます：
「予期しないエラーが発生しました。（英語版では、「予期しないエラーが発生しました。[...]」）
Webパーツメンテナンスページ：適切なアクセス許可がある場合、このページを使用して、Webパーツを一時的に閉じ、個人設定を削除できます。 詳細については、サイト管理者にお問い合わせください。
OSアプリケーションのイベントログ：
ランタイム例外。 詳細については、以下を参照してください。
メッセージ：タイムアウトの期限が切れました。 操作が完了する前にタイムアウト期間が経過したか、サーバーが応答していません。
技術的な詳細：
System.Data.SqlClient.SqlException：タイムアウトの期限が切れました。 操作が完了する前にタイムアウト期間が経過したか、サーバーが応答していません。
System.Data.SqlClient.SqlConnection.OnError（SqlException例外、ブール値breakConnection）で
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj）で
System.Data.SqlClient.TdsParser.Run（RunBehavior runBehavior、SqlCommand cmdHandler、SqlDataReader dataStream、BulkCopySimpleResultSet bulkCopyHandler、TdsParserStateObject stateObj）で
System.Data.SqlClient.SqlDataReader.ConsumeMetaData（）で
System.Data.SqlClient.SqlDataReader.get_MetaData（）で
System.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds、RunBehavior runBehavior、String resetOptionsString）で
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior、RunBehavior runBehavior、Boolean returnStream、Boolean async）で
System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior、RunBehavior runBehavior、Boolean returnStream、Stringメソッド、DbAsyncResult結果）
System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior、RunBehavior runBehavior、Boolean returnStream、Stringメソッド）
System.Data.SqlClient.SqlCommand.ExecuteReader（CommandBehaviorの動作、Stringメソッド）
System.Data.SqlClient.SqlCommand.ExecuteDbDataReaderで（CommandBehaviorの動作）
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader（CommandBehaviorの動作）で
System.Data.Common.DbDataAdapter.FillInternal（DataSetデータセット、DataTable [] datatables、Int32 startRecord、Int32 maxRecords、String srcTable、IDbCommandコマンド、CommandBehaviorの動作）
System.Data.Common.DbDataAdapter.Fillで（DataSet dataSet、Int32 startRecord、Int32 maxRecords、String srcTable、IDbCommandコマンド、CommandBehaviorの動作）
System.Data.Common.DbDataAdapter.Fill（DataSet dataSet）で
Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSet（DataSet typedDataSet、String []テーブル、SqlCommand sqlCommand）
Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSet（DataSet typedDataSet、String []テーブル、String storedProcedureName、SqlParameter []パラメーター）
Microsoft.Office.Project.Server.DataAccessLayer.Generated.ViewDalBase.GetViewReportsByType（Int32 p0、Guid tsUIDprojUID、Guid res_uid）で
Microsoft.Office.Project.Server.BusinessLayer.View.GetViewReportsByType（ViewType viewType、Guid tsUID）で
Microsoft.Office.Project.Server.WebService.PWA.ViewGetViewReportsByType（ViewType viewType、Guid tsUID）で
SharePointのULSログ（\ Program Files \ Common Files \ Microsoft Shared \ Web Server Extensions \ 12 \ LOGS \）には、誤って新しい情報は見つかりませんでした。 ちなみに、ログの読み取りにはSharePoint ULS Log Viewerをお勧めします 。
私が見つけた合計：
1.ファイル「ProjectDrillDown.aspx」でエラーが発生し、一度表示されると、それ自体は処理されません。
2. ViewGetViewReportsByTypeメソッドが呼び出され、SqlException：Timeout expiredで終了するとエラーが発生します。
3. SharePointファームレベル（stsadm -o setproperty -pn database-connection-timeout -pv 60）でタイムアウト時間を変更しても効果はありません。
4. SQLのインスタンスレベルでタイムアウト時間を変更すると、「エラーがリセットされます」が、非常に迅速に再表示されます。 一時的な小屋として、SQLはJobに固定され、数分ごとにインスタンスパラメーターが変更されます。
調査
1.ディープグーグル、定期的な手段の選択、エラーを人為的に再現し、発生の原因を理解しようとする試みは失敗しました。
2. SQL Profilerによるキャッチも失敗しました。ロードされたシステムでは非現実的な数のクエリ（フィルタリング時でも）、およびロードされていないクエリでは-エラーは発生しません。
3. GACから「Microsoft.Office.Project.Server.WebService.PWA.dll」ライブラリを削除し、 .Net Reflectorを使用してガット化する必要がありました。 必要なViewGetViewReportsByTypeメソッドがさらにいくつかを呼び出した結果、バグのあるSQLクエリが見つかりました。 ProjectServer_Publishedデータベースのテーブル関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoへの（他のいくつかのストレージ機能を介した）要求であることが判明しました。
4.ここで私はタンバリンを手に入れなければなりませんでした。UDFは問題なく書かれており、インターネットに関する情報はありません。 しかし奇跡：この関数を呼び出すか、SQL Management Studioで実行計画を作成すると、すべてが機能し始めます！
合計：問題は表関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoにあります。
分析
長い間、何が問題なのか理解できませんでした...
しかし、問題はMSSQL ServerのunningなキャッシュとProject Server 2007の（ActiveXに接続された）Webパーツの作者の曲がった手にありました。
実際、クエリ（ストレージを含む）を実行すると、SQL Serverはクエリの実行プランを作成し、それをキャッシュに入れます。 次のリクエストでは、コンパイルされませんが、キャッシュから取得されます（常にではありません）。 その結果、Project Serverからのクエリ（何が明確ではない）を使用して、SQLがassを介して関数MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfoの実行プランをコンパイルし、タイムアウトが発生しました。 そして、もちろん、キャッシュをリセットするまで（データベースパラメーターを変更するか、関数を再コンパイルすることにより）-Project Serverは機能しません。
ロボトミー
内部の指定された関数には、カーソルと頻繁に変化する5つのテーブルの呼び出しが含まれ、さまざまなサイズのテーブルを（入力パラメーターに応じて）返し、さまざまな実行プランでクエリを構築します。 そして、後者は、この関数の通常の操作では、入力パラメーターに依存するべきではありません（いわゆるパラメータースニッフィング）。 治療オプション：
-要求の前に毎回関数をコンパイルします。
-自動更新せずに特定の「正しい」実行計画を修正します。
-外部変数を内部変数に書き換えて、パラメータスニッフィングを無効にします。
最初の2つのオプションは（OPTIONまたはWITHパラメーターを介した）手順にのみ適しているため、患者に松葉杖が処方されました（3番目のオプション）。 関数の変更された部分を持ち、_parameter接尾辞で変数を追加しました：
ALTER FUNCTION [dbo]。[MSP_WEB_FN_SEC_GetAllObjectsResCanViewByViewIDInfo]（
@res_guid_parameter uniqueidentifier、
@fea_act_uid_parameter uniqueidentifier、
@fAllow_parameter int、
@obj_type_uid_parameter uniqueidentifier、
@view_uid_parameter uniqueidentifier
）
戻り値@ret_obj_rule_info_tblテーブル（WSEC_RULE_INFO int、WSEC_CAT_UID uniqueidentifier）
として
開始
-パラメータスニッフィングを無効にするローカル変数
@res_guid uniqueidentifierを宣言します
@fea_act_uid uniqueidentifierを宣言します
@fAllow intを宣言します
@obj_type_uid uniqueidentifierを宣言します
@view_uid uniqueidentifierを宣言します
@res_guid = @res_guid_parameterを設定
@fea_act_uid = @fea_act_uid_parameterを設定
@fAllow = @fAllow_parameterを設定
@obj_type_uid = @obj_type_uid_parameterを設定
@view_uid = @view_uid_parameterを設定
[...]
予防
SP / UDFを使用するすべてのプログラマー、およびすべてのDBAは、次の記事を読むことを強くお勧めします。
1. パラメータスニッフィングとストアドプロシージャの実行計画 。 2006年までさかのぼる「パラメータスニッフィング」に関する有能な記事であり、依然として関連しています。
2. SQL Server 2005のバッチコンパイル、再コンパイル、およびプランキャッシュの問題 。 SQL Serverの内部動作の公式説明。
3.この問題に関するハブに関する記事がありましたが、興味のある人はほとんどいませんでした。MSSQL 2005、パラメータスニッフィング、ストアドプロシージャの速度を低下させます 。
PS
このエラーは、最新のSP WS2008R2 / SQL2008R2 / MOSS2007SP2 / MOPS2007SP2を備えたプラットフォームでも発生しました。
UPD 02/25/2011：エラーテキストが修正されました。
All Articles