data retrieval - Sporadically Slow Calls From .NET Application To SQL Server -

i have table in sql server inherited legacy system thats still in production structured according code below. created sp query table described in code below table create statement. issue that, sporadically, calls .net sp both through enterprise library 4 , through datareader object slow. sp called through loop structure in data layer specifies params go sp purpose of populating user objects. it's important mention slow call not take place on every pass loop structure. fine of day or more, , start presenting makes extremely hard debug.

the table in question contains 5 million rows. calls slow, instance, take long 10 seconds, while calls fast take 0 10 milliseconds on average. checked locking/blocking transactions during slow calls, none found. created custom performance counters in data layer monitor call times. essentially, when performance bad, it's bad 1 call. when it's good, it's good. i've been able recreate issue on few different developer machines, not on our development , staging database servers, of course have beefier hardware. generally, problem resolved through restarting sql server services, not always. there indexes on table fields i'm querying, there more indexes like. however, i'm hesitant remove or toy indexes due impact may have on legacy system. has experienced problem before, or have recommendation remedy it?

create table [dbo].[product_performance_quarterly](     [performance_id] [int] identity(1,1) not replication not null,     [product_id] [int] null,     [month] [int] null,     [year] [int] null,     [performance] [decimal](18, 6) null,     [gross_or_net] [char](15) null,     [vehicle_type] [char](30) null,     [quarterly_or_monthly] [char](1) null,     [stamp] [datetime] null constraint [df_product_performance_quarterly_stamp]  default (getdate()),     [ea_loaded] [nchar](10) null,     [vehicle_type_id] [int] null,     [yearmonth] [char](6) null,     [gross_or_net_id] [tinyint] null,  constraint [pk_product_performance_quarterly_4_19_04] primary key clustered  (     [performance_id] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on, fillfactor = 80) on [primary] ) on [primary]  go set ansi_padding off go alter table [dbo].[product_performance_quarterly]  nocheck add  constraint [fk_product_performance_quarterlyproduct_id] foreign key([product_id]) references [dbo].[products] ([product_id]) go alter table [dbo].[product_performance_quarterly] check constraint [fk_product_performance_quarterlyproduct_id]  create procedure [ea.analytics.calculations].[usp.getcalculationdata] (     @productid int,                     --products.product_id     @beginyear int,                     --year begin retrieving performance data     @beginmonth int,                    --month begin retrieving performance data     @endyear int,                       --year end retrieving performance data     @endmonth int,                      --month end retrieving performance data     @quarterlyormonthly varchar(1),     --do want quarterly or monthly data?     @vehicletypeid int,                 --what product vehicle type looking for?     @grossornetid int                   --are looking gross of fees data or net of fees data? ) begin      set nocount on      declare @startdate varchar(6),             @enddate   varchar(6),             @vbeginmonth varchar(2),             @vendmonth varchar(2)     if len(@beginmonth) = 1      set @vbeginmonth = '0' + cast(@beginmonth varchar(1)) else     set @vbeginmonth = @beginmonth  if len(@endmonth) = 1     set @vendmonth = '0' + cast(@endmonth varchar(1)) else     set @vendmonth = @endmonth  set @startdate = cast(@beginyear varchar(4)) + @vbeginmonth set @enddate = cast(@endyear varchar(4)) + @vendmonth  --because null values gross_or_net_id , vehicle_type_id represented in  --multiple ways (true null, empty string, or 0) in ppq table, need account possible variations if  --a -1 passed in .net code, represents enumerated value --indicates value(s) should true null.  if @vehicletypeid = '-1' , @grossornetid = '-1'     select         ppq.yearmonth, ppq.performance     product_performance_quarterly ppq         (nolock)              (ppq.product_id = @productid)         , (ppq.yearmonth between @startdate , @enddate)         , (ppq.quarterly_or_monthly = @quarterlyormonthly)         , (ppq.vehicle_type_id null or ppq.vehicle_type_id = '0' or ppq.vehicle_type_id = '')         , (ppq.gross_or_net_id null or ppq.gross_or_net_id = '0' or ppq.gross_or_net_id = '')     order ppq.yearmonth asc  if @vehicletypeid <> '-1' , @grossornetid <> '-1'     select         ppq.yearmonth, ppq.performance     product_performance_quarterly ppq         (nolock)              (ppq.product_id = @productid)         , (ppq.yearmonth between @startdate , @enddate)         , (ppq.quarterly_or_monthly = @quarterlyormonthly)         , (ppq.vehicle_type_id = @vehicletypeid )         , (ppq.gross_or_net_id = @grossornetid)     order ppq.yearmonth asc  if @vehicletypeid = '-1' , @grossornetid <> '-1'     select         ppq.yearmonth, ppq.performance     product_performance_quarterly ppq         (nolock)              (ppq.product_id = @productid)         , (ppq.yearmonth between @startdate , @enddate)         , (ppq.quarterly_or_monthly = @quarterlyormonthly)         , (ppq.vehicle_type_id null or ppq.vehicle_type_id = '0' or ppq.vehicle_type_id = '')         , (ppq.gross_or_net_id = @grossornetid)     order ppq.yearmonth asc  if @vehicletypeid <> '-1' , @grossornetid = '-1'     select         ppq.yearmonth, ppq.performance     product_performance_quarterly ppq         (nolock)              (ppq.product_id = @productid)         , (ppq.yearmonth between @startdate , @enddate)         , (ppq.quarterly_or_monthly = @quarterlyormonthly)         , (ppq.vehicle_type_id = @vehicletypeid)         , (ppq.gross_or_net_id null or ppq.gross_or_net_id = '0' or ppq.gross_or_net_id = '')     order ppq.yearmonth asc  end 

i have seen happen indexes out of date. parameter sniffing problem, different query plan being used different parameters come in stored procedure.

you should capture parameters of slow calls , see if same ones each time runs slow.

you might try running tuning wizard , see if recommends indexes.

you don't want worry having many indexes until can prove updates , inserts happening slow (time needed modify index plus locking/contention), or running out of disk space them.