Post reply

Missing indexes and Frequently used Queries

  • July 28, 2011 at 5:05 am

    #240746

    HI All,

    I want to know how find missing indexes of a tables and avg fragmentation of the tables .

    From my application side some queries are hitting the SQl server ,its causing the high CPU utilization .

    I want to find out which queries are running . They are using SQLJDBC for connecting the sql server .

    I run the profiler for findind the quries by using RPC completed,SQL Statment and SQL batch complted events .

    But those are not clear. [Ex : (@P0 varchar(8000)) SELECT t1.column1,t1.column2 FROM table 1 t1, table2 t2 WHERE t2.colum1=t1.column1 AND (t2.column2 = @P0

    I changed the original tables ]

    Please help me on this .

    By uisng Any scripts we can find out frquently running quiries including time also .

    Thanks

    Lavanya Sri

  • July 28, 2011 at 5:54 am

    #1359968

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

  • July 29, 2011 at 4:57 am

    #1360659

    Hi Gila,

    I am expecting scripts .. can you provide any scripts to find the missing indexes and frequently used queries ???

  • July 29, 2011 at 5:44 am

    #1360694

    Lavanyasri (7/29/2011)

    Hi Gila,

    I am expecting scripts .. can you provide any scripts to find the missing indexes and frequently used queries ???

    She just did.

    You shouldn't just add index without knowing the impact it'll have on the server. The first time I followed those articles the total read on the server went down by 90%. CPU by 80%, duration by 50% and writes went up by only 1-2%.

    It takes time but it works and it's the right way to do it.

  • July 29, 2011 at 7:17 am

    #1360771

    Lavanyasri (7/29/2011)

    I am expecting scripts .. can you provide any scripts to find the missing indexes and frequently used queries ???

    I'm expecting please and thank you, but I'm frequently disappointed.

    There are tonnes of scripts, in the script library here and on the internet in general for missing indexes and half of my article is devoted to finding frequently running queries.

    A quick search will get other scripts for you way faster than waiting for me to search for the scripts and paste them (or their links) in here.

    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

  • July 29, 2011 at 7:30 am

    #1360790

    In case you don't know about those (pardon my french).

    Also this link is amazing : http://tinyurl.com/42ovvds

  • August 1, 2011 at 9:01 am

    #1361621

    Ninja's_RGR'us (7/29/2011)

    In case you don't know about those (pardon my french).

    Also this link is amazing : http://tinyurl.com/42ovvds

    J'allais vous demander si vous parllais francais.

    And in case more queries are needed:

    http://bit.ly/rdvfjD

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 1, 2011 at 9:04 am

    #1361627

    Oui je parle français... and without typos too ;-).

  • August 1, 2011 at 9:08 am

    #1361631

    Ninja's_RGR'us (8/1/2011)

    Oui je parle français... and without typos too ;-).

    yeah yeah. I was debating between parllais and parliez - should have been parliez. Imperfect gets me from time to time.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 1, 2011 at 9:12 am

    #1361634

    SQLRNNR (8/1/2011)

    Ninja's_RGR'us (8/1/2011)

    Oui je parle français... and without typos too ;-).

    yeah yeah. I was debating between parllais and parliez - should have been parliez. Imperfect gets me from time to time.

    No worry, my mom is a french teacher so I can't help myself!

Viewing 10 posts - 1 through 9 (of 9 total)

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