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 import Excel file into SQL Express??

Hello,

I am total beginner to SQL and want to import an Excel file into SQL but I'm not sure where to start. The Excel file is a small company directory, names, addresses etc., not too big.

Any help would be greatly appreciated!!!!hi..
my adivice is dont use excel, create a MS-Access data base and import that into sql..|||thanks for the post.

Assuming I am able to create an MS Access database, what are the steps to importing this file into SQL?

thanks|||1.right click on any database
2.select TASKS and then select IMPORT DATA after clicking
this one dialog box will appear,click NEXT
3.Choose MICROSOFT ACCESS as DATA SOURCE,then BROWSE ur access file then click NEXT
4.Chosse the DESTINATION[SQL native client]
5.Select the SERVERNAME
6.There are two AUTHENTICATION, select the SQL SERVER AUTHENTICATION and give the USERNAME & PASSWORE
7.Select the DATABASE and then click next
8.select "COPY DATA FROM ONE OR MORE TABLES OR VIEWS" then click next
9.the tables what you created in access will be showed here
10.select the tables which you want to insert and then click next
11.select the "EXECUTE IMMEDIATELY" check box and then click next
12.click finish
13.it takes some time to process and finallY, if there are no mistakes,it executes
successsfuly else it shows the error message|||You can't Import / Export using the Express editions.|||

Quote:

Originally Posted by Tros

You can't Import / Export using the Express editions.

Seems it may be round about, Excel -> Access -> SQL Express.|||When you download the SQL Express software, I believe it comes with a 'wizard' called DTS Wizard. It allows you to transfer among all the previously mentioned types.sql

How do I handle single quotes in data fields

I want to set company name in table company to "Don's Autoworks". When I run
a simple query:
update company
set company_name = 'Don''s Autoworks'
where id = 125
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 's'.
However, select queries with 2 single quotes like below work fine:
select * from company
where company_name = 'Mel''s Diner'
What should I do to insert or update data with single quotation marks in it.
Thanks,
Ashhad.>I want to set company name in table company to "Don's Autoworks". When I
>run
> a simple query:
> update company
> set company_name = 'Don''s Autoworks'
> where id = 125
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 's'.
This query should work. Where are you running it?
A|||Query Analyzer, SQL Server 2000, on Windows 2000
"Aaron Bertrand [SQL Server MVP]" wrote:

> This query should work. Where are you running it?
> A
>
>|||The query seems correct. Check it again
Madhivanansql