MS SQL Server - sql server caching question ?

Author

sql server caching question ?

scott

2005-07-16, 7:23 am

Hi,

I have a VB app that user SQL server. I run the application on a PC which
pulls back all records in a table for a report. The first time the report
runs from application start-up its very slow taking about 10 mins. When
running it again it takes 1 min. I read somewhere that SQL server cache
records sets so this would explain the difference in speed.

From another PC however (1 minute after the first test) I run the same
application, same report to the same DB. Again the first time the report is
run it takes 10 minutes. Second time it takes 1 min.

My questions are:

1. I thought SQL would cache the record set for all users/pc. Why does the
second PC still take 10mins to run the report the first time ?

2. Is there anything i can do on SQL server to enable caching of record sets
to be available to all users (i.e looks like it caches per user query) ?

3. Are there any parameters I can amend to allow the cached records to
remain for a longer period of time ?

(note: still have some code optimisation to do)

Thanks for any advice.
Scott

Wayne Snyder

2005-07-16, 9:23 am

SQL uses several techniques for caching, There is caching of the query plan
and caching of the data . Query plan caching - ... Ad hoc caching requires
that the entire query string match exactly ( including parameters) and that
the connections have the same connection settings as well.

All you have to do for data caching is to ensure that there is enough memory
on the server. Data will stay in cache until it is removed to make space for
someone elses needs...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

"scott" <nospamscott@yahoo.com> wrote in message
news:%23fOaP3fiFHA.1788@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I have a VB app that user SQL server. I run the application on a PC which
> pulls back all records in a table for a report. The first time the report
> runs from application start-up its very slow taking about 10 mins. When
> running it again it takes 1 min. I read somewhere that SQL server cache
> records sets so this would explain the difference in speed.
>
> From another PC however (1 minute after the first test) I run the same
> application, same report to the same DB. Again the first time the report
> is run it takes 10 minutes. Second time it takes 1 min.
>
> My questions are:
>
> 1. I thought SQL would cache the record set for all users/pc. Why does the
> second PC still take 10mins to run the report the first time ?
>
> 2. Is there anything i can do on SQL server to enable caching of record
> sets to be available to all users (i.e looks like it caches per user
> query) ?
>
> 3. Are there any parameters I can amend to allow the cached records to
> remain for a longer period of time ?
>
> (note: still have some code optimisation to do)
>
> Thanks for any advice.
> Scott
>

Sgt. Sausage

2005-07-16, 8:23 pm

"scott" <nospamscott@yahoo.com> wrote in message
news:%23fOaP3fiFHA.1788@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I have a VB app that user SQL server. I run the application on a PC which
> pulls back all records in a table for a report. The first time the report
> runs from application start-up its very slow taking about 10 mins. When
> running it again it takes 1 min. I read somewhere that SQL server cache
> records sets so this would explain the difference in speed.
>
> From another PC however (1 minute after the first test) I run the same
> application, same report to the same DB. Again the first time the report
> is run it takes 10 minutes. Second time it takes 1 min.
>
> My questions are:
>
> 1. I thought SQL would cache the record set for all users/pc. Why does the
> second PC still take 10mins to run the report the first time ?

Clarification: SQL Server caches the data pages and index
pages -- not the "record set".

> 2. Is there anything i can do on SQL server to enable caching of record
sets
> to be available to all users (i.e looks like it caches per user query) ?

See above -- what's in the cache are the data pages
and index pages -- and these are available to the server
process itself, not to any specific (or all) users. If the
data engine decides it needs to flush the user's pages, well
then they get flushed. You don't have much control (none that
I'm aware of) over this.

> 3. Are there any parameters I can amend to allow the cached records to
> remain for a longer period of time ?

Again -- please understand the caching that's done. "Records"
are not cached. Data pages. Index pages. These are cached.
"Records" are not.

BTW -- *any* query that takes 10 minutes to run is symptomatic
of a problem. I've got thousands of users hitting hundreds of
millions of records. No production query takes longer than a
minute to run to completion, and 95% of the queries are in
the sub-second response time. You've got something else
seriously wrong: bad design, insufficient hardware (memory,
disk I/O, etc.) I'd start looking there before attempting to
change the way the SQL engine handles caching.

Paul Cahill

2005-07-18, 3:23 am

Try having a look at the query plan and stats I/O using query analyser.
Are indexes being used and are there scans rather than seeks.
Also it might be worthwhile to see if you have any blocks going on.
I recommend you place your SQL in stored procedures for best performance.

You can force data to be held in cache using pintable but like Sausage, I
too have over 500 users, millions of rows etc but things fly.

Paul

scott

2005-07-18, 9:23 am

yip its the code that's the problem - using the query analyser it takes
seconds but the query is repeated. The profiler shows 1000s of queries,
that's why its so slow the first time and all the data is cached for the
second query hence 1min (10% of the first query)....... was a good test to
show the difference cached data can make though.

"Ad hoc caching requires that the entire query string match exactly (
including parameters) " thanks for the advice on the parameters. I didn't
realise exact match was required.

thanks
Scott

David Gugick

2005-07-18, 11:23 am

scott wrote:
> yip its the code that's the problem - using the query analyser it
> takes seconds but the query is repeated. The profiler shows 1000s of
> queries, that's why its so slow the first time and all the data is
> cached for the second query hence 1min (10% of the first
> query)....... was a good test to show the difference cached data can
> make though.
> "Ad hoc caching requires that the entire query string match exactly (
> including parameters) " thanks for the advice on the parameters. I
> didn't realise exact match was required.
>
> thanks
> Scott

Are you using a parameterized query? If not consider changing the code
to use one.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Sponsored Links