I made it to the PASS Summit in Seattle. I am going to take you on the ride with me. I am going to pass along some of the highlights I learned and who did the teaching.
Today, I am attended the pre-conference session with Brent Ozar (@brento) on the basics of SANs and Virtualization for DBAs. This is an area that I am not as strong as I would like to be in, but am always trying to get or provide advice on solutions with these technologies. Here are some tidbits I gleaned from the session today. Nice work Brent.
- Remove as much as possible work from TempDB as more and more system work is being done there. You should consider “scratch” databases to support some of this work instead.
- Avoid RAID 0, you will definitely get an outage.
- RAID 5 – 1 write turns into 2 reads and 2 writes. RAID 6 does this more. Thus RAID 5 while costs less money, it costs more in performance. I knew this before, now I understand why.
- Shared arrays in SANs are easier to manage than Dedicated arrays when you can create a big pool of 30-50 drives to be shared by many servers.
- SAN snapshots are a great tool for back ups, in particular in the data warehouse. Be sure that it is integrated with SQL Server. This is not cloning, which is a bad idea. Snapshots should not take longer than 10 seconds.
- Always review the SAN vendor documentation. They usually have recommendations for setting SQL Server up correctly on their product.
- Data compression is a good thing. Especially for OLAP data.
- Be sure to check the pathing in your SAN. It is frequently the real botteneck on the SAN.
- Use stats from your backup jobs to monitor SAN performance over time. You can find this information in MSDB. This allows you to see changes to the SAN that affected overall performance easily.
- SQLIO is used to test storage in general. It not just a SQL tool, as a matter of fact, SQL Server is not required.
- For more details check out Brent’s site on SANs: http://BrentOzar.com/go/san
- Task manager is worthless in a virtualized environment.
- VMware has some great new counters to see how the host is working.
- In some cases, assigning fewer virtual CPUs to each SQL instance may actually perform better by reducing CPU contention.
- Memory reservations in VM for SQL Server is a great plan. Successful shops will get this taken care of and good VM admins will balance this with failover requirements.
Check out Brent’s website for more great information on SQL Server: http://www.BrentOzar.com.
I wrapped up the night at Lowell’s in Pike Place Market at a networking dinner. Met some SQL guys from around the world and right next door.
Looking forward to tomorrow’s luncheon with SQL Saturday planners.