Tuesday, April 7, 2015

My Favorite FREE Reports in SSMS

Microsoft has given us DBAs/Developers/etc. a wealth of free reports integrated into SQL Server Management Studio (SSMS) in order to see what is happening in our environment. These reports are really nice because you do not need to know the DMV/DMF in order to get the results.

The first report I use in on a database - Disk Usage. In order to get to this report, I need to Right-Click on the database and go to Reports menu.

image

 

Disk Usage

From there, drill into Standard Reports –> Disk Usage

image

This reports gives me a visual of the data and log usage as well as what is going with the data files. From here, I can see if the log file is getting large and/or how full. The Data/Log File Autogrow/Autoshrink indicates that the log file grew on 4/7 and added 200MB (it is autogrow sized correct). I can also see the percentages of data, index, etc. usage on the File Groups.

 

Disk Usage by Top Tables (or Disk Usage by Tables)

The next report is Disk Usage by Top Tables or by Tables. The Top tables will order the report Reserved Data Size. The by Tables report orders the list alphabetically by schema name and then table name.

 

image

 

image

 

Schema Changes

Being able to see changes on the database without too much effort is reserved for this report. It feeds from the default trace, so some things fall off with recycling of the trace files.

image

 

Index Usage Statistics

This report gives usage and operational information about indexes.  The usage statistics are related to Seek, Scans and Updates while the operational is for Insert, Updates and Deletes.

image

These are just a few of the integrated reports in SSMS. I am sure you will find your favorite or even find some at the instance level you might enjoy.

image        image

 

Thomas

Sunday, March 29, 2015

PASS Excel BI VC presents Devin Knight on Getting Started on PowerQuery

Come join us Thursday April 2nd for an Excel BI virtual chapter session at noon central with Pragmatic Work’s Devin Knight who will help us get started with PowerQuery.

Thu, Apr 02 2015 12:00 Central Daylight Time


Getting Started with Power Query by Devin Knight

RSVP: https://attendee.gotowebinar.com/register/1373099073018225154

GETTING STARTED WITH POWER QUERY

Power Query is a free add-in for Excel 2010 or 2013 that provides users an easy way to discover, combine and refine data all within the familiar Excel interface. With Power Query you can now combine and transform data from a variety of data sources all within Excel, which would have previously required a complex ETL tool. Join Devin and learn how you can use Power Query to take your Self-Service BI solutions to the next level. You will also see quick ways to visualize this new data appropriately. BIO Devin a Microsoft SQL Server MVP and the Training Director at Pragmatic Works Consulting. He is an author of six SQL Server books and speaks at conferences like PASS Summit, PASS Business Analytics Conference, SQL Saturdays and Code Camps. He is also a contributing member to the PASS Business Intelligence Virtual Chapter. Making his home in Jacksonville, FL, Devin is the President of the local users’ group (JSSUG).


Devin Knight a Microsoft MVP and the Training Director at Pragmatic Works Consulting. He is an author of six SQL Server books and speaks at conferences like PASS Summit, PASS Business Analytics Conference, SQL Saturdays and Code Camps. He is also a contributing member to the PASS Business Intelligence Virtual Chapter. Making his home in Jacksonville, FL, Devin is the Vice President of the local users group (JSSUG).

Tuesday, March 3, 2015

Simple-Talk Article

In December, I was asked to write an article(s) on Database Refactoring. It started as 2 topics, but then consolidated into one. I really wanted to talk about the database design issues I see with a development department. Please give some time to reading the article and comment. First, I would like to thank for Simple-Talk team for the editing process which was first class and very, very useful.

https://www.simple-talk.com/sql/t-sql-programming/defusing-database-time-bombs-avoiding-the-need-to-refactor-databases/

The comments are really for the new developer who needs some guidance on designing databases. I wish everybody had the MIS class I took at LSU for Entity-Relationship diagrams. Back then(86-91), the ER Diagram and Data Diagram were 2 separate pieces to the database. You looked at the conceptual design before the physical implementation.

I am started to wonder if Agile/Scrum methods are starting to water down the design sessions for application development. Or maybe not :)

Tuesday, January 20, 2015

SQLSaturday #362 – Austin, TX – Let’s Get Weird

I used to spend the 4th of July weekend (or week) in Austin with some buddy’s when we were in our 20s. There was so much to do and enjoy while there it is exciting to go back to this great city. Not only that, but there are some really good SQL Server experts and community organizers in that city – John Sterrett, Jim Murphy, SQLServerIO (Wes Brown – hope he is there), AJ Mendo and more. Catching up with these people will be refreshing.

The event is on Jan31st at Wingate by Wyndham Round Rock Hotel and Conference Center 1209 N IH 35, Round Rock, TX 78664

 

THIS YEAR THERE ARE 2 OFFICIAL SQL SATURDAY PRECON'S ON FRIDAY JANUARY 30th, 2015. TICKETS WILL SELL OUT QUICKLY SO MAKE SURE YOU REGISTER TO SPEND THE DAY WITH SOME OF SQL SERVER'S GREATS!

BECOME AN ENTERPRISE DBA WITH SEAN AND JEN MCCOWN     Eventbrite - Become an Enterprise DBA with Sean and Jen McCown

MURDER THEY WROTE WITH JASON BRIMHALL AND WAYNE SHEFFIELD   Eventbrite - Murder They Wrote

Register

 

Here is the emailed schedule:

Track

Starts

Session Title

Speaker

Business Intelligence

08:30 AM

Registrations

SQLSaturday 362

Business Intelligence

09:00 AM

Capture Change and Apply it with CDC & SSIS

Steve Wake

Business Intelligence

10:15 AM

Getting started with Tabular Analysis Services

Thomas LeBlanc

Business Intelligence

11:30 AM

15 Quick Tips for SSIS Performance

Tim Mitchell

Business Intelligence

12:30 PM

Learn How To Build A Golden Record for Any Subject Over Lunch!

Gene Webb

Business Intelligence

02:15 PM

Writing Your First BimlScript

David Stein

Business Intelligence

03:30 PM

Change Data Capture & Change Tracking Deep Dive

Kevin Hazzard

Business Intelligence

04:00 PM

XML & SQL: Best Frenemies Forever

Richard Heim

Business Intelligence

05:15 PM

Raffle

SQLSaturday 362

DBA, Automation

09:00 AM

DBA Toolbox: Surviving A Disaster

AJ Mendo

DBA, Automation

10:15 AM

SQL Server: Monitoring disk usage

Daniel Janik

DBA, Automation

11:30 AM

Beginning Automation with Powershell

Amy Herold

DBA, Automation

12:30 PM

Embarcadero - Lunch Sponsorship Session

John Sterrett

DBA, Automation

02:15 PM

Database Release Management: Tips to help organize it

TJay Belt

DBA, Automation

03:30 PM

SQL Server Internals

Naomi Williams

DBA, Automation

04:00 PM

SQL Server Bingo – Install, Migration & Config

Mindy Curnutt

DBA, Automation

05:15 PM

Raffle

SQLSaturday 362

DBA, Cloud

09:00 AM

SQL Server Foreign Keys – De-mystifying the Rest of the Story

Mike Byrd

DBA, Cloud

10:15 AM

Getting Started with SQL Server in the Cloud with Microsoft Azure

Anil Mahadev

DBA, Cloud

11:30 AM

SQL 2012 Extended Events

Jason Brimhall

DBA, Cloud

12:30 PM

Amazon Web Service - Lunch Sponsorship Session

John Sterrett

DBA, Cloud

02:15 PM

No DR Site?! No problem!! Enhance your SQL Server HA/DR capabilities using Windows Azure

Kal Yella

DBA, Cloud

03:30 PM

SQL Server Statistics – What Are The Chances?

Lori Edwards

DBA, Cloud

04:00 PM

Introduction to Wait Types and Response Time Analysis

Janis Griffin

DBA, Cloud

05:15 PM

Raffle

SQLSaturday 362

DBA, Security

09:00 AM

Turbocharge Your Career With a Learning Plan

Andy W

DBA, Security

10:15 AM

CIS Benchmarks – A Guide to SQL Server Security

Nancy Hidy Wilson

DBA, Security

11:30 AM

SQL Watchdog - find out instantly when SQL change

Michael Bourgon

DBA, Security

12:30 PM

Cisco - Lunch Sponsorship Session

John Sterrett

DBA, Security

02:15 PM

SQL Security Best Practices & Shrinking Your Attack Surface

Matthew Brimer

DBA, Security

03:30 PM

SQL Injection

Kevin Boles

DBA, Security

04:00 PM

Securing Your SQL Environment

Tim Radney

DBA, Security

05:15 PM

Raffle

SQLSaturday 362

Development

08:30 AM

Registrations

SQLSaturday 362

Development

09:00 AM

Parameter Sniffing the Good and the Bad

Lance Tidwell

Development

10:15 AM

5 TSQL Commands I've Been Missing

Jason Carter

Development

11:30 AM

The Real Value of Name-Value Pairs: Using PIVOT and UNPIVOT

Kevin Wilkie

Development

12:30 PM

Women in Technology Round Table Discussion

Rie Irish

Development

02:15 PM

Windowing Functions

Timothy Costello

Development

03:30 PM

Turbo Boost Performance: In Memory Tables index optimizations

Konstantin Melamud

Development

04:00 PM

T-SQL Throwdown

Hakim Ali

Development

05:15 PM

Raffle

SQLSaturday 362

Thunderdome

09:00 AM

Introduction to Powershell cmdlets for DBAs

Jennifer McCown

Thunderdome

10:15 AM

Query Store - A New SQL Query Tuning Feature

Conor Cunningham

Thunderdome

11:30 AM

Increase your SSIS productivity with Biml

Reeves Smith

Thunderdome

12:30 PM

Microsoft - Sponsor Lunch Session

John Sterrett

Thunderdome

02:15 PM

SQL Server 2014 In Memory technologies - In Memory OLTP (aka Hekaton) & ColumnStore

Reinaldo Kibel

Thunderdome

03:30 PM

Table Vars & Temp Tables - What you NEED to Know!

Wayne Sheffield

Thunderdome

04:00 PM

AlwaysOn Live Deployment

Ryan Adams

Thunderdome

05:15 PM

Raffle

SQLSaturday 362

Thursday, December 18, 2014

PASS – Connect, Share and Learn

The Professional Association of SQL Server (PASS) has a slogan for what they do – CONNECT, SHARE and LEARN. So, what does this mean to me and the SQL Server professionals I have meet in this community (#SQLFamily)?

 

image

First, I connected. Where did that start? SQLServerCentral.Com (SSC). I started reading articles on this site over a decade ago, and made it the main SQL Server resource for me beginning a DBA career. Back then a DBA was someone who either did one of 3 task types (or any combination).

  • DBA Administrator – worked to keep a system up and running including Windows Admin(Clustering), SAN usage, Security and Permissions (ActiveDirectory) and Performance Tuning (Monitor System resources).
  • DBA Developer – wrote T-SQL for stored procedures, triggers and jobs as well as create databases, tables and indexes usually because you were also a developer of software
  • DBA Business Intelligence – ETL with DTS (now SSIS), cubes in SSAS (multidimensional) and reports in SSRS (it was Crystal Report writer for me because SSRS was not released and packaged with Visual Basic) in addition to creating databases, tables and indexes.

Reading articles helped me develop a talent in the administrator position I was in along with getting a MCDBA in SQL Server 2000 (now I have MCITP in 2005 & 2008). The CONNECT part came when I ask my boss if I could go to the PASS Summit in Denver (2007). He had the funds in our department’s budget and included himself in the trip. There, I watched sessions based on the titles and abstracts. I did not know many speaker names except those I saw write articles on SSC. Those main ones I recognized were the ones who answered lots of questions posted on SSC forums as well as Microsoft message boards.

The next year, I was able to attend the Summit in Seattle and this is where I really started to CONNECT. Before that year’s Summit, I watched Patrick LeBlanc start the Baton Rouge SQL Server User Group. Patrick was able to use the resources from PASS to get it started and the networking with local IT professionals started this CONNECT for me. The group was created after the .Net User Group was started in Baton Rouge.

The second Summit I attended got me started wondering about the SHARE aspect. 2 or 3 sessions I attended got me thinking “I can do that”. Now, it was not magic and I was presenting. The first presentations I start doing were every other week Lunch and Learns at work regarding upgrading SQL Server from 2000 to 2005. The first presentation outside of work was for the local SQL Server User Group about SQL Server monitoring with Performance Dashboards. It went well, and I received very candid feedback from employees I worked with. I have learned today that feedback (good and bad) helps me improve the presentations over time.

Getting involved with PASS, I have helped the local user group with presentations, volunteering for 4 SQLSaturday events and promoting SQLPass.org locally. The Data Architecture Virtual Chapter needed help and I became the Chairperson for 2 years and have been able to encourage another DBA to assume the role after I retired which led into a VC mentor role.

The last item is LEARN. Well, if you have read this far, you should already know that all of the things above have helped me LEARN SQL Server - connecting to get the right people to go to with questions and sharing which enhances my skills. They are all learning opportunities which help advance my career and skill set.

Friday, November 7, 2014

PASS Summit: Blogger Q&A with Board of Directors Executive Committee

On Thursday, I was invited as a blogger to attend an intimate conversation (Q&A) with the PASS Board of Directors: Adam Jorgensen (EVP – Executive Vice President), Denise McInerney (VP Marketing) and Thomas LaRock (President). I started to try to understand why I was asked, but gave up due to mental exhaustion from attending the PASS Summit 2014. It was a great event with just way too much to do.

I decided to fish the internet and other community people for questions to ask. They boiled down to info about the BA conference, BOD structure/commitment, location of Summit and inquire about growth of DBAs in our community.  There were other questions, but the hour ran out. But, all three assured us that if you (Us and Ya’ll all) have a question or concern about PASS, each is individually willing to talk one on one through emails or phone calls. They even gave examples of individuals in the community that talked one on one to resolve an issue or understanding without have to use twitter or a blog post be load. We talked some about being mature members (growing up).

BA Conference – Why was it started, who is pushing it and why will it continue?

Thomas LaRock used my career  - from developer to DBA to BI Architect to a want to progress to Business Analytics. I never even thought about that until he mentioned it. Microsoft re-branded its SQL Server area for us as Microsoft Data Technologies – adding what is happening to data in this time and age. These are new opportunities for us data people.

There was no direct answer to the “pushing”, but the word Microsoft came up here and there. They are booked for the BA conference for the next 2 years in Silicon Valley (Santa Clara, CA) where there is a pool of speakers and talent in this area. Microsoft knows we (PASS) do community well, so they want us to help develop the community around BA. Adam mentioned the boards intent is not to split the community, but bring us together.

Denise mentioned how the first PASS Summit was an initiation only conference and they are going in that direction this year to help us get some understanding of what BA is really all about and help start to grow the speaker pool. It made sense. (I just read Mickey’s blog and she mentions there are vendors/exhibitors available as well in this area).

Summit Location – It will be in Seattle for the next 3-4 years (2015-2018 I believe)

There was no answer if it will be in another location. The deal is booking an event like this is starting to get more difficult with convention centers booking 4-5 years out. The BA conference is booked for 2 years (I believe they said that was the original plan/commitment).

Mark’s Question – Talk about the Election problems and why it took awhile for a response

The board and HQ had to abide by PASS By-Laws and laws of the state of IL. After HQ talk will Legal and quorums (12 people needed) were available, then decisions could be made (By the way, I think the decision and action were on the mark). The quorum problem got me to follow up my question about the board structure and time needed to be active in the decisions).

There were many conversations before quorum about what was the right thing to do. Denise ave credit to HQ for the movement of the process.

Board Structure and Time Required

I was amazed when Denise said she spends sometimes 2 hours a day on average (most made up on weekends because of having a ‘job’) and sometimes up to 30 hours in a week a month before a conference. Director At-Large maybe 5 hours a week. It could be as low a 2 hours a week if not much is going on.

The By-laws have the details if you want to read them (that is what they told me), but they did an excellent job making it simple for me.

Each Executive Member (elected – EVP, President and VP-Marketing, and Past-President stands on committee) of Board servers 2 year terms (max 8 years). The other 2 are Microsoft appointed (CA vacated their appointees last year). I asked if they will be replaced and that is being looked at (for help on the Executive Board). The total Board of Directors votes on the 3 Executive Positions.

The opening for president has a requirement of serving at least one year on executive committee. This past election we voted on was for At-Large members of the board. There are something like 6-8 at-large members. Tim Ford was one of them that sat in on the Q&A. I am sure there is more, but I will stop there.

Bill G (Past President) is pushing the Exec Board to get more HQ staff to take load off.

Growth of DBA members of PASS

No growth, but about 1/3 are DBAs. I do not know what to think about this, so I might follow up after it sinks in a little more.

 

Please visit Mark’s site. I am sure he will have a blog on this session. - http://markvsql.com/

 

Side Notes:

All agreed that HQ is doing a fantastic job!!!

The board believes the best way to communicate items to the masses is up to HQs to make that call. The Board is trying to let HQ do the business work of PASS, while the board works on strategy.

The BA stuff is becoming a portfolio with a director (from current At-large board members).

LaRock was all about growth, which is still happening.

Wednesday, October 29, 2014

PASS Summit 2014

Well, it is that time of year again. The Professional Associations for SQL Server (PASS) annual summit. This year we go back to Seattle where Microsoft mostly lives and hope we can get lots of their employees to visit and talk shop.

image

One of my main goals is to Network and get contacts from around the SQL Server world, but I always find 3 new things to learn at the Summit to bring back to my job. There, I can show the value of going to this event to my boss(es). This year the first goal is to find new information about maintaining a robust Data Warehouse since this is my main job at eQHealth. The second is to learn about PowerBI and what can be done in the Cloud (yes, you heard that right I said Cloud). Finally, I need to learn about Column Store Indexes and other improvements in performance of queries. There is always something to learn in this area and many sessions to be a part of.

image

I fly in on Monday night, so I will miss the annual Networking event put on by Steve Jones and Andy Warren. If you are there Monday night, it is worth the time. Networking is a big deal at these events, so get out and start talking.

image

Tuesday, I will start the day with a visit with other PASS volunteers, then attend one of the many great Pre-Conference All-Day sessions with Alberto Ferrari – From 0 to DAX. Tuesday evening is the Welcome Reception and a Volunteer Party.

 

 

image

Wednesday morning I will start the morning at the Community Zone from 7:30AM to 8:30AM. Then, listen to the Keynote. The 10:15AM session will be with Reza Rad on Data Mining. I meet Reza last year and it was a pleasure talking and learning from him. Smart guy. At noon, I will be in the Virtual Chapter lunch as I am a VC Mentor and need to meet and greet our leaders. At 1:30PM, Jen Stirrup is talking PowerBI & R. Peter Meyers (one of the best presenters I know) will be presenting on Master Data Services is at 2:45PM, which might be helpful at my job. At the end of the day it is a toss up between Marco Russo – DAX Patterns and Jon Welch – Continuous Delivery of DW & DMs. Wednesday night is the Exhibitor Reception.

image

Thursday morning has another Keynote on The Cloud, followed by a 10:15 session by Reza Rad (again) on Query M Functions. I will probably have to leave early to man the Community Zone from 11:30-12:30 during lunch. Hosting a Lightening Round session is next at 1:30 which I am really excited about. I will probably have to chill out after the Lightening Round and take a leisure break at the community zone and just visit with people or end the day at Alberto Ferrari’s Adv Model w/ SSAS Tabular. Thursday night is the Community Appreciation Part – EMP Museum which is always fun.

image

Friday morning I present at 10:15AM – Excel 2013 Tips and Tricks for SSAS Multidimensional. Unless I am totally comfortable with the presentation, I will probably take it easy getting up in the morning, rehearse the presentation at the hotel and then end up in the Speaker Ready room and try to sneak in the presentation room to test everything. After that all bets are off, except to fly home late on a red-eye to get back to Baton Rouge.