Post reply

Performance Tuning Question?

  • October 14, 2019 at 10:58 pm

    #3688734

    What are best steps to take to performance tune sql server?

  • October 15, 2019 at 8:24 am

    #3688803

    hardware ?

    SQL instance configuration ?

    T-SQL code ?

    Table design and 3NF?

    you have to give us a little more about your problem so that someone can help you - I could go on for about 6 months about all of the issues you will encounter

    MVDBA

  • October 15, 2019 at 11:13 am

    #3688819

    True

    SQL Server Carpenter - Consultant & Trainer
    Author of Learn T-SQL from Scratch

  • October 15, 2019 at 2:02 pm

    #3688886

    in my experience, most performance problems come from coding that works, but performs poorly on large data sets.

    as a result, i look at the code, here are some of the things I look at for issues:

    • Joins are Sarg-able, meaning all data types match, no functions on columns, no implicit conversions

    • WHERE is Sarg-able, meaning all data types exactly match on both sides of the equals, no functions on columns, no implicit conversions

    • @Parameter datatypes and sizes exactly match the size of columns they are used against

    • Replace @TableVariables with #Temp tables to leverage statistics, unless the @TableVariable has less than 100 rows in every situation.

    • multiple CTEs or cascading CTEs should be replaced with #Temp tables to divide and conquer for better performance

    • anything that uses a VIEW or multiple views needs to be replaced with the underlying tables instead to eliminate duplicate or extra tables

    • convert any scalar functions to inline table value functions

    • Because FOR XML concatenation is early, any concatenation statements must come froma  temp table and not the source, as the WHERE statements are applied after FOR XML is performed.

    • This reply was modified 5 years, 1 month ago by  Lowell. Reason: added FOR XML hint

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 17, 2019 at 9:28 pm

    #3689922

    Thanks appreciate it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply