Can we Preload queries into buffer pool to optimize web application ?

• ebooklub

  Hall of Fame

  Points: 3905

  More actions

  August 9, 2018 at 1:54 pm

  #358049

  Hi , 
   we have web base Reporting application  running  opening  as "dashboard" 

   "Problem" with FIRST load , it  slow ... 
   query behind the dashboard look like 

  declare @p1 int
  set @p1=52
  exec sp_prepexec @p1 output,NULL,N'
  select A,B,C,D 
  from table1 
  where 
  logindate > = CONVERT (VARCHAR(10),GETDATE(),127)
  and 
  clientid in('client1','client2','clientN')'
  select @p1

  execution  plan show
  query used non cluster index with seek on 'logindate' and 'clientid' 

  when query runs for first time after bufferpool is cleaned with code bellow
  /*
  CHECKPOINT -- writes dirty pages to disk, cleans the buffers
  DBCC DROPCLEANBUFFERS -- removes all buffers
  */
  I get following stats 
  Table table1 Scan count 74, logical reads 247410, physical reads 2, read-ahead reads 168660, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
  SQL Profiler  for query  Cpu = 905 and Duration = 26462

  When I execute query again few minutes later  I get stats bellow 
  Table table1 Scan count 83, logical reads 258963, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
   SQL Profiler  for query  Cpu = 800 and Duration = 123

  I used script from https://simplesqlserver.com/2016/01/04/query-the-buffer-pool/ to get percentage of index in bufferpool

  script show that   my index  takes 66% of total cache and 92% of index loaded in cache

   
  Based on facts bellow 
  1.Server memory is 48 GB , table repopulated once a day (at night) by batch 
   2. index already used  with 'seek' operation 
   3. the is no plans to modify existing application   🙂

  Question:
   if I create sql agent job job to run  every 1 hr 
    select A,B,C,D 
  from table1 
  where 
  logindate > = CONVERT (VARCHAR(10),GETDATE(),127)
  and 
  clientid in('client1','client2','clientN')

  will it keep execution plan almost constantly in cache and speed up "dashboard" load