August 2012 - Posts

Like a lot of us, I had set myself some goals @ the start of the year, and I did not make them all that easy

I had set myself the goal to become Microsoft certified in SQL Server 2008 & 2012 {the details on what exactly where the 2012 certifications where not clear when the goal was initially set}

I duly spent the best part of the 1st half of the year, in studies for and taking the 4 exams to become an MCITP 2008 SQL Server for both Database Administrator & Developer

  • Exam 70-432 This was already in the bag from last year (Sept 24th 2011)
  • Exam 70-450 Taken and passed on June 9th

So @ this point I had achieved the 1st of my MCITP certifications SQL Server 2008 Database Administrator

  • Exam 70-433 Taken and passed on June 29th
  • Exam 70-451 Taken and passed on July 22nd

So now I had achieved the 2nd of my MCITP certifications SQL Server 2008 Database Developer

 

Also I had the opportunity to take a SQL 2012 Beta exam when they became available, due to my schedule I was only able to take just one of these - so I choose the new Query based one

  • Exam 70-461 After waiting for the beta results, I found I had indeed passed it April 12th
  • Exam 70-462 Taken and passed this on Aug 26th
  • Exam 70-463 not as yet scheduled, I will be likely attempt that in next few weeks

When I do pass the 70-463 I will have the achieved the newly minted SQL Server 2012 MCSA certification as detailed in http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

  • MCSA: SQL Server 2012

Once{When} I do pass and have the MSCA cert, I can progress onto the next level which is the MCSE: Data Platform this requires 2 more exams

  • Exam 70-464 Developing Microsoft SQL Server 2012 Databases
  • Exam 70-465 Designing Database Solutions for SQL Server 2012

I will also probably look @ taking another exam, So I can get the MCSA in 2008 as well

  • Exam 70-448 Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

As this would then earn me the following certification MCSA: SQL Server 2008

 

This is all good progress, however the main certification goal I had set myself was this advanced certification one Microsoft Certified Master (MCM) in SQL Server 2008

http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx

To achieve this difficult goal, I first needed to gain those 2 pre-requisite MCITP Certifications SQL Server 2008 Database Administrator & SQL Server 2008 Database Developer {these was duly achieved on July 22nd}

So now I had both of the pre-requisites, I could attempt the 2 exams for the MCM SQL 2008 certification

  • Exam 88-970 SQL Server 2008 Microsoft Certified Master: Knowledge Exam, Taken and passed on Aug 13th
  • Exam 99-971 SQL Server 2008 Microsoft Certified Master: Lab Exam, Currently awaiting a date in Sept or Oct when I can attempt this one

I'm in final study mode now for the Lab, I think that there are somewhere around 100-120 people certified @ this advanced level {about 40-50% of those working @ Microsoft}

 

So even as my goals are nearing completion, I do think I will likely achieve them before the year ends,  my learning will continue into next year and well beyond

So with 3 or 4 more 90-xxx exams and a 88-971 Lab to complete - I've no time yet to relax

Right it is time I got back to my studies, perhaps I will see you @ one of the conferences I'll be presenting @ in the remainder of 2012

  • SQL Saturday #162 - Cambridge, UK
  • SQL Rally Nordic - Copenhagen, Denmark
  • SQL PASS Summit - Seattle, US

 

Posted by NeilHambly | with no comments
Filed under:

At times I often want to know what is happening inside my SQL Servers, there are of course a multitude of ways I could "peek" into the activity that has been happening,

Sometimes I just need to get a quick summary of those facts, maybe just to know if anything unusal has happened I'm not yet aware of, an easy way I can do that is to query the DMV sys.dm_os_performance_counters,

As there are tons of blog posts already out there on using the data from this DMV, I'm not going to focus on that but I wanted to share a simple approach I sometime use with this and some other DMVs and that is to PIVOT the data for "Easy Viewing"

I do like to sometimes to retrun my results in the "Pivoted" fashion for easy viewer and the results from this DMV often lend themselves well to this approach -  What I'm I talking about, well let me show you a few examples to illustrate the point

NEW in SQL Server 2012 are the counters for ”Batch Resp Statistics”

SELECT [counter_name],"CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%Batch Resp Statistics%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name]

IN ("CPU Time:Total(ms)","CPU Time:Requests","Elapsed Time:Total(ms)","Elapsed Time:Requests") ) AS Pvt;

Can give you something like the following output

 

Another is reviewing locks

SELECT [counter_name],"OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%:Locks%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name] IN ("OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total")) AS Pvt;

 

The final one I'll show you is the "Waits" in progress me system not much to see)

SELECT [counter_name],"Average wait time (ms)","Waits in progress","Waits started per second","Cumulative wait time (ms) per second"

FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%Wait Statistics%') os_pc

PIVOT (AVG([cntr_value]) FOR [instance_name]

IN ("Average wait time (ms)","Waits in progress","Waits started per second","Cumulative wait time (ms) per second")) AS Pvt

WHERE "Average wait time (ms)" > 0;

Not much to see in this one as it is my Home system

 

The examples Queries as you can see are straight forward to compose, I know you can make a few of your own using the same pattern

Your challenge will be figuring out the steps to take to "Solve the performance hurdles" these type of Queries can reveal

Posted by NeilHambly | with no comments

Hello

With the remaining quarter of the year, promising to be as manic as the rest, having already done the following events

SQLBits 10, SQL Relay 2012, 24HOP (SQL Server 2012), SQLRally 2012 Dallas, TechEd AMS {PASS Ambassador} & 3 SQLSaturday's (Portugal, Dublin, Johannesburg), as well as
SQLDay in Poland, virtual event for Perth UG {Australian}  and not to mention a host of events for my own SQL London PASS Chapter, as the 1st UG event in Belfast {Good luck Ryan with the next one}

I still have the following scheduled events {and a few more potentially in the pipeline before the year is out}

  • 21st August UK SQL London User group, hosted @ Hitachi Office
  • 25th August (Live Meeting) Nepal User Group - Session on Wait Statistics "Why are we Waiting.." {same as my Summit 2011 session)

Sept

Extended Events - New Age of Profiling

Extended Events is the replacement for profiler in many respects, and will be the choice of capturing data from within SQL Server now and in future editions,

So we need to understand the differences it has to profiler "old guard" and why we should be using this new feature for this purpose I'll take you on a Guided tour of the
key aspects, and show you simply how in just minutes you can up and running and capturing data using this new method and how to analyze it effectively

So follow me to the "new age" of understanding your systems activity 


Session's agenda listed here http://www.sqlpass.org/sqlrally/2012/nordic/Agenda.aspx

My session will essentially be the also the same as I'll be presenting @ this year's Summit Nov 6th - 9th SQL PASS Summit

  • I'm excited to be attending my 2nd Summit, I was @ last year’s Summit and where I also did a regular session on Wait Statistics "Why are We Waiting.." I had 310 Attendees showed up for that, it was an amazing event :-)
  • This year I'm doubly excited to again presenting a regular Session, this time on SQL Server 2012 Memory

SQL Server 2012 Memory Management [DBA-312]
Session Category: Regular Session (75 minutes)
Session Track: Enterprise Database Administration & Deployment
Speaker(s): Neil Hambly

SQL Server 2012 brings a wide range of changes, including a key change in how SQL Server memory is managed.

Earlier releases of SQL Server have two memory managers, which have now been merged into one. And that’s just the beginning of the changes for SQL Server memory.

This session will dive into SQL Server 2012's new memory architecture. We'll see how to diagnose memory performance issues and gain insight into memory pressure issues.

We'll also cover the settings to adjust memory configuration levels and their impact.

http://www.sqlpass.org/summit/2012/Sessions/SpeakerDetails.aspx?spid=282

Note: There is the Voting Rounds for Lightning Talks for the Summit for just 1 week (20th - 24th Aug) So please make a vote for those you'd like to see <wink wink>

 

2013 - SQL Cruise {Jan 26th - 2nd Feb 2013} Miami and Eastern Caribbean

Although not 2012 event.. But as it is very exciting and it does set sail in Jan 2013, so I thought I'd tag it on @ the end of the post

I'll be attending and also presenting on the following SQLCruise - Jan 26th - 2nd Feb 2013 - Miami and Eastern Caribbean

Interested and want to know more? Here is the best place to find out about these amazing SQL Cruises http://sqlcruise.com/2013-cruises/

My session will a "Deep Dive" 2-Hour special on SQL Server 2012 Memory Management as detailed below

SQL Server 2012 Memory Management

SQL Server 2012 brings a wide range of changes, including a key change in how SQL Server memory is managed. Earlier releases of SQL Server have two memory managers, which have now been merged into one. And that’s just the beginning of the changes for SQL Server memory. This session will dive into SQL Server 2012′s new memory architecture. We’ll see how to diagnose memory performance issues and gain insight into memory pressure issues. We’ll also cover the settings to adjust memory configuration levels and their impact.

This session is packed with Demo’s; in fact more than half this session is spent demonstrating so as to provide great opportunities to delve deep into memory clerks and SQLOS and use Resource governor and other techniques to ensure good memory utilization. Time is dedicated to discuss real-world experiences of consolidating multiple SQL Systems onto new equipment, how to configure servers with large amounts of RAM (512GB). The session concludes with discussions on LPIM, large pages and memory trace flags.

Does anyone think I was lacking badges in this post ! 

Posted by NeilHambly | with no comments