I have a stored procedure that does a lot more reads when the NOLOCK hint is added to the query. I'm baffled - does anyone know why, please? Details: The query is: SELECT * FROM dbo.<table-name> WITH (NOLOCK). It was doing 40,000 reads and there are less than 2,000 rows. I established that most of these reads are caused by 3 TEXT co...
I was reading over the documentation for query hints: http://msdn.microsoft.com/en-us/library/ms181714%28SQL.90%29.aspx And noticed this: FAST number_rows Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues executi...
Suddenly (but unfortunately I don't know when "suddenly" was; I know it ran fine at some point in the past) one of my queries started taking 7+ seconds instead of milliseconds to execute. I have 1 local table and 3 tables being accessed via a DB link. The 3 remote tables are joined together, and one of them is joined with my local tabl...
Suppose I have the following query: select * from A, B, C, D where A.x = B.x and B.y = C.y and A.z = D.z I have indexes on A.x and B.x and B.y and C.y and D.z There is no index on A.z. How can I give a hint to this query to use an INDEX hint on A.x but a USE_HASH hint on A.z? It seems like hints only take the table name, not the sp...
In SQL Server, there is the option to use query hints. eg SELECT c.ContactID FROM Person.Contact c WITH (INDEX(AK_Contact_rowguid)) I am in the process of getting rid of unused indexes and was wondering how I could go about determining if an index was used as a query hint. Does anyone have suggestions on how I could do this? Cheers...
I have an indexed view that I need to specify the noexpand hint for in order for it to perform reasonably. Unfortunately as seen with regard to modifying the Linq to SQL generated T-SQL query from the NOLOCK hint it appears that there is no easy way to take advantage of these hints directly or is there? My thought is that it would make ...
To perform transformations in my database, I frequently use a chained set of views. Within the views will be common table expressions. For example I would have the following: CREATE VIEW TransformationStep1 AS WITH Transformation1A AS ( SELECT Field1, Field2, Field3, Bla(Field1) AS Calc FROM Table1 ), Transformation1...