Starting to wear down by the third day. The conference was a bit smaller today, I suspect the local folks only hit the first day or two. That makes sense because MS and the Vendors are only here the first two days as well.
Day 3:
Lab Time
I returned to the to the lab and did a few more sessions, unfortunately these all had problems. I got frustrated after a bit and checked in with work until the first session.
Analyzing File and Wait Stats (Andrew Kelly)
It's one thing to know that DMVs (Dynamic Management Views) exist, it's entirely something else to interpret them. That's what got covered in this session.
File Stats (sys.dm_io_virtual_file_stats)
These are PHYSICAL IO ONLY. Logical IO is excluded because it never hits the files, only the cache.
Backups will skew your results pretty heavily. If you are monitoring the Reads you will see a big jump during the backup time window. Take stats before and after backups so you can exclude these counts.
The Writelog waits will let you know if you need to move your Log file off to separate or faster disks.
TempDB will have more IO than you expect. Monitor it to determine if you need to add more files to the file group (wish they mentioned this during the TempDB session).
The two scripts he mentioned for monitoring the performance that he release are:
- gather_file_stats_2008
- report_file_stats_2008
The Stall that is reported can be > query time because stall is reported the sum of all threads and query time is overall.
When you have High Stall writes here are some things to try:
- Add Writeback Cache to your RAID controller
- Rebalance existing caches to 100% write and 0% read. SQL Server does not benefit much from read cache.
- Add spindles
- Separate Data and Log
- Tune your SQL!
Wait Stats
There over 485 of these buggers! Internal and External ones are exposed in SQL 2008.
There is a new type that start with PreEmptive and are used to indicate you are waiting for something outside of the SQL Server's control (such as an OS or web service call).
Wait Time is the overall time being waited for while signal time is how long the task is waiting to get some CPU time. The actual Resource Wait Time = Wait Time - Signal Wait.
It is generally safe of ignore System and OLEDB. The only time you could impact these is by doing linked server calls. Some example waits:
- CXPACKET: Check MAXDOP, lowering it may help.
- LCK_M_%: You've got blocks!
- ASYNC_NETWORKIO: Almost never the network, this is typically a slow client (I'm looking at you Data API!)
- PAGE_IO_LATCH, IO_COMPLETION, WRITELOG: There is a storage issue somewhere.
- PAGELATCH: Internal Resource Contention, check TempDB.
SQL Automation and some Powershell (Buck Woody)
This must be the funniest man at PASS. The session was great and he covered a lot of good stuff. I am going to watch all of his sessions when my DVDs arrive. I learned a lot and didn't realize it because he made it a lot of fun.
Powershell is now part of the "Common Criteria" at MS. That means that every product that is released must be able to managed using Powershell commands. What this really means: MS is not dropping Powershell any time soon and is probably worth learning.
There is a built in "MiniShell" in SQL 2008, you can open it right from object explorer.
Powershell resources to look into:
Advanced Physical Database Design (Boris Baryishnikov)
This was brutal, the guy was nice but his session completely mislabeled. It was about indexing, the database tuning advisor and sparse columns. There is a lot of Physical DB design choices to make when converting from a Logical Model and he didn't cover any of them. This was probably the most annoying session at PASS because the title was so completely inappropriate for the content.
No comments:
Post a Comment