How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups
.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>sql

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
Thanks
SET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?
|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do i measure bandwith usage for merge replication SQL 2005 to Sql server mobile 2005?

Hi,

Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.

Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?

thnx,

pdns.

Not for mobile, but if you had a SQL Server subscriber (Dev, Ent, Std, Express) you can add (unsupported) merge agent parameter "-T 101" which will give command-line output statistics for that particular sync, there might be some net statistics IO output which is not always accurate unfortunately.

However we can take this into consideration for hte next release of SQL Server, although I cannot make any guarantees.

|||

can u tell me full command syntax that how we could use it on command line

thanxs in advanc

Ahmad Drshen

|||Search BOoks Online for topic "Replication merge agent", you should see the command line utility topic. YOu can also drill down into the agent job steps to see the exact parameters and values that your job is using to execute a push/pull merge agent.

How do I determine a measure's data type in an ADOMD cellset?

Given an ADOMD Cellset how do I determine the data type of a cell such as currency, percentage, integer, etc.?

I have found no way of doing this other than explicilty looking for percent signs and currency symbols which is an inheritly BAD thing to do. I've looked through the properties arrays as well as tried different DIMENSION PROPERTY settings and have not found a way to retrieve the data types.

Thank you,

Terry

You are mixing different terms here. Currency and Integer are data types. Percentage, however, is a formatting. Currency also can be viewed as formatting, i.e. you can format integer data type as currency.

Seems like you want to find out the formatting of the cells - you can ask for the FORMAT_STRING cell property to discover how the values of the cells were formatted.

|||

I agree I was mixing terms to an extent, but the basic problem is that anyone visualizing OLAP data in a chart needs to know the type of the data (percentage, currency, integer, etc.) in order to format the axis. Inspecting the already-formatted value or parsing the format string is a very error prone and weak way of arriving at that information.

Terry

|||

Well, I don't think there is something special about chart or about the fact that this is OLAP data as opposed to, say, SQL data. Data is only meaningful when there is understanding of its semantic, and the info such as whether it is currency or percentage is only a small portion of such semantic. Ultimately somebody needs to know what these numbers mean.

Again, if you want to do something special when data was formatted as 'Currency' or 'Percent' - you can look at FORMAT_STRING cell property.

|||

Yes, "ultimately somebody needs to know what these numbers mean." That is why I'm trying to format the user interface correctly! Contrary to your reply, when looking at values on a chart knowing whether they are amounts or percentages is NOT "only a small portion of such semantic". It's a requirement to build a chart accurately, and any competent programmer would cringe at the thought of parsing a format string to arrive as such metadata. The whole point of OLAP is to interpret data. Having available such basic metadata about that data is not too much to ask. If it's the user's sole responsibility to understand the semantics of data, then why not just give them a text-file dump of the SQL tables?

Furthermore, you need to start listening to your users. This applies to every user question and complaint on this forum which you just dismiss in your response out of arrogance. There are a lot of holes in your application and virtually no documentation. You should stop telling everyone they don't know what they're doing, don't need a particular feature, or are obviously doing something wrong.

Thanks for you help and have a happy President's Day,

Terry

|||

I appologize if I sounded arrogant or created impression that I dismiss user complaints. This certainly was not my intent. I personally do try to listen to the users - this is the main reason why I participate in this forum. I am sorry that it looked the opposite to you and other readers. I will watch my language more carefully.

Thanks,

Mosha.

|||

Thank you Mosha.

|||I'm iterating through the CellProperties I have in an ADOMD.NET cellset

with

foreach (CellProperty prop in item.ValueCell.CellProperties)
Debug.WriteLine("name:" + prop.Name + " val:" + prop.Value);

There are no format_string properties?

I have not explicity defined the format_string in the MDX query.

I still don't know how to get the format_string property?

How do I determine a measure's data type in an ADOMD cellset?

Given an ADOMD Cellset how do I determine the data type of a cell such as currency, percentage, integer, etc.?

I have found no way of doing this other than explicilty looking for percent signs and currency symbols which is an inheritly BAD thing to do. I've looked through the properties arrays as well as tried different DIMENSION PROPERTY settings and have not found a way to retrieve the data types.

Thank you,

Terry

You are mixing different terms here. Currency and Integer are data types. Percentage, however, is a formatting. Currency also can be viewed as formatting, i.e. you can format integer data type as currency.

Seems like you want to find out the formatting of the cells - you can ask for the FORMAT_STRING cell property to discover how the values of the cells were formatted.

|||

I agree I was mixing terms to an extent, but the basic problem is that anyone visualizing OLAP data in a chart needs to know the type of the data (percentage, currency, integer, etc.) in order to format the axis. Inspecting the already-formatted value or parsing the format string is a very error prone and weak way of arriving at that information.

Terry

|||

Well, I don't think there is something special about chart or about the fact that this is OLAP data as opposed to, say, SQL data. Data is only meaningful when there is understanding of its semantic, and the info such as whether it is currency or percentage is only a small portion of such semantic. Ultimately somebody needs to know what these numbers mean.

Again, if you want to do something special when data was formatted as 'Currency' or 'Percent' - you can look at FORMAT_STRING cell property.

|||

Yes, "ultimately somebody needs to know what these numbers mean." That is why I'm trying to format the user interface correctly! Contrary to your reply, when looking at values on a chart knowing whether they are amounts or percentages is NOT "only a small portion of such semantic". It's a requirement to build a chart accurately, and any competent programmer would cringe at the thought of parsing a format string to arrive as such metadata. The whole point of OLAP is to interpret data. Having available such basic metadata about that data is not too much to ask. If it's the user's sole responsibility to understand the semantics of data, then why not just give them a text-file dump of the SQL tables?

Furthermore, you need to start listening to your users. This applies to every user question and complaint on this forum which you just dismiss in your response out of arrogance. There are a lot of holes in your application and virtually no documentation. You should stop telling everyone they don't know what they're doing, don't need a particular feature, or are obviously doing something wrong.

Thanks for you help and have a happy President's Day,

Terry

|||

I appologize if I sounded arrogant or created impression that I dismiss user complaints. This certainly was not my intent. I personally do try to listen to the users - this is the main reason why I participate in this forum. I am sorry that it looked the opposite to you and other readers. I will watch my language more carefully.

Thanks,

Mosha.

|||

Thank you Mosha.

|||I'm iterating through the CellProperties I have in an ADOMD.NET cellset

with

foreach (CellProperty prop in item.ValueCell.CellProperties)
Debug.WriteLine("name:" + prop.Name + " val:" + prop.Value);

There are no format_string properties?

I have not explicity defined the format_string in the MDX query.

I still don't know how to get the format_string property?