How to make data(.mdf) to equal size slices!

Question

How to make data(.mdf) to equal size slices!

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I stand by my original recommendation. Fix the root cause. Don't fix the symptoms.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

thanks any way

one last question

If I add more .ndf files to the database will reduce pagelatch_up waits and resource is 5:1:3 no.

Because I found there are three solution

1. enable trace flag 1118,-1 -- >I did this and good for a week

2. Move to the fast drive ---> all are same raid 5 in my environment

3. Add equal data files -----> will at solve atleast for certain level or nothing.

Suggest this then if nothing is the solution then I will force them to change the queries.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

pavan_srirangam (11/22/2010)
thanks any way
one last question
If I add more .ndf files to the database will reduce pagelatch_up waits and resource is 5:1:3 no.
Because I found there are three solution
1. enable trace flag 1118,-1 -- >I did this and good for a week
2. Move to the fast drive ---> all are same raid 5 in my environment
3. Add equal data files -----> will at solve atleast for certain level or nothing.
Suggest this then if nothing is the solution then I will force them to change the queries.

Trace Flag 1118 (for anyone reading along) removes the ability for SQL Server to allocate mixed extents in SQL 2k and beyond. This means SGAM won't be used at all. It also means some database bloat, depending on what's going on.

Moving to a fast drive won't help, it's moving to multiple fast spindles. In this case, also not helping.

Adding in equal datafiles (and they all have to be exactly the same size, including the first one) is a band-aid. TempDB is a special case scenario. Under 99% of circumstances, no userDB should need to do this unless you've got some hardware issues and you just can't get enough space in the same LUN.

All three of the above options are supposed to be temporary fixes in a UserDB until you can fix the underlying causes. Your SGAM frying should not happen in a UserDB. This is a failure to use the optimizations purposely built into SQL Server for tempdb and the like.

I'm sorry I missed the note earlier about the Trace Flag 1118 fixing your issue. This is a design failure. Anything you do here is like trying to hold back a flood with duct tape and balsa wood.

- Craig Farrell

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

LutzM SSC Guru Points: 107049 More actions · Answer 4

pavan_srirangam (11/22/2010)
thanks any way
one last question
If I add more .ndf files to the database will reduce pagelatch_up waits and resource is 5:1:3 no.
Because I found there are three solution
1. enable trace flag 1118,-1 -- >I did this and good for a week
2. Move to the fast drive ---> all are same raid 5 in my environment
3. Add equal data files -----> will at solve atleast for certain level or nothing.
Suggest this then if nothing is the solution then I will force them to change the queries.

It's the other way around! Force them to change the queries first and if there's no solution, get a database tuning expert in. I'm very sure you'll get a load of recommendations before messing around with more filegroups (yet on the very same spindle)....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

pavan_srirangam (11/22/2010)
Suggest this then if nothing is the solution then I will force them to change the queries.

Wrong way around. Fix the cause, not the symptoms

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 6

Ok now I should force my manager for changing the queries.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]