I am investigating one messed up system here it seems where the SQL Server 2005 64bit database machine has 14 GB of memory, 4 dual cores CPU's and SAN disk setup configured with RAID5. The database is around 52 GB in size. The CPU usage seems to be considerable low on average. Database has one data file and one log file.
I am not the most knowledgeable person when it comes to SQL Server 2005 so any advice/recommendation is highly appreciated.
I am suspecting that the slowness in the system could be caused mainly by internal and external fragmentation of tables/indexes. Executing the following query I see that lots of tables/indexes have from 60%-98% average fragmentation.
SELECT object_name(i.object_id) AS tablename,
i.name AS tableindexname,
INNER JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id
I ran the SQL Profiler for few hours while database was performing at its worst with large number of users concurrently connected. Then we analyzed the trace file with the Database Engine Tuning Advisor and there were hardly any recommendation. Taking some of the individual queries from the trace file showing bad response time and running them against the Database Engine Tuning Advisor I got recommendation of about 90% improvement in response time, but after applying those indexes and statistics the response time only improved by about 10% or less.
The disk fragmentation shows also 98% fragmented.
I would appreciate if someone would help me come up with an action plan or better way of investigating:
- Should we rebuild or reorganize indexes? If rebuild and reorganize, which first?
- Since the server has 4 CPUs should we recreate the database with 4 data file, or does that only apply when you have 4 disks (http://blogs.technet.com/mat_stephen/archive/2005/02/02/365370.aspx)?
- If database would be recreated with 4 data files and 4 log files then what is the best way to move data from old database to new one?
- Knowing that statistics have never been gathered for the database, is there a rule of thumb how statistics should be gathered and how often then?
- Is there another better way to tackle/analyze a performance problem in SQL Server 2005 assuming that the application should perform fine with all its indexes in place and the number of concurrent users?
- Does anyone have a good script that could prove that the fragmentation could be causing a lot of slowness in database, if then that is the case?
Best regards and thanks in advance!