Performance Issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Performance Issues

Performance Issues

Rate Topic

Display Mode

Topic Options

Author

Message

Imran Ashraf-452633

Posted Thursday, July 22, 2010 2:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:54 AM
Points: 1,103, Visits: 411

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

Henrico Bekker

Posted Thursday, July 22, 2010 2:58 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692

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

Imran Ashraf-452633

Posted Thursday, July 22, 2010 4:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:54 AM
Points: 1,103, Visits: 411

Indexes are getting fragmented due to the data constantly being inserted and removed.

Post #957043

Grant Fritchey

Posted Thursday, July 22, 2010 12:19 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #957454

Imran Ashraf-452633

Posted Friday, July 23, 2010 1:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:54 AM
Points: 1,103, Visits: 411

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 »

Permissions