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
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
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
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 !