Traceflag To Ignore Index Hints

Jul 23, 2001

Hello.

There is a trace flag that tells SQL Server to ignore index hinting in incoming queries. I'm having a Monday morning problem and I can't remember the trace number nor find it in my notes. Can anyone else come up with it?

Thanks in advance,
-darin

View 1 Replies


ADVERTISEMENT

SQL Seems To Ignore UPDLOCK && ROWLOCK Hints

Dec 3, 2007



I've got a SELECT WITH (UPDLOCK, ROWLOCK) WHERE followed by an UPDATE WHERE statement. The results of the SELECT statement are deserialized in C# and updates are made to the deserialized object. Then the object is serialized back into the table with the UPDATE statement. I've got this code running within a transaction scope with the ReadCommited isolation level.

My service receives requests to update data and the requests can come in on different threads. What I'm seeing, is that once in a while, the log messages from my application indicate that two different threads are able to issue the above SELECT statement and both are receiving results. This is a problem since the thread that issues the last UPDATE will overwrite the changes made by the first. Each thread has its own connection and transaction scope.

I've researched all over the place and have tried a few different things, but all things point to the fact that query hints are just hints and that SQL may or may not pay attention to them. If that's the case, how am I suppose to perform a SELECT with the intention of updating so that no one else can do the same? I haven't tried table level locking, but I'd really like to avoid that if possible.

-Mike

View 4 Replies View Related

Index Hints

May 15, 2001

I am running SQL7 SP2 and and noticing table the query processor table scans when I ussue a between 'date1' and 'date2' instead of using the datetime index. If I put in the index hint (index = ix_datetimeXXXX) the query runs fine. My question is does this index hint restict the use of other indexes in the query and secondly how can I specify multiple index hints? Thanks in advance.

View 1 Replies View Related

SQL 2005: Query Is Not Using Non-clustered Index! Need To Avoid Hints!

Oct 26, 2006

Greetings,

I have two tables:

CustomerOrder
----
ID
CustomerID
StatusID

CustomerOrderDetail
----
ID
Order_ID
StockID
Quantity

CustomerOrderDetail table has clustered unique index for ID and non-clustered for Order_ID

SQL Server 2005 is using table scan for CustomerOrderDetail table When I user the following query:

select
cod.*
from CustomerOrder co
inner join CustomerOrderDetail cod ON cod.Order_ID = co.ID
where
co.StatusID = 8 -- Pending

Both of the tables are pretty big, detail table has more than million records, so scanning the table is a bad idea.

When I specify hint to use index then sql seeks, but how do I make SQL server to use index automatically? I don't want to use hints in my queries.

Thanks!

View 4 Replies View Related

Identity Column On Table And Ignore Duplicate On Index

Mar 27, 2007

Hello,

I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.



So first 5 rows inserted get identity

1

2

3

4

5



If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.



So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.



Is there any way to avoid this?



Thanks,

John

View 3 Replies View Related

Where Is My TraceFlag Output?

May 23, 2000

I turned on TraceFlag 1204 and 1205 to troubleshoot deadlocks.
The 1205 is in the SQL Log, but not the detail I was trying to get.

Please direct me to the proper location for this output.

Thanks,
Dano

View 2 Replies View Related

Hints ?

Jun 16, 2004

Is there anything equivalent available in SQL Server for Oracle HINTS ?

eg : Oracle query

select /* + INdex(sno index1) */ sno from test_table

Thanks,
Sam

View 2 Replies View Related

Hints

Jul 23, 2005

I am kind of confused about the way SQL Server 2000 handles the hintsthat users supply with their SQL statements.[color=blue]>From BOL, it seems that one can specify them with "WITH (...)" clauses[/color]in SQL statements known as table hints. Sometimes, multiple uses ofthis form in a statement is OK. Then there is the OPTION clause forspecifying statement hints. However, the documentation on OPTIONsection discourages their use.Being relatively new to SQL Server and still learning about it, what isthe general practice? Use hints or not? And if so, how (through WITHor OPTION clauses)?Cheers!

View 5 Replies View Related

Query Hints

Sep 12, 2005

My experience with query hints are that they are just that, a hint. What I don't understand is when does SQL decide to ignore your hint?
 

View 5 Replies View Related

Query Hints

Sep 29, 2003

Please advise.

Whilst running a query I recieved the error below.
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

What is ROBUST PLAN hint?.

Help Appreciated.

View 6 Replies View Related

Locking Hints

May 1, 2007

Hi

My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.

I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:

If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?

For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?

Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?

Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?

Thanks!

View 20 Replies View Related

BUG With Join Hints

Apr 20, 2007

I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.





Query 1


select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
This query gives an error:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

Second query is not really what I want but it illustrate that it works fine when getdate() is not used.



Query 2


select *
from DIM_DATE DD
inner hash join (
select A.student_key,
CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current
FROM FACT_STUDENT AS A
) SSE on DD.date_key= date_withdrawn_current
Is there some problem with using function getdate() ? It works fine in SQL Server 2000

This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) )
and (9.00.2050)

View 3 Replies View Related

Confused About Using Lock Hints?

Mar 19, 2006

I noticed that the online books say the following:
Note  The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary.
Also, at another place in online books, it says:
The table hints are ignored if the table is not accessed by the query plan.
From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?

View 24 Replies View Related

JOIN Hints: Why SQL Works As Follows?

Sep 9, 1998

Hi,

Why SQL server dose work as follows when I dose not provide any join hints?
It looks like HASH join is the best plan, but SQL dose not. What kind of
JOIN method is used by SQL optimizer?

Thanks in advance,
Wonhyuk William Chung
wonhyukc@usa.net
MCSE/ MCT



-----------
use northwind
go
select orderid, CompanyName --productname,
from orders o inner join customers c on o.customerID = c.CustomerID
/*
Table `Orders`. Scan count 91, logical reads 184, physical reads 0,
read-ahead reads 0.
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
.0553
*/

select orderid, CompanyName --productname,
from orders o inner hash join customers c on o.customerID = c.CustomerID
/*
hash
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/

select orderid, CompanyName --productname,
from orders o inner merge join customers c on o.customerID = c.CustomerID
/*
merge
Table `Customers`. Scan count 1, logical reads 4, physical reads 1,
read-ahead reads 3.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/

select orderid, CompanyName --productname,
from orders o inner loop join customers c on o.customerID = c.CustomerID
/*
loop
Table `Customers`. Scan count 830, logical reads 1681, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0.
.116
*/

View 1 Replies View Related

Multiple Optimization Hints

Sep 3, 1999

I need to use two hints (INDEX=indexname) and (NOLOCK).
I've tried
(INDEX=indexname),(NOLOCK)
(INDEX=indexname,NOLOCK)
(INDEX=indexname)(NOLOCK)

and nothing works.

Thanks

View 1 Replies View Related

Reason To Use Optimizer Hints

Aug 5, 1999

While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.

I had a query like this within a stored procedure:

SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR

Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!

If I said

SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'

(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.

I got around this by rewriting my statement:

SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR

in other words by manually specifying the index I had created on the Z column.

Hope this helps someone.

View 2 Replies View Related

Subquery Woes; Hints ?

Feb 9, 2008

hi all,
I'm trying to run queries on relatively small tables (a few hundred thousand rows) with subqueries of counts per primary key columns as such:

(ColA in tableA is the primary key)

select * from tableA p
where exists (select 1 from ( select ColA, count(1) cnt
from TableA
group by ColA
having count(1)>1 ) t
where t.ColA= p.ColA)
order by some_col

my problem is that sqlserver 2005 sp5 does not materialize the internal subquery properly, or execute it beforehand and it gets confused as heck and pegs the CPUs at 100% forever.

What hints can I use to solve this issue?
I've tried to use ..... "with ...." to prepare/materialize the table upfront, no luck, one version of statement pegged one cpu at 100%, while the other statement pegged ALL cpu's at 100% -- don't remember which.

My only solution right now was to create these subqueries as PHYSICAL tables -- and this would solve the problem but that would entail creating a lot of un-necessary objects.

thanks much for any feedback!!
Cos

View 5 Replies View Related

Lots Of Txt Files Has To Be Loaded (hints??)

Aug 18, 2006

have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.

just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.


say:
exec SP_loadTXT txt1

or should i use bulk insert?

any approaches are fine. any suggestions are fine too.

View 14 Replies View Related

Join Filter Causes Conflicting Locking Hints

Jul 14, 2006

Hi,

Using Merge replication, I have a table that is filtered using the HOST_NAME() function. The filter also makes use of a function (as the HOST_NAME() is overriden to return some complex data).

Everything replicates and filters just fine. but when I add a join filter on a different table (the join filter is a simple foreign key join) I get the following error when the snapshot agent is run:



Message: Conflicting locking hints are specified for table "fn_GetIDList". This may be caused by a conflicting hint specified for a view.
Command Text: sp_MSsetup_partition_groups
Parameters: @publication = test1



fn_GetIDList is the function used in the original filter.

Thanks for any help



Graham

View 6 Replies View Related

Table And Query Hints Not Working In SSCE 3.1 ?

Aug 23, 2007

I am trying to use hints and they don't seem to be working.
Something like:

select * from TABLE_NAME with (tablock,xlock)

fails.

Documentation clearly states that this is supported (TABLOCK, NOLOCK, XLOCK, etc.), so I must be missing something simple.

Please let me know how do I use Hints in SQL Server 2005 Compact.
Thanks.


PS.
I am using C#. It doesn't work in my code when I use

SqlCeDataReader rs = cmd.ExecuteReader();

And it doesn't work from inside the VS 2005 "query tool" or whatever it is called, when you do "Connect to Database" and so on.

View 1 Replies View Related

Using Lock Hints In Query In An OLE DB Command Component

Dec 3, 2007

Do you know of a way, in the OLE DB Command (Executing a sql command for each record), to specify a lock hint?

Example: in my component, I want to update a table but I want to specify a ROWLOCK or UPDLOCK hint.

When I try using this sql statement in the 'SqlCommand' property:

UPDATE DIM_Accounts WITH (UPDLOCK)

SET COL1 = ''

SSIS gives a syntax error.

Do you know any workarounds?

Thanks!

View 7 Replies View Related

Standard Packages - Where To Store? Hints Needed

Jul 25, 2006

Hi,

I would like to implement a kind of standard packages which can be used in all other processes and will be started using the variables.

But I do not know where to store these kind of packages in "best practise", because we

- would like to use them in Dev and in "Real" also without having to change something in the other processes

- we are storing the packages in the folders of the package store

and as far as I understood I would have to share the package store to all developers though that they would be able to do this?

Then I would better choose another folder with defined access rights I think...

Or would it be better to spend some time in developing a custom component?
But this component would work with recordsets rather than the standard data flow elemtents and therefor I would expect a leak of performance...
Or is it possible to do "trasnformation" from a packae to a custom component?

Thanks in advice!

cheers,
Markus

View 2 Replies View Related

Queue Processing Using Updlock, Readpast Locking Hints

Apr 25, 2008

This article instructed me on how to process rows from a table used as a data queue for multiple processes.

http://www.mssqltips.com/tip.asp?tip=1257

I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).

Connection1:


BEGIN TRANSACTION


SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
--COMMIT TRANSACTION

Connection2:


BEGIN TRANSACTION


SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows

COMMIT TRANSACTION



This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)

Connection1:


BEGIN TRANSACTION


SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate

--COMMIT TRANSACTION

Connection2:


BEGIN TRANSACTION


SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction

COMMIT TRANSACTION




How do I prevent blocking when using these locking hints with ORDER BY?


thanks

View 6 Replies View Related

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

AVG... Need To Ignore 0

Apr 29, 2002

I need to average a column but ignore records that have a 0. Any thoughts?

View 1 Replies View Related

Pls Ignore

Apr 30, 2007

This was not intended here.

View 2 Replies View Related

MS DTC (Very Very Urgent Plz Don't Ignore)

Jan 23, 2002

Hello Everyone,

When i checked the machine this morning i saw that MS DTC service was stopped and when did start it, it was good for a sec or two then it stopped auto matically i was not sure waht was goin on...

Then i checked the Eventviewer, it said the log file is full i tried out to find ms dtc log reset or increase the size but could not find out..where can i find this.

One more thing what is the purpose of DTC, i know in sql 6.5 we have everything in enterprize manager but i can't see the same either in 7.0 or 2000 is the name changed or is it linked to some other source.

Like linked server , does this depend on MS DTC..how and what is the basic purpose of this..kindly tell me what i should do now...

I got to fix it asap..big hassle..

Thanks in advance,

Kavitha

View 1 Replies View Related

Ignore Milliseconds

Mar 7, 2002

Hi
Does anybody know while loading data from text file into sql server, how
can we ignore milliseconds.

regards
JK

View 1 Replies View Related

Ignore Triggers

Jul 23, 2005

Is there a way I can perform an update on a table, but ignore thetrigger (or disable the trigger) each time I run a particular updatescript from a DTS package?I would like subsequent DTS steps to use the trigger except for my lastupdate statement. Is this possible?Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Ignore Some Errors

Jan 25, 2006

In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA

View 2 Replies View Related

Bcp Right Truncation: How To Ignore

Mar 16, 2006

Hi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif

View 2 Replies View Related

Ignore Error In SP

Jul 20, 2005

Is there a way to make a SP ignore an error?e.g. I'm looping through each database on a server, checking of a tableexists then selecting a value from that table. Now I have a database putonto the server where the table exists but all column names aredifferent, my SP is not interested in this database so when it errorswith invalid column name I want it to move onto the next databse and notdisplay any error message.

View 2 Replies View Related

How To Ignore Spaces?

Jan 31, 2008

I'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.




Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )





The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?

View 7 Replies View Related







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