SQL Server Performance

Concseptual questions...Plz explain

Discussion in 'Getting Started' started by rohitkochar, May 30, 2007.

  1. rohitkochar New Member

    1) I am running query select * from <sometable>. Table has a clustered index and non clustered index. Will running above query result in table scanning or index scanning??? When I saw execution plan it was scanning of clustered index??
    Why it must be table scan as i think??

    2) We have a one primary key in a table #%92A#%92 and table ‘B#%92 with composite primary key consisting of three keys . Which one will be more efficient in terms of searching ??? Won#%92t composite primary key hit the performance?
    When more data is inserted in table which leads to index rebuilding?

    3) Table variable is said to perform good on low data while Temporary table is good for
    Large dataset. If in my sproc, I have both large and small dataset, will using both
    Table variable and temporary table enhance performance??

    4) Can I create non-clustered index on temporary table??

    5) When I design the DB, I can follow two concepts create primary key only in my table
    Or can create identity and then clustered index on the column??
    Which approach is better and why??


    Rohit Kochar
  2. FrankKalis Moderator

    Did the interview go well? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  3. rohitkochar New Member

    hey that was so funny Kalis <img src='/community/emoticons/emotion-1.gif' alt=':)' /> .....<br />I did not went for any interview..I was tuning a big sproc where Temp tables <br />were heavily used ...That is why i want to clear the concept before <br />applying in real time....<br />Hope to get some good replies now..<br /><br /><br />Rohit Kochar<br />
  4. Madhivanan Moderator

    1 Make use of sql server help file<br />2 Learn SQL<br /<a target="_blank" href=http://www.sql-tutorial.net/>http://www.sql-tutorial.net/</a> <br /<a target="_blank" href=http://www.firstsql.com/tutor.htm>http://www.firstsql.com/tutor.htm</a> <br /<a target="_blank" href=http://www.w3schools.com/sql/default.asp>http://www.w3schools.com/sql/default.asp</a> <br /><br />3 and 4 - Refer 1 and 2 [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. rohitkochar New Member

    Thanks Madhivanan for your reply and links .
    But these are very conceptual qs...u wont get this on normal Sql tutorials as
    I have gome through many of them...I just wanted to know if any one of
    you came across such situation..If yes how did u solve it ??
    just want to know ur technical views..


    Rohit Kochar
  6. Madhivanan Moderator

    Well. without giving more informations you asked five questions
    Post the SP code

    Madhivanan

    Failing to plan is Planning to fail
  7. rohitkochar New Member

    It is not possible to post sproc code here...as it is very very long...
    I just want the concept and i hope the questions are pretty clear..

    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  8. FrankKalis Moderator

    quote:Originally posted by rohitkochar


    1) I am running query select * from <sometable>. Table has a clustered index and non clustered index. Will running above query result in table scanning or index scanning??? When I saw execution plan it was scanning of clustered index??
    Why it must be table scan as i think??
    The clustered index IS the table. So, a clustered index scan is essentially a table scan with another name.

    quote:
    2) We have a one primary key in a table #%92A#%92 and table ‘B#%92 with composite primary key consisting of three keys . Which one will be more efficient in terms of searching ??? Won#%92t composite primary key hit the performance?
    When more data is inserted in table which leads to index rebuilding?
    Which one will be more efficient depends on the queries run against these tables. Impossible to say without knowing your environment.

    And yes, a composite PRIMARY KEY will hit performance as it will blow up the size of every other table in which these data needs to be stored as FOREIGN KEY.

    quote:
    3) Table variable is said to perform good on low data while Temporary table is good for
    Large dataset. If in my sproc, I have both large and small dataset, will using both
    Table variable and temporary table enhance performance??
    Broad question, broad answer: It depends...

    quote:
    4) Can I create non-clustered index on temporary table??
    Have you tried?

    quote:
    5) When I design the DB, I can follow two concepts create primary key only in my table
    Or can create identity and then clustered index on the column??
    Which approach is better and why??
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21767

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  9. FrankKalis Moderator

  10. satya Moderator

    Not me, unlocked it anyway and moving to appropriate section.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  11. rohitkochar New Member

    Thanks kallis...
    I tried creating a NonClstrdIndex on Temp table and it dint throw any error....
    But how can I be sure that this NonClstrdIndex is used in the temp table as
    you mite be knowing that i cant check the performance of Temp table
    through Execution plan...any way u suggest to check the performance of
    Temp table..Also i dint get any article on inetrnet which uses Nonclstred index
    On Temp table .


    Rohit Kochar
    MCP .Net(Web and SqlServer)
    Company : TCS ,Noida
  12. FrankKalis Moderator

    What?!?<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE #t<br />(<br /> c1 INT<br />)<br />GO<br />CREATE INDEX IX_t ON #t(c1)<br />GO<br /><br />INSERT INTO #t SELECT 1<br />SELECT *<br /> FROM #t<br /> WHERE c1 &gt; 0<br /><br />DROP TABLE #t<br /></font id="code"></pre id="code"><br />Check the execution plan. I bet you will see the IX_t index is used. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />If you need to enforce the use of an index, for whatever reason there might be, change the SELECT to:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> FROM #t WITH (index (IX_t))<br /></font id="code"></pre id="code"><br />The reason why you haven't found any article on nonclustered indices and temp tables might be, that it is questionable if such an index makes sense at all on a temp table. Typically with a PRIMARY KEY and, unless otherwise declared, the associated clustered index you benefit "more".<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  13. Madhivanan Moderator

  14. satya Moderator

    On the point (4): I would suggest create index on the necessary columns only rather than using all the columns and all the data which will not make sense of using temporary tables. Use indexes on temporary tables and for large temporary tables consider using clustered and non-clustered indexes on temporary tables. Don't foreget to delete them after the usage to free up the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.

    http://www.sql-server-performance.com/best_sql_server_performance_tips.asp fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  15. FrankKalis Moderator

    While I agree that you should explicitely clean up after usage, it is actually not strictly necessary as the temp table (and therefore also the associated index) will be removed by SQL Server when the session end, afaik.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  16. satya Moderator

    On a large scale of temp. tables usage and indexes my experience says drop as soon as the query finishes and not until the while connection is terminated by the process. They are better than using composite indexes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  17. FrankKalis Moderator

    Yes, surely it is better to control when what is done. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page