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 |
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
 Posted - 2008-12-17 : 07:44:21
|
| I have a database server, on which only one db is of importance to me. We run queries on it , and the result is loaded to another application from there. Over the last few weeks, These queries have started becoming very slow.I checked on memory,CPU utlization and it all looks good. The size of the database grows to an extent on an every day basis. There is just one file group, which is of the size of 125 gigs right now(.mdf). I am thinking it could have something to do with this since reading and writing from a big file might be slow. Can someone please advise if there is any specific size of the filegroup (.mdf,.ldf) that I should set please. Also, Is this a sure way to speed up the queries ? |
|
|
slada
Starting Member
2 Posts |
Posted - 2008-12-17 : 07:58:08
|
| Size of the data file should not affect your queries. I am currently managing a database which sits in a datafile that is 250GB on disk. I would begin by profiling the long running queries and checking the indexes on the tables they are running against. Are they highly fragmented? Are there correct indexes on the tables in the first place? What does the execution plan on the queries tell you? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-17 : 09:40:28
|
| Well,it is really important to spread data files across filegroups across different disk to reduce I/O contention issues for Large DB. check what slada has said. Also you could take advantages of Table Partitioning if SQL 2005 Ent Edition. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-12-17 : 11:06:05
|
quote:
Originally posted by sodeep
Well,it is really important to spread data files across filegroups across different disk to reduce I/O contention issues for Large DB. check what slada has said. Also you could take advantages of Table Partitioning if SQL 2005 Ent Edition.
Yes, And do you know if there is a specific or an optimizede size to set it to ? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-17 : 11:29:20
|
| Well it is proportionally filled with the files. It is better to put Huge table and indexes in separate FG. |
|
|
|
pkj1383
Starting Member
3 Posts |
Posted - 2009-01-02 : 01:41:55
|
| It is good for your query that put tables on another file group which are not getting used and put tables on another filegroup which get mostly used.I think this will help you.Prashant Joshi |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-03 : 07:20:35
|
quote:
Originally posted by pkj1383
It is good for your query that put tables on another file group which are not getting used and put tables on another filegroup which get mostly used.I think this will help you.Prashant Joshi
So you mean Table Partitioning right? |
|
|
|
|
|
|
|
|
size of filegroups, performance etc