|
| | | Supreme Being
Group: Forum Members
Last Login: 12/15/2005 12:54:00 PM
Posts: 104, Visits: 1 |
| Hello,
I just want to get some feedback from users of WUP2005 about the cpu and memory utilisation of the sqlserver:
I read the article in the KB of WUP:
http://support.ipswitch.com/kb/WP-20050405-DM01.htm
I reduced the amount of raw data kept on the computer. I changed the value to 2 hours and launched the script after backing up the database (as advised).
But I kept the colletion of statistics on all the devices. I am currently monitoring 230 devices with 930 monitors on them on a computer with the following configuration:
Celeron 2,5 Ghz
512MB memory
Win 2000 pro
After doing so, the sqlserver service went down to an utilisation of the cpu of 6% for a few hours and then went back to 100%.
The solution given by the support from Ipswith was to reduce the number of devices on which I am collecting statistics, which I do not want to do as it is crucial for me to get statistics from all my devices.
I was about to install WUP on a P4 (as the processor has cache burst and I thought that it would anyway be better than a Celeron) but before I changed the value of the page file from the "default" 384MB to 1024 minimum and 2048 maximum.
Since then, the sqlserver is still the service using the most memory (around 80MB) but is using 0% of the CPU.
Did some users change the value of the page file and notice a significant difference? Or did they do something different? If yes, what did they do?
Is the installation of IIS and MSDE rather than using the Ipswitch version of MSDE and IIS a good idea to improve the performance of your WUP?
Any idea or comment is welcome :o)
Thanks in advance,
Chris |
| |
| | | 
Time Traveler
Group: WhatsUp Preview Program Members
Last Login: 11/20/2006 10:03:00 AM
Posts: 333, Visits: 1 |
| | |
| | | Supreme Being
Group: Forum Members
Last Login: 12/15/2005 12:54:00 PM
Posts: 104, Visits: 1 |
| No I did not. It's still on the same drive.
And finally, it did not change anything. The sqlserver service is back at 100% of the cpu...
It's really annoying :/ Apart from reducing the number of devices on which you collect data, what can I do?
I would like to know how many devices and monitors people are collecting statistics on? And what kind of server they are using?
Thanks in advance,
Chris.
|
| |
| | | 
Time Traveler
Group: WhatsUp Gold Expert
Last Login: Today @ 2:39:24 PM
Posts: 1,515, Visits: 5,056 |
| You might want to try this. I recently experienced very slooooow response times from one of my WUP servers. the cpu DID NOT went to the roof, yet everything was painfully slow (loading a map in the console for instance could take something like 30 secs). What I did was to reindex the database, see some help here : http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm Bingo ! WUP back to flying performance ! Now I schedule this on a regular basis. Maybe it could help ? At least it won't do any harm.
Reading, writing and arithmetic - If you need to choose, please take option 1. |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 12/15/2005 12:54:00 PM
Posts: 104, Visits: 1 |
| Ok Thanks for the help Sergio,
I had a look and when you say that you reindex your database, you mean rebuild the index, don't you?.
Are you using this script:
------- Start ------
CREATE PROC ind_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 80)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO
------- End ------
Is there some parameters that you change?
When you say a regular basis, what do you mean? How often are you launching this script?
Thanks in advance,
Chris |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 12/12/2008 1:26:26 PM
Posts: 95, Visits: 31 |
| Will the defrag information help with defragmanting an MSDE database? The dbcc command does not reside on my WUP/MSDE box.
__________________________________________
Kyle EmerickWUG 12: IBM x346, Dual 3.6 GHz HT, 4GB RAM, Win2K3 - remote SQL Server DB
~1500 NT Services monitored on ~800 devices
No traps, logs, events, or data collection... just generic services monitored
Polling intervals staggered per map at 60, 65, 70 etc... |
| |
| | |
Time Traveler
Group: WhatsUp Gold Expert
Last Login: Today @ 2:39:24 PM
Posts: 1,515, Visits: 5,056 |
| Much simpler : run osql (and yes, kemerick, this works for msde because osql comes with msde), and within osql run the following script : USE WhatsUp
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO Frequency to run : i've scheduled it everyday now. The only issue with this command is that it will lock each index while rebuilding it, thus blocking wup from accessing the db. So you need to get a maintenance window for wup -if you have reasonable hardware, it can go pretty fast, something like 30 or 60 seconds. Update : look again at the web link i sent in the previous posts. You will find several ways to reduce index fragmentation. The last mentioned method is "online", it will not hold the DB -but it is less efficient. So you can probably run the "online" method every day, then run the "complete" one every week, or something like that. Or find an sql guru (I'm definitely not one).
Reading, writing and arithmetic - If you need to choose, please take option 1. |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 12/15/2005 12:54:00 PM
Posts: 104, Visits: 1 |
| Just by curiosity to assess the time taken by the rebuild of the database, how big is the backup of your database and what is the configuration of your computer?
Cheers,
Christophe |
| |
| | | Supreme Being
Group: Forum Members
Last Login: 12/12/2008 1:26:26 PM
Posts: 95, Visits: 31 |
| IBM x346 - 3.6 GHz HT Dual Processor - 2GB RAM - Win2K3 - 34MB MSDE DB
~1000 devices but most are only monitored via a ping and about half have a couple NT Services monitored.
No traps collected, no logs, no data collection, poll interval is 60 seconds for local servers and remote/overseas servers are polled at 120 seconds.
__________________________________________
Kyle EmerickWUG 12: IBM x346, Dual 3.6 GHz HT, 4GB RAM, Win2K3 - remote SQL Server DB
~1500 NT Services monitored on ~800 devices
No traps, logs, events, or data collection... just generic services monitored
Polling intervals staggered per map at 60, 65, 70 etc... |
| |
|
|