Data Cache Issue

Question

Data Cache Issue

David Kranes

SSCrazy

Points: 2523
More actions
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

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

David Kranes SSCrazy Points: 2523 More actions · Answer 2

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

David Kranes SSCrazy Points: 2523 More actions · Answer 4

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

Muhammad Haseeb Farhan Right there with Babe Points: 728 More actions · Answer 5

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

David Kranes SSCrazy Points: 2523 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

David Kranes SSCrazy Points: 2523 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

David Kranes SSCrazy Points: 2523 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

David Kranes SSCrazy Points: 2523 More actions · Answer 12

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

Best Regards,

~David