Recently, I was pulled into a gig to help troubleshoot an windows failover cluster service issue that was hindering an availability group. Just to give a little background information its a two node windows cluster with two replicas the availability groups are using synchronous mirroring. Once, this was resolved we had a critical database that was behind on synchronization because one of the two replicas (cluster node) was down. This lead to the following question. When will my replica by synchronized?
This question was easy to answer with database mirroring. We could pull up the database mirroring monitor. We don’t have an database replica monitor GUI tool built into SSMS. Lucky for us, its not that hard with availability groups. We just have to use performance monitor. You will see below there is an perfmon collection called “SQLServer:Database Replica” that comes in handy for us.
When will replica be synchronized?
Above, you can see that this secondary replica which is back online is synchronizing as its receiving log bytes and the Recovery Queue shown in the performance monitor is also reducing as the synchronization process is catching up. According to MSDN Recovery Queue is the Amount of log records in the log files of the secondary replica that has not yet been redone.
This month is going to be an exciting month for me. I enjoy training and sharing my knowledge with others and this month I am going to have several opportunities to accomplish that goal.
This weekend, May 10th I will be giving two sessions at SQL Saturday in Houston. I will kick off the morning at 8:30 with my Proactive Monitoring with PBM and CMS presentation and close the day with Table Partitioning: the Secret Weapon for Your Big Data Problems. With Joey D’Antoni, Ryan Adams, Tamera Clark, and Kara in the house I also feel like it might be a PASS Regional Mentor reunion too.
I am making my first visit to Kansas City to present at the Kansas City Developer Conference. I will be giving the following two sessions SQL Server Performance Tuning with Free Tools on the 16th and Table Partitioning: Secret Weapon for Big Data Problems on the 17th.
Finally, I am heading back to my favorite city Wheeling, WV to speak at the Greater Wheeling Chapter of AITP on the 21st. I will be giving a First Look at Availability Groups. This trip will also include an presentation at the Pittsburgh SQL Server User Group on 20th about 12 Steps to Workload Tuning. The month of training will conclude with the first All-Day training by LinchPin People in a series focused on the basics of High Availability and Disaster Recovery.
Its going to be a great month. I hope I get to catch up with you at one of the events.
Today I have some good news to share. David Klee (blog | twitter) and I are announcing a new SQL PASS Virtual Chapter dedicated to High Availability and Disaster Recovery topics. I am excited to work with David to build a virtual chapter from scratch.
When Does HADR Virtual Chapter Meet?
The schedule for this virtual chapter meetings is the second Tuesday of each month at 12pm Central time. Our first meeting will be held next Tuesday, February 11, at noon Central with Ryan Adams (blog | twitter) speaking on SQL Server 2012 AlwaysOn Availability Groups Live Deployment. To RSVP for this free event, register here at GoToWebinar!
Please follow the Twitter tag @SQLPASS_HADR for announcements of upcoming sessions, and check out the home page for the virtual chapter at hadrvc.sqlpass.org for details on the future sessions. Make sure to visit MyPASS dashboard and add the HADR virtual chapter to your MyChapters page so you get the announcements as they happen!
Are you interested in joining us building the HADR Virtual Chapter?
I have a feeling that David and I are not the only ones in the SQL Community interested in building a virtual chapter from scratch. We are currently looking for people to help host meetings, manage the web site, drive marketing, and finding speakers, If you are interested in joining us contact me.
Welcome to the first Throwback Thursday blog post of 2014. Throwback Thursday is a blog series where I dig deep into my evernote collection and find some great content on a single subject and share it with you. In the fourth installment of the Throwback Thursday series we are going to cover the plan cache.
One of the features that is helpful for performance tuning in SQL Server is the plan cache. Many DBA’s understand that when you execute a stored procedure by default the execution plan gets cached so it can be reused. This allows CPU cycles to be saved as you don’t have to recompile stored procedures every time they are executed. What some DBA’s don’t know is that you can leverage the plan cache to get performance metrics. During this Throwback Thursday we are going to focus on some great community articles, white papers and blogs that can greatly improve your performance tuning process though using the plan cache.
Plan Caching in SQL Server 2008 - This is a great white paper by Microsoft that gives you a great introduction into what the plan cache is and how can make your performance tuning life easier.
How to Drop One Plan from Cache – A lot of DBA’s think that DBCC FREEPROCCACHE is used only to drop all the execution plans from your instance. Grant Fritchey does a great job showing how you can utilize this DBCC command to remove only a single plan.
Finding Top Offenders from Plan Cache – This is how I go about finding top offenders for frequently used execution plans. It’s not the only tool in my tool-belt for performance tuning but it can be a good starting point.
Finding Key Lookups in Plan Cache - At the end of the day, a execution plan is cached as XML. Kendal Van Dyke does a great job showing us how we can probe this XML structure to find key lookup operators inside of the plans that are in the plan cache.
SQL Server Plan Cache: Junk Drawer Your Queries – A great article by Tom LaRock that shows you how plans are stored, how to find plans with specific operators, and how to aggregate similar queries to get helpful usage statistics.
Plan cache, adhoc workloads and clearing the single-use plan cache bloat - Kimberly Tripp did a great job explaining how you can monitor and prevent adhoc workloads from bloating your plan cache.
Can You Dig It? Plan Cache Series – I saved my personal favorite for last. Jason Strate did a great job providing an series of several scripts that can be used for reviewing the plan cache.