April 3, 2009 at 12:56 am
Hi I searched for a similar problem but didn't get any info relating to my problem.
Pentium 3 1.26GHz Dual CPU's 4 GIG RAM
Data and Log on separate drives and installation of SQL 2005.
Log is 2GIG Data is 4.5GIG
At peak times CPU's are maxed out 100%
Average disk idle time is above 95% for all 3 drives
Processor Queue length average is 11
Table that gets queried the most has 8.2 million rows
Average user connection 55
Transaction/sec on Tempdb is 2.14
Transaction/sec on our DB is 1.6 Only user DB on the server and it is on transactional replication to another server where we run our reports from.
buffer cache hit ratio average is 99
average wait time in SQL locks is 0
available memory in MBytes is 301
average pages/sec memory is 120
bytes total/sec on network interface is 5996
PF usage is 3.5GIG
PF size initial is 4092MB and max size is 4096MB
What can I look at further?
DB indexes gets reordered everynight and updates statistics as well.
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 3, 2009 at 1:39 am
I rebooted the server and it's still the same
sqlservr.exe kills the CPU's!!!
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 3, 2009 at 6:22 am
Hey there,
It's very difficult to guess based on the information provided, but:
1. If you trace the server looking for queries using excessive CPU, does anything stand out?
2. Do you see lots of parallelism - especially CXPACKET waits in activity monitor?
Cheers,
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2009 at 6:52 am
Just as a quick check, run sp_who2 to see what's happening on the server.
If you really can't figure stuff out from the suggestions so far, I'd look into checking on the waits and queues. Follow this white paper from Microsoft. That will tell you what's causing things to slow down. It's usually a method for tuning queries, but it should work for the server as well.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 3, 2009 at 7:10 am
On top of these sugggestions, I would like to add.
If many worker threads are at Runnable state it means that there is a bottleneck on CPU. Use this query to identify them:
SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id < 255
GROUP BY t2.scheduler_id
From Kalen Delaneys, I haven't tested it yet but could be useful.
it could because of some misbehaving queries. You need to identify them and then tune them.
April 3, 2009 at 7:30 am
2cams (4/3/2009)
I rebooted the server and it's still the samesqlservr.exe kills the CPU's!!!
Hi, how u measure this counters can you please advise me, will be very helpful. you measured it when you experienced slow performance for some time. how much time u measured for.
April 3, 2009 at 7:45 am
What changed 4 days ago? This kind of thing doesn't just happen, something changed.
What you're probably going to have to do is find the worst performing queries and tune them. There's a series running at Simple Talk on this. First part here[/url], second part will be up on Monday.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2009 at 9:36 am
did you say Pentium 3's? cell phones have more power these days and nehalem CPU based servers are cheap
April 6, 2009 at 12:22 am
Hi guys, I'm going through the tips 1 by 1 but on friday we increased the Page swop file on the server from 4gig to 8gig as the 4Gig was smaller than the recomended size. Alas we still got the same issue :crazy:
CXPacket wait type 4 of them max with highest wait time of 6015
And yes Pentium 3's Dual CPU Ancient CPU's
Total RAM ==> 3898884
Available ==> 250680
System Cache 319796
I'm monitoring peak again now and trying to find long running queries causing the havock now ...
Thanx for the tips ... i'm rusted a little EISH
I took those measurements @ slow performance times!!! 5 - 10min duration ...
Only thing I changed before all this havock was remove alerts on SQL that I added to mail me when the log and data files were below 15%.
I'm also currently running a new trace file on our QAS server but of the LIVE DB. I have old trace files but i'm inserting into a Table and run queries off the table to check whats potting ... thanx for the white paper Grant and ur article Gail
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 6, 2009 at 1:47 am
JUST AN UPDATE ...
I create a NEW TRACE FILE when the server was idling @ 100% activity :hehe:
Ran the tuning advisor and it recomended 2 non clustered indexes. It was on the 2 most queried tables and applied them. The activity dropped substantially after the applied indexes. I'm going to monitor and check what I can do with the procedures that query those tables to try and tweak them a little.
JUST ANOTHER PROBLEM. The IT guy @ our datacenter removed the drive that had the log file on it of the distibution database. I can't remove it now. So my replication is on FREEZE mode till I remove the distribution DB. I created another distribution DB but the publisher is also stuff.
I'll have a sqizz on the forum as to remove the whole previous replication setup and setup a new 1.
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 6, 2009 at 1:55 am
Good luck with that, 2cams! :blink:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 3:00 am
EISH :ermm: i'm going through this @ the moment ... http://msdn.microsoft.com/en-us/library/ms188411(SQL.90).aspx
hoping it gets me to remove the stuffed replication setup
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 6, 2009 at 3:03 am
2cams (4/6/2009)
JUST AN UPDATE ...I create a NEW TRACE FILE when the server was idling @ 100% activity :hehe:
Ran the tuning advisor and it recomended 2 non clustered indexes. It was on the 2 most queried tables and applied them. The activity dropped substantially after the applied indexes. I'm going to monitor and check what I can do with the procedures that query those tables to try and tweak them a little.
There's a two-part article over at Simple Talk on finding problematic queries and fixing them. Might be worth a read.
Be careful with Tuning advisor. It's not always right and it often suggests far more than is required. Test out any of its recommendation before you put them on your production server and make sure they really do help the workload. I have heard of cases where tuning advisor's recommendations made things worse
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 3:14 am
2cams (4/6/2009)
JUST ANOTHER PROBLEM. The IT guy @ our datacenter removed the drive that had the log file on it of the distibution database. I can't remove it now. So my replication is on FREEZE mode till I remove the distribution DB. I created another distribution DB but the publisher is also stuff.I'll have a sqizz on the forum as to remove the whole previous replication setup and setup a new 1.
Got a backup of distribution? If you do, it should be as simple as restoring it to new drives. Maybe also speak to the IT guy and see if he can replace the drive. It's possible that it's still intact.
I would also suggest a chat with management, yours or his. He broke your production system by modifying the servers without checking. That's not on, and you shouldn't take the blame for it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 3:47 am
Hi Gail, I was desperate on friday and gave him the go ahead to remove the drive ASAP so it's my own fault coz of desperation and a WELL LESSON LEARNT. I have a backup of the distribution DB.
I'm going to TRY and restore it and hold my thumbs!!!
I managed to remove the replication db objects using sp_removedbreplication and dropped the distribution db using sp_dropdistributiondb.
the publisher is still giving me the error. I hope the backup works!!!
*********UPDATE**********
I restored the Distribution DB DANKIE TOG!!!!!
*********ANOTHER UPDATE**********
After restoring the Distribution DB I used the SSMS to remove the publisher and then I had to drop the distributiondb again and drop the distributor and now I'm REPLICATION-Less :w00t:
STRESS RELIEVED!!! :hehe:
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply