June 23, 2024 at 9:08 am
hi,
q1 As people said if u ghave GAM conention then u can go for adding as many datafiles of equal size in temdb as many cpus u have is it correct in sqlserver 2019 vesion also.
q2) right click on sqlserver instance then select reprots->perfomance dashboard report then click io statistics . i have attched mine , i have read that it should be less than 10 15 ms per transact(avrage of read and wirte). pls correct me.
yours sincerley
June 23, 2024 at 4:05 pm
To be honest, I've found that most such contention is because of what is appropriately referred to as "crap code". đ
I recommend that you figure out what's beating on TempDB and fix that before you add any more files.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2024 at 3:41 am
second question is unanswered.
June 24, 2024 at 4:48 am
one more thing my log file of database (this is not tempdb this is reular db) is increasing like this , should i increase then auto increment?
file is attached.
June 24, 2024 at 10:25 am
my second query is. the place i am looking is correct place for (10 to 15 ms) or it is telling diffrent story?
June 24, 2024 at 10:42 am
one more thing my log file of database (this is not tempdb this is regular db) is increasing like this , should i increase then auto increment?
file is attached.
looks like you have the increase set to 10% - CHANGE it to be a fixed amount (multiple of 64KB - for that small log file likely a 32 MB increment would work fine. - so way to do it is change increment to 32MB then shrink it to the minimum possible and then let it increase to 128MB - this would leave some space free before growing again.
have a look at https://www.brentozar.com/blitz/blitz-result-percent-growth-use/ for info on growth - obviously increment should be adjusted to size of db - I have some db's with a data growth of 5GB (with IFI enabled) but these are multi terabyte sizes where growth happens during nightly batch window when it needs.
June 24, 2024 at 10:43 am
edit: dup post
June 25, 2024 at 5:23 am
artical does not say any thing about critaria , like the increment of datafile or log file should be once in a day or twoice in day?
like if i know 1 gb data is required per day by my datafile or log file so should I keep the increment to 500mb or 1 gb ?
June 25, 2024 at 10:12 pm
I'm confused.... the log file name of your latest graphic doesn't appear to match or relate to any database names in your first graphic. You've also not said anything about how big your current TECHINTFDC database is or the largest index or heap you have. Do you have any idea how big this new database is going to get in the next year?
Also, do you know what the "IFI" that Frederico was talking about is?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2024 at 2:42 am
i think he said it should increment to the size of the database. but still u can answer on my meterics like onece in day or twoice in a day or in a week?
q2) in prerfomace monitor of windows i can check Disk Write Bytes/Sec and in datacollector, can i see or collect this using any dmv?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply