April 23, 2011 at 3:57 am
Which Conditions File And FileGroup are Useful for Our database Optimizations?
April 23, 2011 at 12:32 pm
I'm not sure what you are asking here. What is a "condition" by your thinking and what relation does that have for files or filegroups?
Also, please don't write in large fonts. A normal font gets more attention than this. Most people would be annoyed. Note: I've edited this down.
April 23, 2011 at 1:05 pm
You also posted in the SQL Server 2005 Forum.
Mr Steve Jones has a very valid point but it seem that you are not clear on Filegroups and Files.
Perhaps the follow link may be helpful to you.
You might want to goggle on this subject matter a little.
Perhaps the link listed below may give you a little insight but you should probably address Steve's question.
http://msdn.microsoft.com/en-us/library/ms179316.aspx
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 23, 2011 at 4:54 pm
nirav.gandhi 50053 (4/23/2011)
Which Conditions File And FileGroup are Useful for Our database Optimizations?
It would also be nice to know what "optimizations" means in this particular scenario - assuming poster wants to optimize storage subsystem performance and considering poster is not telling a single word about hardware at hand and supported database there is little we can say other than "follow best practices" 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 24, 2011 at 3:21 am
IF you have mutiple drives not partition or like SAN multiple drives,in this scenerio multiple files and filegroup will help you to optimize the database for performance
IF you have enough space on SAN or you have multiples in a server then drive structure should be as
Data 1
Index 1
Log 1
MSSQL (SQL Server binaries at the time of installation)
Best practices
Data 1 (foir base tables)
Data 2 (for transaction tables
Index 1 (for base tables indexes)
Index 2 (for transaction tables indexes)
Log 1 (Primary Log File)
Log 2 (Backup If primary full then it will work)
MSSQL (SQL Server Binaries at the time of installation)
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 24, 2011 at 4:10 am
Syed Jahanzaib Bin hassan (4/24/2011)
Data 1Index 1
Log 1
MSSQL (SQL Server binaries at the time of installation)
Best practices
Data 1 (foir base tables)
Data 2 (for transaction tables
Index 1 (for base tables indexes)
Index 2 (for transaction tables indexes)
Log 1 (Primary Log File)
Log 2 (Backup If primary full then it will work)
MSSQL (SQL Server Binaries at the time of installation)
That's one possibility, but it's far from The Best And Only Solution. (and I've never seen 2 log files as best practices before)
Seriously, drive layout and data file layout is a complex topic with no single right answer. It depends on the type and size of the databases, the number and activity of the databases on the server, the amount and type of drives available, etc.
An instance with 200 low usage databases is going to need a different drive layout to one with one massive data warehouse, different to one that has 5 low usage databases and one heavily used OLTP database, etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2011 at 4:21 am
Syed Jahanzaib Bin hassan (4/24/2011)
Data 1 (foir base tables)Data 2 (for transaction tables
Index 1 (for base tables indexes)
Index 2 (for transaction tables indexes)
Log 1 (Primary Log File)
Log 2 (Backup If primary full then it will work)
MSSQL (SQL Server Binaries at the time of installation)
Would you be willing to share where you got these recommendations? I'm curious as to the definition of transaction table vs. base table and why the log2.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 24, 2011 at 11:03 pm
Dear All,
I explain more Details about my Project/database.
i have develop on SMS System for one Marketing Company...Daily Marketing people Register his work through a sms.Doing SMS daily around 4000 Record Insert/Update/Delete... And lots of transaction table are connected in sms Table..
For Example...His Product Details,His Attendance,his claim,his location,his Customer visits...
Now We are Develop Report Regrading Our SMS System ...
For the Report Query takes more time for Execution.... The output delay..
Currently Our Database Size 3GB..And in database Design 1 Primary files,1 log file...
Now Give Suggestion can i break database in multiple file groups...doing Creating multiple filie and filegroups is it possible to optimize our database..?
or
what is way to optimize Our Database?
April 24, 2011 at 11:03 pm
Dear All,
I explain more Details about my Project/database.
i have develop on SMS System for one Marketing Company...Daily Marketing people Register his work through a sms.Doing SMS daily around 4000 Record Insert/Update/Delete... And lots of transaction table are connected in sms Table..
For Example...His Product Details,His Attendance,his claim,his location,his Customer visits...
Now We are Develop Report Regrading Our SMS System ...
For the Report Query takes more time for Execution.... The output delay..
Currently Our Database Size 3GB..And in database Design 1 Primary files,1 log file...
Now Give Suggestion can i break database in multiple file groups...doing Creating multiple filie and filegroups is it possible to optimize our database..?
or
what is way to optimize Our Database?
April 24, 2011 at 11:03 pm
Dear All,
I explain more Details about my Project/database.
i have develop on SMS System for one Marketing Company...Daily Marketing people Register his work through a sms.Doing SMS daily around 4000 Record Insert/Update/Delete... And lots of transaction table are connected in sms Table..
For Example...His Product Details,His Attendance,his claim,his location,his Customer visits...
Now We are Develop Report Regrading Our SMS System ...
For the Report Query takes more time for Execution.... The output delay..
Currently Our Database Size 3GB..And in database Design 1 Primary files,1 log file...
Now Give Suggestion can i break database in multiple file groups...doing Creating multiple filie and filegroups is it possible to optimize our database..?
or
what is way to optimize Our Database?
April 25, 2011 at 3:03 am
nirav.gandhi 50053 (4/24/2011)
Now Give Suggestion can i break database in multiple file groups...doing Creating multiple filie and filegroups is it possible to optimize our database..?
That's only going to help if the root cause of the performance problem is IO. If it's not, that will be little to nothing.
Even if it is, you'll have to move the new files to separate physical drives to have any effect.
what is way to optimize Our Database?
Find the queries running slowly, optimise them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 3:47 am
Base Tables are those tables which are using for SELECTION only and set these type of tables in 1 to 10 times in a year
Transaction Tables are those tables which are using for SELECT,INSERTION,UPDATION and DELETION
the mentioned layout is Drives layout and log 2 is for backup if 1 drive full or limit the log size then your database will be work on the log2 file in this scenerio
Best practices always come from the practice
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 25, 2011 at 7:10 am
Optimize the database is so general. Looking at files and filegroups isn't the way to improve performance for most systems. Look at indexing and queries first. As Gail mentioned, without separate drives, you won't see a performance increase from multiple files, and even then the performance increase will depend.
Your base tables, is small, often just live in memory, so separating them out might not matter at all.
April 25, 2011 at 8:40 am
There are FAR more important things to do to improve your database application performance than worry with IO configuration. However, what you seek (general advise on tuning) goes WAY beyond a forum thread (or even multiple threads). You REALLY need to get a performance tuning professional on board to both determine the cause of and help fix your performance issues as well as mentor you on how to do the same.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply