June 19, 2012 at 2:13 pm
Hi,
I was asked to find out what queries send by an application are using hints. I have a SQL Server 2005 Standar Edition. Is there any way that I can acomplish this?
Thanks.
June 19, 2012 at 3:07 pm
All the hints included in a query are stored with the execution plan, so you could query the plans, using XML, to pull the information out of the DMVs. There are a couple of examples on my blog.
"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
June 19, 2012 at 3:10 pm
That is a bit vague in requirement but there are some things you can do. Are these queries pass through queries or stored procs? That will make a big difference in how you proceed.
Also what exactly do you mean by hints? There are table hints and query hints. Are you trying to find both? What do you want to see? The query text itself or the procedure name?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 3:11 pm
Grant Fritchey (6/19/2012)
All the hints included in a query are stored with the execution plan, so you could query the plans, using XML, to pull the information out of the DMVs. There are a couple of examples on my blog.
Wouldn't you still need to scour the application and/or procedures source because there could be queries that are no longer cached?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2012 at 3:16 pm
True. If the queries age out of cache you're in trouble. But, you'll get the most active queries immediately and you can capture more by rerunning the query against the cache on a regular basis.
If I had to do this, that'd be my approach.
"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
June 19, 2012 at 3:49 pm
Thanks guys for the quick response.
These are queries not stored procedures.
Second I would like to capture both table hints and query hints.
June 19, 2012 at 3:50 pm
Sean Lange (6/19/2012)
That is a bit vague in requirement but there are some things you can do. Are these queries pass through queries or stored procs? That will make a big difference in how you proceed.Also what exactly do you mean by hints? There are table hints and query hints. Are you trying to find both? What do you want to see? The query text itself or the procedure name?
Thank you, please can you provide the links to the post you suggest?
June 19, 2012 at 7:13 pm
mvillegascuellar (6/19/2012)
Thanks guys for the quick response.These are queries not stored procedures.
Second I would like to capture both table hints and query hints.
Even with queries, they're stored in cache. You'll still be able to use the cache to retrieve this information.
"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
June 19, 2012 at 8:06 pm
mvillegascuellar (6/19/2012)
Sean Lange (6/19/2012)
That is a bit vague in requirement but there are some things you can do. Are these queries pass through queries or stored procs? That will make a big difference in how you proceed.Also what exactly do you mean by hints? There are table hints and query hints. Are you trying to find both? What do you want to see? The query text itself or the procedure name?
Thank you, please can you provide the links to the post you suggest?
There are two links in the post already (table hints) and (query hints) see above. If you were referring to Grant's blog, you can find it at the link in his signature for the Scary DBA.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2012 at 10:54 am
Hi I just wanted to show you that I finally got the answer I was looking for. Mr. Aaron Bertrand posted on this:
http://dba.stackexchange.com/questions/19527/capture-all-queries-that-use-hints
In summary this is the query I was looking for:
SELECT DB_NAME(t.[dbid]), t.[text]--, qp.query_plan
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE t.[text] NOT LIKE '%dm_exec%' -- to keep this query out of result
AND (
qp.query_plan LIKE '%ForceSeek="1"%'
OR qp.query_plan LIKE '%Forcescan="1"%'
OR qp.query_plan LIKE '%NoExpandHint="1"%'
OR qp.query_plan LIKE '%ForcedIndex="1"%'
OR t.[text] LIKE '%NOLOCK%'
)
-- t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;
Thank you guys.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply