Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Issues


Performance Issues

Author
Message
Imran Ashraf-452633
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 484
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?
Henrico Bekker
Henrico Bekker
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 3230
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.
Imran Ashraf-452633
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 484
Indexes are getting fragmented due to the data constantly being inserted and removed.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
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
Imran Ashraf-452633
Imran Ashraf-452633
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 484
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search