SQL Server Performance

Table scanning

Discussion in 'General Developer Questions' started by Rajeswarisreeram, Mar 7, 2006.

  1. Rajeswarisreeram New Member

    Hi

    Could you please explain aboaut table scanning and it's usages.

    Thanks
    Raje
  2. FrankKalis Moderator

    When SQL Server fulfills a user request, it needs to retrieve the data. That can be done by either seeking or scanning. Scanning simply means that a part or the whole index or table is searched for matching rows.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterst�tze PASS Deutschland e.V. http://www.sqlpass.de)
  3. Madhivanan Moderator

    Scanning is in most cases avoided. I think your question must be about Seeking [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />If your table has no indices all the queries will SCAN table<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  4. mmarovic Active Member

    It means each row of the table is read. If there is condition against table scanned that condition is tested against every table row.
  5. satya Moderator

  6. Rajeswarisreeram New Member

    Hi,

    I have done performance turning using SQL Profiler only. so I donot know about Table scanning.But my interviewer asked me that how will you do Index turning using table scan?

    Could you explain me,
    what are the ways we can do Index/performance tuning?
    how can we do performance tuning using Table scan?
    Thanks

  7. FrankKalis Moderator

  8. Luis Martin Moderator

    I think is trick question.

    If you run profiler and then run Index Tuning Wizard, SQL try to find indexes for each table in order to improve performance.
    Now, if the table is small, then SQL don't suggest any index and then there is a table scan each time that table are used by queries you trace with profiler.

    Hop that help.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  9. Rajeswarisreeram New Member

    Hi guys,
    Thanks for your replies.
    Can you please explain me that

    what are the ways we can do Index/Performance turning?

    Thanks
    Raje
  10. FrankKalis Moderator

    Check out this site. There's plenty of information to discover! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterst�tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  11. Madhivanan Moderator

    Goto Articles Section and Search

    Madhivanan

    Failing to plan is Planning to fail
  12. satya Moderator

    It will be easy for us if have the examples of code and schema used and it is hard to explain in terms, you can get information more by searching as specified above.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Luis Martin Moderator

    I just back from vacation. I think, after 1 month, I will not write the following.<br /><br />1) Run Profiler with the following events:<br /> a) Stores Procedures --&gt; RPC:Completed and Sp<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted.<br /> b) TSQL --&gt; SQL:BatchCompleted <br /><br />2) Keep few columns like: AplicationName, TextData, Duration, LoginName, DataBaseID, SPID, CPU, Read and Writes.<br /><br />3) Filter duration &gt;= (and example) 100.<br /><br />4) Store the trace in one file.<br /><br />5) Run Profiler all day long.<br /><br />After that, open the trace file and find longest (duration) queries.<br /><br />One by one copy and paste the query into Query Analyzer.<br />Take a look execution plan to learn what is goin on with each query.<br />From Query Analyzer run Index Tuning Wizard (ITW) for one query, said the first one (longer one).<br />If ITW find indexes to apply also there is a report about how % of peformance you will win if you apply those indexes.<br />Save the suggested indexes in one file. Change the names for something reasonable. In this way you can find your indexes later.<br />Now, run the query without suggested indexes. Write the time when finished.<br />Write the query again looking execution plan.<br /><br />Open a new window in Query Analyzer, load the suggested indexes and apply them.<br />Run in that new window the query and check the time. Run again and see execution plan.<br />Compare times and executions plans before and after new indexes where stored.<br /><br />If the query run faster, go to the user (you have the login name in profiler) and ask what he/she think when run that query. (You are a winner if the answer is: yeap, may be a litter faster[}<img src='/community/emoticons/emotion-1.gif' alt=':)' />]).<br /><br />Repeat all the same for the second query.<br /><br />In real life, all the work I wrote before is in a test database. I mean a copy of the producction one. NEVER IN THE PRODUCTION DATABASE.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  14. FrankKalis Moderator

    Luis, good that you haven't written it. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  15. Luis Martin Moderator

    Oh!! ..., my English!!!![:I]

    "I just back from vacation. I think, after 1 month, I will not write the following."

    It would have to be : I'm back from vacation, so I'm happy. One month before, the following thing would never write.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  16. mmarovic Active Member

    Luis, wellcome to the club! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] More often then not, I'm struggling to express in english what I have in mind. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]
  17. FrankKalis Moderator

    Same here, however this<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I'm back from vacation, so I'm happy<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />sounds *really* strange to me. <br /><br />Care to explain, Luis? You're a workaholic? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  18. mmarovic Active Member

    I guess Luis is just well rested and full of energy...<br /><br />.<br />.<br />.<br /><br /><br /><br /><br /><br />besides being workoholic (or work-alcoholic) [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  19. Luis Martin Moderator

    Happy to see you guys[<img src='/community/emoticons/emotion-4.gif' alt=':p' />].<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  20. relentlessone New Member

    Figure out exactly which method would be best to tune a SQL statement to a specific database is avery daunting task. Instead there are tools like SQL Optimizer for Visual Studio that will try every permution possible for your sql statement and determine the best one for your application. Doing this manually could take alot of effort and you may miss possiblities. You can try SQL Optimizer for Visual Studio for free for the first 30 days. You can find it athttp://www.extensibles.com/modules.php?name=Products&op=SSP

    Debugging is a state of mind.

    http://www.extensibles.com
  21. Luis Martin Moderator

    What about if application is no written in Visual?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page