June 10, 2009 at 12:39 pm
I am a new DBA to SQL2005.. We have a SQL Server instance that seems to be very heavy on CPU; using up 50-75% CPU consistently. The DB hosts Axway/EDI, which is not really that busy (about 30 transactions/min).
There are no maintenance plans setup on the Server so I checked to see how badly the Indexes are fragmented, below are the top 2 tables (in size):
DBCC SHOWCONTIG scanning 'TRANSACTION0' table...
Table: 'TRANSACTION0' (213575799); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 693156
- Extents Scanned..............................: 86659
- Extent Switches..............................: 86658
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [86645:86659]
- Extent Scan Fragmentation ...................: 32.96%
- Avg. Bytes Free per Page.....................: 2424.0
- Avg. Page Density (full).....................: 70.05%
DBCC SHOWCONTIG scanning 'TRANSMISSION' table...
Table: 'TRANSMISSION' (165575628); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 122786
- Extents Scanned..............................: 15355
- Extent Switches..............................: 15354
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.96% [15349:15355]
- Extent Scan Fragmentation ...................: 97.53%
- Avg. Bytes Free per Page.....................: 1327.4
- Avg. Page Density (full).....................: 83.60%
Microsoft recommends running a REBUILD on anything 30%> and up, I was planning on doing this on both of the tables, however since this was never done before I was wondering how long it would take to do this?
The first table is 5GB with a 750mb Index, and 2nd is 1GB with a 500mb Index. The machine is a dual-core Intel Xeon 2.6ghz with 8GB ram.
Any other maintenance plans I should be considering on this machine? Or a better way to find out what is going on on the server?
Also, attached is the output of sp_who2 output on the DB.
Any help would be greatly appreciated!
June 10, 2009 at 12:56 pm
first of all there are 2 delete statements that are being executed which are taking lot of resources. do a DBCC inputbuffer on spid's 69 and 72. Also check SPID's 54 and 55
if you want to perform an reindex or inndexdefrag perform the action in a test environment to get the approx time.
June 10, 2009 at 1:04 pm
Someone correct me if I am wrong, but i think your showcontig results indicate that you do not have any indexes on those table because the index id = 0. If these tables do not have indexes that would be a cause of high CPU.
below is check for fragmentation using DMVs
SELECT OBJECT_NAME(i.object_id) AS TableName,i.name AS TableIndexName,phystat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
June 10, 2009 at 2:58 pm
I did a DBCC INPUTBUFFER (69) and (72);
Language Event0DELETE FROM EDI_DATA WHERE FK_TRN_PROD_SW = 'P' AND FK_TRN_DIRECTION = 'I' AND FK_TRN_GS_TYPE = 'FA' AND FK_TRN_GS_CONTROL = '00000000060918' AND FK_TRN_GS_INTRN_ID = '2080134580000' AND FK_TRN_APP_PART_CD = '109563165'
Language Event0DELETE FROM EDI_DATA WHERE FK_TRN_PROD_SW = 'P' AND FK_TRN_DIRECTION = 'I' AND FK_TRN_GS_TYPE = 'FA' AND FK_TRN_GS_CONTROL = '00000000019477' AND FK_TRN_GS_INTRN_ID = '2080134580000' AND FK_TRN_APP_PART_CD = '109563165'
With our EDI service, data is always coming in, and the EDI program is always clearing out old data, so this is probably where the DELETE is coming from.
I checked the index_physical_stats for the EDI_DATA table:
When i browse to the table, i see one Index (Unique, Non Clustered).
June 10, 2009 at 2:59 pm
I should also mention that tin the EDI_DATA table, there are columns that store Binary Data (documents).
June 11, 2009 at 8:03 am
best practice is for all table to have at least a clustered index and the EDI_DATA does not have one, and probably the other two table do not have one either. So first, I would add a clustered index to problematic tables.
Next, what are the index keys in the non-clustered index on the EDI_DATA table, and which order are they in? Your delete is based on these columns and if they any one of them is not the first index key the index will not be used effectively if at all. FK_TRN_PROD_SW, FK_TRN_DIRECTION, FK_TRN_GS_TYPE, FK_TRN_GS_CONTROL, FK_TRN_GS_INTRN_ID, FK_TRN_APP_PART_CD.
June 12, 2009 at 6:34 am
Do you have the Performance Dashboard reports installed? There's a missing index portion of the reports that can be useful.
I would also run a trace and trap some of the queries and then exam the query plans. This too will help with the indices. High CPU can be caused by a few different things, but you'll need to take a structured approach to determine the cause.
June 12, 2009 at 9:44 am
Is this a backend for a 3rd party application, or something your company developed itself (it sounds like a 3rd party app)?
I would be very careful before applying clustered indexes to a 3rd party database. Definitely check with the vendor first. You probably won't get much of a response from the vendor, but I would hate to change the schema and then get refused support because you changed something.
IIRC, someone wrote an article on how to approach these situations here on SSC.
June 13, 2009 at 6:51 pm
Yes check with vendor before adding clustered index. There is a way to figure out if all records on a table are unique - perhaps one of the others have this to hand. This at least can show you that the columns to be used for clustered index is infact unique.
June 26, 2009 at 4:10 pm
If you query sysprocesses when your experiencing the CPU pressure what results do you get back in terms of wait types?
Or if you have performance dashboard installed this will graph the wait types you are experiencing for you, what do you see?
-------------------------------------------------------
"With great power comes great responsibility"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply