SQL Server Performance

Transactions/sec For tempdb is Almost Equal to Transactions/sec For _Total

Discussion in 'Getting Started' started by lcerni, Feb 4, 2008.

  1. lcerni New Member

    Using performance monitor our number for transactions/sec for tempdb is almost equal to the number for transactions/sec for _Total. Is there a good way to determine what is the bulk of this? Should I be using Profiler and if so what events, data columns, filters should I select? Or is Profiler not the way to go and I should be using something else like a T-SQL Script? I have been working with Profiler but I must be selecting the wrong things because I don't see it.
  2. Luis Martin Moderator

    Does the application use sorts, temporary tables, reports, etc.?
    That could be a good reason.
  3. lcerni New Member

    Yes, this application uses sorts, temporary tables, reports. However, I have never seen this activity on other databases. So I am concern that there is an issue with this. Maybe not but I want to make sure. While profiling I found for the ObjectName Ghost, sort_init, buildstats, a couple of temp tables that began with #, a couple of declared tables that begin with @, Flush IDES. THe front end application accessing SQL Server 2000/SP3 is powerbuilder and thus I was wondering if powerbuilder was doing something. We are rewriting the front end application into .NET and thus we are trying to be proactive and want to determine if this is an issue or not; will it present problems in the .NET environment.
  4. satya Moderator

  5. satya Moderator

    Do you have any performance issues, if so where and when?
    How about disk & memory counters?
  6. lcerni New Member

    No. If I read the counters properly, we don't have any performance issues at this time.
    Our concern is the Transactions/sec for _Total, tempdb, and DB1. We thought that we would see the transactions/sec high for DB1. However, we see that it is tempdb that is high. If I have done the math correctly, tempdb is 85% of _Total. That doesn't look good to us. Is it? I have never seen that before for a database and thus I am concern that there is a problem. It is possible that this occurrance is real and I was wondering if others have seen this.
    Here are my numbers:
    Wednesday
    20080115 3:29pm - 3:45pm
    ObjectCounterInstanceAvgMinMax
    LogicalDiskAvg Disk Queue Length_Total0.02500.896
    LogicalDiskAvg Disk Read Queue Length_Total0.02300.872
    LogicalDiskAvg Disk Write Queue Length_Total0.00200.024
    LogicalDiskCurrent Disk Queue Length_Total000
    LogicalDiskDisk Bytes/sec_Total104463.8711433.6752736204
    MemoryAvailable Mbytes---395394396
    MemoryPages/sec---0.02801.133
    Network InterfaceBytes Total/secIntel[R] PRO_1000 MT Network Connection47309.283759.506246858.627
    Paging File% Usage_Total1.1321.1321.133
    Paging File% Usage Peak_Total2.1352.1352.135
    PhysicalDisk% Disk Time_Total2.4570.01389.6
    PhysicalDiskAvg Disk Queue Length_Total0.02500.896
    PhysicalDiskAvg Disk sec/Read_Total0.00700.054
    PhysicalDiskAvg Disk sec/Write_Total0.00100.01
    PhysicalDiskDisk Bytes/sec_Total
    PhysicalDiskDisk Reads/sec_Total1.707039.335
    PhysicalDiskDisk Transfers/sec_Total4.3460.263.672
    PhysicalDiskDisk Writes/sec_Total2.6390.234.069
    Processor% Idle Time_Total94.03476.48599.767
    Processor% Interrupt Time_Total0.04700.156
    Processor% Privileged Time_Total1.53404.896
    Processor% Processor Time_Total5.9660.23323.515
    Processor% User Time_Total4.4340.23423.256
    SQLServer:Access MethodsFreeSpace Scans/sec---13.5750177.78
    SQLServer:Access MethodsFull Scans/sec---2.414029.868
    SQLServer:Buffer ManagerBuffer cache hit ratio---99.83899.30499.885
    SQLServer:Buffer ManagerFree pages---17203193109
    SQLServer:Buffer ManagerPage life expectancy---572654226041
    SQLServer:DatabasesData File(s) Size(KB)_Total243254402432544024325440
    SQLServer:DatabasesPercent Log Used_Total444
    SQLServer:DatabasesTransactions/sec_Total6.20.847.536
    SQLServer:DatabasesTransactions/sectempdb5.40.66747.469
    SQLServer:DatabasesTransactions/secDB10.60302.133
    SQLServer:General StatisticsUser Connections---119114123
    SQLServer:LatchesTotal Latch Wait Time (ms)---5.8630130.407
    SQLServer:LocksLock Timeouts/sec_Total0.16906.934
    SQLServer:LocksLock Wait Time (ms)_Total1.307082.34
    SQLServer:LocksLock Waits/sec_Total0.00100.067
    SQLServer:LocksNumber of Deadlocks/sec_Total000
    SQLServer:Memory ManagerMemory Grants Pending---000
    SQLServer:Memory ManagerTarget Server Memory(KB)---273624627360562736408
    SQLServer:Memory ManagerTotal Server Memory (KB)---273624627360562736408
    SQLServer:SQL StatisticsBatch Requests/sec---6.530.46721.335
    SQLServer:SQL StatisticsSQL Re-Compilations/sec---0.00100.067
    SystemContext Switches/sec---1315.773493.4362491.512
    SystemProcessor Queue Length---005
    SystemThreads---866862871
  7. lcerni New Member

    Just an FYI... I found this information most useful to me. In other words because of the code we have a lot of hashing going on and thus there is no issue.
    Object: - SQLServer:Access Methods
    Counter: - Workfiles Created/Sec
    Preferred Value: - < 20
    Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.
    Reference: -

    Access Methods::Workfiles Created/sec: The number of work files created per second. Work files are similar to work tables but are created strictly by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.
    http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
    I'd like to clarify here, as a little side talk, what a worktable is, and what a workfile is. Worktables are internal temporary objects that are created by, as I mentioned, sorting operations, unions, and other similar things. Little do the people know what the workfile is and it's not very well explained in documentation. A workfile is a similar temporary object, but it is something created strictly by a hashing operation. A hashing operation, for those of you who are not familiar with the subject, is one of the types of the joins that we use to join tables under SQL Server. So workfiles will serve those hashing operations, and a hash join is usually kind of a heavy operation under SQL Server. There are the measures of optimization that can be done, like indexes creation and others, which can ease and make the operations much lighter. You see, if you monitor workfiles, you may get a good impression of how much hashing goes under SQL. Usually in a less optimized system you may see high workfiles.
    http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/en/transcripts/wct021904.asp
    Workfiles Created/sec Number of work files created per second. For example, work files could be used to store temporary results for hash joins and hash aggregates.
    http://www.kiongsoftware.com/downloads/Performance Counters for Microsoft Products.pdf

Share This Page