Mikel
[转载]SqlServer怎样获取查询语句的成本 – zzj8704 – 博客园.
在有些大系统中,客户端查询非常复杂,需要显示很多字段和过滤很多条件,例如TFS中的条件过滤,或则用户希望客户端查询能像EXCEL过滤那 样方便
这样可能导致查询语句非常复杂,此时的效率也比较低.
TFS查询条件界面:
如果可以获取查询语句的成本,当SQL语句执行成本很大(可能执行时间会很长时),系统自动提示用户是否继续或则大概需要多长时间,这样会提高用户 体验.
下举一例:
查询SQL Server中的所有列信息表:sys.all_columns
在SQL server 2k8 management studio 中先后执行下面每行语句即可得到Sql语句的成本, 即为第二个语句执行结果中第一行中TotalSubtreeCost字段值.
set showplan_all on
select * from sys.all_columns
第二个语句(select * from sys.all_columns)执行结果:
| StmtText | TotalSubtreeCost | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize |
| select * from sys.all_columns | 0.1736313 | 1 | 1 | 0 | NULL | NULL | 5270 | NULL | NULL | NULL |
| |–Concatenation | 0.1736313 | 1 | 2 | 1 | Concatenation | Concatenation | 5270 | 0 | 0.000527 | 303 |
| 0.01454595 | 1 | 3 | 2 | Filter | Filter | 659 | 0 | 8.44E-04 | 303 | |
| 0.01370243 | 1 | 4 | 3 | Compute Scalar | Compute Scalar | 659 | 0 | 6.59E-05 | 305 | |
| 0.01363653 | 1 | 5 | 4 | Clustered Index Scan | Clustered Index Scan | 659 | 0.01275463 | 0.0008819 | 179 | |
| 0.1585583 | 1 | 36 | 2 | Compute Scalar | Compute Scalar | 4611 | 0 | 0.0004611 | 303 | |
| 0.1523828 | 1 | 37 | 36 | Compute Scalar | Compute Scalar | 4611 | 0 | 0.0011905 | 164 | |
| 0.1511923 | 1 | 38 | 37 | Clustered Index Scan | Clustered Index Scan | 4611 | 0.1379398 | 0.0132525 | 167 |
再执行 set showplan_all off结束查询计划分析结果.
此时可以应用showplan_all来获取Sql查询的成本.
如下为C#简单实现上述应用,在VS2k8中编译通过,只需正确更改Data Source即可运行:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
connectionBuilder.Add(“Data Source”, “your server name or ip“);
connectionBuilder.Add(“Initial Catalog”, “master”);
connectionBuilder.Add(“Integrated Security”, “True”);
string sqlStr = “select * from sys.all_columns”;
Console.WriteLine(GetSqlCost(connectionBuilder.ToString(), sqlStr).ToString());
Console.Read();
}
static double GetSqlCost(string connectStr, string strSQL)
{
double sqlCost;
int timeOut;
SqlDataAdapter adapter;
SqlConnection connection = new SqlConnection(connectStr);
SqlCommand command;
DataTable table;
timeOut = 180;
adapter = null;
try
{
connection.Open();
command = new SqlCommand(“set showplan_all on “, connection);
command.ExecuteNonQuery();
command.CommandText = strSQL;
adapter = new SqlDataAdapter(strSQL, connection);
adapter.SelectCommand.CommandTimeout = timeOut;
table = new DataTable();
adapter.Fill(table);
command.CommandText = “set showplan_all off”;
command.ExecuteNonQuery();
sqlCost = System.Convert.ToDouble(table.Rows[0][“TotalSubtreeCost”]);
}
finally
{
command = null;
connection.Close();
}
return sqlCost;
}
}
}
