February 5, 2011 at 2:15 pm
Is it possible in SQL 2005 to over ride the use of a specific index without disabling the index?
After some testing over time I've narrowed done the problem with a couple of the more intense queries our primary application executes against the DB on a regular basis and it comes down to bad use of Indexes and Hints. The query includes the HINT to use a specific Index and yet that index is not the most effiecinet way to execute the query.
When testing the same query with the Index Hint and then without there is a a 10:1 difference in performance. I can't make the application issue the query without including the Hint on this index and so I can't just disable the index. Is there any way to over ride querys that use hints in a way that makes the query run llike it would if it had no Hints?
As an example:
Assuming the eblow as my query example:
SELECT T.1, T.2, T.n
FROM MYTABLE T WITH (INDEX (I_MYTABLE_10))
WHERE T.1 = @SomeValule
Is there anyway to get teh query engine to ignore the hint in teh query without actually changing the query to not include this Hint?
If there is no way then is there a way in SQL 2005 to disable an index and prevent an error from being raised when a query references that Index in a hint?
Thanks
Kindest Regards,
Just say No to Facebook!February 5, 2011 at 4:25 pm
YSLGuru (2/5/2011)
Is it possible in SQL 2005 to over ride the use of a specific index without disabling the index?After some testing over time I've narrowed done the problem with a couple of the more intense queries our primary application executes against the DB on a regular basis and it comes down to bad use of Indexes and Hints. The query includes the HINT to use a specific Index and yet that index is not the most effiecinet way to execute the query.
When testing the same query with the Index Hint and then without there is a a 10:1 difference in performance. I can't make the application issue the query without including the Hint on this index and so I can't just disable the index. Is there any way to over ride querys that use hints in a way that makes the query run llike it would if it had no Hints?
As an example:
Assuming the eblow as my query example:
SELECT T.1, T.2, T.n
FROM MYTABLE T WITH (INDEX (I_MYTABLE_10))
WHERE T.1 = @SomeValule
Is there anyway to get teh query engine to ignore the hint in teh query without actually changing the query to not include this Hint?
If there is no way then is there a way in SQL 2005 to disable an index and prevent an error from being raised when a query references that Index in a hint?
Let me check my understanding of the scenario...
1- Query is coded as FROM MYTABLE T WITH (INDEX (I_MYTABLE_10))
2- You know for a fact query will perform much better if using index (lets say) I_MYTABLE_XX.
3- Changing the actual code is not an option.
How about this...
Rename index I_MYTABLE_10 as I_MYTABLE_old10
Rename index I_MYTABLE_XX as I_MYTABLE_10
This way query will still point to index I_MYTABLE_10... which is NOW the one you want to use 😀
For a more traditional approach you may want to check "Plan Guides", here: http://msdn.microsoft.com/en-us/library/cc917694.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 5, 2011 at 4:32 pm
Paul,
Well that would certainly be one way to do it. Thanks
Kindest Regards,
Just say No to Facebook!February 5, 2011 at 4:56 pm
YSLGuru (2/5/2011)
Well that would certainly be one way to do it.
...and the other way is Plan Guides; check link on my previous post 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 7, 2011 at 8:54 am
PaulB-TheOneAndOnly (2/5/2011)
YSLGuru (2/5/2011)
Well that would certainly be one way to do it....and the other way is Plan Guides; check link on my previous post 😉
Paul - I did take a look at the item you linked to and I will admit I did not read it thru entirely but thats because it appeared from a summary once over, that it required editing the T-SQL to include the USE PLan syntax. Is that not correct? I have to come up with as solution that does not require altering teh actual T-SQl itself at least not altering how it is intially submitted. The query comes from an application for whcih we have no access so we can't change its logic. However if there is a way to over ride it once it is submitted to the query engine/SQL Server then thats a different story. Do you know if the query plans you linke dto allow for over riding a table hint without editing the source query?
Thanks again
Kindest Regards,
Just say No to Facebook!February 7, 2011 at 10:03 am
YSLGuru (2/7/2011)
PaulB-TheOneAndOnly (2/5/2011)
YSLGuru (2/5/2011)
Well that would certainly be one way to do it....and the other way is Plan Guides; check link on my previous post 😉
Paul - I did take a look at the item you linked to and I will admit I did not read it thru entirely but thats because it appeared from a summary once over, that it required editing the T-SQL to include the USE PLan syntax. Is that not correct? I have to come up with as solution that does not require altering teh actual T-SQl itself at least not altering how it is intially submitted. The query comes from an application for whcih we have no access so we can't change its logic. However if there is a way to over ride it once it is submitted to the query engine/SQL Server then thats a different story. Do you know if the query plans you linke dto allow for over riding a table hint without editing the source query?
"Plan Guides" and 'use plan hint" are two very different animals.
Read "Plan Guides" - this was designed specifically for scenarios like yours where source code cannot be touched.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 7, 2011 at 1:48 pm
OK Paul I will read it through in it entirety this time.
thanks
Kindest Regards,
Just say No to Facebook!February 7, 2011 at 2:01 pm
YSLGuru (2/7/2011)
OK Paul I will read it through in it entirety this time.
Please do. Every decent rdbms has this feature nowadays, SQL Server calls it "Plan Guides", Oracle calls it "SQL Profiles" - same functionality; alter execution plan of a given query without touching the actual query.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 8, 2011 at 9:58 am
PaUL
I have a follow-up question now that i read thru the article on Query Plans. I assume you have used this and if thats wrong please let me know.
If I understand the documentation (in the link you provided) correctly it does not look like there is a way to use a Query Plan to change a Query with a Table Hint (like in my original example) to run like as if it had no Hint. In other words if I want to effectively drop just the Table Hint so that the query optimizer chooses the best plan itself there does not appear to be a way to do this. The @Hints parameter of the query plan allows for over riding the Hints the original query has but if I read this correctly that also prevents the optimizer itself from compiling a "best" plan itself.
So if I use a Query Plan with @Hint set to NULL then the Table hint in the query will be ignored but the query optimizer will also not then compile a plan itself. That sound about right?
I could come up with a Query Plan myself that is far better then what the original application based query uses with its table hint but ideally it would be great if there a way to tell SQL Server to ignore any table hints in the original query and have the optimizer come up with its own best plan.
Does that make sense?
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply