Results 1 to 6 of 6

Thread: sql perforamce

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: sql perforamce

    As stored procedures and queries are running very slow on one of my tables I ran DBCC SHOWCONTIG on that table i got this information.


    - Pages Scanned................................: 78932
    - Extents Scanned..............................: 9978
    - Extent Switches..............................: 11080
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 89.04% [9867:11081]
    - Logical Scan Fragmentation ..................: 10.66%
    - Extent Scan Fragmentation ...................: 32.98%
    - Avg. Bytes Free per Page.....................: 494.7
    - Avg. Page Density (full).....................: 93.89%

    With the above information how can I judge what to do to boost the performance.

    Thanks.

  2. #2
    Join Date
    Aug 2004
    Posts
    3
    The showcontig result indicates some degree of index fragmentation which could be corrected by DBCC DBREINDEX, or DBCC INDEXDEFRAG. However, I doubt it will make huge difference because the fragmentation level isn't that great. There are a lot of reasons why a stored proc runs slow. First of all, take a look at the query execution plan and see if you have table/index scans.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The short answer is you don't. The numbers above look pretty good for read performance. Maybe not so good for update performance, but you can not have everything.

    First thing you must do is determine where the bottleneck is. Is the CPU being railed by long running reports which are sucking the life out of the server? Do you have a lot of lost packets on the network? Or (as is most commonly the case in performance questions) is the application just not up to the job?

    First place to start would probably be Performance Monitor, to check to see if the system is CPU bound or not. After that, you can run a profiler trace to see if you have a lot of one particular query running up a lot of reads, or a lot of CPU, or both. Note, however, that reads in Profiler are both logical and physical reads. It makes no distinction between reading from memory and reading from disk.

    Let us know what you find from these.

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    How can i launch performanc emonitor in sql server?
    I'm very sorry if its an elementary question.
    thanks.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can run performance monitor from your desk. It is more a windows tool than a SQL server tool. Try perfmon.exe in start->run.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    There are also sql server centric perfmons you can select such as:

    SQLServer:Access Methods \ Full Scans/sec
    SQLServer:Locks \ Lock Waits/sec
    SQLServer:Locks \ Number of Deadlocks/sec

    There are a bunch of others too.

Posting Permissions

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