SQL Server Performance Forum – Threads Archive

Table scanning

Hi Could you please explain aboaut table scanning and it’s usages. Thanks
Raje

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)

Scanning is in most cases avoided. I think your question must be about Seeking [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] If your table has no indices all the queries will SCAN table Madhivanan Failing to plan is Planning to fail

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

Table hintshttp://www.sql-server-performance.com/rd_table_hints.asp will help to optimize the query performance by avoiding such table scans (sometimes). If you have any specific problem with TABLE SCAN, then please specify the conditions. 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.

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

Sorry, I don’t understand you. Usually you try to tune performance by the opposite way and try to avoid scans. Can you be more specific? Or was/is this a trick question? —
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)

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.

Hi guys,
Thanks for your replies.
Can you please explain me that what are the ways we can do Index/Performance turning? Thanks
Raje

Check out this site. There’s plenty of information to discover! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Goto Articles Section and Search Madhivanan Failing to plan is Planning to fail

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.

I just back from vacation. I think, after 1 month, I will not write the following. 1) Run Profiler with the following events: a) Stores Procedures –> RPC:Completed and Sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted. b) TSQL –> SQL:BatchCompleted 2) Keep few columns like: AplicationName, TextData, Duration, LoginName, DataBaseID, SPID, CPU, Read and Writes. 3) Filter duration >= (and example) 100. 4) Store the trace in one file. 5) Run Profiler all day long. After that, open the trace file and find longest (duration) queries. One by one copy and paste the query into Query Analyzer. Take a look execution plan to learn what is goin on with each query. From Query Analyzer run Index Tuning Wizard (ITW) for one query, said the first one (longer one). If ITW find indexes to apply also there is a report about how % of peformance you will win if you apply those indexes. Save the suggested indexes in one file. Change the names for something reasonable. In this way you can find your indexes later. Now, run the query without suggested indexes. Write the time when finished. Write the query again looking execution plan. Open a new window in Query Analyzer, load the suggested indexes and apply them. Run in that new window the query and check the time. Run again and see execution plan. Compare times and executions plans before and after new indexes where stored. 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=’

‘ />]). Repeat all the same for the second query. 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. 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.

Luis, good that you haven’t written it. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

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.

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=’

‘ />]

Same here, however this <blockquote id="quote">quote:<hr height="1" noshade id="quote"> I’m back from vacation, so I’m happy <hr height="1" noshade id="quote"></blockquote id="quote"> sounds *really* strange to me. Care to explain, Luis? You’re a workaholic? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I guess Luis is just well rested and full of energy… . . . besides being workoholic (or work-alcoholic) [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

Happy to see you guys[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]. 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.

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

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.

]]>