Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heda_p
Starting Member
5 Posts |
 Posted - 2002-05-11 : 06:45:53
|
| Case Study : Expansion of sql database 450 gb SETUP:Processor/CPU - 8 (averaging 60-80 % usage for peak period.)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 arraysTempdb 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.811Last 4 days of processing paging usage(%): Average 22.977, Minimum 12.238, Maximum 35.136Process 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))Other Detail: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 machine4. 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 databaseWe 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 array2. 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.orcreate 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????Cheers,Prakash |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-11 : 09:21:16
|
| Have you tried running DBCC SHOWCONTIG? That should be the first thing to check. It will give you fragmentation statistics, and if you don't see a lot of fragmentation or extent switches then defragging won't get you much improvement. It's in Books Online, and there's a good article on it here:http://www.sqlteam.com/redir.asp?ItemID=8885DBCC DBREINDEX will perform some index defragmentation, and will defrag data pages on tables with clustered indexes. You can also try DBCC SHRINKDATABASE or DBCC SHRINKFILE to compact and defrag the databases/files you have. All of these are documented in Books Online if you need more specifics on what they do. You may want to perform these operations when the server is offline, as they can take a while to run.By the way, is your problem strictly disk space, or performance, or both? I couldn't quite determine exactly where you were stuck.Some performance tests you might want to consider: if UPDATE operations are happening in batches, with a lot of separate UPDATE statements, try turning off auto update statistics, and add explicit UPDATE STATISTICS command(s) to the end of your batch. Also consider using a regular table instead of a global temp table for the operations that use it; since tempdb and its log are on the same device, a lot of updates to the temp table might be causing some I/O bottlenecking. Multiple UPDATE statements could also be causing a lot of page splits, depending on the nature of the data. Using a lower fillfactor might alleviate this, but it won't save disk space.HTH |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-13 : 17:48:42
|
| You specifically mentioned an increase in the paging rate as being characteristic of the degrading performance.Sustained high Windows paging rates can adversely affect the performance of SQL Server, and definitely fragments server memory. In the extreme case, Windows can spend virtually all its time reading and writing to the pages files. Susatained rates also promote memory fragmentation, which exacerbates the problem over time.On a machine dedicated to SQL Server, there should be very little paging. Occasional bursts, yes, but generally close to zero.Your processes seems to push this model to the extreme. You have already created multiple paging files across several drives, and set the paging file to 4 times available memory. Even this may not be enough.Use the Percent Usage Peak counter of Performance Monitor to determine the optimum size of the paging file. You may find that you need even more disk space, unless you take the previous suggestion of eliminating your temp tables. You may consider another array of drives dedicated to paging only.It may also help if you can cut your 9 day process into discreet actions where you can stop in between and clean up your processes - flushing your cache and freeing up your memory into contiguous blocks. Even though memory seems to be available, the paging rate indicates that SQL is having a hard time juggling it for a week straight. |
|
|
|
|
|
|
|
|
Case Study : Expansion of sql database 450 gb