Need help with hint syntax in SQL Server 2000

Mark D Powell (Mark.Powell@eds.com) writes:
> I have only used hints a few times and it has been a while. Every
> time I try to play with an SQL statement to force use of index over a
> non-indexed path so I can compare the two approaches it take me
> forever because I cannot remember how to code the hints. I just
> cannot figure it out form the books on-line syntax diagrams.
>
> Can someone point to to an article which has numerous examples of how
> to code the hints into the SQL?
>
> It will probably eventually clink since it has in the past, but I
> would rather not spend a couple hours on whatever stupid mistake I am
> making.

FROM tbl alias WITH (INDEX = yourindex)

or

FROM tbl WITH (INDEX = yourindex)

if you don't use aliases.

The WITH keyword is not mandatory in SQL 2000, but it is SQL 2005, so start
using it.

If you want to force the clustered index, I think it's better to say:

FROM tbl WITH (INDEX = 1)

as a name easily could to wrong. But for non-clustered indexes, names is
better over index ids.

There are some older syntax that you have used in the past. Forget about
those.

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

Mark D Powell (Mark.Powell@eds.com) writes:
> Blast. Based on < table_hint > ::=
> { INDEX ( index_val [ ,...n ] )
> I was doing: with index (index_name)

There are just too many ways to specify index hints!

What you missed was the extra layer of parenthesis, found higher up
in the topic. This is what you should try:

WITH (INDEX (myindex))

I see that the syntax I suggested is not in Books Online 2000, but it
appears in Books Online 2008, although it seem to suggest that I should
say:

WITH (INDEX = (myindex))

Nevertheless, I am quite sure that my original proposal works.

> I know a FROM (table) hint on a query against a view is propogated to
> the tables in the view but will an index hint propogate or will I have
> to place the hint into the view itself?

I think you will have to stick the hint in the view itself. Which is
something I would try to avoid. But try using the query as such with
the hint and see if it helps.

But run UPDATE STATISTICS WITH FULLSCAN on the tables first.


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

On Dec 6, 11:00 am, Erland Sommarskog wrote:
> Mark D Powell (Mark.Pow...@eds.com) writes:
>
> > Blast.  Based on < table_hint > ::=
> >     { INDEX ( index_val [ ,...n ] )
> > I was doing:  with index (index_name)
>
> There are just too many ways to specify index hints!
>
> What you missed was the extra layer of parenthesis, found higher up
> in the topic. This is what you should try:
>
>    WITH (INDEX (myindex))
>
> I see that the syntax I suggested is not in Books Online 2000, but it
> appears in Books Online 2008, although it seem to suggest that I should
> say:
>
>    WITH (INDEX = (myindex))
>
> Nevertheless, I am quite sure that my original proposal works.
>
> > I know a FROM (table) hint on a query against a view is propogated to
> > the tables in the view but will an index hint propogate or will I have
> > to place the hint into the view itself?  
>
> I think you will have to stick the hint in the view itself. Which is
> something I would try to avoid. But try using the query as such with
> the hint and see if it helps.
>
> But run UPDATE STATISTICS WITH FULLSCAN on the tables first.
>
> --
> Erland Sommarskog, SQL Server MVP, esq... RemoveThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for the update. I did not get to work on this yesterday but
I intend to try to test today.

-- Mark D Powell --
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

On Dec 9, 11:22 am, Mark D Powell wrote:
> On Dec 6, 11:00 am, Erland Sommarskog wrote:
>
>
>
>
>
> > Mark D Powell (Mark.Pow...@eds.com) writes:
>
> > > Blast.  Based on < table_hint > ::=
> > >     { INDEX ( index_val [ ,...n ] )
> > > I was doing:  with index (index_name)
>
> > There are just too many ways to specify index hints!
>
> > What you missed was the extra layer of parenthesis, found higher up
> > in the topic. This is what you should try:
>
> >    WITH (INDEX (myindex))
>
> > I see that the syntax I suggested is not in Books Online 2000, but it
> > appears in Books Online 2008, although it seem to suggest that I should
> > say:
>
> >    WITH (INDEX = (myindex))
>
> > Nevertheless, I am quite sure that my original proposal works.
>
> > > I know a FROM (table) hint on a query against a view is propogated to
> > > the tables in the view but will an index hint propogate or will I have
> > > to place the hint into the view itself?  
>
> > I think you will have to stick the hint in the view itself. Which is
> > something I would try to avoid. But try using the query as such with
> > the hint and see if it helps.
>
> > But run UPDATE STATISTICS WITH FULLSCAN on the tables first.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq....DeleteThis@sommarskog.se
>
> > Links for SQL Server Books Online:
> > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> Thank you for the update.  I did not get to work on this yesterday but
> I intend to try to test today.
>
> -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Update. I figured out what was wrong and got around the issue but I
think I should have been able to do it differently.

The query was a simple query against a view:
select * from view where col = value

This value was indexed in the primary target table under the view with
5.4M rows. The view is a union all of a two table join to a multi-
table join.

When I coded the index hint the SQL Server plan showed it being used
however a test of the query took 9 minutes instead of the 1 being
taken in the full table scan. A review of the statistics seemed to
indicate that although the index was used to retrieve the data the
index value availalbe in the query was not being applied as all 5.4M
rows were being passed up the plan.

A quick query agains the large table for a count showed only 205 rows
for the value in question and a test query ran immediately to fetch
those rows via the index. I tried coding a view on this table and
using it in a new version of the existing view to get SQL Server to
filter via the index. No luck.

So I took the modified view code I was using and placed the filter
condition into the sql in both parts of the union and the result was
instanct. I gave that to the developer. He was happy enough with the
result.

It seems like SQL Server should have pushed the filter condition down
and performed it against the large table for the query against the
view like it does for the extracted view SQL when submitted as a
query.

I saw the noexpand hint but that seems the opposite of what I think
should have happened. Anyone able to explain why SQL Server was
unable to recognize that it shoul push the where clause condition down
to the table access?

-- Mark D Powell --
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

On Mon, 15 Dec 2008 12:16:40 -0800 (PST), Mark D Powell wrote:

> Anyone able to explain why SQL Server was
>unable to recognize that it shoul push the where clause condition down
>to the table access?

Hi Mark,

Based on your very generic description, the only thing I can say is that
I would first check if the UNION ALL has something to do with it.

For more specific comments, though, we need more information. For
starters, we need the design of all tables and views involved (posted as
CREATE TABLE and CREATE VIEW statements, including all properties,
constraints, and indexes). Some sample data might help as well, though
in this case I would definitely want to see an execution plan (captured
with SET SHOWPLAN_ALL ON) and information about numbers of matching rows
in the various tables.

You can of course replace table and column names to protect sensitive
data, and you can also (in fact, I even encourage you to) simplify the
problem as far as possible.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

Mark D Powell (Mark.Powell@eds.com) writes:
> This value was indexed in the primary target table under the view with
> 5.4M rows. The view is a union all of a two table join to a multi-
> table join.
>...
> It seems like SQL Server should have pushed the filter condition down
> and performed it against the large table for the query against the
> view like it does for the extracted view SQL when submitted as a
> query.
>
> I saw the noexpand hint but that seems the opposite of what I think
> should have happened. Anyone able to explain why SQL Server was
> unable to recognize that it shoul push the where clause condition down
> to the table access?

There are just too many unknowns here. My primary guess it that your
view has some condition that leads to implicit conversion, and thus
preventing an index seek.

The NOEXPAND hint applies to indexed views, which your obviously
isn't. (UNION ALL is not permitted in indexed views.)


--
Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Need help with hint syntax in SQL Server 2000 

On Dec 15, 5:16 pm, Hugo Kornelis
wrote:
> On Mon, 15 Dec 2008 12:16:40 -0800 (PST), Mark D Powell wrote:
> > Anyone able to explain why SQL Server was
> >unable to recognize that it shoul push the where clause condition down
> >to the table access?
>
> Hi Mark,
>
> Based on your very generic description, the only thing I can say is that
> I would first check if the UNION ALL has something to do with it.
>
> For more specific comments, though, we need more information. For
> starters, we need the design of all tables and views involved (posted as
> CREATE TABLE and CREATE VIEW statements, including all properties,
> constraints, and indexes). Some sample data might help as well, though
> in this case I would definitely want to see an execution plan (captured
> with SET SHOWPLAN_ALL ON) and information about numbers of matching rows
> in the various tables.
>
> You can of course replace table and column names to protect sensitive
> data, and you can also (in fact, I even encourage you to) simplify the
> problem as far as possible.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thank you both for the replies. With the holidays I do not know if I
will be able to devote some time to trying to figure out what stopped
SQL Server from pushing the filter condition down but from the replies
I take it that normally it can do so. We do not have very many
complex views in use in our sytem so this is the first time I have
encountered a proble like this. Tryng to test a version of the view
with the union all removed should not be time consuming and I do not
remember if I looked for a difference in data type definitions. I can
try to double check that.

-- Mark D Powell --
 >> Stay informed about: Need help with hint syntax in SQL Server 2000