監測MSSQL執行的效能與時間

SQL端測試

DECLARE @Begin DATETIME

DECLARE @End DATETIME

SET @Begin = GETDATE()

–執行SQL CmdStart

Select name,sum(payment)as pay From member group by name having sum(payment)>40

–SQL CmdEnd

SET @End = GETDATE()

SELECT DATEDIFF(millisecond, @Begin, @End) AS ExecutionTime

C# 程式端測試

//宣告觀察物件提供一組方法和屬性 (Property)，您可以使用這些方法和屬性，精確地測量已耗用時間。

System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_UpdateMember";

SqlParameter Name = new SqlParameter("@Name",TboxName.Text.Trim().ToString());

SqlParameter Id = new SqlParameter("@ID", Convert.ToInt32(TboxId.Text.Trim()));

cmd.Parameters.Add(Name);

cmd.Parameters.Add(Id);

try

{

watch.Start();

cn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Response.Write(ex.Message);

}

watch.Stop();

Response.Write(watch.ElapsedMilliseconds); //單位毫秒

FAQ Book