Profiler read counter

Question

Post reply

Profiler read counter

Animal Magic

SSChampion

Points: 13964
More actions
May 20, 2008 at 2:27 am

#125361

Morning all,
I am looking into an issue where users are experiancing a slow response when they first run a query in the morning. after that first run the response is instant for the rest of the day. I have put a profiler trace on the queries affected and cant really see much difference in the "morning run" and the rest of the day.
The one thing that does stick out is the reads column, this is usually around 30 for a "normal" run, yet in the morning the counter is up around 150 just for that one run. What could be the reasons behind this?
I am not clearing buffers/cache overnight and there are no nightly reindex jobs. There are no maintenance jobs or any other type of jobs running at that time of day which may be interfering.
ps, the duration column stays around 10ms which is the same for the rest of the day, and the users report that it sometimes takes 25 minutes to return data!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 1

Hi

Have a look in your SQL Server Logs - during the evening and see if there are any messages like:

Message

SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Message

SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Message

SQL Server has encountered 14 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

As it mentions the flush is due to DB maintenance or reconfiguration operation, there could be something that is causing a flush to occur - memory maybe required and it needs to be flushed.

let us know

Thanks

Kevin

Animal Magic SSChampion Points: 13964 More actions · Answer 2

Hi Kevin,

Thanks for the response, i have checked the logs and there doesnt seem to be any messages like the ones you posted above.

One thing i do run on a nightly basis is an integrity check using DBCC CHECKDB but i dont see how that would be interfering with things.

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 3

This is an interesting one.

I have heard someone with a problem a while back and what they landed up doing was to run the specific script at a specified time in the early hours of the morning using a scheduled job - so that when users come in the query has already been run and is quick for the first user?

let us know

Thanks

Kevin

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Thats exactly what i have just put in place. i am going to run the query at 6am, they start around 7am. i will see what the results are tomorrow.

My bet is that its not the database side of things that are causing the issues, but as usual i need to prove it, and those read counters were the only things i could see.