June 7, 2011 at 11:38 am
This is more a general conversation as to make sure my understanding is in the wright direction.
First when Page Life Expectancy is low the biggest contributor to this is Not enough physical memory?
Second high I/O times can have a lot of contributors, but I have done enough research and played around enough to see that under estimation of MDF's and LDF's can contribute too? I have set my tempdb to have the same number of files as the CPU's on the server. With the tempdb what matters most with this topic the number of mdf's or ldf's or both? If SQL Server has to auto grow files consistantly does this also contribute to high I/O times? I want to leave the hardware part out of this to make sure that I have a full understanding of SQL Server's part in high I/O times!
June 7, 2011 at 12:24 pm
A consistently low Page Life Expectency is typical of memory pressure. I beleive the metric is consistently less than 300 seconds. Keep in mind some operations like DBCC CHECKDB and reindexes can cause this number to fall, but it starts to recover immediately. I don't like to see numbers even hovering around 300.
On the topic of IO times. There is great debate about cores to files for tempdb, per my last read 2-1 was a good medium, but testing is the only way to be sure. As for auto growth, this can have a huge effect on performance. The growth process is synchronous, it HOLDS ALL writes to the database until the growth has completed, which is why I usually set a minimum of at least 10MB growth, for databases that grow a lot 100-500MB may be more appropriate. I see FAR too many 1MB growth numbers. here is a perfect example of this, I shaved 2 HOURS off a move from SQL 6.5 to SQL 2000 by doing NOTHING BUT starting the databases near the size they would likely be when we were done and having a realistic growth number. My first pass I had started them all small and had small growth numbers. The difference was so drastic I simply couldn't argue.
CEWII
June 7, 2011 at 12:55 pm
Elliott Whitlow (6/7/2011)
A consistently low Page Life Expectency is typical of memory pressure. I beleive the metric is consistently less than 300 seconds. Keep in mind some operations like DBCC CHECKDB and reindexes can cause this number to fall, but it starts to recover immediately. I don't like to see numbers even hovering around 300.
Depends on server and workload.
Page life expectancy of that means your entire data cache is getting wiped out within 5 minutes. Say a server with 60GB of memory has a page life expectancy of 300, that means that 60GB is being tossed out of memory and replaced in 5 minutes.
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
June 7, 2011 at 12:58 pm
Gila,
I agree, were you just clarifying what it meant?
CEWII
June 7, 2011 at 1:03 pm
More emphasising that there's no single hard value where it's good above and bad below. I see the 'page life expectancy must be above 300' tossed around far too much with no explanation and that can be a dangerously low figure on larger machines.
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
June 7, 2011 at 1:15 pm
Ok, I could see that. I would say that for most people the 300 number is at minimum a good starting point. I don't have and haven't had too many machines with more than 32GB of RAM, 8-16 has been really common, and 24 not uncommon.
CEWII
June 7, 2011 at 1:27 pm
OK sounds like i am on the right track. But lets get a little deeper with the tempdb number of files to CPU. What files data files or Log files or both file types? And why? Why not make the tempdb the appropriate size needed for the workload?
June 7, 2011 at 1:39 pm
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
June 7, 2011 at 1:50 pm
That was a good article, but it just goes to show that you may or may not need to do something on your system that some one else has done. I had to have a 1-1 for pressing I/O times to accomidate for not having the hardware.
June 7, 2011 at 2:05 pm
No but that article shows 1:1 isn't necessarily the best and that the guidance to use 1:1 can actually cost you performance. It CAN be be the best but you won't know without trying. GilaMonster can school me if necessary but for most systems I like a 2 core per file and always evenly divisible by 2. Mileage may vary..
CEWII
June 7, 2011 at 2:13 pm
My uneducated guess (if someone asks for TempDB recommendations for a server I've never seen) is number of data files = 1/4-1/2 the number of cores in the system, do not exceed 8 at first. If there is still latch contention (PageLatch on 2:1:3 typically) then double the files, repeat until the latch contention goes away.
That's entirely assuming that they're splitting TempDB because of latch contention, not IO contention, and it does not apply to user databases.
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
June 7, 2011 at 2:15 pm
What if that PageIOLatch is at 2:1:107 ?
June 7, 2011 at 2:20 pm
PageLatch != PageIOLatch. The former is contention on the allocation pages. The latter is IO contention.
For IO contention you want to split the files, but not in the same way. Start by creating a second file on a separate physical drive. If you still have contention, create another file on another separate physical drive.
Or optimise so that tempDB usage is lower.
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
June 7, 2011 at 2:22 pm
Thanks that helps me clearify and what i am faced with!
June 7, 2011 at 2:28 pm
Let me make sure that I understand that you are only talking about data files of the tempdb and not the Log files?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply