Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    12

    Unanswered: Tempdb usage per process

    Hi,
    My problem is the following:
    tempdb is currently 20Gb data space and still some applications request more space .

    Since this server contains multiple applications, I'd like to isolate the application
    that uses most tempdb space / get a detailed view on who is using what.
    It would be ideal if there was a way to find the SQL / temp table
    that cause the problem.

    Can anyone help me with this problem.

    Thanks
    Alren

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    20Gb?!

    TempDB is used by a lot more than you expect - ORDER BY and GROUP BY clauses build temp tables in tempdb when they are executed...

    Have you considered shrinking the tempdb database and then restricting it's growth?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Posts
    12
    I know 20Gb is huge and that tempdb is used by a lot of sqlserver
    internal kitchen.

    This server is hosting a lot of external (bought) applications (CAST for example).
    We do not have the hand on the SP/SQL code.
    The tempdb was restricted to 20Gb and some applications failed.
    I need to be able to say that during peak tempdb usage "this application uses xGb tempdb space running that query".

    This will enable us to find the root cause for this weird behaviour.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You'll need to use 2 tools, Profiler and perfmon. In profiler you need to filter out anything other than dbid #2, and perfmon needs to collect activity against the drives where data and log for tempdb are stored. After collecting data from those 2 tools, you can bring in the output from both into SQL Profiler and correlate the times. I can't guarantee that you'll be able to provide the answer in the format you wish, because if the growth is contributed by GROUP BY/ORDER BY, you may not see the query text.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2006
    Posts
    115
    i use the following 2 sql to check the tempdb usage
    1.
    select physical_io & memory usage from master..sysprocesses to filter out the comparsive "large" process ..

    2. select o.loginame,
    name = o.name,
    rowtotal = convert(int,sum(rowcnt(i.doampg))),
    data = sum(data_pgs(i.id, i.doampg)) * 2,
    index_size = sum(data_pgs(i.id, i.ioampg)) * 2
    from tempdb..sysindexes i, tempdb..sysobjects o
    where o.type="U" and o.id=i.id and o.id in (select id from sysobjects where type="U")
    group by o.loginame,o.name

    to check the data size of temp table(including # table) ... then locate the table owner & check it's sybase process ...

    but for the disk used by order or group by ... i hv no direct method ...

  6. #6
    Join Date
    Jul 2006
    Posts
    115
    oh ... sorry.. wrong forum ... these sqls i used in sybase server ... hope it can be used in sql server too ...

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    As I mentioned earlier, to determine who uses tempdb, you'll have to correlate the output from both tools (profiler and perfmon). In the latter I use Access Methods/Worktables created/sec counter. The beauty of it is that the correlation can be done using SQL Profiler.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •