April 8, 2013 at 7:19 pm
Hello Everyone
I have a SQL database that is the backend to a Java application. There are indexes on the column in the tables. The java app does not use any stored procedures. It passes in all the queries from the front-end. Some are simple and some are dynamically built on the front-end.
How can I see if the query that is being passed in, is actually using the index or performing a full table scan?
It seems that some of the queries are taking a long time to return any data to the front-end. I just need to verify what is actually happening.
Thank you in advance for all your assistance, comments and suggestions.
Andrew SQLDBA
April 8, 2013 at 7:32 pm
Use SQL Server Profiler to run a trace on your database. When creating the trace, tick show all events, and go under the Performance heading to choose a showplan option.
Ref: http://msdn.microsoft.com/en-nz/library/ms190233%28v=sql.100%29.aspx
April 9, 2013 at 5:03 am
That would surely cause issues in performance if I were to select "all events".
I just need to know if there is a way to show if a query is actually using the index that is on a table, or if the query is performing a full table scan each time it queries using a select statement.
Thanks
Andrew SQLDBA
April 9, 2013 at 5:12 am
Apologies if the following is no good for you but have you tried using Adam Machanic's sp_WhoIsActive? http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
If you're able to trigger the action that runs some of the slow queries then run sp_whoisactive, you should have enough info from there to investigate the slowness.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
April 9, 2013 at 7:15 am
You can query the execution plan from the plan cache. The execution plan will show how the query was resolved and will include if indexes were referenced at all and if so, scanned or seeked. To get the plan, something like this:
SELECT deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE dest.text LIKE ('something from your query that identifies it');
That will show you the plan that was used to execute the query.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply