Prepared SQL Plan Vs. Procedure Plan

Nov 23, 2005

I am working on tuning the procedure cache hit ratio for my server. We have
added 4 Gb of memory to the server, which has helped. In addition, I have run
the DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratio
up to about 84% (from 68%).

When I use the performance monitor on the server and look at SQL Server Cache
Manager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, but
the Procedure Plan hit ratio is down around 55%. I've done some research on
different tuning techniques, but can't seem to find 1. a clear definition of
the difference between the prepared sql plan and the procedure plan and 2.
other than adding memory and running dbcc freeprocache, how can I get the
procedure plan cache raised? I do know that there are some procedures that
need to be modified to be called fully qualified (e.g. exec dbo.sp_###
instead of exec sp_###), but I don't think that those will increase the
procedure plan by 30% or more.

Any insight you can give would be greatly appreciated.

Thanks,
Michael

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200511/1

View 1 Replies


ADVERTISEMENT

Execution Plan Caching For Prepared Statements

Nov 22, 2007

I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise.

When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters.

I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen.

The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms).

Please comment and advise,
Thanks

View 6 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

Is only one plan is kept for one query in plan cache?

i heard generally hash is created for a query and plan is search with this hash.

View 2 Replies View Related

Cached SQL Plan Vs. Stored Proc Plan

Dec 12, 2002

We have a debate in our team about embedded SQL vs. Stored Procs.

The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?

I can't find any definitive information on pros and cons between the two methods.

If there are no major performance issues, or gotchas, I guess it comes down to developer preference.

SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.

Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic


Any info would be appreciated.

thanks

Kevin

View 4 Replies View Related

Stored Procedure Create Execution Plan?

Apr 3, 2007

i have a few stored procedures called by an application that i would like to create execution plans for every time they are run. is there a way to do this? or are execution plans only done through the Sql Server Query Editor? these queries make comparisons between a temp table and a master table and setting up the data for the tables is time consuming. so id like to automate it by setting the store procedure to create the execution plan at runtime.

View 2 Replies View Related

Query Plan For Stored Procedure Not Being Generated

Mar 13, 2008

I have a stored procedure that I want to test for performance , however, I cannot view the estimated query plan execution. I am not using any nested subqueries. Any ideas why the plan will not generate?


Thanks in advance!

View 1 Replies View Related

Execution Plan For Single Stored Procedure From Profiler

Apr 13, 2007

I'm trying to get the execution plan for a single stored procedurefrom Profiler. Now, I've isolated the procedure but I get allexecution plans. Any ideas on how to connect the SPIDs so that I onlyget the execution plan for the procedure I'm watching and not thewhole of the server?

View 4 Replies View Related

Actual Execution Plan Vs Estimated Execution Plan

Jul 7, 2006

The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks

View 1 Replies View Related

SQL Server 2008 :: Query Execution Plan Of Stored Procedure

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 8 Replies View Related

How To Find Query Plan For A Stored Procedure Using Temporary Tables

Oct 25, 2006

This post is related to SQL server 2000 and SQL Server 2005 alleditions.Many of my stored procedures create temporary tables in the code. Iwant to find a way to find the query plan for these procsRepro--***********************************use pubsgoCREATE PROCEDURE Test @percentage intASSET Nocount on--Create and load a temporary tableselect * into #Temp1 from titleauthor--Create second temporary tablecreate table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ordint, rolaylityper int)--load the second temporary table from the first oneinsert into #Temp2 select * from #Temp1goset showplan_Text ONgoEXEC Test @percentage = 100GOset showplan_Text OFFgo**************************************I get the following errorServer: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp2'.Server: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp1'.I do understand what the error message means. I just want to know abetter way of finding the query plan when using temp objects.My real production procs are hundreds of lines with many temp tablesused in join with other temp tables and/or real tables.Regards

View 3 Replies View Related

SQL Server Admin 2014 :: Procedure Plan Cache Is Cleared Automatically

Sep 17, 2015

The plan cache is cleared automatically (every 30-60 seconds) on our high load OLPT server.

Can't find a reason for it. I have been logging procedure cache thru dm_exec_cached_plans, every second for a couple of minutes.

It shows that it doesn't grow beyond 200Mb. SQLserver has 64Gb available.

DBCC FREEPROCCACHE is not executed.
dm_os_memory_clerks shows the same 200Mb max (approximately).

I disabled the SQL Agent, still happens, so it's not a job causing the problem.

I disabled the Resource Governor, still the same problem.

View 9 Replies View Related

SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 1 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

SQL Maintenance Plan

Mar 5, 2001

I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan.
Thanks!

View 1 Replies View Related

Maitenance Plan

May 1, 2001

Hi All,

I have around 10 maintenance Plans on my server and all the plans are running fine except for one which takes for ever....Yesterday I scheduled it to run at 1 AM and it was still running when I came in this morning...Usually this job used to take only 2 minutes and now it takes forever....

I tried to delete the maintenance Plan and created a new one , but no luck..

But If I try to back up the database by right clicking the database --
All Tasks --- Backup database , it takes less then 2 minutes..But If I try to run the job created by the Maintenance Plan to back up the database it takes forever and all I can see the status of the Job as ' Executing Job Step 1"..

I created a test database and restored the database in Issue onto the test database and created a maintenance Plan for the test database and it runs fine and takes less than 2 minutes.. and I don't see any security issues on this...

B'cause the job neither fails nor executes ..So there are no error messages to look in to ...

Please let me know if any one has encountered the same problem and how to resolve it..

Thanks in Advance
Venu

View 1 Replies View Related

Maint. Plan

May 31, 2001

In maint. plan when reorg is selected for a user db, it will also work on
sys tables in user database. Is it a good thing to do ? How do I avoid sys tables in a user database not to be part of maint optimization plan ? Some script would be helpful !


Thanks,
Ivan

View 3 Replies View Related

Exectution Plan

Jun 22, 2001

When looking at the Est. Execution Plan of a query in QA, what should I be looking for? What numbers are acceptible? Does anybody have a link to any information on this subject?

TIA,

Chris

View 1 Replies View Related

Db Maintenance Plan

Oct 23, 2001

Hello All

I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.

I would be very grateful for any ideas

View 1 Replies View Related

Plan For Installing SP3

Dec 27, 2000

Hi,

Can anyone help me in comming up with a plan to install SP3

Thanks
Reddy

View 4 Replies View Related

Maintenance Plan

Aug 31, 2000

I have a strange thing in one of our Maintenance plans.

On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)

The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.

What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?

Any other suggestions or ideas on what happened..

ann

View 1 Replies View Related

Maintenance Plan

Oct 4, 1999

I've created a database maintenance plan to backup a database, but it just
isn't happening, am i missing something. The maintenance plan appears to be
created successfully.

responses appreciated.

thanks
Todd Minifie

View 6 Replies View Related

DBA Admin Plan

Jan 11, 2000

Hi there,
Could anyone please help me, I have been given the responsibility of planning and implementing the admnistration of 25 existing SQL Servers (6.5 and 7.0) countrywide and I am unsure as to where I should start. I am relatively new to SQL Server and if someone could just point me in the right direction or possibly recommend any good publications which I could refer to, I would be extremely grateful.

View 3 Replies View Related

Capacity Plan

Mar 9, 2000

Hi!

I got two questions and please forward all your suggestions:

1: Is there any document for capacity planning of SQL Server 6.5 database?

2: Is there a way to find out total number of transactions per day in SQL Server?

Thanks in advance!

View 1 Replies View Related

Bad Execution Plan

Aug 21, 2001

I'm new to SQL server but familiar enough with databases to know this doesn't seem right.
Here's the situation:
I have a table with real estate property information. There are about 650,000 rows in it. I have a nonclustered non-unique index on the city where the property is located. There are about 40 unique values in this index.

I do a simple query like:
SELECT city,address from propinfo where city= 'CARLSBAD'. The query will return about 4,000 rows. The problem is that the execution plan that it chooses is to do a full table scan. I.E. Even though there is an index on City, it chooses to look through 650,000 rows rather than do an index seek. Something sounds inefficient here. BTW, this happens in both SQL 7 and SQL 2000. Can anyone explain why this happens? I've got to think that SQL Server is more efficient here.

View 5 Replies View Related

Query Plan

Jul 23, 2002

I am noticing a discrepency in query plans when a process is run in Analyzer as either a proc or as straight sql.

I have a query that uses a view of 5 tables that have a check constraint on the year. When I run my query in query analyzer and state year = 1999 along with over parameters then the query plan only looks at the one table.

When I take that query and make a stored proc and run the process passing the year = 1999 along with other parameters the plan states that it is looking at all of the tables in the partitioned view.

Thanks,

Here is a copy of the proc


create procedure testproc
@CUST_I varchar(6),
@FISCAL_DD_D tinyint,
@FISCAL_MM_D tinyint,
@FISCAL_YY_D smallint,
@cont_cvarchar(1),
@invoice varchar(9)
as
Select
CONT_C,
INVC_I,
DIV_C,
REG_C,
LOC_I,
INVC_D,
CUST_I,
CR_PREF_C,
FISCAL_DD_D,
FISCAL_MM_D,
FISCAL_YY_D,
PAY_CODE,
REF_TEXT,
EC_TYPE,
ADJUST_A,
ALLOWANCE_A,
MAT_A,
TAX_A,
FRT_A,
REEL_A,
OTHER_A,
GST_A,
PRIOR_BAL

from MY_FIVE_YEAR_VIEW

whereFISCAL_YY_D = @FISCAL_YY_D
AND cont_c = @cont_c
AND FISCAL_DD_D = @FISCAL_DD_D
AND FISCAL_MM_D = @FISCAL_MM_D


AND (REF_TEXT LIKE '%' + @CUST_I + '%' or REF_TEXT LIKE '%' + @invoice + '%' )


order by cust_i, pay_code

View 1 Replies View Related

Maintenance Plan

Oct 1, 2004

Can you generate script for a maintenance plan?

I know how to script a job, I was wondering about a plan.

If not, whats the best way to record the configuration?

Thanks

Lystra

View 3 Replies View Related

Maint. Plan Help

Aug 8, 2006

I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!

View 1 Replies View Related

Backup Plan

Aug 13, 2006

I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.

My new plan is looking like this:

1) Run a complete database backup once a day

2) Run Transaction Log backups every hour

My questions are as follows:

1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?

2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.

3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.

Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!

Thanks in advance~

View 1 Replies View Related

Help With Backup Plan

Feb 7, 2004

Hi all,

I am new to SQL and was given a SQL server to administer. There are lots of databases on the server and no one seems to know which one is used and which one is not. I try to do some cleanning.

1. Can I find out if there is any activity on a database so I can delete them off and how?

2. There are backup jobs that run every night which do a full backup. I am thinking of changing it to do full backup once a week and differential every night. These databases are updated daily. Some have lots of activities, some have less. Does that sound reasonable? and if I do differential backup, should I create separate back up file or should I add on to the full backup file?

Any comments or suggestions are greatly appreciated.

View 2 Replies View Related

SQL Maint. Plan Help

Apr 14, 2004

I have setup maintenance plan to run nightly.

(1) Maint plan is setup to backup the databases that users actually use to update company information (2) diffrent databases.
(2) maint plan backs up the master database this is setup to run by itself.
(3) maint plan is setup to backup the model - msdb databases together.

I setup the email notification option to inform me if my backups were successfull. below is an example that I'm recieving everymorning on every database. Something about the single user mode is causing the problem. can somone explain this to? Maybe give me a direction to follow to correct this problem. Below is an example error message that I'm recieving.

I can backup any database by right clicking selecting all tasks then backup database and I get a complete backup to problem. Why is my Maint. Plan not going through. the rebuilding of indexs is successful.

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'My Server name' as 'My_Domain_NameMy_SQL_Admin_Account (trusted)
Starting maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:00 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Leo'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database Leo: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database SlcBom: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports... 0 file(s) deleted.

End of maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

Emailing report to operator 'Admin, SQL'

Thnaks for any help

SQL Rookie

View 1 Replies View Related

Maintenance Plan

Apr 7, 2008

hi everyone..
this is a little bit weird ..
i am trying to make a backup strategy. i am using sql2005.
when i go to maintenance plan. right click >> new maintenance plan...
nothing happens..
if i go with the maintenance plan wizard everything goes normally.
after doing the backup, if i right click on it and press
modify , nothing happens too.
what i mean by nothing happens is that it doesn't open the "design view".
the back up is doing normally.. but i need to set a range of 5 days before overwriting the oldest backup.
any idea what is going on or what am i missing?!
thank you

View 2 Replies View Related

Maintenance Plan??

Apr 9, 2008

Windows Server 2003R2 w/ Sp2
SQL 2005 w/ Sp2

Created weekly (full backup) and a daily (differential backup) Maintenance Plans using the wizard. I formatted the server, installed the OS and SQL. Restored the full backup (No Recovery Mode), then restored the differential backup (Recovery Mode), tested and all worked well.

Then I noticed the original Maintenace Plans I created (Full and Differential) were gone; makes sense as I had formatted the server.

Is there a way to create a Maintenance Plan file or script that I can save and just add back to the server??

Hope that makes sense.
Any help appreciated.
Kerry

View 3 Replies View Related

Execution Plan

Feb 2, 2006

Hello, I have been looking at the execution plan for a procedure call and the select, compute scalar, stream aggregates, constant scan, nested loops, asserts are all at 0% cost, the PK costs are 2% apart from a rogue 7% and a few 20%, tables scans are all at 23%. The query cost realtive to the batch is 100%. What does this all mean?
I have put non-clustered indexes on all the table attributes that are involved in the select statements but this has made no difference, i am guessing this is because my tables are not heavily populated and i may have seen a difference if i had thousands of entries in the tables the select statements acted on, is this assumption correct?
Does anyone else bother using the execution plan to tweak there DB or is it a negligible tool?

Jill

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved