J & J - 정성태의 닷넷 이야기

사용자

메뉴

최근 덧글

[정성태] VT sequences to "CONOUT$" vs. STD_O...[정성태] NetCoreDbg is a managed code debugg...[정성태] Evaluating tail call elimination in...[정성태] What’s new in System.Text.Json in ....[정성태] What's new in .NET 9: Cryptography ...[정성태] 아... 제시해 주신 "https://akrzemi1.wordp...[정성태] 다시 질문을 정리할 필요가 있을 것 같습니다. 제가 본문에...[이승준] 완전히 잘못 짚었습니다. 댓글 지우고 싶네요. 검색을 해보...[정성태] 우선 답글 감사합니다. ^^ 그런데, 사실 저 예제는 (g...[이승준] 수정이 안되어서... byteArray는 BYTE* 타입입니다...

글쓰기

제목

이름

암호

전자우편

HTML

홈페이지

유형

내용

<div style='display: inline'>
<h1 style='font-family: Malgun Gothic, Consolas; font-size: 20pt; color: #006699; text-align: center; font-weight: bold'>SqlCommand를 이용해 Microsoft SQL 서버의 쿼리 실행 계획을 구하는 방법</h1>

보통, SSMS 도구를 이용해 쿼리 실행 계획을 보게 되는데요. 직접 쿼리해서 구해오는 것도 가능합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
How do I obtain a Query Execution Plan?
; <a target='tab' href='http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan'>http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan</a>
</pre>
 
위의 글에 설명된 것처럼, 다음의 5가지 중에 하나를 실행해 주면 이후의 쿼리에 대해 실행 계획을 별도의 resultset으로 반환받게 됩니다. 
 
<ul>
<li>SET SHOWPLAN_TEXT ON</li>
<li>SET SHOWPLAN_ALL ON</li>
<li>SET SHOWPLAN_XML ON</li>
<li>SET STATISTICS PROFILE ON</li>
<li>SET STATISTICS XML ON</li>
</ul>
 
동작 유무를 확인하기 위해 곧바로 SSMS의 쿼리 창에서 직접 테스트를 해볼 수도 있겠지요. ^^ 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
getting an execution plan in C#
; <a target='tab' href='http://dbaspot.com/sqlserver-programming/467308-getting-execution-plan-c.html'>http://dbaspot.com/sqlserver-programming/467308-getting-execution-plan-c.html</a>
</pre>
 
제 테스트 DB에서는 다음과 같은 쿼리를 수행해 봤고, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
USE UnitTestDB2
GO

SET SHOWPLAN_TEXT ON
go

select * from mytable
go

SET SHOWPLAN_TEXT OFF
GO
</pre>
 
예상했던대로 SSMS에서는 2개의 resultset으로 결과를 반환하는데, 하나는 쿼리 수행 문이고 또 다른 하나는 쿼리 실행 계획입니다. 
 
<img alt='query_plan_in_cs_1.png' src='/SysWebRes/bbs/query_plan_in_cs_1.png' /> 
 
<hr style='width: 50%' /> 
 
그런데, C#에서 ADO.NET을 이용해 쿼리를 수행하는 경우 이를 하나의 Command에 넣으면 오류가 발생합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
command.CommandText = "SET SHOWPLAN_TEXT ON; select * from mytable; SET SHOWPLAN_TEXT OFF";
reader = command.ExecuteReader();
</pre>
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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
</pre>
 
즉, 한 줄에 넣으면 안 되고 하나의 Connection에 연이어서 쿼리를 수행해야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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();
</pre>
<a name='pquery'></a>
 
그런데, 재미있는 특징이 하나 있습니다. 일반적으로 ADO.NET 쿼리 실행 시에 Parameterized Query 방식을 쓰게 되는데요. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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 &lt;&gt; @id";
reader = command.ExecuteReader();
</pre>
 
일단 이렇게 Command.Parameters 컬렉션에 인자가 들어가면 해당 쿼리 수행은 실행 계획의 영향을 받지도 않을 뿐더러 그렇다고 쿼리가 수행되지도 않습니다. 그냥 결과 자체를 반환하지 않습니다. 
 
그럼 어떻게 하냐고요? ^^ 할 수 없습니다. 그냥 ad-hoc 쿼리 식으로 입력해 줘야 합니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
command.CommandText = "SET SHOWPLAN_TEXT ON; ";
command.ExecuteNonQuery();

command.CommandText = "select * from mytable WHERE id <> 0";
reader = command.ExecuteReader();
</pre>
 
성공하면 첫 번째 resultset에서 쿼리를 얻고, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
select * from mytable WHERE id &lt;&gt; 0 
</pre>
 
다음 resultset에서 실행 계획을 얻습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
 |--Clustered Index Seek(OBJECT:([UnitTestDB2].[dbo].[mytable].[PK_mytable]), S
EEK:([UnitTestDB2].[dbo].[mytable].[id] &lt; (0) OR [UnitTestDB2].[dbo].[mytable].[
id] &gt; (0)) ORDERED FORWARD)
</pre>
 
(<a target='tab' href='http://www.sysnet.pe.kr/bbs/DownloadAttachment.aspx?fid=788&amp;boardid=331301885'>첨부된 파일은 위의 소스 코드를 반영한 프로젝트</a>입니다.) 
 
<hr style='width: 50%' /> 
 
참고로 자바의 경우 "Microsoft SQL Server JDBC Driver"를 이용해서, 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
자바에서 "Microsoft SQL Server JDBC Driver" 사용하는 방법
; <a target='tab' href='http://www.sysnet.pe.kr/2/0/1116'>http://www.sysnet.pe.kr/2/0/1116</a>
</pre>
 
다음과 같은 소스코드로 가져올 수 있습니다. 
 
<pre style='margin: 10px 0px 10px 10px; padding: 10px 0px 10px 10px; background-color: #fbedbb; overflow: auto; font-family: Consolas, Verdana;' >
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) {}
 }
 }
}
</pre>

<hr />[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]
</div>

첨부파일

스팸 방지용 인증 번호

1996 (왼쪽의 숫자를 입력해야 합니다.)