Troubleshooting SQL Server 2000 Databases
These questions are derived from the Self-Test Software Practice Test for Exam # 70-228 – Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition.
Objective: Managing, Monitoring, and Troubleshooting SQL Server 2000 Databases
SubObjective: Optimize database performance
(Single Answer, Multiple Choice)
You are the SQL administrator for your company. You have a database named Personnel that you monitor on a weekly basis. This database has a primary filegroup and two secondary filegroups.
Recently, you have noticed that the Page Splits/sec counter of the SQL Server: Access Methods object has been increasing steadily and has now reached an alarming level.
What should you do?
- Add more RAM to the SQL server.
- Create another filegroup for Personnel.
- Increase the size of the paging file on the SQL server.
- Move the Personnel database to a hard disk with more free disk space.
- Rebuild the indexes of the Personnel database with a fill factor of 60.
- Rebuild the indexes of the Personnel database with the default fill factor.
E. Rebuild the indexes of the Personnel database with a fill factor of 60.
You should rebuild the indexes of the Personnel database with a fill factor of 60. Page splits occur when an index page is full. When full pages exist, an update to the index will cause a change in the page because a new index page must be inserted. One way to reduce page splitting and fragmentation is to tune the fill factor of the index nodes. Rebuilding the indexes will defragment the index pages and should decrease the Page Splits/sec counter.
You should not add more RAM to the SQL server, create another filegroup for Personnel, or increase the size of the paging file on the SQL server. This may help improve overall performance, but by itself would not affect the Page Splits/sec counter.
You should not move the Personnel database to a hard disk with more free disk space. This may improve database performance by giving the database more room to grow. However, index pages will still be full and will continue to split.
You should not rebuild the indexes of the Personnel database with the default fill factor. With the default fill factor of zero, the pages are completely filled. With this solution, you would still have a lot of page splits.
1. Microsoft SQL Server 2000 Administrator’s Companion – Creating and Using Indexes
– Creating Indexes – Using Fill Factor to Avoid Page Splits