none
what is best answer for "How to optimized SQl Server Query"

    Question

  • please give me idea 
    when some one ask how to optimized the complex query what should i say.
    what are step to tunnung your sql serer queries.
    • Moved by SSISJoost Monday, September 30, 2013 8:51 AM tsql related
    Monday, September 30, 2013 6:27 AM

Answers

All replies

  • You can see this great article:

    Query Tuning


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Monday, September 30, 2013 6:37 AM
  • 1.You need to see an execution plan of the query. (In order to understand how SQL Server process the query)

    2.You can SET STATISTICS TIME,IO ON to see what tables are heavily accessed and more.

    3. If it is pretty complex query, is there any possibilities to re-write it?

    And much more......


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 30, 2013 6:42 AM
    Answerer
  • please give me idea 
    when some one ask how to optimized the complex query what should i say.
    what are step to tunnung your sql serer queries.

    Hello,

    This MS article can be of help to you. http://msdn.microsoft.com/en-us/library/ff650689.aspx

    It also requires some amount of practice and ability to understand execution plan to tune the queries.You should also be aware of TSQL fundamentals and way they work


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, September 30, 2013 7:33 AM
  • Hi,

    Interesting question. Do you want to be able to answer to someone about something you cannot do?

    In brief, query optimization is all about analyzing data access paths of the query (or queries) and optimizing those paths. This can be done by applying indexing strategies or by rewriting the query.

    The above is true only if you have a system that does not have resource bottlenecks. If you have system with resource bottlenecks, first you have to deal with them and then optimize the queries. Only if you think that poor queries and indexing is the reason of system resource bottlenecks you can start optimize the queries to resolve overall system performance state.


    HTH, Regards, Dean Savović

    Monday, September 30, 2013 7:34 AM
  • Hi Ajay Margaj,

               

     Collecting the nesscary information required for optimization

               1)Execution Plan(Estimated plan, Actual plan)

                          Estimated plan is  purely based on statistic, you can get it without actual execution of the query

                        Actual execution plan - is more accurate for tuning that estimated, you can eliminate the issue caused by assumtion like incorrect statistic, parameter sniffing,Memory allocation,etc   

    In SSMS Query -> Include Actual execution plan

                2) Statistics IO, even though you have the execution plan, Statistics IO will give you clear picture by table wise access, even for more complex query you can identify out where you need to optimize

    SET  STATISTICS IO ON
    -- your Query 
    SET  STATISTICS IO OFF

             i recommend to use SQL Sentry plan explorer(Free tool) to identify the area that is perfoming poorly or need to improve.

            In the execution plan itself if your query need any index to perform better that DTA thinks, you will get those index in missing index section, please consolidate with the existing index and create the best index that make use for all possible querys.

    After you have this information,

    Step1) if you have more that one query, you need to take a look at "Percentage relative to batch", higher the percentage more attention it needs(first review the highest percentage)

    Such as the below screen shoot.

    Step2)

    identify the opteration which is taking more cost, for example in above screen shot for 2 and 3 query, Table insert is taking 90%

     Eliminate Table scan and index scan for large table by adding appropiate index with key columns, Achieve index seek in all possible place.

    Step3)

    If you have any lookup , (rid lookup, Bookmark lookup) eliminate it by altering existing index by adding select clause columns in INCLUDE section of non-clustered index, if there are not appropriate index create one.

    http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup/

    http://blog.sqlauthority.com/2009/10/08/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup-part-2/

    Step 4) Compare Actual Vs Execution number of rows- if there is any difference update statistics

    Step5)

    Parameter Sniffing - if the Query plan is already exists in cache, the existing Plan was generated for some other parameter which may it gets more or very less data that you are passing parameter now.

    Case 1 : Complied parameter access more data than the current parameter - Server Memory allocated for the complied parameter is wasted comparitively low memory in need for current parameter

    Case 2 : Complied parameter access very less data than the current parameter  - more server memory is needed so memory speel may happen in tempdb that makes execution very slow.

    Solutions

    Remap input parameters to local variables
    OPTIMIZE FOR query hint
    RECOMPILE each execution

    Step 6) Look for Sort operation that can be eliminated by adding appropriate index,

    I have given some possible cases that you may see during optimization, there are more and more cases that can only learnt by pratice.

    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Monday, September 30, 2013 8:16 AM
    Monday, September 30, 2013 8:15 AM
  • >>>Eliminate Table scan and index scan for large table by adding appropiate index with key columns, Achieve >>>index seek in all possible place.

    There are manycases where Index Scan performs better than Index Seek ( returning large set of data for example)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 30, 2013 8:46 AM
    Answerer
  • Optimization bible step-by-step, easy to follow:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, September 30, 2013 10:34 AM
    Moderator
  • There is never a single answer to this question. It depends on the environment and database, tables, keys other constraints and the list goes on...

    To get started you need to break the code in to smaller chunks so that you can really know what is taking lot of time to execute. And then find why. This can be achieved by looking at execution plan and statistics.

    There will be several factors that influence. If you consider stored procedure, parameter sniffing may be an issue or some times some DML statements will require recompilation every time.

    Fragmentation will be another issue. There will certainly be a single approach. If one approach works awesome for a query, the same approach may be a disaster for other query or environment.

    Friday, October 04, 2013 7:26 PM