Is It Possible To Save And Force A Plan?

Jul 23, 2005

I have a complex query (16 table join) that until five days ago took 30
sec to run. It now takes eight hours. I restored a backup of the
database from five days ago and the query plans are completely
different for the same exact query. Five days ago the final estimated
row count is 1.6M now it is 1.7E+10 OUCH! The amount of data added in
those five days is insignificant (percentage wise). I rebuilt all the
indexes using DBCC DBREINDEX with no change in the plan or results. Is
there anyway to save the query plan (from the backup) and run it
against the new data?

TIA,
Tracy

View 6 Replies


ADVERTISEMENT

Anyway To Force SQL Server To Save Store Procedure With Errors?

Feb 12, 2008

When I create/alter a store procedure in SQL Server 2005, SQL server always checks for syntax errors first and won't let me save the change if it detects any error. Is there a way we can force the SQL server to save the store procedure that fails the syntax check?



I know SQL server will allow such invalid store procedures if you detach & re-attach the entire database from one SQL server to another server. However, if I try to manually create the same store procedure from one server on a different server with a script, then it won€™t let you save the store procedure if the linked server (or the table) can€™t be accessed from the new sql server.



How do you get around this?





Thanks

View 13 Replies View Related

SQL 2012 :: How To Force Server To Generate A New Query Plan

Oct 30, 2015

Select A.* from A inner join B on ( A.ID= B.ID )

I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?

View 7 Replies View Related

No Description - Modify Backup Maintenance Plan Failes To Save.

Apr 26, 2007

Hello,



SS2k5 - 9.00.3050 (x64) - Most current SP



I've created a backup maintenance plan 'All User Databases'. Plan saved and executes.



Modify plan to add new subplan step to remove old backup files, save fails with a Studio Dialog encapsulating the message -> No Description



Cannot save the Maintenance Plan.



Any ideas about how to save the Plan?



Thank You... Covi

View 13 Replies View Related

SQL Tools :: Unable To Save A Maintenance Plan In 2005 / Class Not Registered

Nov 2, 2015

Unable to save a maintenance plan in sql server 2005, SP3. Error is class not registered.

View 2 Replies View Related

SQL Server 2005 - Save Tran Save Point Name Case Sensitive?

Feb 11, 2006

Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.

View 4 Replies View Related

How Can I Change The Default Save-As/Save Directory

Jun 26, 2007

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.



I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?



Thanks,

Nanci





View 2 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

Prepared SQL Plan Vs. Procedure Plan

Nov 23, 2005

I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed 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 theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1

View 1 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

Force Detach

Sep 15, 2005

Hi

I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use.
Is there a Possibility to make a "force detach" or something?

Thanks for Help

Greets

View 3 Replies View Related

Force Shrink Log

Jan 15, 2002

Id like to know how I force the transaction log to shrink in v2000?

I have sp_force_shrink_log but this only works in v7.

... Also, how do I set the log to truncate on checkpoint?


Many thanks,
Lauryn

View 1 Replies View Related

Force Primary ID

May 12, 2008

How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc.

Pretty much how to force it being on number I would like to begin.

View 5 Replies View Related

Force Restore

Sep 7, 2005

Hi All

Forgive my ignorance. I have been told you cannot run a force restore without SQL Agent running? Is that so?

Also, is it possible to execute a force restore from a command line. If so, how would you go about do this.

I ask these questions because recently our database and MSBD files became corrupted and couldn't restore them in the normal way. Had to manually rebuild MSDB file.

Thanks

View 5 Replies View Related

Can I Force Printing?

Sep 1, 2005

I have a script or SP that takes a very long time to performmultiple tasks, and after each one there is a PRINT statementthat shows the time and what was just accomplished, to help memonitor what's happening and estimate how long it will take.In a script, I can put a GO after each PRINT to cause the outputto appear immediately, but that's not possible inside an SP.Instead, it seems the output goes to a buffer, and the bufferis only output when a PRINT causes the buffer to become full.Sometimes there is a long delay before the buffer fills. Isthere a way other than GO to cause immediate printing? (Iguess I could just use longer messages, and fill up thebuffer with every one, but that's not very elegant.)Thanks,Jim GCountrywide Home Loans

View 2 Replies View Related

How To Force A Commit In A Sp

Jul 20, 2005

I've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!

View 3 Replies View Related

Force Sql Login

Oct 23, 2007

I set up odbc to link to sql express from access 2003, using sql authentication to force a login, and did not click 'save password'. when i open up access again, and open table, it goes right in without a login. How can I set so it does a login, at least each time Access opens?

View 1 Replies View Related

Force Encryption

Jun 8, 2006

I want to configure SQL 2k5 Express so that all connections are
encrypted. I've found the documentation that gives the How-To steps,
but I don't see the SSL option in the Network Configuration.

Is this possible with the Express edition?

Do I need to do something special with the installation?



Thanks,



OldSam

View 1 Replies View Related

Force A Commit

Sep 3, 2007

Hi,

My data flow has several transformations:
1. Search an employee, if the employee already exists, update it, otherwise insert it.
2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.

At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....

the question is, Its possible to force a commit?


Thanks!

View 5 Replies View Related

How Do I Force Parameterization?

May 2, 2008

I have a application that runs non-parameterized queries against SQL, that are filling my proc cache. The most common one is like the below:





Code Snippet
DELETE FROM Item_Temp WHERE SessionID = '1232345'

The numeric value of the SessionID changes for every time it runs. How do I force parameterization of this without changing the application code?

View 14 Replies View Related

Force New Page.

Apr 3, 2007

I have a report that has multiple graphs on it. I would like to force a page break (new page) in between the different reports causing a report to be generated on it own page. Im assuming that Im missing something here because it seems like a easy/usable feature. Any help would be great.



Thanks,



ZDB

View 1 Replies View Related

Force Exit

Sep 11, 2007

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

View 5 Replies View Related

Force Drop Database

Jul 9, 2004

Hi everybody,

I would like to know if there would be any special way to force drop a database from an ASP.NET page.

When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.



Thanks in advance

View 3 Replies View Related

Force Protocol Encryption

Aug 26, 2004

I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?

Thanks!

View 1 Replies View Related

How Can I Force A DTS Task To Fail?

Sep 14, 2000

I have a DTS package in which I would like it to fail if the table is empty. I would like to create the first task as this check (i.e. select @cnt = count(*) ... if @count = 0 then fail the task (to prevent further DTS Task processing.

Does anyone have an example or suggestion on how to set this particular Execute SQL Task to fail?

Thank You

Jamie Reis

View 4 Replies View Related

How To Force Users From A Database?

Dec 1, 2000

Does anyone know a way to force out all users from a particular database? I need to script this to perform some maintenance at a particular time every day.

Thanks for your help,

Les

View 2 Replies View Related

How To Force An Index On The View

Jan 21, 2004

I created a view from a table with out any where clause. All the rows from the table will be in the view with some extra info.

The table has a few indexes.

In my stored procedure I am storing two columns from the table ( IdHi , IdLo - primary key ) into a temp table and joining the temp to the view.
Here the query is taking too much time and not using the index. Can I force the primary key index on View?

Thanks!

View 1 Replies View Related

Force To Execute Some Code On RUN In QA

Feb 15, 2006

Hi,
is it possible to to modify/add to QA (using options ,macro ..)to execute line bellow on run of each script opened
from *.sql file

select
'script= put you file name'+ char(13)+
'server = ' + cast(@@servername as char(20))+ char(13)+
'dbname= ' + cast(DB_NAME()as char(20)) + char(13) +
'execution date= '+ cast(getdate()as char(24))+ char(13)+
'executed by = '+ system_user

thanks
Alex

View 2 Replies View Related

Force A SSIS Pkg To FAIL

Mar 26, 2008

Does anyone know how to force a SSIS step to fail? I don’t want to export a file if no records were written.

I found this but not quite sure how to implement it:
To do this, double-click on the connector line joining 2 of your
ExecuteSQL tasks and change the "Evaluation operation:" to any of the
options that include "Expression". This enables you to enter a
boolean expression that must evaluate to true for the path to be
followed. Assuming you've got an int parameter called "sp1rtnvalue"
which must evaluate to 1 for the flow to continue; your expression
would be: @sp1rtnvalue == 1

Am I suppose to define a @sp1rtnvalue in the stored procedure that creates the file I want to monitor? Do a record count in the stored procedure. In my pkg I enter the expression @sp1rtnvalue == 1 when I run that step the expression will evaluate to false and fail?

If so where exactly do I enter the Boolean expression (@sp1rtnvalue == 1) , what tab in what dialog/wizard??

I hope that made sense.

Thank you

View 7 Replies View Related

Clustering, Can I Force A Cluster?

Apr 18, 2007

I've been using some of the data mining capability in SQL 2005, and have been very impressed. But I've mostly been using decision trees/neural nets, and now have a problem that would seem to require clustering, but I'm not sure how to approach it...

Have list of parts by serial number and part number, and in a data cube have crunched a bunch of data to give me each parts' relative reliability.

So what I can do so far is to ask the cube "for a certain part number, which specific serial numbers are our 'bad actors'", ie have an unacceptably high failure rate compared to the average, by standard deviation.

The next logical question would be... what factors do these particular "bad" parts have in common? Environmental exposure, usage rates, a particular repair facility, etc etc. I have that type of raw data in other tables, no problem.

So what it seems like I would want to do is force a clustering algorithm to choose a certain segment of parts as a cluster and then use DM to determine what characteristics those parts share. Then we could see if there was any corrective action we could take, or whether we should just chuck those parts in the bin (if the DM didn't find any reasonable relationships between the parts). But I don't know enough about the clustering/association type DM to figure it out.

How does the group think I could approach this?

Geof

View 5 Replies View Related

Force IS To Use Column Headings

Feb 2, 2006

Hi,
I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.

The problem:
IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.

Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?

Thanks guys...

View 4 Replies View Related

Brute Force Attack

Oct 22, 2007

Hi to all,

Im looking at the sql server log, and I see a strange behavior.
Im getting at least 5 "sa" login attempts from an sepcific IP address.
How can I avoid this, lock that IP or add a delay to 'sa' failed logins?

Thanks so much

Cristian

View 10 Replies View Related







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