Review: Performance Tuning with SQL Server DMV’s

Posted on November 3, 2011 by Mr Andrew Dykstra

This month’s book review goes to Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson and Tim Ford.

This is the first technical book I purposefully set out to read from cover to cover.  I picked the book for a numbers of reason.  For one – I owned it.  That’s a good start.  I’ve also met both authors, respect their work and contribution to the SQL Server community, and I have a desire to learn more about Dynamic Management Views.  With regards to SQL Server, my two points of interest are administration and business intelligence.  This book fits squarely the administration category for me.

Davidson and Ford took what I think is a brilliant approach in this book.  They broke up the DMVs into six groups with each group addressing a different lay or aspect of tuning SQL Server.  They are as follows:

  1. Connections, Sessions and Requests
  2. Query Plan Metadata
  3. Transactions
  4. Indexing Strategy and Maintenance
  5. Physical Disk Statistics and Utilization
  6. OS and Hardware Interaction

These are also the chapter titles for chapters two through seven, chapter one being a summary introduction for using DMVs.  This approach kept a strong focus through the book, working from the outside (connections) to the inside (SQL OS) of SQL Server.

In each chapter Davidson and Ford name relevant DMVs for that area of SQL Server and highlight important columns found in those DMVs.  This is to be expected of any book on DMVs.  Where I feel Davidson and Ford distinguish themselves with this book is by providing usable sample scripts and examples of when and why these DMVs would be useful.

I recommend this book for anyone wanting a better understating of DMVs and their usefulness.  “This book is here to de-mystify the process of collecting the information you need to troubleshoot SQL Server problems.” And that is does.

Posted in Bonus Post | Leave a comment

Picking A Good Line

Posted on October 27, 2011 by Mr Andrew Dykstra

No, not a pick up like.  No, not a good consulting line.  Not even a good excuse to go to PASS Summit or on SQL Cruise.  I’m talking about planning for the growth and well being of your data environment.

One of my favorite recreational activities is mountain biking.  A group of friends brought me along on a ride while I lived in Australia and I fell in love with the sport.  I’m not super extreme, I don’t jump over roads and now that I’ve got two young children I rarely get out to ride.  In fact, I think I only did about 50 miles this year.  Considering my first year back in the states I did well over 500 miles, that 50 miles looks pretty small.  But the fact still remains I love to ride.  One of the best mountain biking techniques I was ever taught was how to pick a good line.

Pick a What?

Surely there’s a better line than this.

When mountain biking, a ‘line’ refers to the path you chart for yourself through obstacles, over obstacles or around corners.  It’s basically where you’ve decided to put your tires as you ride.  Picking a good line means a smooth, fast and enjoyable ride.  Picking a bad line could mean anything from a slow, bumpy ride to falling off a cliff.  There are a number keys to picking a good line such as knowing your equipment and knowing your individual skill level.  The key I want to highlight is getting the most out of your vision.

The central vision is what can pick out the most detail.  When mountain biking, I use it to pick where I want to ride - my lines.  The farther ahead I can get myself to look down the trail, the better I can pick my lines.  The peripheral vision is brilliant at addressing immediate obstacles and coordinating my body to stay on track with the path my central vision picked out.  Most people I know, myself included, have to work on keeping the central and peripheral visions separate.  Most people want to use their central vision to watch the obstacles right in front of them rather than farther out so they can build momentum.

Distance Counts

So here’s the connection.  I monitor file growth.  I use that data to tell the future.  The better I do that the less surprised my boss is when I say I need another $xx,000 to increase storage.  My boss is less surprised and has time to prepare.  I just picked a good line and made my ride smoother.  I monitor slow running queries.  I start seeing cursors and stars in select statements.  I use the data to tell the future.  I plan a training event for the dev team or casually slip a flier for the next SQL Saturday to the lead developer.  They get free training, I get better queries and we have something in common to build on.  I just picked a good line and made everyone’s ride smoother.  Last one.  I attend free training events, subscribe to RSS feeds of my favorite DBAs, developers, mountain bikers and comedians.  I’m happier.  I learn about Denali SQL Server 2008 R3 SQL Server 2012.  I make a plan for rolling out…  you get the point.

What lines are you picking?  Are you using the data around you to make forecasts?  Are you planning for growth or change?  Have you picked out a couple solutions for hurt points?  Are you reading that book that’s been sitting on your coffee table to 3 months that could bring your career to a new level?  Use your central, detailed vision to pick good lines and made your journey smoother, faster and more fun.

Posted in Planning, SQL Server | Leave a comment

The Spirit of the Community

Posted on October 6, 2011 by Mr Andrew Dykstra

I just had a conversation at the last Colorado Springs SQL Server User Group meeting about how the SQL Server community is different, about how the community gives, about how much everyone in the community wants to see other people succeed.  After going dark for way too long, I am happy to see that the spirit of SQL Server community is stronger than ever.  What is the spirit of the SQL Server community?  Generosity.  And it runs deep.  Don’t believe me?

Read the Blogs

Blogs are full of free help, free insight and free solutions.  Many SQL Server professionals share their scripts, methods, findings and troubles on their blogs.  Why?  In addition to being a type of online resume, blogs are a way to give back.  I could list dozens of blogs the exemplify information sharing and giving back, but there would be too many I would miss.  A good place to start, which is also a blog that should be on the list, would be here or here.

Go to an Event

It doesn’t really matter which event you choose, just pick one and go.  There are local user groups, an online event such as 24 Hours of PASS, a SQLSaturday, a SQLRally, a SQLCruise or the PASS Summit. Why go?  To experience the spirit of the SQL Server community.  It will be at all of those events.  Don’t get me wrong, it’s not in the event.  It’s in the people at the event.  Listen to the way people talk.  Listen to the way they ask questions.  They’re engaged, they care.  Why?  They’ve been there.  Someone has helped them before and they’re grateful.  They want to return the favor and help someone else.

Read the Hashtags

For starters, #SQLHelp.  Have a question?  #SQLHelp.  Have an answer? #SQLHelp.  The latest hashtag and the reason for my writing is the #SQLGive hashtag.  One of the most consistent bits of feedback I hear about the PASS Summit is the number of homeless in Seattle.  This year, we’re giving back.  Bring unused/unopened toiletries, clothing, non-perishable food items and more to the Perpetual Technologies  in booth #516 or SQLSentry in booth #512 (see the map here).  Kendal Van Dyke (blog | @SQLDBA) teamed up with a few volunteers and Union Gospel Mission make a way for everyone at PASS Summit to give back.  Take a few minutes to when you’re packing or when you get to Seattle and consider what you can give.   Join the community, be generous, give back.

Posted in Community, SQL Server | Tagged , , | Leave a comment

Where to Put Your Package: Part 2

Posted on October 4, 2011 by Mr Andrew Dykstra

This post is a continuation from my first post on SQL Server Integration Services (SSIS) package storage.  I’ll finish up by covering Backup and Execution considerations.

BACKUPS

Missed backups?  Carrying on from security, how will SSIS packages be backed up?  How critical are deployed SSIS packages?  For a file system deployment, SSIS packages are backed up the same way any other file or folder is backed up.  Copy to an external hard drive, thumb drive or CD.  Maybe with backup software.  If the scheduled backup of that server is considered acceptable double check to be sure what that schedule is, that the SSIS packages are actually included in the backup and what the restore plan is in case a package needs to be recovered.

Backing up the msdb database is, well, backing up the msdb database.  As DBAs we’re taking regular backups of our system databases.  So have another look at the backup and recovery plans for the system databases and the msdb database in particular.

EXECUTION

Finally to package execution.  There are three basic methods for executing SSIS packages; using a GUI, agent job or command line.  Using the GUI, SSIS packages can be executed from either management studio (SSMS) or the dtexecui.exe utility.  An added benefit to the dtexecui.exe utility is it will generate copy-pasteable code.  This code can be used when creating an agent job that can be scheduled and run as any other agent job.  When using file system storage, the code generated in dtexecui.exe can also be used to execute a package from the command line (dtexec).  Put that same code in a batch file and the package can be executed using a Windows scheduled task.

SUMMARY

The things to consider when selecting where SSIS packages are more of the same.  What storage options are available?  Is the security adaquate?  Are the packges able to be backed up and restored? And finally how will the code be executed?  These are questions data professionals ask and answer everyday.

Posted in SSIS | Leave a comment

Where to Put Your Package

Posted on October 27, 2010 by Mr Andrew Dykstra

I know, I know… now stop giggling.  This is part one of a presentation I did recently for my local SQL Server user group.  We’ve added a 20 minute beginner level presentation to the meetings and that’s where this presentation fits in.  The idea is to walk through basic security, backups and execution of SQL Server Integration Services (SSIS) packages with respect to where the package is stored.

STORAGE OPTIONS

The Two Tribes of SSIS

There are two places to store SSIS packages; File System and the SQL Server msdb database.  Neither is a bad option, necessarily, as long the basics are covered.

With File System storage, you’re basically saving you SSIS packages as .dtsx files in a folder on your SSIS server.  This can make deployment of a package very simple.  If the package was built in Business Intelligence Development Studio (BIDS) it can literally be dragged and dropped from the BIDS project folder to the C:\Program Files\Microsoft SQL Server\[version#]\DTS\Packages folder.  This is the default location for SSIS packages.  For SQL Server 2005 the version number is 90 and 100 for SQL Server 2008.  Other package deployment options are the package deployment utility generated from BIDS or the Import Package wizard available in SQL Server Management Stuidio (SSMS).

The other option for package storage is in the SQL Server msdb database.  First off, this requires SQL Server to be installed on the machine SSIS packages will be deployed to.  In SQL Server 2005 SSIS packages are stored in the msdb.dbo.sysdtspackages90 table.  SQL Server 2008 stores them in the msdb.dbo.sysssispackage table.  Both versions of SQL Server store the actual package with the image data type.  There are two methods for getting packages into the msdb database: the package deployment utility from BIDS or the Import Package utility from SSMS.  No drag and drop here.

SECURITY

Don’t get all excited and start dragging and dropping packages into file system storage just yet.  Let’s have a look at some security considerations.  File system storage only uses Windows security.  Straight ahead Windows domain or local users and groups.  This is the same as securing any other file or folder.

The msdb database on the other hand uses SQL Security.  First off users must have access to the SQL Server instance.  After that, the msdb database. Finally once granted access to the msdb database there are three security roles that can be assigned to a user account.  MSDN does a good job of explaining them here.  So here’s the summary of each role:

  • db_dtsadmin can do anything to anypackage including granting or revoking permissions.  The sysadmin role has this same power.
  • db_dtsltuser can SELECT, INSERT, UPDATE, EXECUTE and DELETE their own packages.
  • db_dtsoperator can SELECT and EXECUTE any SSIS package

On a side note, Windows administrators are able to view and stop any currently running SSIS package.

In part 2 of this post I’ll cover backing up your SSIS packages and touch on execution options.  If you were at my presentation please head over to speakerrate.com and give me feedback or just use “rate it” button below.  Also, you can download the slide deck from the presentation here: Where to Put Your Package.

Posted in SSIS | Tagged , , | Leave a comment

I’m Not in Kansas Anymore

Posted on October 1, 2010 by Mr Andrew Dykstra

I didn’t start in Kansas either. Yesterday Chris Shaw (blog|twitter), Marc Beacom (blog|twitter), Jason Horner (twitter) and I climbed in Chris’s F-250 super duty, turbo charged, diesel truck left on a SQL Road Trip from Colorado Springs/Denver to  SQL Saturday #53 in Kansas City.  Chris asked me a couple times if I was tired of talking tech.  Of course the answer was no.  Great conversation made for a quick 11 hour commute.  In all I’m looking forward to tomorrow’s event, meeting new people and writing in the cool, not-so-Kansas, not-so-mountain, morning air.

Posted in Bonus Post | Tagged , | Leave a comment