query optimizer

Author	Message
shahbaz.oradba	shahbaz.oradba Posted 9 years ago #534037
SSC-Enthusiastic Group: General Forum Members Points: 159 Visits: 102	Hi, how can we know that particular query is taking long time and we have to optimize that,and i heared that query optimization through sqlserver profiler degrades the perfomance.? what is diff between clustered index seek and bookmark look? 5
	Quote
GilaMonster	GilaMonster Posted 9 years ago #534090
SSC Guru Group: General Forum Members Points: 115844 Visits: 45520	shahbaz.oradba (7/14/2008) Hi, how can we know that particular query is taking long time and we have to optimize that,and i heared that query optimization through sqlserver profiler degrades the perfomance.? Sorry, I don't understand your question. what is diff between clustered index seek and bookmark look? http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/ The bookmark lookup was renamed to key lookup in SQL 2005. 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 We walk in the dark places no others will enter We stand on the bridge and no one may pass 8
	Quote
stephen.christie	stephen.christie Posted 9 years ago #534097
Mr or Mrs. 500 Group: General Forum Members Points: 537 Visits: 11094	GilaMonster (7/15/2008) shahbaz.oradba (7/14/2008) Hi, how can we know that particular query is taking long time and we have to optimize that,and i heared that query optimization through sqlserver profiler degrades the perfomance.? If you are going to use the SQL Profiler, it does put some overhead onto the server, but again, it will depend on what you are actually looking for,as well as how many results you want in the profiler. Filter as much as you can in profiler will also decrease its overhead. there are a number of other ways to check once you have the query that is running a long time. what is diff between clustered index seek and bookmark look? http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/ The bookmark lookup was renamed to key lookup in SQL 2005. To answer this question, Index Seek, is a good thing, means that it is using the clustered index and it is seeking only the rows it needs. If you read the previous post, it explains it very nicely. 8
	Quote
bitbucket-25253	bitbucket-25253 Posted 9 years ago #535447
SSCrazy Eights Group: General Forum Members Points: 9507 Visits: 25280	Finding stored procedures and the time required for same You may want to refer to sys.dm views and functions for example Select * sys.dm_exec_query_stats to find those taking the longest to perform. This reports among other items the Total elapsed time, in microseconds, for completed executions of the query plan. For an explanation of the data returned refer to BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eb7b58b8-3508-4114-97c2-d877bcb12964.htm Querying this and other sys.dm views and functions might yield you the data you need in the shortest period of time and with the least additional work load on the server. If everything seems to be going well, you have obviously overlooked something. Ron Please help us, help you -before posting a question please read Before posting a performance problem please read 8
	Quote
GilaMonster	GilaMonster Posted 9 years ago #535452
SSC Guru Group: General Forum Members Points: 115844 Visits: 45520	Just bear in mind that the entriesd in the query stats dmv are transient and are removed when the plan is discarded from cache. It cannot be guaranteed that everything that has run on the server is in the plan cache 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 We walk in the dark places no others will enter We stand on the bridge and no one may pass 6
	Quote
Jack Corbett	Jack Corbett Posted 9 years ago #535478
SSC-Insane Group: General Forum Members Points: 24060 Visits: 14905	shahbaz.oradba (7/14/2008) Hi, how can we know that particular query is taking long time and we have to optimize that,and i heared that query optimization through sqlserver profiler degrades the perfomance.? what is diff between clustered index seek and bookmark look? Profiler is a GUI for server side traces. You can setup a server side trace that logs long running queries which will have minimal impact on performance. See BOL for how to. Jack Corbett Applications Developer Don't let the good be the enemy of the best. -- Paul Fleming At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work Check out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best help Need an Answer? Actually, No ... You Need a Question How to Post Performance Problems Crosstabs and Pivots or How to turn rows into columns Part 1 Crosstabs and Pivots or How to turn rows into columns Part 2 8
	Quote

Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.

You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

query optimizer

query optimizer

Permissions

Select a forum

SQLServerCentral

Search