CODEWITHSUNDEEP
sql-serverperformancet-sql
Alex Zhulin
Alex Zhulin

Reputation: 1305

How to find low performance code in my t-sql procedure

I have some nested procedures which show low performance. In order to find bottle neck I inserted into t-sql code some debug marks for measure performance of chunks of code which I suspect in low performance. This debug marks look like:

select @start_point = GETDATE() -- start measuring point
---
open @session_license_fee_cur -- suspected chunk of code
---
select @end_point = GETDATE()-- end measuring point
select @duration = datediff(ms, @start_point, @end_point)
select @log_info_total = 'Opening cursor license_fee (bills_supp_create_license_fee) (@class_id = ' + cast(@class_id as nvarchar) + ')';

exec bills_supp_save_calculation_log @duration, @log_info_total, @house_id, @account_id, @log_level -- procedure for creating log (simple insert into log table pes_bl_bills_calculation_log_total)

After running the procedures I run query from pes_bl_bills_calculation_log_total table to find lowest performance code. It looks like this

    set @session_license_fee_cur =  cursor static for 
    select activity_id
            , addendum_id
            , service_id
            , active_from
            , active_to
    from dbo.bills_supp_get_activate_license_fee_for_sessions_by_house(@active_from, @active_to, @house_id)

select @start_point = GETDATE()
---
open @session_license_fee_cur
---
select @end_point = GETDATE()
select @duration = datediff(ms, @start_point, @end_point)
select @log_info_total = 'Opening cursor license_fee (bills_supp_create_license_fee) (@class_id = ' + cast(@class_id as nvarchar) + ')';
exec bills_supp_save_calculation_log @duration, @log_info_total, @house_id, @account_id, @log_level

In other words open @session_license_fee_cur works very slowly (about 501980 ms).

I’m trying to run this chunk of code with given parameters in SQL Server Management Studio in order to look on query plan and try to optimize it. I run it like this

declare @active_from date = '01.03.2014'
declare @active_to date = '01.04.2014'
declare @house_id integer = 11927
        select activity_id
                , addendum_id
                , service_id
                , active_from
                , active_to
        from dbo.bills_supp_get_activate_license_fee_for_sessions_by_house(@active_from, @active_to, @house_id)

But it works very fast (returns 3000 records in about 0(zero) seconds). What the difference in opening cursor in procedure 

open @session_license_fee_cur

And running it in SQL Server Management Studio?

declare @active_from date = '01.03.2014'
declare @active_to date = '01.04.2014'
declare @house_id integer = 11927
        select activity_id
                , addendum_id
                , service_id
                , active_from
                , active_to
        from dbo.bills_supp_get_activate_license_fee_for_sessions_by_house(@active_from, @active_to, @house_id)

Where is my bottle neck?

Upvotes: 0

Views: 90

Answers (1)

Aasish Kr. Sharma
Aasish Kr. Sharma

Reputation: 556

Find Top 5 expensive Queries from a Read IO perspective

http://www.sqlservercentral.com/scripts/DMVs/102045/

Upvotes: 1

Related Questions