Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query optimizer Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 11:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 5:03 AM
Points: 71, 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?
Post #534037
Posted Tuesday, July 15, 2008 1:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #534090
Posted Tuesday, July 15, 2008 1:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 6:15 AM
Points: 485, Visits: 11,021
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.
Post #534097
Posted Wednesday, July 16, 2008 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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
Post #535447
Posted Wednesday, July 16, 2008 11:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #535452
Posted Wednesday, July 16, 2008 12:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:01 PM
Points: 10,295, Visits: 13,280
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

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
Post #535478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse