none
Buffer Pool Optimization For Performance

    Pergunta

  • We have an application with several modules. One of the module used every morning is Appointments.

    We plan to execute a SQL job at 8AM which does something like below.

    select * from appointments where appointmentdate between <yesterday's date> and <today date>

    Will this speed up loading of Appointments in application.

    Thanks!


    quarta-feira, 6 de junho de 2012 22:25

Respostas

  • Only if the bottleneck is reading data from disk and nothing else causes those pages to be removed from the buffer pool before they are actually needed.  The odds of the pages remaining in memory is very dependent on how big the data set is, what other activity there is, how much memory there is, and how your queries execute.  I wouldn't count on this being a fix to a performance problem, you would be better off troubleshooting the root cause of performance problems rather than trying to do this.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marcado como Resposta zzabvxx quinta-feira, 7 de junho de 2012 14:50
    quinta-feira, 7 de junho de 2012 11:59
    Moderador

Todas as Respostas

  • Only if the bottleneck is reading data from disk and nothing else causes those pages to be removed from the buffer pool before they are actually needed.  The odds of the pages remaining in memory is very dependent on how big the data set is, what other activity there is, how much memory there is, and how your queries execute.  I wouldn't count on this being a fix to a performance problem, you would be better off troubleshooting the root cause of performance problems rather than trying to do this.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marcado como Resposta zzabvxx quinta-feira, 7 de junho de 2012 14:50
    quinta-feira, 7 de junho de 2012 11:59
    Moderador
  • Also, wouldn't loading that complete table result in the flushing of SQL Buffers, which were carefully populated using complex heuristics on how and what to buffer by the SQL Server engine?   (Heuristics developed over many years by crazy smart SQL engine developers and PMs)

    My main concern would be - What harm is being done by this?

    quinta-feira, 7 de junho de 2012 15:27
  • Also, wouldn't loading that complete table result in the flushing of SQL Buffers, which were carefully populated using complex heuristics on how and what to buffer by the SQL Server engine?   (Heuristics developed over many years by crazy smart SQL engine developers and PMs)

    My main concern would be - What harm is being done by this?

    The buffer pool doesn't use complex heuristics, it uses the LRU-K algorithm where K is the number of references being tracked, which for SQL Server is 2.

    http://www.cs.cmu.edu/~christos/courses/721-resources/p297-o_neil.pdf

    Certain operations in the engine mark pages as disfavored when they are read into the buffer pool, CHECKDB and large table/index allocation order scans, allowing those pages to be flushed fast outside of LRU-K since they are unlikely to be reused again.  It is possible that the SELECT * scan would cause page disfavoring and flush it's own pages, not previously cached pages as a result.  In general you shouldn't be trying to manually manage what is cached like this, this used to be possible with DBCC PINTABLE in SQL Server 2000 and prior, but today if you have caching issues or I/O performance issues either increase the memory to expand the size of the cache or upgrade the I/O subsystem to meet the I/O demand for the environment.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    quinta-feira, 7 de junho de 2012 15:49
    Moderador
  • Thanks for the link to LRU-K, I read through and found it very enlightening (brought me back to my Computer Science classes of many years ago).

    In my last comment, "Complex" was the wrong word, "Sophisticated" is better.   It's better to let SQL Server determine what to cache via LRU-K, than to try and trick it via loading an entire table via SELECT *.   As you say (the link confirms) LRU-K should be resilient enough to not let SELECT * push out more frequently used data.  In effect, all this [SELECT *] statement should do is use lots of IO (effectively doing zero work), waste some electricity, wear mechanical spindles a bit, and most importantly: decrease usable IO for other real work that SQL may need to do. 

    quinta-feira, 14 de junho de 2012 18:15