+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Detect what is using tempdb

  1. Detect what is using tempdb

    I'm looking for ways to detect what is using tempdb.

    My first thought was to scan all procedures looking for '%create%table%#'
    and examine each hit.

    My second thought was to use the Profiler, but I can just barely use it.
    Still seems like the best choice and it's been on my learn list for a while.

    Any other posibilites?


    Thanks,
    Jay



  2. Re: Detect what is using tempdb

    On 10 11 , 7 32 , "Jay" wrote:
    > I'm looking for ways to detect what is using tempdb.
    >
    > My first thought was to scan all procedures looking for '%create%table%#'
    > and examine each hit.
    >
    > My second thought was to use the Profiler, but I can just barely use it.
    > Still seems like the best choice and it's been on my learn list for a while.
    >
    > Any other posibilites?
    >
    > Thanks,
    > Jay


    Use profiler with filter for database id 2 which is the id of temp
    database.


  3. Re: Detect what is using tempdb

    What version of SQL Server are you using? Do you have snapshot isolation
    enabled in any database?

    Not all tempdb usage is simply from CREATE TABLE #temp. Aside from the
    obvious other ways that #temp tables are created (e.g. SELECT * INTO #temp),
    there are many other operations which use tempdb, including cursors, table
    variables, work tables, sorting, hash/merge operations, snapshot / row
    versioning, DBCC commands, etc. etc.

    If you are using SQL Server 2005, you can at least look at the following to
    determine how the current usage is distributed:

    SELECT
    user_object_perc = CONVERT(DECIMAL(6,3), u*100.0/(u+i+v+f)),
    internal_object_perc = CONVERT(DECIMAL(6,3), i*100.0/(u+i+v+f)),
    version_store_perc = CONVERT(DECIMAL(6,3), v*100.0/(u+i+v+f)),
    free_space_perc = CONVERT(DECIMAL(6,3), f*100.0/(u+i+v+f)),
    [total] = (u+i+v+f)
    FROM (
    SELECT
    u = SUM(user_object_reserved_page_count)*8,
    i = SUM(internal_object_reserved_page_count)*8,
    v = SUM(version_store_reserved_page_count)*8,
    f = SUM(unallocated_extent_page_count)*8
    FROM
    sys.dm_db_file_space_usage
    ) x;

    If it is weighted toward user objects, then yes, you may want to check out
    all instances of #table or @table. However if it is weighted somewhere
    else, then that type of investigation is not going to yield much fruit.

    In addition, you can look at DMVs such as sys.dm_exec_requests and
    sys.dm_db_session_space_usage to try to trace back to who is filling up
    tempdb.

    If you're not using SQL Server 2005, then I just wasted a lot of time
    relaying this information to you. In the future, please specify the version
    of SQL Server you are using.




    "Jay" wrote in message
    news:e9taXX5CIHA.5160@TK2MSFTNGP05.phx.gbl...
    > I'm looking for ways to detect what is using tempdb.
    >
    > My first thought was to scan all procedures looking for '%create%table%#'
    > and examine each hit.
    >
    > My second thought was to use the Profiler, but I can just barely use it.
    > Still seems like the best choice and it's been on my learn list for a
    > while.
    >
    > Any other posibilites?
    >
    >
    > Thanks,
    > Jay
    >



  4. Re: Detect what is using tempdb

    Well Aaron, I do appreciate the input. I was asking because of 3 of the 2000
    instances, however, I will be doing the same thing on our lone 2005 instance
    in a few weeks, so this will still be useful.

    One question though, you referenced "@table", which I assume to be 'DECLARE
    TABLE @Table". I was of the distince impression that construct used memory,
    not tempdb. Would you please clarify.


    "Aaron Bertrand [SQL Server MVP]" wrote in message
    news:OukCcr6CIHA.1208@TK2MSFTNGP05.phx.gbl...
    > What version of SQL Server are you using? Do you have snapshot isolation
    > enabled in any database?
    >
    > Not all tempdb usage is simply from CREATE TABLE #temp. Aside from the
    > obvious other ways that #temp tables are created (e.g. SELECT * INTO
    > #temp), there are many other operations which use tempdb, including
    > cursors, table variables, work tables, sorting, hash/merge operations,
    > snapshot / row versioning, DBCC commands, etc. etc.
    >
    > If you are using SQL Server 2005, you can at least look at the following
    > to determine how the current usage is distributed:
    >
    > SELECT
    > user_object_perc = CONVERT(DECIMAL(6,3), u*100.0/(u+i+v+f)),
    > internal_object_perc = CONVERT(DECIMAL(6,3), i*100.0/(u+i+v+f)),
    > version_store_perc = CONVERT(DECIMAL(6,3), v*100.0/(u+i+v+f)),
    > free_space_perc = CONVERT(DECIMAL(6,3), f*100.0/(u+i+v+f)),
    > [total] = (u+i+v+f)
    > FROM (
    > SELECT
    > u = SUM(user_object_reserved_page_count)*8,
    > i = SUM(internal_object_reserved_page_count)*8,
    > v = SUM(version_store_reserved_page_count)*8,
    > f = SUM(unallocated_extent_page_count)*8
    > FROM
    > sys.dm_db_file_space_usage
    > ) x;
    >
    > If it is weighted toward user objects, then yes, you may want to check out
    > all instances of #table or @table. However if it is weighted somewhere
    > else, then that type of investigation is not going to yield much fruit.
    >
    > In addition, you can look at DMVs such as sys.dm_exec_requests and
    > sys.dm_db_session_space_usage to try to trace back to who is filling up
    > tempdb.
    >
    > If you're not using SQL Server 2005, then I just wasted a lot of time
    > relaying this information to you. In the future, please specify the
    > version of SQL Server you are using.
    >
    >
    >
    >
    > "Jay" wrote in message
    > news:e9taXX5CIHA.5160@TK2MSFTNGP05.phx.gbl...
    >> I'm looking for ways to detect what is using tempdb.
    >>
    >> My first thought was to scan all procedures looking for '%create%table%#'
    >> and examine each hit.
    >>
    >> My second thought was to use the Profiler, but I can just barely use it.
    >> Still seems like the best choice and it's been on my learn list for a
    >> while.
    >>
    >> Any other posibilites?
    >>
    >>
    >> Thanks,
    >> Jay
    >>

    >




  5. Re: Detect what is using tempdb

    That is a common misconception. While there are scenarios where table
    variables can exist solely in memory, in most cases you will see I/O in
    tempdb.





    "Jay" wrote in message
    news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    > Well Aaron, I do appreciate the input. I was asking because of 3 of the
    > 2000 instances, however, I will be doing the same thing on our lone 2005
    > instance in a few weeks, so this will still be useful.
    >
    > One question though, you referenced "@table", which I assume to be
    > 'DECLARE TABLE @Table". I was of the distince impression that construct
    > used memory, not tempdb. Would you please clarify.



  6. Re: Detect what is using tempdb

    > That is a common misconception. While there are scenarios where table
    > variables can exist solely in memory, in most cases you will see I/O in
    > tempdb.


    Then why is the average size of our tempdb so much smaller than what I've
    become accoustom to seeing and why do the programs that use table variables
    run so much faster?

    >
    >
    > "Jay" wrote in message
    > news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    >> Well Aaron, I do appreciate the input. I was asking because of 3 of the
    >> 2000 instances, however, I will be doing the same thing on our lone 2005
    >> instance in a few weeks, so this will still be useful.
    >>
    >> One question though, you referenced "@table", which I assume to be
    >> 'DECLARE TABLE @Table". I was of the distince impression that construct
    >> used memory, not tempdb. Would you please clarify.

    >




  7. Re: Detect what is using tempdb

    Take a look at this:

    Find out what's happening behind the scenes of TempDB database:
    http://www.sqlcommunity.com/Default....id=65&tabid=56

    HTH

    Thank you,
    Saleem Hakani (World Wide Microsoft SQL Server Community)
    SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    Articles, SQL Clinic and a lot more SQL Server fun.
    Register (Free):
    http://www.sqlcommunity.com/Registra...2fDefault.aspx


    "Jay" wrote in message
    news:O$45Yd8CIHA.5044@TK2MSFTNGP03.phx.gbl...
    >> That is a common misconception. While there are scenarios where table
    >> variables can exist solely in memory, in most cases you will see I/O in
    >> tempdb.

    >
    > Then why is the average size of our tempdb so much smaller than what I've
    > become accoustom to seeing and why do the programs that use table
    > variables run so much faster?
    >
    >>
    >>
    >> "Jay" wrote in message
    >> news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    >>> Well Aaron, I do appreciate the input. I was asking because of 3 of the
    >>> 2000 instances, however, I will be doing the same thing on our lone 2005
    >>> instance in a few weeks, so this will still be useful.
    >>>
    >>> One question though, you referenced "@table", which I assume to be
    >>> 'DECLARE TABLE @Table". I was of the distince impression that construct
    >>> used memory, not tempdb. Would you please clarify.

    >>

    >
    >



  8. Re: Detect what is using tempdb

    Saleem,

    Intresting site. However, I can't seem to find the download link on any of
    the pages, so I can't look at any of the stuff.

    Jay

    "Saleem Hakani" wrote in message
    news:A8D62047-148E-461D-8729-1F2F0377B6E5@microsoft.com...
    > Take a look at this:
    >
    > Find out what's happening behind the scenes of TempDB database:
    > http://www.sqlcommunity.com/Default....id=65&tabid=56
    >
    > HTH
    >
    > Thank you,
    > Saleem Hakani (World Wide Microsoft SQL Server Community)
    > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    > Articles, SQL Clinic and a lot more SQL Server fun.
    > Register (Free):
    > http://www.sqlcommunity.com/Registra...2fDefault.aspx
    >
    >
    > "Jay" wrote in message
    > news:O$45Yd8CIHA.5044@TK2MSFTNGP03.phx.gbl...
    >>> That is a common misconception. While there are scenarios where table
    >>> variables can exist solely in memory, in most cases you will see I/O in
    >>> tempdb.

    >>
    >> Then why is the average size of our tempdb so much smaller than what I've
    >> become accoustom to seeing and why do the programs that use table
    >> variables run so much faster?
    >>
    >>>
    >>>
    >>> "Jay" wrote in message
    >>> news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    >>>> Well Aaron, I do appreciate the input. I was asking because of 3 of the
    >>>> 2000 instances, however, I will be doing the same thing on our lone
    >>>> 2005 instance in a few weeks, so this will still be useful.
    >>>>
    >>>> One question though, you referenced "@table", which I assume to be
    >>>> 'DECLARE TABLE @Table". I was of the distince impression that construct
    >>>> used memory, not tempdb. Would you please clarify.
    >>>

    >>
    >>

    >




  9. Re: Detect what is using tempdb

    Hi Jay, you will be able to see the Download button once you are logged in to
    SQL Community portal. If you don't have an account then register one at the
    following link:
    http://www.sqlcommunity.com/Registra...2fdefault.aspx

    FYI: Registration is absolutely free and quick.

    Once you are logged in to SQL Community portal you may visit
    http://www.sqlcommunity.com/Default....id=65&tabid=56 to download the
    script for "Find out what's happening in TempDB database" (you should be able
    to see the download link at the bottom right corner of that script pane.
    --
    Thank you,
    Saleem Hakani
    HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
    SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    Articles, SQL Clinic and a lot of SQL fun.
    Register (Free):
    http://www.sqlcommunity.com/Registra...2fdefault.aspx


    "Jay" wrote:

    > Saleem,
    >
    > Intresting site. However, I can't seem to find the download link on any of
    > the pages, so I can't look at any of the stuff.
    >
    > Jay
    >
    > "Saleem Hakani" wrote in message
    > news:A8D62047-148E-461D-8729-1F2F0377B6E5@microsoft.com...
    > > Take a look at this:
    > >
    > > Find out what's happening behind the scenes of TempDB database:
    > > http://www.sqlcommunity.com/Default....id=65&tabid=56
    > >
    > > HTH
    > >
    > > Thank you,
    > > Saleem Hakani (World Wide Microsoft SQL Server Community)
    > > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    > > Articles, SQL Clinic and a lot more SQL Server fun.
    > > Register (Free):
    > > http://www.sqlcommunity.com/Registra...2fDefault.aspx
    > >
    > >
    > > "Jay" wrote in message
    > > news:O$45Yd8CIHA.5044@TK2MSFTNGP03.phx.gbl...
    > >>> That is a common misconception. While there are scenarios where table
    > >>> variables can exist solely in memory, in most cases you will see I/O in
    > >>> tempdb.
    > >>
    > >> Then why is the average size of our tempdb so much smaller than what I've
    > >> become accoustom to seeing and why do the programs that use table
    > >> variables run so much faster?
    > >>
    > >>>
    > >>>
    > >>> "Jay" wrote in message
    > >>> news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    > >>>> Well Aaron, I do appreciate the input. I was asking because of 3 of the
    > >>>> 2000 instances, however, I will be doing the same thing on our lone
    > >>>> 2005 instance in a few weeks, so this will still be useful.
    > >>>>
    > >>>> One question though, you referenced "@table", which I assume to be
    > >>>> 'DECLARE TABLE @Table". I was of the distince impression that construct
    > >>>> used memory, not tempdb. Would you please clarify.
    > >>>
    > >>
    > >>

    > >

    >
    >
    >


  10. Re: Detect what is using tempdb

    Thanks, but no thanks. I'm not giving you my email address.

    "Saleem Hakani" wrote in message
    news:84E1F3A6-37CA-40BA-ADCA-754E91C8D902@microsoft.com...
    > Hi Jay, you will be able to see the Download button once you are logged in
    > to
    > SQL Community portal. If you don't have an account then register one at
    > the
    > following link:
    > http://www.sqlcommunity.com/Registra...2fdefault.aspx
    >
    > FYI: Registration is absolutely free and quick.
    >
    > Once you are logged in to SQL Community portal you may visit
    > http://www.sqlcommunity.com/Default....id=65&tabid=56 to download
    > the
    > script for "Find out what's happening in TempDB database" (you should be
    > able
    > to see the download link at the bottom right corner of that script pane.
    > --
    > Thank you,
    > Saleem Hakani
    > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
    > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    > Articles, SQL Clinic and a lot of SQL fun.
    > Register (Free):
    > http://www.sqlcommunity.com/Registra...2fdefault.aspx
    >
    >
    > "Jay" wrote:
    >
    >> Saleem,
    >>
    >> Intresting site. However, I can't seem to find the download link on any
    >> of
    >> the pages, so I can't look at any of the stuff.
    >>
    >> Jay
    >>
    >> "Saleem Hakani" wrote in message
    >> news:A8D62047-148E-461D-8729-1F2F0377B6E5@microsoft.com...
    >> > Take a look at this:
    >> >
    >> > Find out what's happening behind the scenes of TempDB database:
    >> > http://www.sqlcommunity.com/Default....id=65&tabid=56
    >> >
    >> > HTH
    >> >
    >> > Thank you,
    >> > Saleem Hakani (World Wide Microsoft SQL Server Community)
    >> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
    >> > Articles, SQL Clinic and a lot more SQL Server fun.
    >> > Register (Free):
    >> > http://www.sqlcommunity.com/Registra...2fDefault.aspx
    >> >
    >> >
    >> > "Jay" wrote in message
    >> > news:O$45Yd8CIHA.5044@TK2MSFTNGP03.phx.gbl...
    >> >>> That is a common misconception. While there are scenarios where
    >> >>> table
    >> >>> variables can exist solely in memory, in most cases you will see I/O
    >> >>> in
    >> >>> tempdb.
    >> >>
    >> >> Then why is the average size of our tempdb so much smaller than what
    >> >> I've
    >> >> become accoustom to seeing and why do the programs that use table
    >> >> variables run so much faster?
    >> >>
    >> >>>
    >> >>>
    >> >>> "Jay" wrote in message
    >> >>> news:uauihE7CIHA.5976@TK2MSFTNGP02.phx.gbl...
    >> >>>> Well Aaron, I do appreciate the input. I was asking because of 3 of
    >> >>>> the
    >> >>>> 2000 instances, however, I will be doing the same thing on our lone
    >> >>>> 2005 instance in a few weeks, so this will still be useful.
    >> >>>>
    >> >>>> One question though, you referenced "@table", which I assume to be
    >> >>>> 'DECLARE TABLE @Table". I was of the distince impression that
    >> >>>> construct
    >> >>>> used memory, not tempdb. Would you please clarify.
    >> >>>
    >> >>
    >> >>
    >> >

    >>
    >>
    >>




+ Reply to Thread
Page 1 of 2 1 2 LastLast