Post reply

Data Cache Issue

  • January 12, 2010 at 9:27 am

    #138105

    Hello, I have a stored procedure that we use to perform customer searches on for a CRM database. When we run a customer search for the first time it runs very slow but very fast for subsequent runs. Would it be beneficial to load the tables into cache to improve performance? We are currently using SQL Server 2000 SP 3. Is the memory management noticeably better in SQL Server 2005 and 8? Thank you for your comments.

    Best Regards,

    ~David

  • January 13, 2010 at 1:45 pm

    #1103296

    First - pinning a table in memory is no longer supported and shouldn't be considered. SQL Server 2005/2008 manages this much better.

    Second, define first time you are running your search. Is this the first time after a server restart? If so, how often are you restarting the server?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 13, 2010 at 1:51 pm

    #1103299

    Jeffrey, what I mean is I am running it for the first time logging in. The server has been up for over 35 days and remains up. I log in and run the sp calls and they take too long. Once they complete I run them again several times and they run much faster. When I log in and try again the next day the same cycle occurs again. It appears that the cache is getting flushed.

    Best Regards,

    ~David

  • January 13, 2010 at 3:39 pm

    #1103355

    That is normal behavior - and yes, the data you are accessing is not in the data buffer and has to be loaded. Once loaded, your queries will get the data from memory instead of having to load it from disk.

    Not sure you can really do anything about it - unless you schedule a job to run in the morning to run a typical search. That would load the data in the buffer and then your queries would be faster. However, if the data is getting flushed out of the buffer cache - then there are other queries that need that memory more than yours.

    To solve that issue - you probably need to add memory, but really can't say for sure without looking at your buffer cache hit ratio, page life expectancy, etc...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 14, 2010 at 5:49 am

    #1103537

    Jeffrey, this is as I expected. Would you have some SQL I could use to check the buffer cache hit ratio, page life expectancy and whatever is necessary? I appreciate all you help.

    Best Regards,

    ~David

  • January 14, 2010 at 6:37 am

    #1103555

    Hi David,

    Have you checked to run this sp using sqlcmd ?

    I faced same problem long ago. it was not a buffer cache issue. Please let me know using sqlcmd

    Thanks,

    Muhammad

  • January 14, 2010 at 6:41 am

    #1103557

    Muhammad, yes I am running this using osql as well a through our application. Unfortunately this is SS 2000. It runs fast after the initial call.

    Best Regards,

    ~David

  • January 14, 2010 at 6:57 am

    #1103566

    Have you looked at optimising the query/indexes? If the amount of data that the query needs can be reduced, then the initial call should be faster (and subsequent even faster than that)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2010 at 7:03 am

    #1103570

    The queries and indexes are optimized. They are only returning in some cases 3 rows. Would you have any scripts to cache the buffer cache hit ratio? Thank you. 🙂

    Best Regards,

    ~David

  • January 14, 2010 at 7:05 am

    #1103572

    David Kranes (1/14/2010)

    The queries and indexes are optimized. They are only returning in some cases 3 rows.

    It's not the number they're returning that the problem, it's the amount that has to be read to generate those 3 rows.

    Would you have any scripts to cache the buffer cache hit ratio? Thank you. 🙂

    Performance monitor. SQL Server Buffer Manager: Buffer cache hit ratio

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2010 at 7:22 am

    #1103581

    Gail, I know I can use performance monitor but can't you determine this with a query in SS? I am more of an Oracle DBA and you sure can do this in Oracle. Any help is greatly appreciated. Thank you!

    Best Regards,

    ~David

  • January 14, 2010 at 7:40 am

    #1103598

    You can, but it's done by querying the view that contains the perfmon values. It's also harder to interpret, as it's not immediately apparent what the number that the view returns means.

    SELECT * FROM sysperfinfo WHERE counter_name like 'Buffer cache hit ratio%'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 14, 2010 at 7:49 am

    #1103603

    Gail, thank you for the query. I am viewing performance monitor right now and it is very helpful!

    Best Regards,

    ~David

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply