March 20, 2009 at 6:37 am
Hi,
I didn't find any way how to supply hint FORCESEEK in SQL 2005.
Do anybody any idea?
Tx
March 20, 2009 at 6:41 am
looks like a 2008 hint not 2005
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 20, 2009 at 6:52 am
I know that FORCESEEK is available in 2008, but I need it in 2005 😉
March 20, 2009 at 8:15 am
It was introduced in 2008. There just is no equivalent in 2005.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2009 at 8:22 am
plavec (3/20/2009)
I know that FORCESEEK is available in 2008, but I need it in 2005 😉
Perhaps you could tweak query and indexes so that hints are not necessary. Post query, index definitions and exec plan here if you want a hand.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2009 at 8:24 am
plavec (3/20/2009)
I know that FORCESEEK is available in 2008, but I need it in 2005 😉
Are you sure you "need" it?
* Noel
March 21, 2009 at 7:40 am
noeld (3/20/2009)
plavec (3/20/2009)
I know that FORCESEEK is available in 2008, but I need it in 2005 😉Are you sure you "need" it?
yes, im sure 😎
i tried SQL used for query prepared index, but SQL stil used table scan
March 21, 2009 at 8:01 am
Perhaps you could tweak query and indexes so that hints are not necessary. Post query, index definitions and exec plan here if you want a hand.
query = view
im trying use indexes on tables use from view.
datamodel is strong that may be email will be better
March 21, 2009 at 9:26 am
plavec (3/21/2009)
query = viewim trying use indexes on tables use from view.
datamodel is strong that may be email will be better
Then please post the definition of the view, the definitions of the tables used by the view, the definitions of the indexes on those tables and the query that you're running against the view.
Oh, and the execution plan, saved as a .sqlplan file, zipped and attached.
We can probably help you either change the indexes or the query so that you get good performance without resorting to hints, but not without lots more info.
If the optimiser is picking a table scan, it's because there's no appropriate index. Most likely the indexes you have are not covering and are not selective enough to be used. In cases like this, a table scan is more efficient that an index seek with lots and lots of bookmark lookups.
Index seeks are not always the best way to run a query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 7:44 am
Tx for your help. We changed model and solved problem.
But still I'd like to know if somebody knows how to makes compensation for FORCESEEK in 2005 or 2000.
April 1, 2009 at 7:57 am
plavec (4/1/2009)
Tx for your help. We changed model and solved problem.But still I'd like to know if somebody knows how to makes compensation for FORCESEEK in 2005 or 2000.
Nice to know you fixed the problem.
There is no equivalent in 2000 or 2005. You need to go with traditional performance tuning and optimization methods. The best you can do is try to force the use of a particular index through an index hint. But honestly, hints should only be used as a last resort, especially something like the forceseek hint.
Here's a query you can run against AdventureWorks2008
SELECT*
FROM HumanResources.Employee --WITH (FORCESEEK)
WHERE SickLeaveHours = 59
AND Gender = 'F'
AND MaritalStatus = 'M'
This results in a clustered index scan. If you comment out the WITH clause you can force the SEEK operation. It performs the seek against a different index and does a key lookup operation. The number of reads goes from 9 to 170. While I got a seek operation, because there's nothing magic about a seek, I hurt performance, not helped it. The answer in a case like this is not to force hints to try to change behavior but to create a covering index or possibly change the query to information already available through another index... there are lots of possibilities that don't involve hurting performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 8:07 am
I don't like to use hurting performance at any time therefor we changed model.:-)
April 1, 2009 at 9:48 am
we have a few of these going back to the old sql 2000 days. you need to use a hint
select cola, colb, colc from sometable with(index=indexname) where colb = whatever
might not be exact but the code is close
only time i've seen this needed is when we had statistics problems and it would not pick up the index, if you have a composite index and not using all the columns in the where or in one case the select was grabbing so much data that it thought that a scan was better than a seek. in that case we changed the clustered index from the PK column to the column in the where clause and it went to clustered index seek and fixed the issue. the new clustered index wasn't on a unique column value, but it fixed that problem in that case
April 2, 2009 at 4:44 am
I misunderstand what do you mean with the statistics problem. Could you write more details about it?
April 2, 2009 at 5:48 am
I think he's referring to the statistics being out of date or insufficiently sampled. Just because you have auto-stats turned on doesn't mean that all the statistics are getting updated frequently enough. Sometimes, on some systems, you need to update the statistics manually. Further, most of the time statistics are sampled across the range of data. You can, when it's needed, perform a full scan of the data to update the statistics. By the way when an index is created or rebuilt, a full scan is performed, so be careful about updating statistics with sampled data right after a rebuild of the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply