October 31, 2002 at 9:31 am
I have just upgraded to SQL2000 from SQL7. When I started up SQL2000 there was an incredible amount of disk activity and the server was slow. The profilier showed the lazy writes/sec were maxed out. I applied SP2 and that stopped the excessive disk hits. I have since changed the backup plan from simple to full and the excessive disk activity is back only profilier shows page reads/sec to be about 50%. Is this normal? I have not tried switching back to simple backup yet. I can stop the disk hits temporarily by stoping and restarting the server. Any Ideas?
October 31, 2002 at 11:35 am
Is there a lot of activity on the server? Is this log activity or data activity? Check for writes to the files.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 31, 2002 at 12:21 pm
We have about 50 users on the server and the transactions/sec are in the 10% range of the performance monitor. Log space used is 393m and 62% full after last nights full backup and half a day of transactions. What is the best (most informative) way to track writes to disk? Thanks!
November 1, 2002 at 8:51 am
I have noticed that after a full backup the disk hits won't stop. In performance monitor the %disk time is 100% and the %Disk Read time is 90%. I can make the disk activity stop by stopping SQL Server and restarting it but I'm sure this disk activity is not supposed to be happening and it makes the server slow. Help!!
November 1, 2002 at 11:12 am
Try this.
DBCC REINDEX all the indexes
Run sp_updatestats
then DBC UPDATEUSAGE
(see BOL for more info)
I have seen some issues with upgraded databases acting just a bit odd until you do these things.
May not be the solution but I would start there first.
Also, keep in mind that if you have and bulk inserts that those transactions are logged when recovery model is FULL. These means an increase in TL transactions and writes.
Furthermore are there any hardware differences such as RAID type or no RAID before?
Edited by - antares686 on 11/01/2002 11:15:19 AM
November 4, 2002 at 4:32 am
Hi, I manage over 18 SQL Servers, and have been through many upgrades and performance issues over the last 18 months. Here are a few of the things I've noticed about SQL Server 2000.
The primary performance issue I have noticed is how the database and logs are setup. You should never setup a database and its log on the same drive, this is almost always going to take a major performance hit. In addition on high usage systems where the database and logs are using IDE drives that share the same controller (example: 1 drive is master and the other is slave on the same controller) this will also create major performance hits as the database usage increases.
I would suggest to anyone who is using SQL Server 2000 to setup multiple SCSI configurations, one to support the Log and the other to support the data itself.
In addition, the backup model you choose can also effect preformance, for instance, if your applications are using many Select into statements, or bulk copy statements, then you'll find that the Log file grows very quickly. The backup recovery model best used for these types of databases are Bulk-Logged, this will increase the speed.
Another things to note, is that the master database is also one of the work horses of your systems. This is where all the magic is done at the SQL level, I've sometimes noticed that the indexes can get corrupted under certain circumstances, and dropping and re-creating these indexes usually solves the problem. This problem tends to show itself more with stored procedures that use temporary tables.
Keep in mind that you cannot do incremental backups of the system database, instead you must do full backups.
Also, keep in mind the following about indexes on primary keys. Using clustered indexes on Auto-Incrementing numeric fields will work very fast initially, but over time, this will create "hot spots" on the hard drive where the data resides, these hot spots appear as a major increase of drive activity, and seems like constant activity.
Also, when querying an SQL Database, you can determine the queries that could kill your scalability and preformance by using the SQL Query Analyizer. Here you can see if Clustered indexes are being used for the query, or if full table scans are being done.
I hope that some of this information is helpful.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply