Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Issues Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 2:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:14 AM
Points: 1,115, Visits: 456
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?
Post #956982
Posted Thursday, July 22, 2010 2:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
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....


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #957009
Posted Thursday, July 22, 2010 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:14 AM
Points: 1,115, Visits: 456
Indexes are getting fragmented due to the data constantly being inserted and removed.
Post #957043
Posted Thursday, July 22, 2010 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 14,205, Visits: 28,534
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #957454
Posted Friday, July 23, 2010 1:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:14 AM
Points: 1,115, Visits: 456
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.
Post #957803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse