Compilations vs recompilations/sec

I see high number of compilations/sec and not recompilations/sec ? Whats the
difference between the two.. in other words when does it compile vs when
does it recompile ? I could not get a good feel about this even after
skimming through this article
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? Can someone give me the SQL for when it may
recompile in the above condition ? Also will this show as recompilation/sec
or compilation/sec in perfmon ?Are you having performance issues, or is this a general question?
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||First, thanks for the link, it was good reading. However, I would hardly
consider it useful if you're only going to skim it.
Clearly, the queries on your system is either not getting cached, or plans
are timing out and being removed from the cache (I don't know those
specifics about SQL Server). But again I must ask why you are asking the
question in the first place.
The plan/procedure/query cache is part of the Query Optimizer and is quite
possibly the single most complicated portion of a database engine. It is
also an area that DBA's seldom have to muck with, with the exception of some
basic understanding.
So, I must ask why you are asking the question in the first place? Are you
having performance problems, or are you just curious?
Jay
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||I am seeing high compilations/sec on one of our SQL Servers as high as 500
and zero recompilations/sec. No one is complaining yet, but curious to know
why compile vs recompile..
"JayKon" <spam@.nospam.org> wrote in message
news:uUkmBPe4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> First, thanks for the link, it was good reading. However, I would hardly
> consider it useful if you're only going to skim it.
> Clearly, the queries on your system is either not getting cached, or plans
> are timing out and being removed from the cache (I don't know those
> specifics about SQL Server). But again I must ask why you are asking the
> question in the first place.
> The plan/procedure/query cache is part of the Query Optimizer and is quite
> possibly the single most complicated portion of a database engine. It is
> also an area that DBA's seldom have to muck with, with the exception of
> some basic understanding.
> So, I must ask why you are asking the question in the first place? Are you
> having performance problems, or are you just curious?
> Jay
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||Well try not to "skim" next time since this article is very specific about
what recompilation is. But in a nut shell a recompile is when the plan is in
cache and it gets invalidated for one of the many reasons the article
explains so that the next time a user tries to use that plan it must be
recreated or recompiled. A compile is when it never was in cache to begin
with. If you have lots of compiles it means you have lots of adhoc queries
and sql server is either not caching them at all or you have so many that
they don't stay in cache long enough to be reused. I would read the article
several times in depth as it is one of the very best articles on cache
behavior out there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||Thanks Andrew.
What about this statement ? Can you help me here ?
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? can you give an example of a literal ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Well try not to "skim" next time since this article is very specific about
> what recompilation is. But in a nut shell a recompile is when the plan is
> in cache and it gets invalidated for one of the many reasons the article
> explains so that the next time a user tries to use that plan it must be
> recreated or recompiled. A compile is when it never was in cache to begin
> with. If you have lots of compiles it means you have lots of adhoc queries
> and sql server is either not caching them at all or you have so many that
> they don't stay in cache long enough to be reused. I would read the
> article several times in depth as it is one of the very best articles on
> cache behavior out there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||A literal is an actual value. I'm not going to give you an actual example
because I would have to type in more than 8000 characters!
But suppose you have a table with a column of varchar(max). A query like the
following would be an example of one with a literal longer than 8K:
UPDATE mytable
SET bigcolumn = 'Some very very long string that is longer than 8000
characters ....'
WHERE key_column = 42
The plan for the above query would not be cached.
Of course I could have made the column nvarchar(max) and then I would only
have to type in 4001 characters, but that is still to many for me to type
right now.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew.
> What about this statement ? Can you help me here ?
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that
> contains a literal larger than 8 KB. Suppose that this batch creates a
> temporary table, and then inserts 20 rows in that table. The insertion of
> the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? can you give an example of a literal ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>|||Kalen,
What about
UPDATE mytable
SET bigcolumn = 42
WHERE key_column = 'Some very very long string that is longer than 8000
characters ....'
Would the plan for this query not be cached too ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>A literal is an actual value. I'm not going to give you an actual example
>because I would have to type in more than 8000 characters!
> But suppose you have a table with a column of varchar(max). A query like
> the following would be an example of one with a literal longer than 8K:
> UPDATE mytable
> SET bigcolumn = 'Some very very long string that is longer than 8000
> characters ....'
> WHERE key_column = 42
> The plan for the above query would not be cached.
> Of course I could have made the column nvarchar(max) and then I would only
> have to type in 4001 characters, but that is still to many for me to type
> right now.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>|||No, this would give you an error, because key columns cannot be longer than
900 bytes.
But if the WHERE included a non-key column that was compared to a literal
longer than 8000 bytes, it is the same as the example I gave. A literal
anywhere in the query that is longer than 8000 bytes will keep the plan from
being cached.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:u4qDLJi4HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> What about
> UPDATE mytable
> SET bigcolumn = 42
> WHERE key_column = 'Some very very long string that is longer than 8000
> characters ....'
>
> Would the plan for this query not be cached too ?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>A literal is an actual value. I'm not going to give you an actual example
>>because I would have to type in more than 8000 characters!
>> But suppose you have a table with a column of varchar(max). A query like
>> the following would be an example of one with a literal longer than 8K:
>> UPDATE mytable
>> SET bigcolumn = 'Some very very long string that is longer than 8000
>> characters ....'
>> WHERE key_column = 42
>> The plan for the above query would not be cached.
>> Of course I could have made the column nvarchar(max) and then I would
>> only have to type in 4001 characters, but that is still to many for me to
>> type right now.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion
>> of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons
>> the article explains so that the next time a user tries to use that
>> plan it must be recreated or recompiled. A compile is when it never was
>> in cache to begin with. If you have lots of compiles it means you have
>> lots of adhoc queries and sql server is either not caching them at all
>> or you have so many that they don't stay in cache long enough to be
>> reused. I would read the article several times in depth as it is one
>> of the very best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ?
>>Whats the difference between the two.. in other words when does it
>>compile vs when does it recompile ? I could not get a good feel about
>>this even after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for
>> the batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this
>> batch creates a temporary table, and then inserts 20 rows in that
>> table. The insertion of the seventh row will cause a recompilation,
>> but because of the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>>
>