What does this little check box do?

Ever wander around SQL Server properties and wonder what these little check boxes turn on? I do, and I get very tempted to check them. Here is one of those tempting little boxes that seems pretty handy, Use query governor to prevent long running queries.

Syntax

How Does it Work?

It’s simple. This option, available in SQL Server 2008 standard and forward, will prevent long running queries based on run time measured in seconds. If I specify a value of 180 the query governor will not allow any execution of a query that it estimates will exceed that value. Notice it says ESTIMATES which means it will be based on optimizer estimates and not ACTUAL run times. It does NOT KILL an actively running query after designated amount of time.  There is no worries for rollback scenarios or partial data.

CAUTION

This is an advanced option, keep in mind this is a server instance wide option. This will also effect your maintenance queries, so please use with caution, this is not “a let me check this box for fun” option.

But Wait There’s More

Now there is a query “transaction” based option available to us that will limit a specific query. This option will estimate a transaction and prevent it from running if it will go over the boundary we have set. Notice we set the limit before the query and then back to 0 after.

Again, playing with any old check box is not a recommended practice. Make sure you research it first and understand the full impact before checking that tempting little box.

Please Don’t Do This!

Please, please, please Admins do not leave your default index fill factor at 0. This means you are telling SQL Server to fill the page 100% full when creating indexes. This also means you are forcing it to a new page when additional inserts are done. These are called PAGE SPLITS which can take time to perform and is a resource intensive operation. Having a high fill factor will cause more index fragmentation, decrease performance and increase IO.

If you find that this is how your system is configured, all is not lost. You can correct this by changing the default value so that new indexes will be created with proper factor and rebuilding your existing indexes with another fill factor value. I like to use 80 across the board for most, of course there is always the “it depends” scenario that arises but 80 is a pretty safe bet. One of those “it depends” would be on logging table that has the correct clustering key and never gets updates in between values (make sense?), I don’t want a fill factor of 80.  I’d want 0/100 to maximize page density as page splits wouldn’t occur if the clustered key is monotonically increasing.

Note, having the additional 20% free on a page will increase your storage requirements but the benefit outweighs the cost.

Example syntax for changing the default

Example script for rebuilding in index with new fill factor

 

System-Versioned Temporal Tables

Every once in a while, I like to take a moment and learn something new about the latest SQL Server gizmos and gadgets. Today I came across system-versioned temporal tables and it peeked my interest, so I figured I’d investigate and share my finding with you.

How many of you have need to track data changes over time? I’ve needed this many times for things like auditing, investigating data changes, data fixes, and trend analysis of values over time. Having to do this is the past has been a very daunting task at times and sometimes nearly impossible. This is where system-versioned temporal tables will really help out. They have given us a new way to do just that with a new user table type. It keeps a full history of those data changes and gives us a way query it order to do point in time analysis. What I really like about this is that you can’t INSERT or UPDATE data into the datetime columns as they are automatically generated with the insert, which is great for auditing.

Syntax for Temporal Table Creation

Note we now have 2 required datetime2 fields that will be populated with our temporal history data for each row.

Lets insert some records using INSERTS and see how the data looks.

This great illustration from Microsoft shows just how the history is tracked. For each INSERTED record, the SysStartTime will be populated in our BeginDate field. Each additional UPDATE/DELETE/MERGE our current record is copied to a history table and EndDate is updated with SysEndTime.

How do you query it?

It uses a new clause FOR SYSTEM_TIME that you can now query using it combined with AS OF, FROM TO, BETWEEN AND, CONTAINED IN, ALL

Results

Note the current record has end date of  9999-12-31 23:59:59 because its the current state of the record. It hasn’t been modified yet so it gets the default future datetime.

If you are lucky enough to be using SQL Server 2017 I highly recommend playing around with this new gadget, it may be of significant use to you.

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.

TIL: Microsoft Azure Part 2

Last week I started a multi-part series on Today I Learned (TIL) about Microsoft Azure.  This is part two of what I am learning in Azure.

Today’s topic is simply about Tenants, Subscriptions, Subscription Roles, Resource Groups, and Tags.

It’s Always Good to Start with Pictures

Here is a glimpse of how these topics relate. I will define and explain each below.

What is a Tenant?

In simplest terms, a Tenant is container for multiple subscriptions. An example of two subscriptions would be Azure and Office 365. They would be owned by one account, an individual or a company. A very large enterprise may use multiple subscriptions to better manage billing between divisions.

What Are Azure Subscriptions?

Basically, it’s just an ownership account. Think of it as just creating a billing and usage management account, whether it is a personal subscription or an enterprise level. The account allows you to group and manage multiple subscriptions for billing and reporting.

A subscription can encompass a mix IaaS, PaaS and SaaS services.  All subscription management, reviewing billing reports, and creating new subscriptions can be done through http://account.windowsazure.com site, but you need to be an account administrator.

How Do I Get Subscriptions?

You can get them through a Trial, MSDN, Pay as you go using a credit card, Azure Resellers (called Cloud Solution Providers or CSPs) or Enterprise Agreements.

What are the Subscription Server Roles?

Microsoft offers roles based on “Least Privilege” within Azure at the subscription level. There are several roles that secure the access to your cloud environment. These three main accounts below are all very powerful accounts and should be limited to only a few.

The top role is the Account Administrator. Think of this account in terms of what Enterprise Administrator is in your on-premises Active Directory. The Account Administrator has full rights. They have access to the account’s full financials and billing information for all subscriptions within the account, they can also create, delete and modify subscriptions.

The next role is the Service Administrator. This role is like the Domain Admin. It’s one level down from the account administrator and has full rights to the services in the subscription. They can do everything an account administrator can do with few exceptions, such as viewing the billing details of the subscription.

There is also the role of a Subscription or Co-administrator. This role is like System Admin(SA) in SQL Server.  This role can create and delete resources within the subscription but has no control over billing or the ability to change the authentication source such as AD.

The three accounts above control the Role Based Access (RBAC) for the rest of the users accounts on a resource level. They can assign users or groups of users, the rights to manage only the resources they need for their particular roles. These are roles such as Owner, Contributor and Reader of a resource group.

What’s a Resource Group?

A resource group is a container that separates resources into groups. Things that can exist in this container are things like VMs, NICS, Storage, Web Apps, SQL and Virtual Networks (VNETS). The “objects” within a resource group can be created, updated, and deleted as a group. One easy example of a resource group can be a development environment, all parts associated to that environment are contained in that in resource group.

What is a Tag?

The next granular level of organizing are Tags. These allow for adding your own meta-data to objects in Azure. Think of these as labels or categories for reporting and organizing things like billing. For instance, if the resource groups within an ERP environment are tagged as “ERP”, then those resource groups would get categorized together for management purposes. If you’ve ever used extended properties in SQL Server this is the same basic concept. There are however limits to the amount of tags an individual resource can have, which is currently 15. Your Azure billing statement is grouped by tags, which makes this almost a mandatory feature.

Summary

In this part we covered Tenants, Subscriptions, Subscription Roles, Resource Groups, and Tags. Hopefully you got a basic understanding of each and how the relate to each other. Next, I will dive a little into the differences between Azure SQL Database and SQL Server on IaaS.

 

TIL: Microsoft Azure Part 1

I thought maybe it would be a good idea to start a multi-part series on Today I Learned (TIL) about Microsoft Azure. As part of my new job I am currently learning as much about Azure as possible. As I learn things, I will blog to share what I am learning. It will cover beginner level things initially and gradually progress to more advanced topics.

Today’s topic is simply…. What the heck is Azure, how do I get to it, and what is the difference between IaaS, PaaS, and SaaS?

What is Azure?

According to Microsoft. “Microsoft Azure is a growing collection of integrated cloud services that developers and IT professionals use to build, deploy, and manage applications through our global network of data centers. With Azure, you get the freedom to build and deploy wherever you want, using the tools, applications, and frameworks of your choice.”

How do I get started in Azure Portal?

MS has a great walk through you can do to get you started. There is a free 30-day trial you can utilize to play around with along with $200 in Azure credits. I highly recommend getting an account and clicking through everything just to get the feel of all the offerings it has.

http://account.windowsazure.com

What is the difference between IaaS, PaaS, and SaaS?

You may have heard or seen the acronyms IaaS, PaaS and SaaS. Well what are they? Let’s start with their definitions and then how it pertains to SQL Server.

What is IaaS? (HOSTING)

Infrastructure as a Service or IaaS – Microsoft provides infrastructure capabilities such as an operating system, storage and network connectivity in a cloud offering. Basically, it’s the same as you would have on Premises, Virtual Machines and all its requirements to run your applications. You are able to install software such as SQL Server (aka SQL Server in IaaS) and configure as needed. They host your applications and workloads just as you normally have used, only difference is that it is in the cloud (their data centers). This is very similar to the concept of using a co-location facility (CoLo) data center to store your servers, only with a lot more automation and features. One of the biggest benefits being that you do not have to maintain the underlying hardware or data center.

It’s like asking a Network\Storage administrator to setup a virtual machine for you and you can decide on all the requirements you want. Such as I need 5 drives with X amount of storage on certain types of disks, and this many CPUS.

What is PaaS? (BUILD)

Platform as a Service or PaaS – This is the next level they offer in which you do not have control over the infrastructure and don’t install the software. That is all chosen (standardized) for you based on your “tier” requirements and the platform you need, such as SQL Server (aka Azure SQL Database) or MySQL/Postgres. I will cover more on these services in a follow up post. .

I think of PaaS as when you ask a Network\Storage administrator to give you a box to install SQL on and they give you a Templated VM with all it parts configured including SQL Server already installed. MS offers many different PaaS services – including Cloud Services, Websites, Storage and Azure SQL Database.

What is SasS? (CONSUME)

Software as a Service or SaaS – This simply put are things like Office 365. It’s applications that are consumed in the cloud, no hardware or software is maintained by the company. You just pay for the service and log in to the software essentially.

Summary

So, in Part 1, we’ve covered the basics of what IaaS, PaaS and SaaS means and how they can be leveraged. Next I will cover subscriptions and roles.  As I learn things I will continue to drop little tidbits like this, look for them over the next few weeks.

Blogging at SQLPerformance.com

I am honored to be a Guest Blogger at SQLPerformance.com. As a member of the SentryOne Product Advisory Council (PAC) I will be writing occasionally for the site. You can catch my first blog post It’s Not You It’s Me (I/O Troubleshooting) through simple changes.

SQLPerformance.com is about providing innovative and practical solutions for improving SQL Server performance. Whether you are running a 3rd party application database where very little can be changed, or you are a DBA at a site where getting the application developers to change anything is next to impossible, they cover both the “how” and the “why.”

Time for a Change

I am ecstatic to say I have joined Denny Cherry and Associates Consulting.

Lone No More

I am happy, excited, and nostalgic to announce that I am hanging up my Lone DBA hat and becoming a consultant. Yep, you read that correctly, I’ve decided after 16 years that I am going to change things up a bit. I am switching gears and will be helping those who are Lone DBAs and others by lending them a hand with their work loads.

Don’t get me wrong, I absolutely love being a Lone DBA. So, I will continue to speak on the topic and mentor others in that boat, but it’s time to give myself a little more freedom. Over the past 16 years I have been on call 24/7, even working while in labor, on vacations, nights, and weekends. I really think now is the time to slow down just a bit. My normal speed is 150 miles an hour so down shifting to 100 will allow me to spend time on what is important to me, my family.

Why DCAC?

First and foremost, the people. I am looking forward to working with and learning from Denny (B|T), Joey (B|T), and Kerry (B|T). These guys are wicked smart and most importantly know the importance of the SQL Community. They have a wealth of knowledge to share and I cannot wait to tap into it. With DCAC, I will be getting exposure to so many new environments as they are a renowned global provider of IT consulting and work on the cutting edge. I absolutely love learning new things and can’t wait to dive into things like Azure, which they excel at.

DCAC will give me the flexibility to speak and blog more which I genuinely want to do. Getting out into the community is also a strong passion of mine and moving out of the Lone DBA role will give me greater ability to do so.

Thanks, DCAC for bringing me on board, can’t wait to get started.

About Denny Cherry & Associates Consulting

The vetted and certified experts at Denny Cherry and Associates Consulting assist companies with attaining IT goals such as HA, scalability, SQL Server virtualization, migration and acceleration reliably, while finding ways to save on costs. With clients ranging from Fortune 50 corporations to small businesses, their commitment to each is the same: to provide a deft, high-speed IT environment that leverages every aspect of their platform: from architecture, to infrastructure, to network.

DCAC was named by CIOReview’s 20 Most Promising Azure Solution Providers of 2016.

Among Giants

Since becoming a Database Administrator I’ve always looked at Microsoft MVP’s as the giants in our field.  I never once thought I could be among them. I am very humbled to be recognized as a Microsoft Data Platform MVP for 2017. Thank you to those that deemed me worthy enough to nominate me.

What is an MVP?

According to Microsoft, the MVP Award is an annual award that recognizes exceptional technology community leaders worldwide who actively share their high quality, real world expertise with users and Microsoft. Microsoft MVPs represent a highly select group of experts. MVPs share a deep commitment to community and a willingness to help others.

How did I get here?

There is no magic formula to becoming an MVP.  I blog, I tweet, I did a couple podcasts, I speak at SQL Saturdays, I run my local chapter, and I am a Regional Mentor, but that doesn’t mean you have to do the same.  The point is I try to give back to that community what they have given to me. That’s all it takes. I share what I know and just do my thing, somehow that worked and you can do it too.

What can you do to help others achieve this?

NOMINATE, NOMINATE, NOMINATE!  There are so many valuable members in the community that have not become an MVP simply because they have never been nominated. I’ve had some tell me that they thought I was already an MVP so never thought to do so. I think for a lot of us waiting in the wings for our chance this is the case. So take the time and nominate someone you deem worthy, whether you think they are already one or not.

Here is the link to do so.

https://www.mvp.microsoft.com/en-us/Nomination/NominateAnMvp

Thank You

While I said it above, thank you again to all of those who nominated and believed in me. I could not have done it without the support of the #SQLFamily all these years. I’m honored to be a Microsoft MVP.

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/