about T-SQL Scripts tuning

  • 892717952

    SSC-Addicted

    Points: 419

    There are four questions nee all help, many thanks!

    1. when I create an index in a table or views, is there any good way to konw (or evaluate) the index is necessary and correct?

    2. do we need to create one index in a view in which situation? if we create some indexs in the tables which is being used in a view, for this view, do we need to create a index for the view?

    3.when we use differenct T-SQL script to get same result, is there any tool to know which kind of T-SQL is better?

    4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?

  • Erland Sommarskog

    SSC-Insane

    Points: 23803

    892717952 (9/14/2013)1. when I create an index in a table or views, is there any good way to konw (or evaluate) the index is necessary and correct?

    That depends. If you have a live system, the missing_indexes DMVs can help you; if the candidate index is not in that DMV, the index may not help.

    But if you are working on a new system, and do not have any production-like data, it can be difficult. You will have to rely to your skills and understanding of idnexes.

    2. do we need to create one index in a view in which situation? if we create some indexs in the tables which is being used in a view, for this view, do we need to create a index for the view?

    Indexed views is fairly advanced feature, and not all views are indexable. Go with indexes for tables in the first place.

    3.when we use differenct T-SQL script to get same result, is there any tool to know which kind of T-SQL is better?

    Not that I know, but the Red Gate people here may have something up their sleeves. In any case, what is "better" is not always obvious. There are some bad things you should avoid, but there can be several different constructs that can be considered "good" - but still perform bad in some situation.

    4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?

    The system-health session tracks deadlocks. You can also get deadlock information in the SQL Server errorlog by enabling trace flag 1222. A deadlocks is rarely due to a single process, but rather the coincidence of two or more processes.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • dan-572483

    SSCoach

    Points: 15702

    1) If you know that certain columns will often be used as search criteria (such as an employee ID or sales close date), those would be great candidates to include in indexes.

    If certain columns will often be requried in query result sets but others will not be (such as if you're using a view, but for other situations like reporting as well) then a covering index including these columns is a great idea because the query can read only the index and skip the table itself, which may be much larger if there are many columns not needed for the query.

    3) In SQL Management Studio you can highlight different versions of a query and view an Estimated Execution Plan to compare their relative cost.

  • 892717952

    SSC-Addicted

    Points: 419

    Thanks for your kind help !

  • Jeff Moden

    SSC Guru

    Points: 994661

    892717952 wrote:

    1. when I create an index in a table or views, is there any good way to konw (or evaluate) the index is necessary and correct? 

    Yes.  It involves precisely the same method you would use to determine if your code is working correctly.  You need to build a shedload of test data and that's not really a difficult thing to do once you know how.

     

    892717952 wrote:

    2. do we need to create one index in a view in which situation? if we create some indexs in the tables which is being used in a view, for this view, do we need to create a index for the view?

    Possibly but, again, if you don't have data to test against, you'll need to build a shedload of it to know for sure.  There's not much of a difference between writing an index for a view and writing an index for any other query with the exception being an "materialized" view, which is also known as an "indexed view".

     

    892717952 wrote:

    3.when we use differenct T-SQL script to get same result, is there any tool to know which kind of T-SQL is better?

    Yes... there are actually quite a few.  SQL Profiler or Extended Events can help a whole lot here.  You don't actually need either of those, though.  The old tried and true method of simply capturing the date and time of when something starts and when it ends and then calculating the difference between those as a duration can be very effective.  While that doesn't tell you the internal nuances of things like the number of Logical or Physical Reads or the amount of CPU time that was brought to bear in the runs, it's a really good first indication of which is better because the other things will frequently (usually, but there are exceptions) follow the duration.  Again, you need a lot of test data to know for sure and it's just not that difficult to build such test data for the particular query you're working on once you know how.

     

    The use of the ACTUAL Execution Plans also help a whole lot in this area BUT, there's an extreme caveat... the Execution Plans can seriously aid you in determining what the actual resources used where but even the ACTUAL Execution Plan is fraught with "cost-based estimates".  The key word there is that they are estimates and you should NEVER use the Execution Plans to make the final decision as to which code is actually better for duration and resource usage.  I've demonstrated many times how what it suggests is the best (especially when it comes to "Percent of Batch") can actually be the diametric opposite what actually occurs during the runs.

     

    892717952 wrote:

    4. when the database has deadlocks, how can we know which (one) processes cause these deadlocks?

    See Erland's post above.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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