July 24, 2009 at 6:11 am
how to check whether the query is doing a table scan or index scan.
We used to get queries like "query running slow" but i dont know how to check that whether the query is using index /table scan. Also if doing index scan then which index.
please help with material to sort such type of performance issues....
Will Appreciate the help.
July 24, 2009 at 6:14 am
If you are running this query in SSMS then there is a button on the toolbar called "Include Actual Execution Plan". You can use this to display what the query is actually doing when it is run. It will display as a separate tab in the results pane. Hover over an object and you will see a lot more information about the operation.
July 24, 2009 at 8:22 am
Hi,
Beside index seek and index scan there are other factors that influence the query. You have to checkout the I/O,cpu and other things to point out the exact problem in the query. Check out google on how to read the execution plan and other things involved in improving the performance of a query. If you get the basics of execution plan initially you will find it interesting while debugging the performance problems
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 24, 2009 at 8:27 am
Saurabh Aggarwal (7/24/2009)
please help with material to sort such type of performance issues....Will Appreciate the help.
The best place to start for analysing and resolving performance issues are these two articles by Gail.
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/%5B/url%5D
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/%5B/url%5D
Go through them and u'll understand how to drill down to specific performance culprits.
July 24, 2009 at 8:22 pm
I am talking about a live query in production so we cant do a lot analysis, just in a short how can i see whether my query is doing a table scan or index scan
July 24, 2009 at 11:14 pm
Execution plan of a query tells you whether it's doing a index scan or index seek.
July 25, 2009 at 11:53 am
Hi Pradeep thanks for response, might i am asking a stupid ques, but i am not having too deep knowledge in sql server, Could u pls help me to explain how can we generate a queryplan of a running query
July 25, 2009 at 7:56 pm
From your management studio, click on Query->Include actual Execution Plan.
Then run your query.
There will be a seperate tab besides results table which will say Execution Plan.
It tells you abt what plan was executed to run the query. Hover your mouse over different objects shown there to see the details.
Additionally, you can fire this query before running your query
set showplan_text on . This will give you the execution plan in text format.
July 25, 2009 at 8:59 pm
Sorry to say pradeep but you didn't understand my question, I am talking about a running query in production
July 25, 2009 at 9:15 pm
Saurabh Aggarwal (7/25/2009)
Sorry to say pradeep but you didn't understand my question, I am talking about a running query in production
Unless you run the query you cannot find the execution plan. The other way to do it is to get the estimated execution plan (query->Display estimated execution plan). You cannot find the execution plan of the queries that are being run at the background or are invoked from the application....
If the query is a select query, you definately can do it. If it's an update or a delete query, you may want to avoid running those queries since it's production environment. In that case, you can view the estimated execution plan(sql server might not use the same plan during actual execution of the queries).
July 25, 2009 at 9:18 pm
Pls go through This Article on understanding execution plans.
July 26, 2009 at 3:41 am
Actually, you can get the plan of a query that has been run by the app or another user, providing the execution plan for that query is still in cache.
SELECT st.text, qp.plan
FROM sys.dm_exec_cached_plans cp
INNER JOIN sys.dm_exec_query_stats qs
on cp.plan_handle = qs.plan_handle
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY
sys.dm_exec_query_plan(qs.sql_handle) qp
WHERE st.text LIKE @QueryText
That'll return a bunch of xml for the second column. Save that xml as a .sqlplan file and open in management studio.
This will return the actual execution plan that the query used, without the run-time information that the actual exec plan that management studion returns would have.
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
July 26, 2009 at 3:59 am
might this query will provide me the queryplan but could i will understand that which query is related to which spid/application.
For eg if i am troubleshooting the slowmess of spid 55, which is still in running state and app team is saying that query taking long time from usual...
July 26, 2009 at 4:33 am
Saurabh Aggarwal (7/26/2009)
For eg if i am troubleshooting the slowmess of spid 55, which is still in running state and app team is saying that query taking long time from usual...
Then you need to find out first what query Spid 55 is running. If you don't know that, there's no way to tell anything else.
The two DMVs that you need for that are sys.dm_exec_requests and sys.dm_exec_sql_text. Use Books Online and the query samples I gave you earlier, you should be able to figure out how to get the query that Spid 55 is running.
I wouldn't start with exec plan if troubleshooting an unusually slow query. Query plan's more for queries that are always slow. If it's one query that's slower than usual, start with looking at the locking and wait types (waits are also in sys.dm_exec_requests)
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
July 27, 2009 at 9:03 am
You could also get the plan using a Profile trace. "Showplan" events.
A table scan and Index scan are pretty much the same thing in that they retrieve all rows either of which may need tuning. Small tables (usually less than 64 data pages) usually generate a scan operation because that is the quickest method for the engine to return the data. Additionally if the query is retrieving a significant amount of data (above 25 percent) a scan will also be peroformed.
You really need to look at the whole picture and starting with the query plan is great, but not all "scans" are bad.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply