Memory - 4 GB for sql 4 GB for other application(Data cruncher - used for olap cubes) Total 8 GB RAM AWE enabled.
(Perf. Monitor shows 1 GB free as a average usage for peak period)
(Sql:cache hit ratio average 93, maximum 100.)
(Sql:Buffer cache hit ratio average 1652, maximum 1398778.)
Disk I/O(One compaq sanbox containing):
3 raid arrays of each 6 disks(each 36 GB).
1 raid array 0+1 of total six drives of that 3 is actually used.
2 raid 0 arrays
Tempdb data file, tempdb log file and database log file resides on raid 0+1 array.
File group 1 and primary filegroup resides on first raid 5 array.
filegroup 2 resides on second raid 5 array.
filegroup 3 resides on third raid 5 array.
Each file group is containing three datafiles on the same array.
Data cruncher application uses 1st raid o array for sorting the data. It uses primary filegroup to insert data into tables each table size is approximately 3-9 GB.
Last raid 0 array used for extraction of data files from sysbase.
During the peak period for say 1-2 hours Avg disk queue length it averages to 19 for a array(6 disks means 12 is ok)
Win2k with sp2 and sql2k with sp2(clustered)
Total database full is 400 GB database size is 450 GB now. Every month almost 30GB of additional data is added.
Total Page file size is 16 GB and this is distributed on cdef drives(4 GB each). Performance monitor shows no problem with first 4 days than going upword trend is shown:
First 4 days of processing paging usage(%): Average 1.115, Minimum 0.605, Maximum 3.811
Last 4 days of processing paging usage(%): Average 22.977, Minimum 12.238, Maximum 35.136
Process for data transformation:
All the processing happens in 9 days in a month (31st to 8th) One time job.
Normally one table size is 2-5 GB(2-9 million rows)
I copy all the data in global temp table and then run update, in the end truncate original table and drop clustered constraint and insert updated data and create constrained. I create only nonclusterd indexes on temp tables when joining table with some other table. If update one or two field of the whole table then generally I dont even create index. As per tests clustered index even slow down the updates. While creating nonclustered index on read only temp tables use fill factor 100 with pad index and statistics norecompute. As using partitioned views(not distributed) we use dynamic sql to fire query directly on monthly table (tablename_ + Date(format 112))
1. Northwind and pubs is there on the database. And one more database which is having always last month data(size ariund 30 GB) this database resides on a raid 0 filegroup.
2. While tables distributed in file groups this is taken care that read only tables are in different filegroup than updatable filegroup.
3. Money is the main constraint DC can't be moved to another machine
4. IIS is installed but not used for anything.
6. Havn't diasabled any services but there is no indication that memory is the problem.
7. Defrag is something we don't do. What are the possible impacts of this?
8. DBCC INDEXDEFRAG is not requirted as we drop the data and insert the data and then create clustered index.
9. Auto stats is on for the database
We are going to get 18 new disks and my plan to do these changes:
1. create two 0+1 arrays of 2 disks each, move database log file to one and tempdb log file to the second array.
2. Add two disks to tempdb 0+1 array
2. To get the best performance compaq tells us to use raid array of 6 disks only.
So plan is to create two raid 5 arrays of 6 disks and create one file group using both arrays(I am not sure it's possible please suggest) and create one datafiles on each disk array.
create one filegroup on each disk array and create two data files for each filegroup, datafile1 will reside on disk 1 and datafile 2 reside on disk 2 for both filegroup.
Any suggestions on current setup or expantion plan???? Is these ratio are telling something extra?????
As now we are almost out of disk space is it fragmantation who is creating our processes slow? Database restore is not a sure thing here as it fails sometimes so I can't backup and restore to remove fragmantation. Indexes are all fine. Any other way remove fragmantation????