Showing posts with label MCM. Show all posts
Showing posts with label MCM. Show all posts

Tuesday, January 15, 2013

Pro SQL Server 2012 Practices



Hello Dear Reader.  Every now and then professionally you get to be a part of something really cool.  Back in the spring of 2012 Jonathan Gennick of Apress contacted me about participating in a book.  His idea?  Get together a lot of really amazing SQL Server Professionals in order to write on subject they were passionate about.

The list of professionals involved was one that made me immediately want to own the book.  When I got the chance to help I jumped!  I wasn't alone.

The companies whose employees collaborated on this book come from Red Gate SoftwareSQL Skills,Brent Ozar Unlimited, Pragmatic Works, and many others.  They are MVP's, MCM's, and regular old SQL Community folk like myself.

We all decided to write blogs reviewing/describing each others chapters.  I will be listing and updating them here.

If you're interested here is a link for the book on Amazon.  Big Thanks to Apress, Jonathan, and my fellow Authors!


Reviews/Summaries

Jes Schultz Borland @grrl_geek

1/14/2013   Book Review: Pro SQL Server 2012 Practices Indexing Outside the Bubble


Herve Roggero  @hroggero

1/15/2013 Chapter Review:  The Utility Database (By Chris Shaw) and how it applies in Cloud Computing 


I'll be updating this page on my blog daily as we crank out new reviews.  All the authors have pitched in and we'll be writting them out one day at a time.  Tomorrow SQL MVP Chris Shaw (@SQLShaw | Blog) all around awesome guy will have his review  on Compliance and Auditing. Thursday the Dr.  of Database Design himself SQL MVP Louis Davidson(@drsql | Blog) is up on Release Management.

Hope to see you then!

Thanks,

Brad

Friday, July 29, 2011

How Do You Learn?

http://www.flickr.com/photos/ale-darling/4150209007/sizes/z/in/photostream/

A lot of brilliant people have spent a lot of time, some have made careers out of, learning about how we as people learn.  Whether you subscribe to the Kolb’s, Gregorc’s, Honey & Mumford’s Models, or Flemings VAK/VARK model, at this point in your career you’ve found a way to learn and it works for you. 

I once had a professor, Dr. William Perry, that said repeatedly throughout his courses “Learn to Love to Learn!”.  He would talk to us about how as IT professionals you had better enjoy learning because you would be doing it for the rest of your life.  He would also add that if you did not like to learn you were in the wrong field.  He couldn’t have been more right.

“So Balls,” you say, “How do you learn?”

Glad you asked Dear Reader, let’s dive right in.

MY FLASH CARDS            


 Here is a picture of one small part of my Desk.  If you’re someone who has worked with me you’ve probably seen my flash cards.  I started this pile of flash cards when I was working on beautiful Ft. Monroe in Virginia.  I was studying for my TS and then my ITP Certifications for SQL 2005.  I added to my flash cards next when I was working for the Office of the President up in D.C.  I was studying for my TS in SQL 2008 at the time.  As I would take the train in I would read my book and highlight sections that I wanted to make flash cards out of.  Then I would re-read the book making the flash cards.

After studying for and getting my Certifications, I didn’t want to just forget what I had worked on.  So I continued to review them.   Sometimes people would walk up and say “What are you studying for?”, and I would reply with the name of the Certification I was working towards.  Sometimes I would simply reply by saying, I just want to keep the information fresh in my mind.

As working situations arise, having this information fresh in my mind proved beneficial time after time.  Learning provided better situational awareness, which only served to reinforce how important it was to continue to learn. 

Pretty quickly on I started finding myself in situations where, I would have an issue resolve it, and I’d want to continue to learn from it.  So I would make a flash card on the script, or the situation.  My focus had been on learning from books alone, and that was pretty narrow.  I realized it was just as important to expand not only what I was learning, but where I learned it from.

Back in 2009 our VP of Microsoft Technologies had told me about this upcoming event called, 24 Hours of PASS.  It was an online event, so I could watch these 1 hour training sessions from my Computer.  I persuaded work to let me work remotely, so I could watch these training sessions.  This was my first exposure to PASS, and my first window into the SQL Community, (other than the forums for SQLServerCentral.com ).

There was amazing session after amazing session, I learned about NUMA from Thomas Grohser (@tgrohser), I first heard Louis Davidson (@drsql | Blog) discuss relational design, and got my first ever view of Brent Ozar(@BrentO | Blog).  I was hooked, and I had gotten a lot of good information.

“So Balls,” you say, “Where do you get your learning material from books and webinars?”

Great question Dear Reader, but the answer is I get it from just about everywhere.

LEARN FROM EVERYTHING


http://www.flickr.com/photos/vertigocycles/3057051812/sizes/m/in/photostream/
When you look at my flash card pile up above, know that I’ve thrown a lot out.  I gave some to friends, and the flash cards have been a constant evolution.  Sometimes material in books are wrong, sometimes material on Books Online are wrong, and sometimes you never know what will be useful.

Here are the places that I take notes from that turn into flash Cards, and would be my recommendations.



A.      Books
Books are a great source of knowledge and there are a lot of really good SQL Server books out there.  Right now I’m reading Microsoft 2008 Internals, Click Here to View.   I’m also reading Professional SQL Server 2008 Internals and Troubleshooting, Click Here to View.  The great thing about books is that when you buy them, you can always have them with you.  Nothing beats having knowledge at your finger tips.  IPAD, Knook, whatever buy them and study them.


B.      SQL Server Central
Whether it be the Question of the Day, an Article in the newsletter, or a really great discussion on the forums.  There is more information than you could ask for in a lifetime.  You can get help with your problems, and do plenty of learning as well.


Last month at MagicPASS we had a Microsoft Certified Master speak to us about the Relational Engine and how Cache works Internally.  It was free, we had tacos, and there are a lot of great DBA’s just like you at the meetings.  This is a fabulous place for networking and for learning.  Go to the PASS website and find the User Group nearest to you.  Don’t forget the Virtual Chapters which provide FREE webinars monthly, sometimes twice a month!


D.      24 Hours of PASS
This is another FREE event, I hope your sensing a trend here you don’t have to spend a lot of money to learn, and it will have the TOP names in the field of SQL Database Administration and Business Intelligence.  All it requires is your time.


E.       Channel 9
This is Microsoft’s online learning resource for the Public.  You can find Tech Ed Presentations, Informational videos by the Project Teams, and even new technology previews.  Another great FREE source.


F.       Webinars
Companies like Pragmatic Works, Idera, and Confio are constantly putting on Webinars monthly and for FREE!  You just need to go to their websites and sign up for their free training News Letters.


G.     SQL Saturday
Hey We have one of these coming up in Orlando on September 24thClick Here to Register!  This is an event where some of the top names in our filed present to you for FREE!  You will have MVP’s like Andy Warren(@sqlandy | blog), Rodney Landrum (@sqlbeat | Blog), or Jorge Serraga (@sqlchicken | Blog) just to name a few, presenting for you Dear Reader.


H.      MCM Videos
I wrote a Blog on this once already, MCM video’s You Should Be Watching These, and you should be.  They are a wealth of information, that scratches the surface on what you need to be a Master!


JUST GO LEARN!
http://www.flickr.com/photos/walkadog/3573598435/


There is also a lot of great training that is not for free, SQL Rally, PASS Summit, and training courses offered by companies like SQL Skills or SQL Cruise.  They are worth their weight in gold, and you don’t have to look far to find a lot of people with the same opinions.

However you learn Dear Reader, just make sure you are learning.  I make flash cards from all these thing, and I review them.  I walk around and share them with the DBA’s I work with, and sometimes we have a quiz of the day on a topic.  I’m sure there are a lot of places that I’ve left off the list if you want to add any please do in the comments below!

Thanks,

Brad






Tuesday, April 12, 2011

T-SQL Tuesday 17: Apply

Welcome to another T-SQL Tuesday!  This week is hosted by Matt Velic (blog|twitter), so Matt what is this week about?
APPLY Knowledge
Recently on Twitter, I heard the claim that “If you don’t understand the APPLY operator, your skills are somewhere around the 50th percentile at best.” While I believe that Adam was giving a warning to self-proclaimed experts (possibly one he might have been interviewing at the time…), I also believe that we could take it as a challenge as a T-SQL blogging community to learn more about APPLY and the ways in which we can use it in our work.
Please share how you use this wonderful feature. Maybe you know how APPLY works inside and out? Perhaps you’ve got a fantastic user defined function (UDF) to share? Or maybe your experience revolves around using Dynamic Management Functions (DMFs) in your never-ending quest for SQL Server performance? Let the community know as it is time to study!
All right, here we go then this is all about the Apply!

START WITH THE BASICS

The Apply operator was added to SQL Server with SQL 2005.  Apply  is a logical join operator, it allows you to perform a join on a Table Valued Function.  I think the most common way that people use Apply is to get the SQL Text or Query Plan using DMV’s.

SELECT
    er.session_id
    ,st.text
    ,qp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp


The previous query will return any currently executing query on your system.  Using the CROSS APPLY is like using a left join, but on a Table Value Function, there is also an OUTER APPLY which is similar to the functionality as a right join.  In this case we are Using CROSS APPLY to return the Text of the SQL Statement being executed, as well as the Query Plan.


The Query Plan can have some over head associated with it, if you’re on a busy system I would recommend commenting out the Query Plan unless you’re truly looking for it.

SURE BUT CAN IT PLAY THE VIOLIN

So the DMV route is pretty well known, but how do you apply this in a non-troubleshooting capacity?  For this next example I’m using the AdventrueWorks2008R2 Database.

So let’s toss out a Case Scenario.  Our boss has come to us, there is an application that the company uses and as part of the expanded functionality we’ll be adding a lookup of Customer’s information.   We need to provide a list of Customer Names on demand, or the Name of a particular Customer, that are identified to us as People and not businesses.  (Not a complicated Scenario, but hey let’s start out practical).

If you Expand AdventureWorks2008R2 | Programmability | Functions| Table-valued Functions, you’ll see dbo.ufnGetContactInformation.  This Table Valued Function is exactly the type that we can use in the CROSS APPLY Statement.

Our Table Value Function accepts the @PersionID parameter, and we will be using the SALES.CUSTOMER table.   We take a quick look at the table to see what details we pull back.

SELECT
    *
FROM
    SALES.Customer

We see that names are not stored in this table, but we know we can retrieve them through our function.  Okay we don’t know that, but if you right click on the function and script it out you can read through it and see what it is doing.

So let’s use our CROSS APPLY statement.
SELECT
    ci.FirstName
    ,ci.LastName
    ,ci.JobTitle
    ,sc.AccountNumber
   
FROM
    SALES.Customer sc
    CROSS APPLY dbo.ufnGetContactInformation(sc.PersonID) ci

So now we can see that this can be used in a non-troubleshooting capacity.

 So let’s think about a couple other possibilities.  Let’s select an order being delivered and we want to know the last update in its location.  We have a customer looking at an item on a web page, and we want to display a coupon for the item.

There are a lot of possibilities for CROSS APPLY, I would suggest watching  the MCM Waits and Queues video by Paul Randal (blog|twitterthis link to the Demo will cover a great scripts by Glenn Berry (blog|twitter) using CTE’s & one by Joe Sack (blog|twitter) that makes a really good use of OUTER APPLY.

/*
How do you clear the server's wait
stats?
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

=================================================
BY: Glenn Berry & Paul Randal


CTE to get wait stats.

This CTE get's the wait stats as they are occuring on the
system and give's you the total seconds of wait, the
# of occurances, as well as the top percentage of where
your wait time is coming from. 

The Exclusions of certian wait types were recommendations
by Paul Randal in the MCM wait types video series
==================================================
*/

WITH Waits AS
(SELECT
    wait_type,
    wait_time_ms /1000.0 AS Waits,
    (wait_time_ms - signal_wait_time_ms)/1000.0 AS Resources,
    signal_wait_time_ms/1000.0 AS Signals,
    waiting_tasks_count AS WaitCount,
    100.0 * wait_time_ms /SUM(wait_time_ms) OVER()AS Percentage,
    ROW_NUMBER()OVER
    (ORDER BY wait_time_ms DESC)AS RowNum
FROM sys.dm_os_wait_stats
WHERE
    wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT','XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
)


SELECT
    W1.wait_type,
    CAST(W1.Waits AS DECIMAL(12,2))AS Wait_S,
    CAST(w1.Resources AS DECIMAL(12,2))AS  Resources_S,
    CAST(W1.Signals AS DECIMAL(12,2))AS Signal_S,
    W1.WaitCount,
    CAST(W1.Percentage AS DECIMAL(12,2))AS Percentage,
    CAST(AVG(W1.Signals)/ W1.WaitCount AS DECIMAL(12,8))AS AvgWait_S,
    CAST(AVG(W1.Resources)/ W1.WaitCount AS DECIMAL(12,8))AS AvgRes_S
FROM
    Waits AS W1
    INNER JOIN Waits AS W2
ON
    W2.RowNum <=W1.RowNum
GROUP BY
    W1.RowNum,W1.wait_type,W1.Waits,W1.Percentage, w1.Resources, w1.WaitCount,w1.Signals
HAVING
    SUM(W2.Percentage) -W1.Percentage <99
/*
=================================================
BY: Joe Sack

This will give you the waits as they are happening
on a system, and give you the query text, as well
as the execution plans as they are occuring

I added the @@SPID to the query so we would not
see our own query plan come back while trouble
shooting
==================================================
*/


Select
    DB_NAME(est.dbid) AS DatabaseName,
    owt.session_id,
    owt.wait_duration_ms,
    owt.wait_type,
    owt.blocking_session_id,
    owt.resource_description,
    es.program_name,
    est.text,
    est.dbid,
    eqp.query_plan,
    es.cpu_time,
    es.memory_usage
from sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON
    owt.session_id=es.session_id
INNER JOIN sys.dm_exec_requests er ON
    es.session_id=er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.plan_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE es.is_user_process=1
    AND es.session_id<> @@spid;
 
Here are the Scripts, but go watch to video to see how to use them!

Thanks,

Brad

Monday, February 14, 2011

MCM Video Series: You Should Be Watching These!

Back in November at PASS Microsoft Announced changes to the Microsoft Certified Master program.  Instead of 3 weeks of training and exam’s in Redmond and close to a $20,000 price tag, you can take a Knowledge Exam and a Lab exam at select Prometric Testing Centers for a price of $2,500 ($500 for the Knowledge exam, $2,000 for the Lab – provided that you pass both on your first try).
One of the things that Microsoft did as well was get the good folks over at SQL Skills to tape 70 different videos, some are 2 parts with a lecture and a demo, on a vast array of subjects to give you an idea of the scope of what you will need to know to pass these exams.
So you may be saying “Ball’s, I won’t be going for an MCM.  It would be nice, but I don’t have the time, the money, or [insert perfectly reasonable excuse of choice]. Why should I care about these videos?” Well, Dear Reader the reason you want to watch these MCM videos  is that they will make you better at what you do, even if you have no desire to pursue this Certification.
So let us discuss how these videos will make you better and why Certifications are important.
How These Videos Will Make You Better.
There are millions, billions, probably more than trillions of things I do not know.  The things I know the most about are my kids, comic books, and SQL Server.  Every time I learn something I get a little bit better.   Whether it’s how the best way to make one of my son’s/daughter’s smile, or that Action Comic’s #775 is the best Superman Story Ever , or the physical structure of a Data Record and how it is stored on a Page, learning these things make me better at things that I love to do.
Paul Randal (blog|twitter), the man himself, does an internal Deep Dive in Data Structures, Kimberly Tripp(blog|twitter)  covering Indexing Internals, Brent Ozar (blog|twitter)  on Virtualization, Bob Beauchemin (blog|twitter)  on Security and Encryption, that’s an awesome line up!  And normally you would pay top dollar in order to get this training, and Dear Reader it is available for FREE!
I’m watching this, the guys I’m working with are watching these video’s, and we’ve even had a couple lunch-in’s where we’ll get a conference room a projector and we’ll watch and discuss the topic that are covered.
Learning makes you better, and this is a golden opportunity to better yourself, even if you have no desire to get the MCM Certification chances are there is a video that covers a SQL topic that you are using daily, and it can probably teach you something that will help you do your daily grind in a better less grinding kind of way.
Why Certifications are Important
Personally I’m a big proponent of Certifications; I was in awe of them before I attempted to get one.  I always thought that just to have a Microsoft Certification you had some super reservoir of knowledge.  When I decided to go for my first certification I got the 70-431 book off of Amazon, I studied it cover to cover, made flash cards, did all of the examples, took the exam and…..failed. 
The crushing defeat lasted just long enough for me to leave the testing center and get into the car.  By the time I turned on the car, I knew exactly what I would do.  While it was still fresh in my mind I would take the items that I was weakest on and I’d make a study guide, to cover those topics like crazy.  I didn’t fail by much and I was already itching for another try.  One week later I took the exam again and got a 902, I was stoked!
I knew the material, I was able to use it at work, and I grew as a professional because I had pushed myself.  You can push yourself without going for the certification, but that piece of paper shows that you took the time to pursue something, and I believe that is important. 
I’ve had employers tell me that they did not value Certifications, but never in the interview.  I have had managers where arguing with them about why Certifications are important happened so often, I could have listed it on my resume as a hobby.  But the second a client wanted to know who the DBA on the project would be they were more than happy to point out that I was their “Microsoft Certified” DBA.  Whenever we were interviewing potential DBA candidates they were quick to mention my Certifications when they introduced me as their Sr. DBA’s. 
I’ve heard the criticisms about brain dumps and Certification Boot Camps, but the fact is if you’re not a DBA, if you’re a hiring manager with no DBA experience, you may not know how to sniff out the real ones from the fakes.  However, if you are a DBA and you studied and earned the Certifications then you know what the other person should know.
In an interview to me a Certification is not a free pass that you know what you’re doing; it is a bulls-eye saying BRING IT ON!  I want tough questions, I want technical questions, I want to make sure that the people in the room to feel confident that they are getting an experienced professional, and if I don’t know the answer to a question then I’m not too proud to ask because I like to learn, and I hope Dear Reader that you like to learn as well. 
So go get learning on what it takes to be a Master, and maybe get motivated to go become one.
Happy Learning!

Brad