January 18, 2007 at 3:47 pm
We have a SQL Server that recently went from < 20% average CPU to > 60% average CPU utilization, which is causing performance problems and timeouts.
.We can't find any hardware problems (no disk / memory), also the server has been rebooted,
.There aren't any processes consuming excessive CPU or disk resources (Profile trace used to confirm this),
.Perfmon and Task Manager on the box indicate high outbound network traffic from the Windows server (average around 40% on a gigabit adapter), but no indication as to what process in SQL is sending the data, (the only product on there is SQL),
.Idera Diagnostic Manager shows no issues worth reporting.
Does anyone have any good ideas on how to troubleshoot this?
January 18, 2007 at 4:11 pm
Has there been any software/stored proc/trigger changes made? If so, search your trace for these new items and see how they are impacting your server. If not, continue on below.....
You stated that there are not any processes consuming excessive CPU resources. I would recommend loading your Profiler data into a table so you can group processes together to see which processes are using the most CPU. You may not be seeing anything excessive, but the additional CPU may not be comming from one Event. Find out where most of the CPU cycles are being spent and focus there.
Just my .02.
January 18, 2007 at 4:16 pm
The query optimiser may have detected that it needs to run a query in a different way and therefore has generated a new execution plan.
Have a look what tables are being accessed while the heavy CPU use it taking place then try updating statistics and rebuilding indexes on these tables.
January 19, 2007 at 8:57 am
I ran a trace grabbing everything from the server, making sure I included a period where the CPU utilization was spiking, and saved the data into a table on my local SQL server. Nothing shows up as a pattern - no specific tables, no specific queries. Before we got into doing the heavy analysis, I updated statistics and rebuilt indexes (DBREINDEX) on all tables in all databases on the server (out of hours, of course. I included the script below, in case anyone is interested)
Unfortunately, we don't have complete control over the server - it's actually a subsidiary's machine, we just get to fix it when it breaks. They said they didn't implement any code or object changes in the previous few days.
Script to run DBREINDEX for all tables on all databases :
USE Master
GO
SET NOCOUNT ON
DECLARE @sql VARCHAR(1024)
DECLARE @DBName VARCHAR(255)
DECLARE @TableName VARCHAR(255)
IF EXISTS ( SELECT *
FROM Tempdb.dbo.sysobjects
WHERE Name = '##TableNames')
BEGIN
DROP TABLE ##TableNames
END
-- Global Temp table to hold table names for each database
CREATE TABLE ##TableNames
(TableName VARCHAR(255))
-- Get all the database names, except for sys databases
-- Customize to exclude amy DB's we dont want ptocessed
DECLARE csrDB CURSOR FOR
SELECT Name
FROM SysDatabases
WHERE sid != 1
OPEN csrDB
FETCH NEXT FROM csrDB
INTO @DBName
-- Outer loop cursor processes each DB
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '===================================================================================================='
PRINT 'Processing : ' + @DBName
PRINT ' '
SELECT @sql = 'DELETE ##TableNames;
INSERT ##TableNames
SELECT name
FROM ' + @DBName + '.dbo.Sysobjects
WHERE Type = ''U''
AND name != ''dtProperties'' '
EXEC (@SQL)
DECLARE csrTable CURSOR FOR
SELECT *
FROM ##TableNames
OPEN csrTable
FETCH NEXT FROM csrTable
INTO @TableName
-- Inner loop cursor processes each table in the current DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'DBCC DBREINDEX(''' + @DBName + '.dbo.' + @TableName + ''')'
PRINT ' Table : ' + @TableName
EXEC (@SQL)
FETCH NEXT FROM csrTable
INTO @TableName
END
CLOSE csrTable
DEALLOCATE csrTable
FETCH NEXT FROM csrDB
INTO @DBName
END
CLOSE csrDB
DEALLOCATE csrDB
PRINT '===================================================================================================='
IF EXISTS ( SELECT *
FROM Tempdb.dbo.sysobjects
WHERE Name = '##TableNames')
BEGIN
DROP TABLE ##TableNames
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply