July 22, 2010 at 2:08 am
Hi All,
We have a database that is being used to collect data from around 3000+ user computers. There is 2 data collections, 1 for collecting service data each hour and 2 performance collection that occurs every 15 mins. The Service data collection only retains just 1 collection of data so when the next collection is due it removes the Data for that computer first. The performance collection collects data every 15 mins and retains it for the day so at midnight it removes all the data.
The current design of the DB is using a single mdf file and I was thinking of splitting this out.
mdf file for the system stuff, 1 ndf for the service collection and the 2nd ndf for the performance data collection. The DB is approx 10GB by the end of the day and my thinking is that the collections hit different tables so if I group the tables together for each collection and place it on different files it could improve performance. We are currently using SQL Server 2005 on a VM with SAN storage and 2.5GB memory allocated.
Just wondering if this would improve the performance?
July 22, 2010 at 2:58 am
We are currently using SQL Server 2005 on a VM
Go with tin or...
Have a look at your indexes....this scenario sounds ideal for fragmentation....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 22, 2010 at 4:08 am
Indexes are getting fragmented due to the data constantly being inserted and removed.
July 22, 2010 at 12:19 pm
Just grouping by functional area isn't necessarilly going to help with I/O. Do you have multiple disks and multiple controllers? If not, just adding another file and filegroup isn't likely to help performance much.
It's collecting 10gb of data a day, which could add up quickly, but you're only keeping a day's worth, right? In order to avoid fragmentation, what about recreating the database each night?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2010 at 1:54 am
Got a job that rebuilds the indexes on a daily basis and auto stats run throughout the day.
We have been provide with Disk using SAN storage so do not really have any control over where the storage is provided. The disks can be sat on the same LUN so that was not helpful and we are unable to dictate how we want the disk to be provided.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply