쿼리속도 체크

원문: http://mystop.tistory.com/192  (항상감사합니다)

사용하기
sp_checkqry "select top 10000 * from tsp_requestsub with (index=tsp_requestsub_idx_con)"

실행후 쿼리의 속도가 1/100 단위로 나옴


프로시져만들기

CREATE procedure sp_checkqry
@status varchar(2000) ,
@status2 varchar(2000) = ''  ,
@status3 varchar(2000) = ''  ,
@status4 varchar(2000) = ''  ,
@status5 varchar(2000) = ''  ,
@status6 varchar(2000) = ''
as

set transaction isolation level read uncommitted
set nocount on

declare @aad1 datetime
declare @aad2 datetime
SELECT  @aad1 = CAST(getdate() AS datetime)
        begin        
                  exec(@status)
        end
SELECT  @aad2 =CAST(getdate() AS datetime)
select datediff(ms,@aad1, @aad2) As '↑ 실행 시간: 1/100초'


if @status2 <>  ''
        begin        
        SELECT  @aad1 = CAST(getdate() AS datetime)
                       
                        exec (@status2)
               
        SELECT  @aad2 =CAST(getdate() AS datetime)
        select datediff(ms,@aad1, @aad2) As '↑ 실행 시간: 1/100초'
        end
if @status3 <>  ''
        begin        
        SELECT  @aad1 = CAST(getdate() AS datetime)
                        
                        exec (@status3)
               
        SELECT  @aad2 =CAST(getdate() AS datetime)
        select datediff(ms,@aad1, @aad2) As '↑ 실행 시간: 1/100초'
        end
if @status4 <>  ''
        begin        
        SELECT  @aad1 = CAST(getdate() AS datetime)
                       
                        exec (@status4)
               
        SELECT  @aad2 =CAST(getdate() AS datetime)
        select datediff(ms,@aad1, @aad2) As '↑ 실행 시간: 1/100초'
        end
if @status5 <>  ''
        begin        
        SELECT  @aad1 = CAST(getdate() AS datetime)
                       
                        exec (@status5)
               
        SELECT  @aad2 =CAST(getdate() AS datetime)
        select datediff(ms,@aad1, @aad2) As '↑ 실행 시간: 1/100초'
        end