Post reply

table tuning

  • September 5, 2010 at 11:26 pm

    #144958

    I have 30 tables in a Db whereas in 1 table i dont have any rows or (0 rows)

    but still it takes 10 to 20 seconds to give 0 records found . i don't have any clue on y this happens can you help me out on this????/

  • September 6, 2010 at 2:22 am

    #1216791

    What code do you run that takes 20sec?

    Could you post the actual execution plan?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 6, 2010 at 2:31 am

    #1216796

    is it simple "select count(*)" or sumthing else is also running along with this??

  • September 6, 2010 at 2:53 am

    #1216808

    I had performed Select * from table name and it is showing all the columns with 0 rows but thee duration is abt 20 secs and my doubt is y it is taking time for a table which has 0 rows?

  • September 6, 2010 at 4:34 am

    #1216840

    Is that table is on dev enviroment( or local system ) or production. additionally , do you face same problem with other tables also ? my guess is "network problem" could be there

    Give a test, create a table and try to do "select count(*) from... " for it and see how much time its taking.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 6, 2010 at 4:38 am

    #1216847

    Let me guess...

    It's a heap (no clustered index)?

    If you run SELECT * FROM sys.dm_db_index_physical_stats for that table, how many allocated pages are there?

    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

  • September 7, 2010 at 6:35 am

    #1217272

    Hi,

    I'm not a DBA, and visit these forums to learn, not to post. However, I'm thinking:

    Would this behavior occur if the table once held a lot of records, and they were deleted (not truncated)?

    I.e does SQL Server retain a "high water mark" of space equal to the max records that the table used to hold unless the table is truncated?

  • September 7, 2010 at 6:52 am

    #1217289

    SwayneBell (9/7/2010)

    Would this behavior occur if the table once held a lot of records, and they were deleted (not truncated)?

    I.e does SQL Server retain a "high water mark" of space equal to the max records that the table used to hold unless the table is truncated?

    No and no.

    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

  • September 7, 2010 at 7:23 am

    #1217322

    Those were my thoughts too Gail. Indexing!

  • September 7, 2010 at 10:27 pm

    #1217709

    premkuttan.lakshmanan (9/5/2010)

    I have 30 tables in a Db whereas in 1 table i dont have any rows or (0 rows)

    but still it takes 10 to 20 seconds to give 0 records found

    Now my guess is , this is an interview question

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 8, 2010 at 1:01 am

    #1217745

    Yeah seems to be

  • September 8, 2010 at 1:50 am

    #1217759

    Doesn't sound like an interview question and, if you look at the second post the OP made, he says that he ran the query and got the results.

    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

Viewing 12 posts - 1 through 11 (of 11 total)

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