better method for DTS: larger

Showing posts with label larger. Show all posts

Friday, March 30, 2012

IS this true?

We've been experiencing some performance degradation recently. We are a
small company partnered with a MUCH larger one. Someone from the other
company said they had experienced some of what we are seeing and recommended
that stored procedures which only return one row of data should use output
parameters instead of "selecting" the data as a resultset.
Is this really that much faster?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> We've been experiencing some performance degradation recently. We are a
> small company partnered with a MUCH larger one. Someone from the other
> company said they had experienced some of what we are seeing and
> recommended that stored procedures which only return one row of data
> should use output parameters instead of "selecting" the data as a
> resultset.
> Is this really that much faster?
It is a lot faster, however. Unless you're running several hundred of these
a second, you probably won't notice any difference. So like all performance
analysys, you need to find what resource is limited in your system and
identify the biggest consumers of that resource.
David|||Thanks for the reply...yes during our busiest hours we are definitely
running hundreds of these a second.....if not thousands. I know this is
not the main performance bottleneck in our system at this point, but if
comparatively speaking it is a big difference then it would probably be
worth it in our case.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> We've been experiencing some performance degradation recently. We are a
>> small company partnered with a MUCH larger one. Someone from the other
>> company said they had experienced some of what we are seeing and
>> recommended that stored procedures which only return one row of data
>> should use output parameters instead of "selecting" the data as a
>> resultset.
>> Is this really that much faster?
> It is a lot faster, however. Unless you're running several hundred of
> these a second, you probably won't notice any difference. So like all
> performance analysys, you need to find what resource is limited in your
> system and identify the biggest consumers of that resource.
> David
>|||I agree with David in that it is unlikely making that change will
dramatically speed up your app as there are usually other things that can be
done first. Make sure you owner qualify all objects, especially the sp
calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
they are called as an RPC and not a batch.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
> Thanks for the reply...yes during our busiest hours we are definitely
> running hundreds of these a second.....if not thousands. I know this is
> not the main performance bottleneck in our system at this point, but if
> comparatively speaking it is a big difference then it would probably be
> worth it in our case.
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> We've been experiencing some performance degradation recently. We are a
>> small company partnered with a MUCH larger one. Someone from the other
>> company said they had experienced some of what we are seeing and
>> recommended that stored procedures which only return one row of data
>> should use output parameters instead of "selecting" the data as a
>> resultset.
>> Is this really that much faster?
>> It is a lot faster, however. Unless you're running several hundred of
>> these a second, you probably won't notice any difference. So like all
>> performance analysys, you need to find what resource is limited in your
>> system and identify the biggest consumers of that resource.
>> David
>|||Not sure what you mean as "an RPC and not a batch"? Yes we do
exhaustively "FULLY" qualify all objects from db.owner.object. I'm also
going thru all old webservices and making sure EVERYTHING is using
parameterized queries and not just stringing together exec statements and
parameters.
I think there are a few of the latter remaining but I'm stomping them
out....
I just ensured the NOCOUNT is indeed in every sproc in the system. That
made a difference in tonights benchmarks. Also setting isolation level
appropriately where needed. I think we can get further though.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucWcHE9aGHA.4612@.TK2MSFTNGP03.phx.gbl...
>I agree with David in that it is unlikely making that change will
>dramatically speed up your app as there are usually other things that can
>be done first. Make sure you owner qualify all objects, especially the sp
>calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
>they are called as an RPC and not a batch.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply...yes during our busiest hours we are definitely
>> running hundreds of these a second.....if not thousands. I know this is
>> not the main performance bottleneck in our system at this point, but if
>> comparatively speaking it is a big difference then it would probably be
>> worth it in our case.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> We've been experiencing some performance degradation recently. We are
>> a small company partnered with a MUCH larger one. Someone from the
>> other company said they had experienced some of what we are seeing and
>> recommended that stored procedures which only return one row of data
>> should use output parameters instead of "selecting" the data as a
>> resultset.
>> Is this really that much faster?
>> It is a lot faster, however. Unless you're running several hundred of
>> these a second, you probably won't notice any difference. So like all
>> performance analysys, you need to find what resource is limited in your
>> system and identify the biggest consumers of that resource.
>> David
>>
>|||Also wondered...if there are stored procs being called from a webservice
where it is just strung together i.e.
string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
parm2.ToString()
versus using a parameterized version, is it true that qualifying the object
above really doesn't help? I'm told that examples like the one above won't
be cached...that it won't cache unless it is parameterized...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ucWcHE9aGHA.4612@.TK2MSFTNGP03.phx.gbl...
>I agree with David in that it is unlikely making that change will
>dramatically speed up your app as there are usually other things that can
>be done first. Make sure you owner qualify all objects, especially the sp
>calls. Ensure they all have SET NOCOUNT ON at the beginning and make sure
>they are called as an RPC and not a batch.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:OVOtD3zaGHA.4796@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the reply...yes during our busiest hours we are definitely
>> running hundreds of these a second.....if not thousands. I know this is
>> not the main performance bottleneck in our system at this point, but if
>> comparatively speaking it is a big difference then it would probably be
>> worth it in our case.
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23JEFYoyaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23oTs4XuaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> We've been experiencing some performance degradation recently. We are
>> a small company partnered with a MUCH larger one. Someone from the
>> other company said they had experienced some of what we are seeing and
>> recommended that stored procedures which only return one row of data
>> should use output parameters instead of "selecting" the data as a
>> resultset.
>> Is this really that much faster?
>> It is a lot faster, however. Unless you're running several hundred of
>> these a second, you probably won't notice any difference. So like all
>> performance analysys, you need to find what resource is limited in your
>> system and identify the biggest consumers of that resource.
>> David
>>
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
> Also wondered...if there are stored procs being called from a webservice
> where it is just strung together i.e.
> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
> parm2.ToString()
> versus using a parameterized version, is it true that qualifying the
> object above really doesn't help? I'm told that examples like the one
> above won't be cached...that it won't cache unless it is parameterized...
>
In that case you would still reuse the plan for the proc, it's just the
"exec ..." batch that needs to be parsed, and it has a trivial plan.
But none of this is likely to help. This is just general best practice
stuff. Running around your application fixing up little stuff like this is
absolutely the wrong way to go about analyzing and improving the performance
of an application.
You must discover what your acutal problems are and address those.
David|||I have to agree and disagree with David on this<g>. First I agree that you
should determine where the bottlenecks really are and address them vs.
guessing or making random changes that may help. You can start by getting
perfmon and trace data and seeing what is out of whack. I also agree that
sending a batch (your string from the webservice) will result in two plans.
One for the batch and one for the execution of the sp. The plan for the sp
will get reused. But in my experience the batch itself does not always
result in a trivial plan that does not get cached. If it does get cached
but not reused you can have a lot of performance issues associated with it
when called a lot. You can end up with 10's or hundreds of thousands of very
similar plans that have the same hash bucket in the cache. This can result
in increased durations of each new call to search for the correct plan. You
can see this easily if you select from syscacheobjects and take a look at
the SQL and usecounts columns. Lack of owner qualifying can also be a big
hit on performance when you call a sp hundreds or thousands of times per
second due to the serialization that will occur in the cache searching
process. If yours are all owner qualified you should be all set in that
department. But one thing to note. You do not need to DB qualify the objects
if they are in the same db as where you are executing them from. As a matter
of fact it actually will be a tiny hit (most of the time not enough to
detect or worry about) in performance. The only time you need to specify
the db is when it is actually in another db.
--
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
>> Also wondered...if there are stored procs being called from a webservice
>> where it is just strung together i.e.
>> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
>> parm2.ToString()
>> versus using a parameterized version, is it true that qualifying the
>> object above really doesn't help? I'm told that examples like the one
>> above won't be cached...that it won't cache unless it is parameterized...
>>
> In that case you would still reuse the plan for the proc, it's just the
> "exec ..." batch that needs to be parsed, and it has a trivial plan.
> But none of this is likely to help. This is just general best practice
> stuff. Running around your application fixing up little stuff like this
> is absolutely the wrong way to go about analyzing and improving the
> performance of an application.
> You must discover what your acutal problems are and address those.
> David
>|||We were closely following BOL when we did the FULL qualification ... I quote
from SQL2005 BOL
SQL Server 2005 has an efficient algorithm to find any existing execution
plans for any specific SQL statement. In most systems, the minimal resources
that are used by this scan are less than the resources that are saved by
being able to reuse existing plans instead of compiling every SQL statement.
The algorithms to match new SQL statements to existing, unused execution
plans in the cache require that all object references be fully qualified.
For example, the first of these SELECT statements is not matched with an
existing plan, and the second is matched:
SELECT * FROM Person.Contact
SELECT * FROM AdventureWorks.Person.Contact
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>I have to agree and disagree with David on this<g>. First I agree that you
>should determine where the bottlenecks really are and address them vs.
>guessing or making random changes that may help. You can start by getting
>perfmon and trace data and seeing what is out of whack. I also agree that
>sending a batch (your string from the webservice) will result in two plans.
>One for the batch and one for the execution of the sp. The plan for the sp
>will get reused. But in my experience the batch itself does not always
>result in a trivial plan that does not get cached. If it does get cached
>but not reused you can have a lot of performance issues associated with it
>when called a lot. You can end up with 10's or hundreds of thousands of
>very similar plans that have the same hash bucket in the cache. This can
>result in increased durations of each new call to search for the correct
>plan. You can see this easily if you select from syscacheobjects and take a
>look at the SQL and usecounts columns. Lack of owner qualifying can also be
>a big hit on performance when you call a sp hundreds or thousands of times
>per second due to the serialization that will occur in the cache searching
>process. If yours are all owner qualified you should be all set in that
>department. But one thing to note. You do not need to DB qualify the
>objects if they are in the same db as where you are executing them from. As
>a matter of fact it actually will be a tiny hit (most of the time not
>enough to detect or worry about) in performance. The only time you need to
>specify the db is when it is actually in another db.
> --
> Andrew J. Kelly SQL MVP
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
>> Also wondered...if there are stored procs being called from a
>> webservice where it is just strung together i.e.
>> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
>> parm2.ToString()
>> versus using a parameterized version, is it true that qualifying the
>> object above really doesn't help? I'm told that examples like the one
>> above won't be cached...that it won't cache unless it is
>> parameterized...
>>
>> In that case you would still reuse the plan for the proc, it's just the
>> "exec ..." batch that needs to be parsed, and it has a trivial plan.
>> But none of this is likely to help. This is just general best practice
>> stuff. Running around your application fixing up little stuff like this
>> is absolutely the wrong way to go about analyzing and improving the
>> performance of an application.
>> You must discover what your acutal problems are and address those.
>> David
>|||Also, there is alot of cross db queries going on here. This makes it much
simpler as all webservices/apps etc...can use the same connection string as
the initial catalog makes no difference.
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:Oa1q8vebGHA.1208@.TK2MSFTNGP02.phx.gbl...
> We were closely following BOL when we did the FULL qualification ... I
> quote from SQL2005 BOL
> SQL Server 2005 has an efficient algorithm to find any existing execution
> plans for any specific SQL statement. In most systems, the minimal
> resources that are used by this scan are less than the resources that are
> saved by being able to reuse existing plans instead of compiling every SQL
> statement.
> The algorithms to match new SQL statements to existing, unused execution
> plans in the cache require that all object references be fully qualified.
> For example, the first of these SELECT statements is not matched with an
> existing plan, and the second is matched:
>
> SELECT * FROM Person.Contact
> SELECT * FROM AdventureWorks.Person.Contact
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>>I have to agree and disagree with David on this<g>. First I agree that
>>you should determine where the bottlenecks really are and address them vs.
>>guessing or making random changes that may help. You can start by getting
>>perfmon and trace data and seeing what is out of whack. I also agree that
>>sending a batch (your string from the webservice) will result in two
>>plans. One for the batch and one for the execution of the sp. The plan
>>for the sp will get reused. But in my experience the batch itself does
>>not always result in a trivial plan that does not get cached. If it does
>>get cached but not reused you can have a lot of performance issues
>>associated with it when called a lot. You can end up with 10's or hundreds
>>of thousands of very similar plans that have the same hash bucket in the
>>cache. This can result in increased durations of each new call to search
>>for the correct plan. You can see this easily if you select from
>>syscacheobjects and take a look at the SQL and usecounts columns. Lack of
>>owner qualifying can also be a big hit on performance when you call a sp
>>hundreds or thousands of times per second due to the serialization that
>>will occur in the cache searching process. If yours are all owner
>>qualified you should be all set in that department. But one thing to note.
>>You do not need to DB qualify the objects if they are in the same db as
>>where you are executing them from. As a matter of fact it actually will be
>>a tiny hit (most of the time not enough to detect or worry about) in
>>performance. The only time you need to specify the db is when it is
>>actually in another db.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
>> Also wondered...if there are stored procs being called from a
>> webservice where it is just strung together i.e.
>> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
>> parm2.ToString()
>> versus using a parameterized version, is it true that qualifying the
>> object above really doesn't help? I'm told that examples like the one
>> above won't be cached...that it won't cache unless it is
>> parameterized...
>>
>> In that case you would still reuse the plan for the proc, it's just the
>> "exec ..." batch that needs to be parsed, and it has a trivial plan.
>> But none of this is likely to help. This is just general best practice
>> stuff. Running around your application fixing up little stuff like this
>> is absolutely the wrong way to go about analyzing and improving the
>> performance of an application.
>> You must discover what your acutal problems are and address those.
>> David
>>
>|||I think this is a misprint but I will double check. I believe it should
have been:
SELECT * FROM Contact
SELECT * FROM Person.Contact
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:Oa1q8vebGHA.1208@.TK2MSFTNGP02.phx.gbl...
> We were closely following BOL when we did the FULL qualification ... I
> quote from SQL2005 BOL
> SQL Server 2005 has an efficient algorithm to find any existing execution
> plans for any specific SQL statement. In most systems, the minimal
> resources that are used by this scan are less than the resources that are
> saved by being able to reuse existing plans instead of compiling every SQL
> statement.
> The algorithms to match new SQL statements to existing, unused execution
> plans in the cache require that all object references be fully qualified.
> For example, the first of these SELECT statements is not matched with an
> existing plan, and the second is matched:
>
> SELECT * FROM Person.Contact
> SELECT * FROM AdventureWorks.Person.Contact
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>>I have to agree and disagree with David on this<g>. First I agree that
>>you should determine where the bottlenecks really are and address them vs.
>>guessing or making random changes that may help. You can start by getting
>>perfmon and trace data and seeing what is out of whack. I also agree that
>>sending a batch (your string from the webservice) will result in two
>>plans. One for the batch and one for the execution of the sp. The plan
>>for the sp will get reused. But in my experience the batch itself does
>>not always result in a trivial plan that does not get cached. If it does
>>get cached but not reused you can have a lot of performance issues
>>associated with it when called a lot. You can end up with 10's or hundreds
>>of thousands of very similar plans that have the same hash bucket in the
>>cache. This can result in increased durations of each new call to search
>>for the correct plan. You can see this easily if you select from
>>syscacheobjects and take a look at the SQL and usecounts columns. Lack of
>>owner qualifying can also be a big hit on performance when you call a sp
>>hundreds or thousands of times per second due to the serialization that
>>will occur in the cache searching process. If yours are all owner
>>qualified you should be all set in that department. But one thing to note.
>>You do not need to DB qualify the objects if they are in the same db as
>>where you are executing them from. As a matter of fact it actually will be
>>a tiny hit (most of the time not enough to detect or worry about) in
>>performance. The only time you need to specify the db is when it is
>>actually in another db.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
>> Also wondered...if there are stored procs being called from a
>> webservice where it is just strung together i.e.
>> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
>> parm2.ToString()
>> versus using a parameterized version, is it true that qualifying the
>> object above really doesn't help? I'm told that examples like the one
>> above won't be cached...that it won't cache unless it is
>> parameterized...
>>
>> In that case you would still reuse the plan for the proc, it's just the
>> "exec ..." batch that needs to be parsed, and it has a trivial plan.
>> But none of this is likely to help. This is just general best practice
>> stuff. Running around your application fixing up little stuff like this
>> is absolutely the wrong way to go about analyzing and improving the
>> performance of an application.
>> You must discover what your acutal problems are and address those.
>> David
>>
>|||That's fine, it is not that big of a deal wither way.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uMo$N5ebGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Also, there is alot of cross db queries going on here. This makes it much
> simpler as all webservices/apps etc...can use the same connection string
> as the initial catalog makes no difference.
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:Oa1q8vebGHA.1208@.TK2MSFTNGP02.phx.gbl...
>> We were closely following BOL when we did the FULL qualification ... I
>> quote from SQL2005 BOL
>> SQL Server 2005 has an efficient algorithm to find any existing execution
>> plans for any specific SQL statement. In most systems, the minimal
>> resources that are used by this scan are less than the resources that are
>> saved by being able to reuse existing plans instead of compiling every
>> SQL statement.
>> The algorithms to match new SQL statements to existing, unused execution
>> plans in the cache require that all object references be fully qualified.
>> For example, the first of these SELECT statements is not matched with an
>> existing plan, and the second is matched:
>>
>> SELECT * FROM Person.Contact
>> SELECT * FROM AdventureWorks.Person.Contact
>>
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23741ivYbGHA.4032@.TK2MSFTNGP02.phx.gbl...
>>I have to agree and disagree with David on this<g>. First I agree that
>>you should determine where the bottlenecks really are and address them
>>vs. guessing or making random changes that may help. You can start by
>>getting perfmon and trace data and seeing what is out of whack. I also
>>agree that sending a batch (your string from the webservice) will result
>>in two plans. One for the batch and one for the execution of the sp. The
>>plan for the sp will get reused. But in my experience the batch itself
>>does not always result in a trivial plan that does not get cached. If it
>>does get cached but not reused you can have a lot of performance issues
>>associated with it when called a lot. You can end up with 10's or
>>hundreds of thousands of very similar plans that have the same hash
>>bucket in the cache. This can result in increased durations of each new
>>call to search for the correct plan. You can see this easily if you
>>select from syscacheobjects and take a look at the SQL and usecounts
>>columns. Lack of owner qualifying can also be a big hit on performance
>>when you call a sp hundreds or thousands of times per second due to the
>>serialization that will occur in the cache searching process. If yours
>>are all owner qualified you should be all set in that department. But one
>>thing to note. You do not need to DB qualify the objects if they are in
>>the same db as where you are executing them from. As a matter of fact it
>>actually will be a tiny hit (most of the time not enough to detect or
>>worry about) in performance. The only time you need to specify the db is
>>when it is actually in another db.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:ucMpteSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:uiul6sMbGHA.404@.TK2MSFTNGP04.phx.gbl...
>> Also wondered...if there are stored procs being called from a
>> webservice where it is just strung together i.e.
>> string strsql = " exec mydbase.dbo.sproc " + parm1.ToString() + ',' +
>> parm2.ToString()
>> versus using a parameterized version, is it true that qualifying the
>> object above really doesn't help? I'm told that examples like the one
>> above won't be cached...that it won't cache unless it is
>> parameterized...
>>
>> In that case you would still reuse the plan for the proc, it's just the
>> "exec ..." batch that needs to be parsed, and it has a trivial plan.
>> But none of this is likely to help. This is just general best practice
>> stuff. Running around your application fixing up little stuff like
>> this is absolutely the wrong way to go about analyzing and improving
>> the performance of an application.
>> You must discover what your acutal problems are and address those.
>> David
>>
>>
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23OOu3flbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>I think this is a misprint but I will double check. I believe it should
>have been:
> SELECT * FROM Contact
> SELECT * FROM Person.Contact
>
Queries with unqualified object names will be cached and reused. It's just
that the scope of the reuse is limited to the connections with the same
user. Since most applications connect with a small number of users (usually
only one), this is no big deal.
Run this
drop table t
create table T(id int primary key, v varchar(50))
dbcc freeproccache
select user_id()
go
select * from T
go
select * from dbo.T
go
select a.value PlanUser, sql_text.*
FROM sys.dm_exec_query_stats
cross apply sys.dm_exec_plan_attributes (plan_handle) a
cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
where a.attribute = 'user_id'
This puts two different plans into the cache. One that's good for all
users, and one that's only good for one user.
David|||Thanks for the clarification guys....
Andrew .. while as you said, it might not make that much difference if we DO
qualify to the db level, I don't want to be wandering around teaching that
this is the "proper" way to do something if indeed it isn't. So, thanks
again!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OZnd5BqbGHA.3352@.TK2MSFTNGP03.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23OOu3flbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>>I think this is a misprint but I will double check. I believe it should
>>have been:
>> SELECT * FROM Contact
>> SELECT * FROM Person.Contact
>>
> Queries with unqualified object names will be cached and reused. It's
> just that the scope of the reuse is limited to the connections with the
> same user. Since most applications connect with a small number of users
> (usually only one), this is no big deal.
> Run this
> drop table t
> create table T(id int primary key, v varchar(50))
> dbcc freeproccache
>
> select user_id()
> go
> select * from T
> go
> select * from dbo.T
> go
>
> select a.value PlanUser, sql_text.*
> FROM sys.dm_exec_query_stats
> cross apply sys.dm_exec_plan_attributes (plan_handle) a
> cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
> where a.attribute = 'user_id'
>
> This puts two different plans into the cache. One that's good for all
> users, and one that's only good for one user.
>
> David
>|||David,
Yes that is true, but I don't understand why BOL was stating you needed to
specify the DB name?
--
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OZnd5BqbGHA.3352@.TK2MSFTNGP03.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23OOu3flbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>>I think this is a misprint but I will double check. I believe it should
>>have been:
>> SELECT * FROM Contact
>> SELECT * FROM Person.Contact
>>
> Queries with unqualified object names will be cached and reused. It's
> just that the scope of the reuse is limited to the connections with the
> same user. Since most applications connect with a small number of users
> (usually only one), this is no big deal.
> Run this
> drop table t
> create table T(id int primary key, v varchar(50))
> dbcc freeproccache
>
> select user_id()
> go
> select * from T
> go
> select * from dbo.T
> go
>
> select a.value PlanUser, sql_text.*
> FROM sys.dm_exec_query_stats
> cross apply sys.dm_exec_plan_attributes (plan_handle) a
> cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
> where a.attribute = 'user_id'
>
> This puts two different plans into the cache. One that's good for all
> users, and one that's only good for one user.
>
> David
>|||My recommendation (and most others that I know of) has always been to only
fully qualify (db name) when actually going across dbs. But to always owner
qualify. One good reason of when fully qualifying all objects can byte you
is when you have multiple db's on the same server with the same sp. Now you
have to maintain the code separately for each db even though the sp is
exactly the same.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:u%23T7VLrbGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Thanks for the clarification guys....
> Andrew .. while as you said, it might not make that much difference if we
> DO qualify to the db level, I don't want to be wandering around teaching
> that this is the "proper" way to do something if indeed it isn't. So,
> thanks again!
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:OZnd5BqbGHA.3352@.TK2MSFTNGP03.phx.gbl...
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23OOu3flbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>>I think this is a misprint but I will double check. I believe it should
>>have been:
>> SELECT * FROM Contact
>> SELECT * FROM Person.Contact
>>
>> Queries with unqualified object names will be cached and reused. It's
>> just that the scope of the reuse is limited to the connections with the
>> same user. Since most applications connect with a small number of users
>> (usually only one), this is no big deal.
>> Run this
>> drop table t
>> create table T(id int primary key, v varchar(50))
>> dbcc freeproccache
>>
>> select user_id()
>> go
>> select * from T
>> go
>> select * from dbo.T
>> go
>>
>> select a.value PlanUser, sql_text.*
>> FROM sys.dm_exec_query_stats
>> cross apply sys.dm_exec_plan_attributes (plan_handle) a
>> cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
>> where a.attribute = 'user_id'
>>
>> This puts two different plans into the cache. One that's good for all
>> users, and one that's only good for one user.
>>
>> David
>|||On Wed, 3 May 2006 22:50:12 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>My recommendation (and most others that I know of) has always been to only
>fully qualify (db name) when actually going across dbs. But to always owner
>qualify. One good reason of when fully qualifying all objects can byte you
>is when you have multiple db's on the same server with the same sp. Now you
>have to maintain the code separately for each db even though the sp is
>exactly the same.
I will NOT allow a stored procedure to contain an explicit reference
to the database that it is part of. Backup that database, and restore
it under another name, and you are in trouble. Restore it to another
database on the same server - say to have a copy of production for
testing or development - and you are SCREWED. I was burned years ago
by such references in such a situation, and that is one mistake I
learned from.
Roy Harvey
Beacon Falls, CT|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e%23F4xTybGHA.4604@.TK2MSFTNGP02.phx.gbl...
> David,
> Yes that is true, but I don't understand why BOL was stating you needed to
> specify the DB name?
>
Yes, that must be a doc bug.
David|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:lkri52h0a4ffh4sa9l3rrcn7nk4pgu8p1h@.4ax.com...
> On Wed, 3 May 2006 22:50:12 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>My recommendation (and most others that I know of) has always been to only
>>fully qualify (db name) when actually going across dbs. But to always
>>owner
>>qualify. One good reason of when fully qualifying all objects can byte
>>you
>>is when you have multiple db's on the same server with the same sp. Now
>>you
>>have to maintain the code separately for each db even though the sp is
>>exactly the same.
> I will NOT allow a stored procedure to contain an explicit reference
> to the database that it is part of. Backup that database, and restore
> it under another name, and you are in trouble. Restore it to another
> database on the same server - say to have a copy of production for
> testing or development - and you are SCREWED. I was burned years ago
> by such references in such a situation, and that is one mistake I
> learned from.
>
I absolutely agree, but I take the sentiment further: I don't like to
schema-qualify objects in the same schema.
David|||I totally disagree with that. Not qualifying the schema can be a definite
performance killer.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23tA2AC4bGHA.3956@.TK2MSFTNGP04.phx.gbl...
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:lkri52h0a4ffh4sa9l3rrcn7nk4pgu8p1h@.4ax.com...
>> On Wed, 3 May 2006 22:50:12 -0400, "Andrew J. Kelly"
>> <sqlmvpnooospam@.shadhawk.com> wrote:
>>My recommendation (and most others that I know of) has always been to
>>only
>>fully qualify (db name) when actually going across dbs. But to always
>>owner
>>qualify. One good reason of when fully qualifying all objects can byte
>>you
>>is when you have multiple db's on the same server with the same sp. Now
>>you
>>have to maintain the code separately for each db even though the sp is
>>exactly the same.
>> I will NOT allow a stored procedure to contain an explicit reference
>> to the database that it is part of. Backup that database, and restore
>> it under another name, and you are in trouble. Restore it to another
>> database on the same server - say to have a copy of production for
>> testing or development - and you are SCREWED. I was burned years ago
>> by such references in such a situation, and that is one mistake I
>> learned from.
>
> I absolutely agree, but I take the sentiment further: I don't like to
> schema-qualify objects in the same schema.
> David
>|||Wow, it is really hard sometimes when we see all you who definitely have an
enormous amount of experience with the product still disagreeing. Could
this just be one of those things that could depend upon ones own environment
and practices?
In regards to the comment by Roy, while maybe it is a common practice, here
we NEVER restore the same database on the same server under a different
name...and we are currently trying to get all connections on the web groups
servers to use the same connection string (even the same initial catalog)
since we were told this would affect our pooling (I've heard many differing
opinions on this as well). We also tend to frown on having the same stored
proc in multiple databases...what compelling reason would there bo to do
this? I'm really trying to simplify this environment, not make it
harder...I've had a monster thrown at me that I had nothing to do with
creating and I know it's going to take time....BUT I don't want to
"improve" something now under false info just to fix it properly again.
So I'm wondering in our environment if things are broken up to much maybe.
What guidelines do you all use to determine what a database should consist
of? When do you start a separate database vs. just adding more tables to an
existing one? Here we have db's setup based strictly in a "categorical"
type sense. One for ordering products...another one for storing the actual
products (since more than one ordering system uses the same products) and
another one for keeping track of membership info and another one for driving
our website....
I tend to wonder if the breakup into smaller db's is just causing more
headache than it's worth. Especially since there is now so much inter-db
chatter going on. Maybe that's an indication as to whether they should be
broke up or not...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u2jLdQ6bGHA.5116@.TK2MSFTNGP04.phx.gbl...
>I totally disagree with that. Not qualifying the schema can be a definite
>performance killer.
>
> --
> Andrew J. Kelly SQL MVP
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23tA2AC4bGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:lkri52h0a4ffh4sa9l3rrcn7nk4pgu8p1h@.4ax.com...
>> On Wed, 3 May 2006 22:50:12 -0400, "Andrew J. Kelly"
>> <sqlmvpnooospam@.shadhawk.com> wrote:
>>My recommendation (and most others that I know of) has always been to
>>only
>>fully qualify (db name) when actually going across dbs. But to always
>>owner
>>qualify. One good reason of when fully qualifying all objects can byte
>>you
>>is when you have multiple db's on the same server with the same sp. Now
>>you
>>have to maintain the code separately for each db even though the sp is
>>exactly the same.
>> I will NOT allow a stored procedure to contain an explicit reference
>> to the database that it is part of. Backup that database, and restore
>> it under another name, and you are in trouble. Restore it to another
>> database on the same server - say to have a copy of production for
>> testing or development - and you are SCREWED. I was burned years ago
>> by such references in such a situation, and that is one mistake I
>> learned from.
>>
>> I absolutely agree, but I take the sentiment further: I don't like to
>> schema-qualify objects in the same schema.
>> David
>|||I believe the main reasons why people have multiple dbs with similar schemas
/ objects are due to security reasons. There are just times when a db may
host data for multiple clients and it is a client rule that their data can
not be housed int he same db as someone else's. IN general if you have
tables in one db that you always query from another it warrants the question
of why? But there are no hard and fast rules. Each application has it's own
requirements and it is up to you to decide what is best for your
environment. As for qualifications I have yet to come across a situation
where fully qualifying (db.owner.object) all objects makes sense when the
objects are in the same db. I think there are always more reason why not to
do it than to do it. If you happen to have a business requirement for it
then by all means do it. If not it will probably byte you somewhere down the
line. As for the owner qualification I will leave you with this kb:
http://support.microsoft.com/?id=263889
for performance reasons alone.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uLXBzY6bGHA.3484@.TK2MSFTNGP03.phx.gbl...
> Wow, it is really hard sometimes when we see all you who definitely have
> an enormous amount of experience with the product still disagreeing.
> Could this just be one of those things that could depend upon ones own
> environment and practices?
> In regards to the comment by Roy, while maybe it is a common practice,
> here we NEVER restore the same database on the same server under a
> different name...and we are currently trying to get all connections on
> the web groups servers to use the same connection string (even the same
> initial catalog) since we were told this would affect our pooling (I've
> heard many differing opinions on this as well). We also tend to frown on
> having the same stored proc in multiple databases...what compelling
> reason would there bo to do this? I'm really trying to simplify this
> environment, not make it harder...I've had a monster thrown at me that I
> had nothing to do with creating and I know it's going to take time....BUT
> I don't want to "improve" something now under false info just to fix it
> properly again.
> So I'm wondering in our environment if things are broken up to much maybe.
> What guidelines do you all use to determine what a database should consist
> of? When do you start a separate database vs. just adding more tables to
> an existing one? Here we have db's setup based strictly in a
> "categorical" type sense. One for ordering products...another one for
> storing the actual products (since more than one ordering system uses the
> same products) and another one for keeping track of membership info and
> another one for driving our website....
> I tend to wonder if the breakup into smaller db's is just causing more
> headache than it's worth. Especially since there is now so much inter-db
> chatter going on. Maybe that's an indication as to whether they should be
> broke up or not...
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u2jLdQ6bGHA.5116@.TK2MSFTNGP04.phx.gbl...
>>I totally disagree with that. Not qualifying the schema can be a definite
>>performance killer.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:%23tA2AC4bGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
>> news:lkri52h0a4ffh4sa9l3rrcn7nk4pgu8p1h@.4ax.com...
>> On Wed, 3 May 2006 22:50:12 -0400, "Andrew J. Kelly"
>> <sqlmvpnooospam@.shadhawk.com> wrote:
>>My recommendation (and most others that I know of) has always been to
>>only
>>fully qualify (db name) when actually going across dbs. But to always
>>owner
>>qualify. One good reason of when fully qualifying all objects can byte
>>you
>>is when you have multiple db's on the same server with the same sp. Now
>>you
>>have to maintain the code separately for each db even though the sp is
>>exactly the same.
>> I will NOT allow a stored procedure to contain an explicit reference
>> to the database that it is part of. Backup that database, and restore
>> it under another name, and you are in trouble. Restore it to another
>> database on the same server - say to have a copy of production for
>> testing or development - and you are SCREWED. I was burned years ago
>> by such references in such a situation, and that is one mistake I
>> learned from.
>>
>> I absolutely agree, but I take the sentiment further: I don't like to
>> schema-qualify objects in the same schema.
>> David
>>
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4DWRJ7bGHA.1204@.TK2MSFTNGP02.phx.gbl...
>I believe the main reasons why people have multiple dbs with similar
>schemas / objects are due to security reasons. There are just times when a
>db may host data for multiple clients and it is a client rule that their
>data can not be housed int he same db as someone else's. IN general if you
>have tables in one db that you always query from another it warrants the
>question of why? But there are no hard and fast rules. Each application
>has it's own requirements and it is up to you to decide what is best for
>your environment. As for qualifications I have yet to come across a
>situation where fully qualifying (db.owner.object) all objects makes sense
>when the objects are in the same db. I think there are always more reason
>why not to do it than to do it. If you happen to have a business
>requirement for it then by all means do it. If not it will probably byte
>you somewhere down the line. As for the owner qualification I will leave
>you with this kb:
> http://support.microsoft.com/?id=263889
> for performance reasons alone.
>
Here's the issue in that KB:
When making an RPC call from client code you should owner-qualify the
procedure name if the procedure is in another schema. If you don't it will
cause a brief compile lock as the session starts to compile a plan for the
stored procedure, discovers that one already exists, and then decides to use
the existing plan. This compile lock, while it's not a big resouce hog, can
limit the total throughput of invocations of the procedure because only one
session can own the compile lock at a time. The real problem is not that
the cache miss, or even a batch compilation. The issue is that this
situation causes a compile lock on the target procedure, and so instead of
just wasting a bit of CPU, it makes sessions "wait in line" to execute the
procedure.
This KB cannot support a general performance best-practice of
schema-qualifiing object names in stored procedurs, views or dynamic SQL.
That KB only applies to how stored procedures are invoked, not how they are
coded internally, and thus only apply to client code, not stored procedures,
views or dynamic SQL. And it is not even clear if this issue exists on 2005
when a user's default schema can, and usually should, be the dbo schema.
When the user's default schema is dbo, the initial lookup for the plan may
succeed.
David|||Yes the KB mainly talks about locks during compile time and I have seen that
bite more people than I can count. As for general owner qualification how
could it not help. For one it removes any ambiguity and the chance that
someone will try to access the incorrect object by mistake if they happen to
have two with different owners. But I have been told by members of the dev
team that there is extra lines of code called when you do not specify the
owner. It makes sense as it has to do some extra processing and security
code execution. One of the MVP's did a test a while back on simple calls
with and without owner qualifying the objects in a loop. I don't have the
results handy but there was a definite difference. While this may not cause
performance issues for most people it is just one more thing you can do to
ensure this is taken out of the loop. Why would you not want to qualify it?
--
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uEjDkt%23bGHA.3348@.TK2MSFTNGP03.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4DWRJ7bGHA.1204@.TK2MSFTNGP02.phx.gbl...
>>I believe the main reasons why people have multiple dbs with similar
>>schemas / objects are due to security reasons. There are just times when a
>>db may host data for multiple clients and it is a client rule that their
>>data can not be housed int he same db as someone else's. IN general if
>>you have tables in one db that you always query from another it warrants
>>the question of why? But there are no hard and fast rules. Each
>>application has it's own requirements and it is up to you to decide what
>>is best for your environment. As for qualifications I have yet to come
>>across a situation where fully qualifying (db.owner.object) all objects
>>makes sense when the objects are in the same db. I think there are always
>>more reason why not to do it than to do it. If you happen to have a
>>business requirement for it then by all means do it. If not it will
>>probably byte you somewhere down the line. As for the owner qualification
>>I will leave you with this kb:
>> http://support.microsoft.com/?id=263889
>> for performance reasons alone.
> Here's the issue in that KB:
> When making an RPC call from client code you should owner-qualify the
> procedure name if the procedure is in another schema. If you don't it
> will cause a brief compile lock as the session starts to compile a plan
> for the stored procedure, discovers that one already exists, and then
> decides to use the existing plan. This compile lock, while it's not a big
> resouce hog, can limit the total throughput of invocations of the
> procedure because only one session can own the compile lock at a time.
> The real problem is not that the cache miss, or even a batch compilation.
> The issue is that this situation causes a compile lock on the target
> procedure, and so instead of just wasting a bit of CPU, it makes sessions
> "wait in line" to execute the procedure.
> This KB cannot support a general performance best-practice of
> schema-qualifiing object names in stored procedurs, views or dynamic SQL.
> That KB only applies to how stored procedures are invoked, not how they
> are coded internally, and thus only apply to client code, not stored
> procedures, views or dynamic SQL. And it is not even clear if this issue
> exists on 2005 when a user's default schema can, and usually should, be
> the dbo schema. When the user's default schema is dbo, the initial lookup
> for the plan may succeed.
>
> David
>
>
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:epssQZDcGHA.4224@.TK2MSFTNGP04.phx.gbl...
> Yes the KB mainly talks about locks during compile time and I have seen
> that bite more people than I can count. As for general owner
> qualification how could it not help. For one it removes any ambiguity and
> the chance that someone will try to access the incorrect object by mistake
> if they happen to have two with different owners. But I have been told by
> members of the dev team that there is extra lines of code called when you
> do not specify the owner. It makes sense as it has to do some extra
> processing and security code execution.
Absolutely. But that is during compile time. Once the plan is compiled,
it's done.
>One of the MVP's did a test a while back on simple calls with and without
>owner qualifying the objects in a loop. I don't have the results handy but
>there was a definite difference. While this may not cause performance
>issues for most people it is just one more thing you can do to ensure this
>is taken out of the loop. Why would you not want to qualify it?
>
Ok, here's why I don't want to qualify objects.
Extra typing.
Objects cannot be easilly moved between schemas.
Developers have to remember and correctly identify their default schema.
In all programming languages I like local things to be named with relative
names.
Not huge reasons. But if there's no real performance penalty, good enough.
David|||all of this is mouse testicles. it doesn't add up to anything anyone
is going ot measure, let alone slow down a decent production
environment.
rebuild your indexes, stats, and EXAMINE how data is pulled from the
data.
Odds are you can change an index or 3 and get your speed back.
Give us some ideas of how big your tables are. Look first at the
biggest tables, and the tables that are growing the fastest.
If you are inserting or changing large amounts of data, REALLY examine
your clustered indexes.sql

IS this true?

Monday, March 19, 2012

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup to
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.
Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last full
> backup and I want only changed data since last full or differential backup to
> be backed up and shipped to offisite location. Currently the only option is
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.
|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done since
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>
|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transaction
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>
|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup t
o
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last fu
ll
> backup and I want only changed data since last full or differential backup
to
> be backed up and shipped to offisite location. Currently the only option i
s
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done sinc
e
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
>|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row
.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transacti
on
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:
>

Monday, March 12, 2012

Is there performance penalty for returning resultsets instead of rows

> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
I wasn't sure about the overhead so created the following procs.
CREATE PROC Proc1 AS
SELECT * FROM
(SELECT 1 AS Test
UNION ALL SELECT 2
UNION ALL SELECT 3
<snip>
UNION ALL SELECT 40) As Test
GO
CREATE PROC Proc2 AS
SELECT 1
SELECT 2
SELECT 3
<snip>
SELECT 40
GO
I then ran a test of 10,000 iterations with the code below. The app and SQL
were on the same machine:
private void AdHocTest()
{
_connection.Open();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = _connection;
command.CommandText = "dbo.Proc1";
RunTest(connection, command, 10000);
command.CommandText = "dbo.Proc2";
RunTest(connection, command, 10000);
_connection.Close();
}
private void RunTest(SqlConnection connection,
SqlCommand command,
int iterations)
{
DateTime startTime = DateTime.Now;
for (int i = 0; i < iterations; ++i)
{
//execute command and consume all results
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read()) ;
}
while (reader.NextResult());
reader.Close();
}
System.Diagnostics.Trace.WriteLine(
string.Format("Test {0} duration is {1}",
command.CommandText,
DateTime.Now.Subtract(startTime).ToString()));
}
The single result method was 1 second for all 10,000 iterations and the
multiple result method was about 2 seconds. I would expect an even more
pronounced difference with SQL on a separate box.
Of course, this test didn't include the overhead of the server cursor or
client processing. In your actual application, the performance difference
probably won't matter unless you have a lot of users.
Note that you might run into issues with a single 'generic' paging solution.
There are many different pagination techniques and no single one is best for
all situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:OKkF9y3aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> I'm using server cursor for generic paging.
> Interesting part is this:
> FETCH RELATIVE @.StartRow FROM cur
> WHILE @.PageSize > 1 AND @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM cur
> SET @.PageSize = @.PageSize - 1
> END
> Instead of single result set with N rows, this returns N result sets with
> 1 row. I can handle that in my client, that's not an issue.
> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
> Thanks,
> Dejan> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
If a count of the number of rows is not needed for your paging technique,
you can still provide the user with separate 'get count' button. We used
that technique in one of our apps because we had tables with hundreds of
millions of rows and querying was ad-hoc. Users could still navigate with
'next' and 'prev' button as well as jump to specific pages.
Note that SELECT COUNT(*) will use the narrowest useful index so index
tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
using a cursor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
> Thanks Dan for these numbers. In the mean time I performed some tests of
> my own. To my surprise, I found out that main problem is not with cursors
> and result sets, but with SELECT COUNT(*)!
> For 50k rows and when page is at the beginning of table COUNT takes about
> 10x more time than fetching 30 full rows!
> I need that count, to display exact number of pages to user.
> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
> Dejan|||I forgot to add that you can get an accurate table rowcount in SQL Server
2005 with:
SELECT rowcnt
FROM sysindexes
WHERE id = OBJECT_ID('dbo.MyTable)
AND indid IN(0,1)
The returned value is an approximation in SQL 2000 and might not be
accurate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23874LaHbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> If a count of the number of rows is not needed for your paging technique,
> you can still provide the user with separate 'get count' button. We used
> that technique in one of our apps because we had tables with hundreds of
> millions of rows and querying was ad-hoc. Users could still navigate with
> 'next' and 'prev' button as well as jump to specific pages.
> Note that SELECT COUNT(*) will use the narrowest useful index so index
> tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
> using a cursor.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
> news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Friday, March 30, 2012

IS this true?

IS this true?

Monday, March 19, 2012

Is there such thing as "incremental" backup for SQL server?

Is there such thing as "incremental" backup for SQL server?

Monday, March 12, 2012

Is there performance penalty for returning resultsets instead of rows

better method for DTS

Blog Archive

About Me