Thankful DBA

This week is Thanksgiving in the United States, so I thought it fitting to write a quick blog on what I am thankful for as a DBA. These are in no particular order and feel free to respond with something you are thankful for. I’d love to hear it.

  1. Glenn Berry’s Diagnostic Scripts- (B|T) Used these for years. Really a great set of scripts and explanations that we all should be grateful for.
  2. Ola Hallengren’s (BMaintenance scripts. Index Optimization, Backup, and Integrity Checks for all! They have become an industry standard and continue to get better and better.
  3. RCSI (Read Committed Snapshot Isolation) –My Readers can stop blocking Writers! Thanks to Kendra Little (B|T) for this great blog.
  4. SSMS Results to grid and copy with header- I do this a million and one times a day. Ctrl+Shift+C .
  5. Query Store – Having the plan run stats and being able to force a plan, LOVE IT! Thanks Conor Cunningham and Microsoft for that one.
  6. Availability Groups – Easy setup and trustworthy. And, well, I like the name better than Mirroring.
  7. DMV’s (Dynamic Management Views)- Show me the money! It has all the SQL Server Internals goodies, mine for the taking.
  8. Profiler– #ProfilerForLife nuff said, my most trusted friend.
  9. Columnstore Indexes – I feel the need, the need for speed! Who doesn’t like up to 10x Query Performance gains and 10x the data compression?
  10. Paul Randal’s Waits Library (B|T)– I can’t tell you how many times I’ve referred to this. So much useful information!
  11. Adam Machanic’s SP_whosisactive (B|T) – This is my GO TO, for seeing what’s actively going on, it’s the first thing I run.
  12. Sentry One Plan Explorer– Execution Plans on STEROIDS! Yes, please. Love the detail and ease of use.
  13. RedGate’s SQL Prompt- My coding is downright ugly. With a quick Ctrl+K, Ctrl+Y my code is sleek and readable. Not to mention I love the code snippets.
  14. Grant Fritchey’s (B|T) Execution Plans book- I can’t wait for 3rd Edition, someone took my very loved highlighted, tabbed, marked up copy. I need another!
  15. Power BI – It puts the slicing and dicing into the user’s hands, giving Management easy visualizations of their data for analysis. Less reports for me to write, yippie.  Thank you Microsoft.
  16. dbatools – Great Power Shell Modules for migrating databases. No more doing it the hard way.

Last and most importantly I am grateful for #SQLFamily, Bloggers, and Twitter. I learn from you every damn day!

Happy Thanksgiving!

~Monica

Quick Model Database Tidbit

Are you using your Model Database to its full potential?

I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all.

What is that Model Database for?

The model database is basically the default setup (template) for all other databases created on a SQL Server instance. All databases created after install will inherit the properties of this database.

Why Configure It?

Using the model can insure consistency within your environment and is a quick way to automate your database setups. Below is a list of things I’ve used in my environments and others.

Top (in no particular order) Settings I have Implemented Through Model

  • Default Growth Settings
  • Query Store Settings
  • Recovery Models
  • Read Committed Snapshot Isolation
  • Allow Snapshot Isolation
  • Auto Update Statistics Asynchronously
  • Compatibility Levels

Now there are some things that databases will NOT inherit from the model, some of these I learned the hard way.

  • File Groups
  • CDC (Change Data Capture)
  • Collations
  • Database Owner
  • Encryption

Scripts to turn these options on

What Other Things Can You Do?

Now, you can go above and beyond just the database properties. You can add tables, views, triggers, functions etc. to your model database and every time a new database is created those objects will also exist. Why is this useful? In the past, I’ve used this for tracking my DDL (data definition language) changes. I created a trigger that would insert into a table the user, object, date and time, text snippet of any ALTER\DROP\CREATE statement that was run on a database. For it to work, the trigger needed to exist on all databases.

Final Words

We all know each environment is different, so don’t just go and implement everything, tailor it to your needs. I suggest you take a look at yours and see if there is anything you can adjust. You may be surprised on what you can tweak.

Note:

*In testing this, I have found that if you create a new database using CREATE DATABASE with T-SQL the Auto-Growth sizes do not get inherited by new database, but everything else did. If I create new database using GUI these setting do propagate.  Not sure if this is by design or a bug.

Synchronous VS Asynchronous Statistics Updates

One of the things I’ve been able to implement to help with performance is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. It’s a simple change that can have a big impact when implemented in highly transactional OLTP environments. Notice I said OLTP not OLAP, since data in an OLAP environment tends to not be as dynamic, so it’s rare to enable this in a data warehouse.

So, what’s the difference between the two and why does it help?

Synchronous (defaulted as AUTO_UPDATE_STATISTICS =TRUE)

By default, when Auto Update Statistics is set to True, the SQL Server Query Optimizer will automatically update statistics when data has met a threshold of changes (insert, update, delete, or merge) and the estimated rows are now potentially stale. When statistics are stale, execution plans can become suboptimal which can lead to degradation in performance.

This best practice option ensures your statistics stay up to date as much as possible. Each time a cached query plan is executed the Optimizer checks for data changes and potentially generates new statistics. This behavior is exactly what we want, but there is a catch. The caveat to this is that a cached query plan will be “held” while the statistics are updated and will recompile to use the new values before running. This caveat can slow down the execution process dramatically.

Auto Update Statistics Asynchronously (AUTO_UPDATE_STATISTICS_ASYNC =TRUE)

This option does the same thing as the above, but with one significant difference. It allows the Optimizer to run a query and then use the updated the statistics. Where this option differs from synchronous is that a query will NOT be “held” while the statistics are updated. Queries can run “as is” until the query optimizer completes the statistics updates and then the query will recompile to begin to use them the next time it runs.

Confused Yet, so now in English. 

When the Asynchronous setting is set the query will run like it is until all statistics its uses are up-to-date, then it will run with the new numbers. It does not have to wait for all the new numbers to be updated to run. That’s where you get your performance boost, by not having to wait.

Check your settings using TSQL on ALL Databases

How to Turn it on TSQL

GUI

Under Database Properties > Options

NOTE: To enable this option Auto Update Statistics must be left ON.

Last Words

Remember every environment is different be sure to test this before implementing into production. A simple change from synchronous to asynchronous can make a difference.  It is definitely something to add to your performance tuning tool belt.

Does Your Code Have a Preamble?

Okay, here is a pet peeve of mine, I think every stored procedure, function, view etc. should all contain a block of code I refer to as a preamble. If yours doesn’t I strongly recommend you start adding it. It drives me crazy when I see code with no documentation of any kind telling me what it is for and when it was written or changed.

Why? A preamble documents the use, need, and changes for the code. It also leaves bread crumbs as to how why and what you did. I don’t know about you but I may code something and not have to change it for two years. When I do, I then think back and say why did I do that or who changed this code last. Working as a lone DBA, leaving bread crumbs was critical as I constantly jumped from task to task.

Above is the example of my preamble I use for all code I write. It tells who wrote it, what it is, what it is called by, how to run it, and lists any changes done to it.  I find one of the most helpful items on this is the Run documentation.  Here I place an exact run statement. It will show how the parameters should look and gives me a quick way to test it.

There are a million and one reasons as to why you should be doing this in your code. If you’re not doing it just take a second and start doing it. You’ll thank me for it later.

Just Check ALL the Boxes

Today I ran into something on a client server I unfortunately see too often.  The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean. So unfortunately, that means they take the CYA (cover your ass) approach and they check all of them. Now, not only have I seen this with alerts but also with things like security configurations as well. My advice is to always in to take a second and research what each option is before you check the little boxes, especially when it comes to security. Always follow the rule of less is more.

In the example below the administrator enabled alerts for an operator using the CYA approach. They checked email, pager, and netsend.

So, what’s the big deal? This server experienced an insufficient resources (space) alert that fired every minute and by having PAGER notifications enabled it caused the error log to bloat, consumed unnecessary space, and created noise in the logs.

The administrator of this environment really only needed to configure the email notification, as the company did not use netsend nor have pagers duties configured. To be honest, I have yet to see an environment use more than that, and per Microsoft both Pager and Net Send will be removed in future versions.

So, the morale of the story is, please take the time to research what the little checkboxes are before you enable them. The example above is a pretty benign one, but you can imagine what kind of messes you can get yourself in for other more critical things like security.

A Side Note:

If you want to learn how to setup your alerts and operators I’ve already written a blog on that with scripts you can find it here.

You can also visit github.com/dc-ac for a full install script that includes the Alert and Operator setups https://github.com/DC-AC/SQL2016_Scripted_Install

VLFs the Forgotten Foe

How many of you check the amount of Virtual Log Files (VLFs) your transaction logs have?

Working as a consultant now, I see this as something that is often ignored by DBAs.  This is an easy thing maintain and yet so many don’t know how to. Keeping these in check can give you a performance boost not only on startup but with your insert/update/delete as well as backup/restore operations. SQL Server performs better with a smaller number of right sized virtual log files.  I highly recommend you add this to your server reviews.

What is a VLF?

Every transaction log is composed of smaller segments called virtual log files. Every time a growth event occurs new segments, virtual log files, are created at the end of your transaction log file. A large number of VLFs can slow things down.

What causes High VLFs?

As transactions force growth of the log file, inappropriate log file sizing or auto-growth settings can cause a high number of VLFs to occur.  Each growth event adds VLFs to the log file.  The more often you grow in conjunction with smaller growth segments, the more VLFs your transaction log will have.

Example

If you grow your log by the default 1 MB you may end up with thousands of VLFs as opposed to growing by 1GB increments. MSDN does a great job on explaining how a transaction logs work for a deeper dive I recommend reading it.

How do I know how many VLFs my log files have?

It’s very easy to figure out how many VLFs you have in your log file.

Make sure you are on the context of the database you want to run it against. In this case TEMPDB and run the DBCC LOGINFO command.

The query will return a result set of all LSNs created for that database, the COUNT of those rows is the amount of VLFs you have.

Now there are many ways you can get fancy with it using TSQL, so have fun with it. Write something that rolls through all your databases and gives you the record counts for each. There are plenty of useful examples on the internet.

The VLF counts should be under 100 ideally, anything above should be addressed.

*New for 2017 is a DMV that will give you an even easier way to get the VLF counts sys.dm_db_log_stats ( database_id ) .

How do you Fix?

These transaction log files should be shrunk until there are only two VLFs, then grown in chunks back to the current size.

  • Perform Shrink using DBCC SHRINKFILE

  • Regrow your log in an increment that makes sense to your environment. However, if your file growth is in excess 8GB it is recommended to grow in 8000MB chunks while manually regrowing the file. Your autogrowth should be set to a lower value. There is no set rule to what those values should be, it may take trial and error to figure out what is best for your environment.

Note: Growing out you log can cause a performance hit and block on going transactions, be sure to perform this during a maintenance window.

It’s that simple, now go take a look at your files. You may be surprise on what you find.

Lone DBA Podcast

I recently had the pleasure of being a guest on a Podcast episode with the SQL Data Partners Carlos Chacon (B|T) and Steve Stedman (B|T).  If you haven’t had a chance to attend one of my sessions on Survival Tips for the Lone DBA, this is great insight into it. I share via questions and answers how it is to be a Lone DBA.

http://sqldatapartners.com/2017/03/28/episode-89-lone-dba/

Run Book, Run!!!

run-bookHow many of you actually have a “Hit-by-the-Bus” handbook? What is that, you ask? It is a document that explains how to execute all your jobs and SSIS packages. In addition, I preference mine with all key elements someone might need, like where passwords are stored, architectures, backup times, where are backups stored, etc… then dig into the job steps. The purpose of this document is so that someone with some SQL skills could step in if needed. You never know when you will be hit by a bus or win the lottery and someone has to take over for you.

Important things to note:

  • Step by Step with Pictures
  • Diagrams – Pictures are worth a thousand words
  • Plain English— Do this, then this, because of this, and watch out for that
  • Jobs- Rerun information, what to do if fails, what not to rerun when
  • Make a HARD Copy

Here is an example:

SERVER NAME

JOBS

LoadEDIDataandValidate: Imports a file \\EDI_FTP\CUSTOM_HOLD_RELEASE\EDI35020110908.log of EDI records that were sent from Gentran to Server A and Server B. It then validates that Server A and Server B have posted those records to their systems. Alerts are sent when something does not post with 15 minutes or record is in QUEUE status on Server B for more than 60 minutes. Server A and Server B data are kept separate on purpose do not combine those tables. As of 3/9/2015 It also sends out a TXT message if count is >50 that have not been posted.

Schedule: Runs Daily every 15 minutes between 2:16 and 11:21 am. This corresponds with 15minutes after Gentran begins and ends its daily processing.

Steps: Executes SSIS Package EDI350Import.dtsx and executes 2 stored procedures; jobValidateEDIServerAEDI350ServerB and jobValidateEDI350

Rerun: Can be rerun any time. Right Click on Agent job and Choose Start Job at step… There is only one.

sample

Here are some other examples of rerun information (try to be a clear as possible:)

Rerun: Can be rerun prior to 4 pm. If run post 4pm you’ll have to manually change the date (@pdate) of the data being pulled. Always verify no partial data was brought into table before rerunning clear out any data loaded.

Rerun: Do not rerun. Load the data manually to Server X for any missing data and use date_billed as key field for data pull

Rerun: This job will fail if there is a duplicate XXX number. You’ll need to resolve the duplicate before you can successfully rerun. It can be rerun prior to 4 pm. If run post 4pm you’ll have to manually change the date (@pdate) of the data being pulled. Always verify no partial data was brought into table before rerunning clear out any data loaded.

Why Share My Knowledge?

Don’t try to build job security into what you do. I know many that worry about giving up the knowledge to others. Having the sole “how to” knowledge for some, gives them a sense of job security. While to a point that might be true, it also locks you in to your current position. Many that hoard their knowledge never advance because they find themselves invaluable in their current position. “We can’t move them because they are the only ones who know about such and such”. Why put yourself in that position? If you can’t ever be replaced, you also can’t move up.

As a lone dba, I find this run book to be vital. It allows me to direct someone to the book and I can walk them through running anything I need them to in my absence.  It allows me to take a vacation or a day off while giving others the tools to get things done.

Why is it important to have a hard copy?

I’ve found over the years having some tangible steps in hand to follow and make notes helps those who have to cover for me. It’s very easy for them to grab a book off my shelf and follow step 1, 2, and 3. It also gives them a place to take notes as they go through the steps that I can later use to modify documentation for better clarity.

If you don’t have a run book I highly suggest you take the time to make one. Now keep in mind a run book is only a helping guide. I automate as much error handling as possible and build in code to minimize the use of this of this book.  However, in my opinion it is invaluable.  The book can give you some space for someone else to cover for you and when that day comes when you win the lottery, you will have left everyone with great notes on how to run things.

Now, off to buy that lottery ticket. Wish me luck!

This Idera ACE Has Been Busy

This year has been a whirlwind so far, thanks to the Idera ACE program. For those that don’t know what that is …

What is an Idera ACE? (According to Idera)

ace

“ACEs (Advisors & Community Educators) are active community members who have shown a passion for helping the community and sharing their knowledge. We help the ACEs pursue that passion by sponsoring travel to select events and offering guidance for soft skill training.”

Requirements to become an Idera ACE:

  • Enthusiastic members & leaders of the SQL community
  • Accomplished contributors to the SQL community
  • Good speaker, writer and presenter
  • Demonstrated a passion for educating fellow community members

Being an ACE has been both a very busy and very rewarding experience for me. Idera has given me the means to be able to share my knowledge as a Lone DBA and help others who are also in this predicament make the most of it. Since October last year, thanks to the generosity of the ACE program and the exposure it has given me, I have started my own blog, presented at a total of 9 SQL Saturdays, and 2 User Groups. I have also hosted 2 Idera #SQLChats on Twitter (links below) and participated in a SQL Hangout with Cathrine Wilhelmsen (B|T).

hangoutSo far, I have given my Lone DBA session to over 200+ SQL professionals, tweeted in SQL topic specific Idera #SQLChats to with a combined over 600 tweet interactions and had 200+ views on a video chat SQL Hangout.

One of my biggest talking points I try to convey is the power of networking and getting “virtual co-workers”.  Making those connections with others in the community is vital when you are a Lone DBA. I speak on the importance of building those relationships with those that can help you with their experience and expertise. Being an ACE has allowed me to vastly grow my network of “virtual co-workers”, by letting me travel to so many SQL Saturdays. I’ve had the pleasure in meeting so many speakers and attendees.  I make it a point at each of these events to make new co-workers and offer up any help I can give others.

The biggest reward for me is after my session is when attendees do their homework. Yes, I assign homework.  During the session, I ask each attendee to take advantage of what the SQL community has to offer by getting on Twitter and begin growing their own personal network.  Usually within a few days, many of them have created a Twitter account and has sent me a tweet.  I then take the opportunity to introduce them to the #sqlfamily.  I get a kick out of sitting back and watching each of them get involved in the community because me. It makes me giggle every time.

Of course, all good things must come to an end.  My year as an ACE is wrapping up in the next few months and I just wanted to take a minute and say thank you to Idera for a wonderful program. I encourage everyone to take full advantage of these types of programs and make the most of what they have to offer. I urge those that do, to not only take advantage for themselves but also to pay it forward. Give back to the community in any way you can. We can all benefit from each other with our shared experience and knowledge. The ACE program has really motivated me to get more involved and contribute to the #sqlfamily.

Stay tune to what comes next for me.

SQL Saturdays

 Washington DC

ABQ, New Mexico

Richmond, Virginia

Atlanta, Georgia

Pensacola, Florida

Louisville, Kentucky

Kansas City, Missouri

            User Groups

Richmond Virginia

Nashville Tennessee

            SQL Chats

Building Name Recognition

Building Your Career as SQL Developer or DBA

 

Back to Basics: Why not parameterize?

I think sometimes those of us that have been doing database administration/development for a while take it for granted that everyone knows the basics. One such basic is parameterizing stored procedures. This allows us to potentially consolidate multiple stored procedures into a single procedure.  It’s as simple thing to do that many don’t.

I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it be a sort, a where clause, or even just an extra field or two that makes it different, some developers think you need a different procedure for each one . Why not consolidate and parameterize?

Exhibit A

The code below is an example of a real work scenario.  Originally, it was 8 stored procedures and with 8 correlated reports. By simply adding a Report Type parameter I was able to make it one stored procedure and as well as consolidate to a single report.

To add a new dataset just right click on Datasets and choose Add Dataset. Since the report is a stored procedure we set the dataset connection string to the stored procedure name and its parameters. This is just my preferred method. You can also choose the stored procedure from the drop down.

rep

rptTrackMonthlyStats @ReportType, @year, @startdate, @enddate

rp

In the Report Type parameter, choose add Available Values. I typed in each option so the user could choose which report layout/data they wanted to see from drop down. That parameter will be passed to the stored procedure upon execution and the proper dataset will be returned. The users will never see the T, TD etc. they only see the label so it doesn’t make any difference to them what those are.

Parareport connectiom

You can even go as far as using these parameters to hide and show different report elements, but that’s for another time. Stay tuned for more back to the basics.

NOTE: There are some reasons not to do this, like the reuse of the execution plans and parameter sniffing but in these cases consolidating would not be an issue as they use the same parameters.