SQLAgentMail: block

Showing posts with label block. Show all posts

Wednesday, March 21, 2012

Question on SQL Server and the cache

I'm new to databases and I'm trying to understand how the memory cache
works with sql server (I think Oracle calls it the "Block Buffer")
I fire up sql server
I query a table (ie select * from mytable where myid = 10)
This query's plan ends up in cache?
Do the returned rows end up there too?
Does it matter if myid is an index or not for the cache to utilised?
If the query did not have a where clause, would the entire table end up in
memory?
Sorry for the dumb questions... just trying learn
TIA> This query's plan ends up in cache?
Maybe. SQL Server caches plans to avoid the compilation costs. However,
trivial plans (probably this one) are not cached since little work is needed
to compile.
> Do the returned rows end up there too?
Data are read into the buffer cache. Execution plans are kept in separate
plan cache.
> Does it matter if myid is an index or not for the cache to utilised?
No.
> If the query did not have a where clause, would the entire table end up in
> memory?
If there is sufficient buffer cache, yes. If not, cache is reused according
to (roughly) a LRU algorithm so that the most recent and frequently accessed
data stay in cache.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> I'm new to databases and I'm trying to understand how the memory cache
> works with sql server (I think Oracle calls it the "Block Buffer")
> I fire up sql server
> I query a table (ie select * from mytable where myid = 10)
> This query's plan ends up in cache?
> Do the returned rows end up there too?
> Does it matter if myid is an index or not for the cache to utilised?
> If the query did not have a where clause, would the entire table end up in
> memory?
> Sorry for the dumb questions... just trying learn
> TIA
>|||Some additional information inline...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> This query's plan ends up in cache?
> Maybe. SQL Server caches plans to avoid the compilation costs. However, trivial plans (probably
> this one) are not cached since little work is needed to compile.
Check the syscacheobjects table. Cotains one row for each plan. You can see how much a plan has been
reused, type of plan etc.
>> Do the returned rows end up there too?
> Data are read into the buffer cache. Execution plans are kept in separate plan cache.
By "Data", Dan is referring to data pages as well as index pages.
>> Does it matter if myid is an index or not for the cache to utilised?
> No.
>> If the query did not have a where clause, would the entire table end up in
>> memory?
> If there is sufficient buffer cache, yes. If not, cache is reused according to (roughly) a LRU
> algorithm so that the most recent and frequently accessed data stay in cache.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> I'm new to databases and I'm trying to understand how the memory cache
>> works with sql server (I think Oracle calls it the "Block Buffer")
>> I fire up sql server
>> I query a table (ie select * from mytable where myid = 10)
>> This query's plan ends up in cache?
>> Do the returned rows end up there too?
>> Does it matter if myid is an index or not for the cache to utilised?
>> If the query did not have a where clause, would the entire table end up in
>> memory?
>> Sorry for the dumb questions... just trying learn
>> TIA
>>
>|||Thanks guys
Some additional questions
What does the cache hit ratio relate to? The data or the index pages being
in memory? Or both?
By LRU algorithm, you mean the lazy writer process, correct? From my
reading, it seems that this sweeps through and ages out stale queries from
the query cache to make room for new queries. At the same time, does it age
out the data and index pages from their data and index caches that were
generated from the aged out query?
You guys are a great help. I struggle a lot because I'm more of a visual
learner (ie I need to see process flows rather than just read text). I
can't seem to find good material.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Some additional information inline...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
> >> This query's plan ends up in cache?
> >
> > Maybe. SQL Server caches plans to avoid the compilation costs.
However, trivial plans (probably
> > this one) are not cached since little work is needed to compile.
> Check the syscacheobjects table. Cotains one row for each plan. You can
see how much a plan has been
> reused, type of plan etc.
>
> >
> >> Do the returned rows end up there too?
> >
> > Data are read into the buffer cache. Execution plans are kept in
separate plan cache.
> By "Data", Dan is referring to data pages as well as index pages.
>
> >
> >> Does it matter if myid is an index or not for the cache to utilised?
> >
> > No.
> >
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >
> > If there is sufficient buffer cache, yes. If not, cache is reused
according to (roughly) a LRU
> > algorithm so that the most recent and frequently accessed data stay in
cache.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> >> I'm new to databases and I'm trying to understand how the memory cache
> >> works with sql server (I think Oracle calls it the "Block Buffer")
> >>
> >> I fire up sql server
> >> I query a table (ie select * from mytable where myid = 10)
> >>
> >> This query's plan ends up in cache?
> >> Do the returned rows end up there too?
> >> Does it matter if myid is an index or not for the cache to utilised?
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >>
> >> Sorry for the dumb questions... just trying learn
> >>
> >> TIA
> >>
> >>
> >
> >
>|||> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
In perfmon, you have cache hit ration counters for data/index pages as well as for plan pages.
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
Yes. Each page has a cost associated. A bit simplified, we can say that each reference to a page
increases the cost by 1. Each time Lazywriter sweeps, the cost for each page decreases by 1. When
cost goes to 0, the page goes in the free list. The algorithm for plan pages is a bit different, but
the same principal applies. So apart for the costing algorithms, data/index pages are handled the
same way as plan pages.
> I
> can't seem to find good material.
I have a feeling that "Inside SQL Server" from MS Press, by Kalen Delaney has some good info on
this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:kqSdnZDRD4fItDnenZ2dnUVZ_vidnZ2d@.comcast.com...
> Thanks guys
> Some additional questions
> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
> You guys are a great help. I struggle a lot because I'm more of a visual
> learner (ie I need to see process flows rather than just read text). I
> can't seem to find good material.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
>> Some additional information inline...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> >> This query's plan ends up in cache?
>> >
>> > Maybe. SQL Server caches plans to avoid the compilation costs.
> However, trivial plans (probably
>> > this one) are not cached since little work is needed to compile.
>> Check the syscacheobjects table. Cotains one row for each plan. You can
> see how much a plan has been
>> reused, type of plan etc.
>>
>> >
>> >> Do the returned rows end up there too?
>> >
>> > Data are read into the buffer cache. Execution plans are kept in
> separate plan cache.
>> By "Data", Dan is referring to data pages as well as index pages.
>>
>> >
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >
>> > No.
>> >
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >
>> > If there is sufficient buffer cache, yes. If not, cache is reused
> according to (roughly) a LRU
>> > algorithm so that the most recent and frequently accessed data stay in
> cache.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Dodo Lurker" <none@.noemailplease> wrote in message
> news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> >> I'm new to databases and I'm trying to understand how the memory cache
>> >> works with sql server (I think Oracle calls it the "Block Buffer")
>> >>
>> >> I fire up sql server
>> >> I query a table (ie select * from mytable where myid = 10)
>> >>
>> >> This query's plan ends up in cache?
>> >> Do the returned rows end up there too?
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >>
>> >> Sorry for the dumb questions... just trying learn
>> >>
>> >> TIA
>> >>
>> >>
>> >
>> >
>

Question on SQL Server and the cache

Saturday, February 25, 2012

Question on client side and server side transactions

Hi All,
Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
stored procedure, if a client has already started a transaction? The reason,
I am asking this question is that, though an error occured in a stored
procedure which was called within a transaction (on the client side), the
rest of the statements in the stored procedure were executed, which was not
desired.
I am have opened a connection and started a transaction in vb.net and called
a stored procedure. The stored procedure calls several other stored
procedures and adds/updates records in linked tables. It so happened that,
one temporary table did not exist and that particular statement failed when
I
tried to select records from the temporary table. But the following
statements were executed. Why was the transacton not being honoured? Ideally
,
when a statement failed, the transaction should be aborted; this is what I
understand.
Any suggestions?
Thanks
kdThen you have to check for error on your own with the variable @.@.Error.
BEGIN TRANSACTION
<Dosomething>
IF @.@.Error = 0
COMMIT TRANSACTION
ELSE ROLLBACK TRANSACTION
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kd" <kd@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0056D0F3-9A2D-46D5-9667-C01FD7D671B2@.microsoft.com...
> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The
> reason,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was
> not
> desired.
> I am have opened a connection and started a transaction in vb.net and
> called
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed
> when I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured?
> Ideally,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||The piece you're missing, is that not all errors cause a Stored Proc to
terminate and return... Most, in fact, just set the value of @.@.Error and
continue processing the next statement. If you want the stored Proc t ostop
and return upon encountering an error from a specific statement, you have to
code that yourself, for each statement, by testing the value of @.@.error
immediately after teh statement executes...
Declare @.Err Integer -- At Beginning of SP
/* ******
Other stuff
*********/
<Statement>
Set @.Err = @.@.Error
If @.Err <> 0 Begin
If @.@.TranCount > 0 Rollback
Raiserror('This is message', 16,1)
Return (@.Err)
End
<rest of Stored Proc>
"kd" wrote:

> Hi All,
> Is it required to provide BEGIN TRANSACTION...END TRANSACTION block inside
> stored procedure, if a client has already started a transaction? The reaso
n,
> I am asking this question is that, though an error occured in a stored
> procedure which was called within a transaction (on the client side), the
> rest of the statements in the stored procedure were executed, which was no
t
> desired.
> I am have opened a connection and started a transaction in vb.net and call
ed
> a stored procedure. The stored procedure calls several other stored
> procedures and adds/updates records in linked tables. It so happened that,
> one temporary table did not exist and that particular statement failed whe
n I
> tried to select records from the temporary table. But the following
> statements were executed. Why was the transacton not being honoured? Ideal
ly,
> when a statement failed, the transaction should be aborted; this is what I
> understand.
> Any suggestions?
> Thanks
> kd
>|||Hi,
I don't have BEGIN TRANSACTION..END TRANSACTION block in the stored
procedure. I have begun a transcation in the client side in vb.net.
try
connection.Open()
trans = connection.BeginTransaction()
... 'called stored procedure
trans.Commit()
command.Connection.Close()
Catch ex As Exception
trans.Rollback()
End Try
My first question is this. Does the stored procedure and all the other
stored procedures, UDFs, etc called by the stored procedure be part of the
transaction started on the client side?
My second question is, what are the errors that cause a stored procedure to
terminate and return? I could may be dummy code for the errors and check
whether the transaction started at the client is honoured at the server end.
Regards,
kd
"CBretana" wrote:
> The piece you're missing, is that not all errors cause a Stored Proc to
> terminate and return... Most, in fact, just set the value of @.@.Error and
> continue processing the next statement. If you want the stored Proc t ost
op
> and return upon encountering an error from a specific statement, you have
to
> code that yourself, for each statement, by testing the value of @.@.error
> immediately after teh statement executes...
> Declare @.Err Integer -- At Beginning of SP
> /* ******
> Other stuff
> *********/
> <Statement>
> Set @.Err = @.@.Error
> If @.Err <> 0 Begin
> If @.@.TranCount > 0 Rollback
> Raiserror('This is message', 16,1)
> Return (@.Err)
> End
> <rest of Stored Proc>
> "kd" wrote:
>

Wednesday, March 21, 2012

Question on SQL Server and the cache

Question on SQL Server and the cache

Question on SQL Server and the cache

Saturday, February 25, 2012

Question on client side and server side transactions

SQLAgentMail

Blog Archive

About Me