SqlCommand를 이용해 Microsoft SQL 서버의 쿼리 실행 계획을 구하는 방법
보통, SSMS 도구를 이용해 쿼리 실행 계획을 보게 되는데요. 직접 쿼리해서 구해오는 것도 가능합니다.
How do I obtain a Query Execution Plan?
; http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
위의 글에 설명된 것처럼, 다음의 5가지 중에 하나를 실행해 주면 이후의 쿼리에 대해 실행 계획을 별도의 resultset으로 반환받게 됩니다.
- SET SHOWPLAN_TEXT ON
- SET SHOWPLAN_ALL ON
- SET SHOWPLAN_XML ON
- SET STATISTICS PROFILE ON
- SET STATISTICS XML ON
동작 유무를 확인하기 위해 곧바로 SSMS의 쿼리 창에서 직접 테스트를 해볼 수도 있겠지요. ^^
getting an execution plan in C#
; http://dbaspot.com/sqlserver-programming/467308-getting-execution-plan-c.html
제 테스트 DB에서는 다음과 같은 쿼리를 수행해 봤고,
USE UnitTestDB2
GO
SET SHOWPLAN_TEXT ON
go
select * from mytable
go
SET SHOWPLAN_TEXT OFF
GO
예상했던대로 SSMS에서는 2개의 resultset으로 결과를 반환하는데, 하나는 쿼리 수행 문이고 또 다른 하나는 쿼리 실행 계획입니다.
그런데, C#에서 ADO.NET을 이용해 쿼리를 수행하는 경우 이를 하나의 Command에 넣으면 오류가 발생합니다.
command.CommandText = "SET SHOWPLAN_TEXT ON; select * from mytable; SET SHOWPLAN_TEXT OFF";
reader = command.ExecuteReader();
Unhandled Exception: System.Data.SqlClient.SqlException: The SET SHOWPLAN statements must be the only statements in the batch.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
...[생략]...
at ConsoleApplication1.Program.Main(String[] args) in d:\...\ConsoleApplication1\Program.cs:line 28
즉, 한 줄에 넣으면 안 되고 하나의 Connection에 연이어서 쿼리를 수행해야 합니다.
command.CommandText = "SET SHOWPLAN_TEXT ON";
command.ExecuteNonQuery();
command.CommandText = "select * from mytable";
reader = command.ExecuteReader();
if (reader != null)
{
using (reader)
{
do
{
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
} while (reader.NextResult());
}
}
command.CommandText = "SET SHOWPLAN_TEXT OFF";
command.ExecuteNonQuery();
그런데, 재미있는 특징이 하나 있습니다. 일반적으로 ADO.NET 쿼리 실행 시에 Parameterized Query 방식을 쓰게 되는데요.
command.CommandText = "SET SHOWPLAN_TEXT ON; ";
command.ExecuteNonQuery();
SqlParameter param = new SqlParameter("@id", System.Data.SqlDbType.Int);
param.Value = 0;
command.Parameters.Add(param);
command.CommandText = "select * from mytable WHERE id <> @id";
reader = command.ExecuteReader();
일단 이렇게 Command.Parameters 컬렉션에 인자가 들어가면 해당 쿼리 수행은 실행 계획의 영향을 받지도 않을 뿐더러 그렇다고 쿼리가 수행되지도 않습니다. 그냥 결과 자체를 반환하지 않습니다.
그럼 어떻게 하냐고요? ^^ 할 수 없습니다. 그냥 ad-hoc 쿼리 식으로 입력해 줘야 합니다.
command.CommandText = "SET SHOWPLAN_TEXT ON; ";
command.ExecuteNonQuery();
command.CommandText = "select * from mytable WHERE id <> 0";
reader = command.ExecuteReader();
성공하면 첫 번째 resultset에서 쿼리를 얻고,
select * from mytable WHERE id <> 0
다음 resultset에서 실행 계획을 얻습니다.
|--Clustered Index Seek(OBJECT:([UnitTestDB2].[dbo].[mytable].[PK_mytable]), S
EEK:([UnitTestDB2].[dbo].[mytable].[id] < (0) OR [UnitTestDB2].[dbo].[mytable].[
id] > (0)) ORDERED FORWARD)
(
첨부된 파일은 위의 소스 코드를 반영한 프로젝트입니다.)
참고로 자바의 경우 "Microsoft SQL Server JDBC Driver"를 이용해서,
자바에서 "Microsoft SQL Server JDBC Driver" 사용하는 방법
; https://www.sysnet.pe.kr/2/0/1116
다음과 같은 소스코드로 가져올 수 있습니다.
import java.sql.*;
public class DBTest {
public static void main(String[] args)
{
String connectionString = "jdbc:sqlserver://...[서버주소]...:1433;databaseName=...[DB명]...;user=...[계정]...;password=...[암호]...";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionString);
String plan = "SET SHOWPLAN_TEXT ON";
stmt = con.createStatement();
stmt.execute(plan);
String SQL = "SELECT * FROM Account";
stmt = con.createStatement();
rs = stmt.executeQuery(SQL);
while (rs.next()) {
System.out.println(rs.getString(1));
}
stmt.getMoreResults();
rs = stmt.getResultSet();
while (rs.next()) {
System.out.println(rs.getString(1));
}
plan = "SET SHOWPLAN_TEXT OFF";
stmt = con.createStatement();
stmt.execute(plan);
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]