May 8, 2002 at 12:22 am
SETUP:
Processor - 8
Memory - 4 GB for sql 4 GB for other application(Data cruncher - used for olap cubes)
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.
Normally one table size is 2-5 GB(2-9 million rows)
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.
Process for data transformation:
I copy all the data in global temp table and then run update, in the end truncate orogional table and drop clustered constraint and insert updated data and create constrained.
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.
or
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 this????
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????
Thanks,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 8, 2002 at 5:24 am
Not running Win2K AS? Are you using the /3GB switch for SQL? /PAE to make the memory above 4g available to your other application?
Andy
May 8, 2002 at 7:53 am
Ok there are a few things to consider.
How often do the DC's do there thing (i saw the other post)? Once a month, daily, etc.
How often are writes to the database compared to reads. Do you update stats (sp_updatestats), usage (DBCC UPDATEUSAGE) and defrag indexes (DBCC INDEXDEFRAG) periodically? These things can cause slow downs and improper queyr plan developement.
How many CPUs in the box? Be sure you don't out class you CPUs.
How many different databases on this server excluding standard system dbs (and did you drop pubs and northwind when installed)? Hopefully if a high use DB then 1 but not neccessarily to limit that way but design and layout must take all things into account.
Do all tables in the database receive updates or do you have some static to low update/insert tables? Static tables can help you best and cause less worry of file fragmentation if in own filegroup and may not need to be RAID 10 as RAID 5 is just as great but lower cost of ownership.
Have you considered moving DC to it's own box off SQL server and put a high bandwith connection between the two (preferable with a switched hub 1 connect direct to other)? SQL hates to share memory and you hurt SQLs performance by taking resources away, it you can do over a 10/100/1000 MB connection between the two you may see better performance from DC as SQL is performing better.
How do you have Win2K Application Responce under Performance options set? Should be Optimize for Background service on SQL boxes.
Do you have any other servers running on the box (IIS, Exchange, etc.)? You should move these to other homes to free up memory and resouces for SQL.
How do you have your paging file setup on the server? One drive one large file, split two drives, size of file is 1gb, etc. This can impact overall performance of a server.
Have you disabled any nonessential services? Things like Task Scheduler and Alter may not be needed in you setup and are consuming memory resources if running.
Do you periodically defrag the HDs? If not then consider doing so but you will need o take the server offline or you may want to look at a defragmenter like diskkeeper to do this live or in off CPU pak hours.
As for your server setup, some of these questions will help me best answer you.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 11:28 pm
Additional information asked....
1. Money is the main constraint so can't use win2k AS.
2. Using /PAE(AWE) swith to use extended memory.
3. All the processing happens in 9 days in a month (31st to 8th) One time job.
4. Writes looks like the main cause of delay of processing as per performance monitor.
During the peak period for say 1-2 hours it averages to 26 for a array(6 disks means 12 is
ok)
5. 8 processors(CPU) is averaging 60-80 % for peak period.
6. Memory is not the constraint sql shows 1.5 GB free as a average
7. Sql:Buffer cache ration is sometime unbeleaveable as it goes to 1000.
8. Auto stats is on for the database. As I said 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))
9. DBCC INDEXDEFRAG is not requirted as we drop the data and insert the data and then create
clustered index.
10. 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. How
does this affect?
11. While distributing tables in file group this is taken care that read only tables are in
different filegroup than updatable filegroup.
12. As money is the main constraint DC can't be moved to another machine.
13. How do you have Win2K Application Responce under Performance options set? Should be
Optimize for Background service on SQL boxes? I dont have any idea about this question?????
I don't know if there are some options for this?????
14. Not exchange but IIS is installed but not used for anything.
15. Total Page file size is 16 GB and this is distributed on cdef drives(4 GB each).
Performance monitor shows no problem with paging.
16. Havn't diasabled any services but there is no indication that memory is the problem.
17. Defrag is something we don't do. Whatbare the possible impacts of that
Hope this clarifies questions about setup....
Thanks,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply