Post reply

Estimating Tempdb space for a query

  • October 3, 2005 at 6:40 pm

    #102835

    I have a query that blows the tempdb because it uses all the 50GB available on that volume. I need to know how much space the query will use.

    I'm looking at the showplan and can I see the estimated rows and how long each row will be. I don't think I should add all the nodes to come up with the total space requirements. Won't there be some space re-use? For example, after a Nested Loop operation, I am thinking that all the lower levels space will be released. Is this assumption correct?

    Please give me your comments. Thank you,

     

    Ben

     

     

     

     

  • October 3, 2005 at 10:33 pm

    #594716

    How about posting the query and the tables involved. Maybe we can come up with a solution that reduces your tempdb usage.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 3, 2005 at 11:51 pm

    #594726

    Hi,

        The entire space (50 GB) has been occupied by Tempdb probably because there was no restriction to the database file size. What U need to do is to restrict the database growth size by going to the files properties in the database properties. (right click on Tempdb database, click properties and go to the files sub menu.... In management studio. U can then specify the initial size of the database and the growth. Restrich the growth to few GB....(about 5 to 10 GB should be more than enough...) and then try executing your queries again.)

  • October 6, 2005 at 2:14 pm

    #595659

    You may also want to pose the sp_spaceused results for each table in the query as well. The collective may be able to optimize your query, but let's face it, big is big no matter how you slice it.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 4 posts - 1 through 3 (of 3 total)

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