Low performance on SQL Server 2008 Std 64 bit

Last Post 25 Jan 2010 09:17 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Zaurb
New Member
New Member

--
05 Nov 2009 03:38 PM
Hello!

I have installed a Windows Server 2008 64bit with SQL Server 2008 Standard edition + Microsoft Business Solution Navision 5.0 SP.1

My boss ha launched a procedure directly on server which usually takes about 18 hours on our production server (Windows 2003 / SQL Server 2000). Today he tells me that the procedure is still running after two and half days.

How should I troubleshoot the SQL Server installation to find out the reason. I tried to do the following, however, I don't know how exaxtly interpret results:

1) DBCC PROCCACHE
--------------------------------------
num proc buffs 39384
num proc buffs used 288
num proc buffs active 399
proc cache size 2328
proc cache used 30
proc cache active 20
--------------------------------------

2) DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'spt_fallback_db' table...
Table: 'spt_fallback_db' (117575457); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_fallback_dev' table...
Table: 'spt_fallback_dev' (133575514); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_fallback_usg' table...
Table: 'spt_fallback_usg' (149575571); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC SHOWCONTIG scanning 'spt_monitor' table...
Table: 'spt_monitor' (1115151018); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 8024.0
- Avg. Page Density (full).....................: 0.86%
DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (1131151075); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 12
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 66.67% [2:3]
- Logical Scan Fragmentation ..................: 25.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page..............
william.alber
New Member
New Member

--
11 Nov 2009 01:10 PM
High SOS_SCHEDULER_YIELD supposedly indicates CPU pressure - is the box processing a considerable amount of other workloads? ASYNC_NETWORK_IO indicates waiting on the network for over 3 hours - does the query in question utilise remote joins?

Could you give us some idea of what the procedure does, that might help in making an educated guess as to the problem.
SQLUSA
New Member
New Member

--
25 Jan 2010 09:17 AM
Any progress?

Generally I prefer not to have long jobs like over an hour. Breaking it up to job steps is one solution, optimizing a second.

Kalman Toth, Business Intelligence Architect
SQL Server 2008 Training - http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy