John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

John McCormack's blogs

Your Azure SQL Database and Managed Instance is too big

15th January 2021 By John McCormack Leave a Comment

Don’t buy for the future

With the cloud, it’s important to understand the concepts of scaling and elasticity. Your cloud services should be scaled at the right size to handle current workloads, with a small buffer. With Azure SQL Database, there is no need to buy that 32 vCore service, when all you need right now is 4. The rest will be available when you need them. You should be ready to scale up or out when the time is right but not before. With the cloud, you should shift your thinking from a CAPEX operating model to and OPEX operating model.

CAPEX vs OPEX

Capital Expenditure (CAPEX) describes where an up-front purchase is made to provide value into the future. e.g. A Server. Once it is purchased, you don’t need to buy it again and can use it until it outlives is usefulness. It’s a big upfront expense to provide long term value. For something like a database server, you might forecast what size you will need in 5 years and buy accordingly.

Operational Expenditure (OPEX) on the other hand describes ongoing costs and operating expenses. Items such as electrical power, rent and staff salaries come under OPEX. Pay As You Go cloud solutions fall under operational expenditure (OPEX) as they are ongoing costs. With this operating model, you should only pay for what you need, and adjust your scale as driven by demand, not future projections.

Resource utilisation and purchase models

Example graph for Azure SQL Database

DTU Model

Small to medium single databases can use the DTU purchase model which is a blend of cpu, memory and IO. They come with fixed storage per instance type. When monitoring performance, you should be looking mainly at DTU percentage used and storage percentage used.

If you are averaging above 85% and have regular peaks higher than that for DTU usage, you should scale up. You can adjust this figure based on how relaxed or nervous you are the DTU getting pinned at 100%.

If you are averaging below 50% and have few or no peaks above this, you can scale down. (Providing the lower class has enough storage) Again, adjust this to suit your own needs but there is no need to pay double unless you absolutely need to bake in a lot of spare capacity.

vCore

The vCore purchase model provides more flexibility than the DTU model. You can choose your compute and storage resources separately. There is no way to allocate extra memory however, this is bound to the number of vCores you have. Managed Instances only use the vCore purchase model.

I would apply a similar rule to scaling up or down here based on needs. Any instance using < 50% of CPU is a good candidate for scaling down.

Azure SQL Database Service Tiers

Ensure you are on the right service tier. Azure SQL Single DB comes with a choice of Basic, Standard and Premium.

Basic (DTU)

If you are on the basic service tier, then from a cost optimization process there is not too much more to say. It has the cheapest instance types, and the lowest resources so if it is doing a job for you, then there is no need to change. It only offers 7 days backup retention so be aware.

Standard (DTU)

A large number of workloads will be running on standard. If you are on the standard tier, you can scale down based on utilisation however you should bear a couple of things in mind.

  • S2 and below does not allow columnstore indexing
  • S1 instance types and below use HDD storage and this might have an impact on your query durations
    • S2 and above uses SSD

Premium (DTU)

Moving to the premium service tier should be well thought out as it includes the most expensive instances, and may mean that you are over provisioned. If you are on a premium instance, look closely at your utilisation, particularly DTU percent used. For seemingly over provisioned instances, you should also take note of a couple of factors before making a decision. Premium is particularly good for IO intensive queries due to a higher average IOPS and a lower IO latency.

Scaling down from premium service tier means:

  • A decrease in average IOPS from 25 to (1-4) IOPS per DTU
  • An increase in IO latency from 2ms to 5ms
  • A new maximum database size of 1 TB

* Changing instance size but staying with premium service tier affects resources only

If you can handle the drop in instance size and/or reduction in service tier, I would recommend scaling down. Remember to keep a closer eye on newly changed instances, and scale back up if performance is no longer acceptable.

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu

General Purpose (vCore)

General purpose is the budget option but is suitable for a lot of production workloads. If you are currently on GP and you are happy with your performance, then do not change to a higher service tier.

Business Critical (vCore)

Most production workloads can run in Business Critical but you might be over provisioned. 1 read-only replica is built into the price so you can offload read-only workloads to your replica. It doesn’t come cheap though and is more than double the price of a General Purpose instance due to increased hardware and licensing. Scaling down to General Purpose will take away this replica so beware if you use it. A good option may be to scale to a lower Business Critical Instance, so that you still have a read replica built into the price.

Hyperscale (vCore)

Hyperscale is not currently available with Managed instance. (January 2021)

Your instances on Hyperscale are roughly double the cost of General Purpose but less than Business Critical. The main considerations are database size and log write throughput. On an exceptionally busy and large database, hyperscale is often the best choice.

Be aware of the reduced performance and features available if you think you can scale down to General Purpose. Changing tiers down from Hyperscale is not a native or easy task. It isn’t supported so you will need to export and import your data to a new database.

Scaling down to GP means:

  • Your database must be less than 4TB
  • You will move to premium remote storage, rather than local SSD
  • You cannot continue to use In-Memory OLTP
  • It’s a lot of work

https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-general-purpose-business-critical

Summary

The main thing is to regularly review your instances and make sure you are not paying for too much headroom. Make sure that by scaling down, you still have the functionality you need. If you scale down and performance is not what you expected, you should scale back up. Cost optimization shouldn’t come at the expense of performance. It is about ensuring that you are paying for what you need, and nothing more.

If you missed it, please post 1 in this cost optimization series, please have a read about Azure IaaS SQL Backups.

IT Certification Category (English)728x90

Filed Under: Azure, cost-optimization, front-page Tagged With: azure billing, cost optimisation, cost optimization, managed instance, SQL Database, SQL DB

Azure IaaS SQL Backups – Stop burning money

14th January 2021 By John McCormack 2 Comments

This post is part of a planned series on 7 ways for data professionals to save money in Azure. It’s so easy for costs to escalate in the cloud, and being proactive is the best way to optimise your costs, and make sure you aren’t spending more than you need to. In this post, I look at how to cut costs by implementing an Azure IaaS SQL Backups retention policy and ensuring you are not paying for more than you need.

Azure IaaS SQL Backups

This advice is to specific on-premises or IaaS backups which are going directly into an Azure blob storage account. Whilst blob storage can be reasonably cheap, if you keep every backup with no retention policy, the costs will soon escalate.

Scenario

You have 5 databases on a SQL Server Instance. You take daily full backups of each database on your instance. You also take log backups every 15 minutes as each database is in full recovery mode. This means in 1 week, you will have 35 full backups and 3,360 transaction log backups. This multiplies to 1,820 full and 174,720 t-log backups over 52 weeks. Multiply this for 7 years or more and the costs can get very expensive.

Your company policy only requires point in time restore functionality for 2 weeks of data. On top of that, daily full backups for the previous 3 months and a weekly full backup for between 3 months and 7 years must be kept.

For simplicity, lets assume an average full backup size of 25GB and a log backup size of 100MB.

Costs with no planned retention policy

TimescaleFull backupsLog backupsBlob storage tier
(East US – GPV2 – LRS – Standard
)
Monthly Cost
Previous 7 years (5 DBs)12,7751,226,400Hot£4,784.79 + £1856.92
£6,641.71

Costs with a planned retention policy

TimescaleFull backupsLog backupsBlob Storage tier
(East US – GPV2 – LRS – Standard)
Monthly Cost
Previous 14 days706,720Hot £27.13
£10.42
£38.55
15 days to 93 days3900Cool£110.45
94 days to 2,555 days17580Archive£45.40
TOTAL2,2186,720Mixed£194.40

So if we optimise the storage to only keep the full and t-log backups that we need, and we use cool and archive storage correctly, we can save over £6,000 per month. There are four main ways to actively manage data retention in order that you optimise your cloud costs, without breaching your data retention policy.

1. Azure Blob Storage lifecycle management

Lifecycle management rules are a simple way to manage files within blob storage. You can either delete files or move them to a cheaper storage class. The rules are fairly customisable so this a basic option which works well without too much configuration.

There are huge savings to be made by moving storage to the archive tier. If you’re holding on to backups for regulatory purposes or to adhere to company policies but won’t be using them regularly, the archive tier provides great value for money. Your data may take several hours to be available when you make a request so it’s not suitable for any disaster recovery scenario but providing you don’t mind waiting, you will cut your Azure bill substantially.

https://docs.microsoft.com/en-us/azure/storage/blobs/storage-lifecycle-management-concepts?tabs=azure-portal

2. SQL Server IaaS Agent extension

If you have SQL Server running in Azure VMs, you can opt to have them automatically backed up by the SQL Server IaaS Agent Extension. Whilst there is some customisation available, you can’t retain your backups beyond 30 days. This makes it unsuitable for our scenario here but if all you need is 30 days or fewer, then this is a great option. Having Azure automatically handle the backups, gets you one step closer to Platform as a Service whilst still retaining the flexibility or running a full SQL Server instance.

sql server iaas agent extension backup

Automated backups for SQL Server 2014

Automated backups for SQL Server 2016+

3. Azure Backup (SQL Server VMs)

You can use Azure backup, via recovery services vault to configure backups on multiple servers. You can choose to back up specific databases or all, including any new ones added after configuration by selecting AUTOPROTECT. There are very detailed retention options which allow you to set a very fine grained retention policy.

4. Roll your own solution

This isn’t needed as much as it used to be. It used to be the case that the lifecycle rules were very inflexible and to keep certain backups, and change storage options, you needed to use your own script. My preferred choice was using PowerShell and Azure Automation Runbooks. With one client, this solution saved hundreds of thousands of pound per year.

* I would only recommend this if the two options above don’t provide you with the flexibility you need to manage your storage retention, according to your own complex requirements.

P.S Don’t forget backup compression

With all of these solutions, don’t forget to enable compression on your backups. This will help you save even more money in addition to the retention policy you have just put in place.

IT Certification Category (English)728x90

Filed Under: Azure, cost-optimization, front-page Tagged With: azure billing, backups, cost optimisation, cost optimization, sql backups

Pool breaks to avoid burnout in IT – tsql2sday

12th January 2021 By John McCormack 2 Comments

Avoid burnout in IT

t-sql tuesday logo

This month’s t-sql Tuesday is hosted by James McGillivray (b|t) and he says:

Breaks are critical for our mental health. Write a post about relaxation techniques, dream destinations, vacation plans or anything else relating to taking a break for your own mental health.

Covid-19 WFH burnout

When we were first locked down in our homes in 2020, due to the first wave of Coronavirus, I felt like there was not much to do other than work. Football was cancelled, my kids activities were cancelled, we couldn’t go to the pubs or restaurants. So I worked. Extra hours were available to me and I just did them, often at my desk from 8-8. I had a small spare bedroom office which suited me fine when I worked from home 1 day a week, but it wasn’t ideal for spending so much time in.

I don’t know if I reached burnout but I know I was a bit fatigued. Other than to work as it was paying the bills, I had no appetite to do much else online. Friends and family were meeting by zoom and I didn’t feel like I wanted to join in. My own blogging productivity mostly stopped. I had been wanting to create some training content and I just couldn’t face it. I had an expensive mountain bike gathering dust because I just didn’t want to go outside.

New House

Fast forward to September and we moved house. I moved to a larger house with a dedicated garage/office. I know I’m incredibly lucky to be able to do this but it also allowed me one more thing, my own pool table. I’ve wanted one forever, actually I would have loved a snooker table but I couldn’t squeeze one into the space.

  • white two story garage
    Outside the garage
  • John playing pool to avoid burnout in IT
    John playing pool
  • Garage office
    Upstairs in the office

My short term escape to avoid burnout in IT

When I’m taking a break, I like to knock a few balls around on the pool table. Having something to do in my own house has been a godsend with other facilities constantly being shut down to prevent the spread of covid-19. It’s great to have the option to do this at home.

Relaxation goals

I’ve made it one of my 2021 goals to take 28 days holiday in a warm climate this year. We have an extended family holiday booked for Florida at the end of June. I don’t know if this will happen but I hope it does. I’d also like to spend two other single weeks away somewhere like Tenerife. Having these planned will break up the year and motivate me to work hard the rest of the time.

My wife and son went to Tenerife last January with my sister-in-law and niece. I could have gone but we knew nothing of coronavirus at the time and I thought I would just work to bring in some money, since I was on a short term contract. I was going to take a break in March and also planned on going to London and Lingen for SQLBits and DataGrillen during the year. Missing that trip was my biggest regret of 2019 because I ended up going nowhere.

Even if you can’t go anywhere, it’s still worth taking time off work to help avoid burnout in IT. I just hope this year that I do get to enjoy some sunshine with my family and friends.

IT Certification Category (English)728x90

Filed Under: Personal, T-SQL Tuesday Tagged With: burnout, personal, pool, relaxation

Let me solve your SQL Server problems

11th January 2021 By John McCormack Leave a Comment

I’ve started a SQL Server consultancy business, focussing on short term engagements with ongoing support where needed. I’m keen to know what problems you are having with SQL Server that you would like someone to have a look at it.

What do you get from me?

In my bespoke SQL Server health checks ,I’ll present the results with actionable steps for you to take. View a sample health check. I also offer cost optimization reviews to help you save £1,000s from your Azure Bill.

What is the cost?

The SQL Server health check is £295 + VAT. The cost optimization review depends on the size of your environment. You can book a free introductory call to discuss.

Why would you want my help?

Experience

I’ve been working with SQL Server for around 10 years now. I’ve worked with large multinational companies and privately held enterprises, that operate on a global scale. I have a track record of solving problems related to SQL Server performance and I’m an experienced production DBA as well.

I also have a proven track record in cost optimisation of cloud resources, on both AWS and Azure. In my last contract, I saved the customer over £400k per year between SQL and storage costs.

My Certifications

  • MCSE: Data Management and Analytics certification
  • AWS Cloud Solutions Architect Associate
  • Azure: AZ-900 & DP-900

Client feedback

DBA Troubleshooting Impact

It’s been a genuine pleasure John. You’ve come to the rescue so many times I’ve lost count.

Duncan Lundie, Head of Application Operation, City Facilities Management

Best of luck John. It’s been a pleasure and you’ve been a lifesaver (more than…) a few times

Ronnie McGee, Senior Project Manager, City Facilities Management

Thanks for everything John. You will really be missed in terms of approach, achievements, mindset and skills! It’s been great working with you and hopefully our paths will cross again.

Stephen Meeke, Principal Engineer, City Facilities Management
Cost optimization (cloud)

Superb work on project X! Proactive, detailed, outcome driven and focused!

Nick Prudhoe, CTO, City Facilities Management

John is doing a fantastic job on Project X pushing the boundaries and saving thousands of pounds to Mercury. For me, it’s not just the cost savings which makes him stand out but the “Agile” thinking and drive to make things better.

Gautam Sivakumar, Cluster Lead, City Facilities Management

Public Profile

I’ve spoken at major data platform conferences such as SQLBits, Data Scotland and DataGrillen. I blog regularly and I also contribute to open source projects on GitHub. I try to be a member of the SQL community who gives as much as he takes.

What other things can I help with?

  • I can review and fix your indexes.
  • Tune queries, stored procedures or views to return data faster.
  • Health check of your instance, to reassure you or tell you what needs to be fixed.
  • Review your cloud expenditure and identify savings.
  • Configure Availability Groups.
  • Most production DBA activities/tasks.

These are just a few examples, you can ask me anything.

Let’s do this, send me your details to request a SQL Server Health Check.

If you prefer, you can book a free 15 minute introductory call directly with me.

Alternatively, you can call me on:  +44 (0) 330 133 2710.

Filed Under: front-page Tagged With: sql server health check, sql server problems

Firefighting – When your SQL Server is in big trouble

6th January 2021 By John McCormack Leave a Comment

When your SQL Server is in big trouble

It’s so important to stay calm when your SQL Server is in big trouble. I’m talking about when blocking is through the roof, when CPU is pinned and people are yelling from all different directions. Staying calm isn’t just about a state of mind, you need to have a process that you work through, that you have practised and you know inside out.

Part 1

How bad is it?

Silhouette of a firefighter in front of a blaze

In this post, I want to describe what I call a priority 2 problem. It is serious and business impacting but the server is online. It needs immediate attention however or it could escalate to a P1.

P1 – Business critical system(s) are unavailable
P2 – Serious impairment of functionality on a critical system
P3 – Performance is slower than usual but requests are completing
P4 – Performance is generally sub optimal and should be improved

Get a colleague on comms

One thing that is guaranteed to happen is that people who are not directly involved in fixing the issue, will want updates. They have good reason to want updates and are well meaning, but the constant need to reply to emails or Teams messages will seriously impact the speed at which you are working.

Back in the good old pre-covid days of office working, this could easily be achieved by someone sitting near you and the two of you communicating verbally. With remote working being more and more common now, I recommend you have some kind of audio channel open that you can speak when you need to and silence is ok too. This could be a phone call on speaker, or a teams call. The technology isn’t the main thing here, the idea is that you can express updates vocally to someone capable of communicating with the wider organisation.

Where possible, your colleague should be technically capable of answering related questions. They should open a chat in Teams or Slack or whatever software your organisation prefers and provide regular updates there. They should answer questions, keep to the update schedule and most importantly, divert people away from you.

A pre practiced routine

Now the scenarios can be different, in this one I want to talk about how I would handle a SQL Server which appears to be functioning (barely) but is extremely slow. In other words, it is a priority 2 incident as defined above. Helpdesk supervisors are calling tech support to advise and customers are calling them as the website is unusable. On top of that, the MI team and data scientists are contacting the DBAs directly because their queries won’t run.

Have a script or checklist

In my script, I tend to use well established community stored procedures. The sort of ones that most DBAs know about and many use. If you start trying to write new queries in the middle of a slow down, that is going to cost you time. Stick with what works, what is established and what you have used before and know well.

I’ve mentioned these in previous posts but the main things I am going to run are:

  1. sp_whoisactive – https://github.com/amachanic/sp_whoisactive
  2. sp_blitzfirst – https://www.brentozar.com/first-aid/
  3. sp_blitzlock – https://www.brentozar.com/first-aid/

sp_whoisactive

I always run this first because it it designed specifically for showing you current database activity, and it has saved my bacon so many times in the past. Simply running this parameter free will show you what is running on your SQL Server right now. It is ordered by query duration descending so the long runners are at the top. It can give you blocking information, information on wait types and on open transactions as well. For advanced features, you need to use some parameters.

sp_blitzfirst

If you can find what you need with sp_whoisactive, you may not even need to use this stored procedure, Where this procedure comes into its own is it tells you when certain things have recently changed or when they out of the ordinary.

e.g. sp_blitzfirst will tell you if:

  1. The plan cache has been recently erased
  2. You have high CPU utilization from a program other than SQL Server
  3. How long each wait type has been waiting during the previous 5 seconds

If something stands out that you don’t understand, there will be data in the URL column that you can copy/paste into a web browser. The web page will give you an explanation of the problem.

sp_blitzlock

This proc is all about deadlocks. You might not need to run it if the first two have given you enough information to fix the issue in hand. However, if deadlocks are at the root of your problems, this will tell you which queries are involved and when. It’s also easier to read than deadlock graphs.

There are more things that I’ll run in the fullness of time but we’ll leave them for later. I only want to focus on these 3 things for now to zero in on the immediate problem.

The script

USE dba

/*
	To run this, you also need to install:
	- sp_whoisactive
	- sp_blitzfirst
	- sp_blitzlock
*/

-- Raises an error if you run the whole script in error
RAISERROR ('Dont run it all at once',20,-1) WITH LOG

-- sp_whoisactive with various parameters
EXEC sp_whoisactive @find_block_leaders = 1 --,@get_locks = 1
EXEC sp_whoisactive @sort_order = 'sql_text' -- Tells at a glance if you have a lot of the same query running. For the F5 report runner troublemakers


-- What has been hurting us in the last 5 seconds. Look for wait stats, and anything out of the ordinary, such as the plan cache has been recently erased.
EXEC dbo.sp_BlitzFirst @expertmode = 1


-- Are we experiencing deadlocks 
EXEC sp_BlitzLock

-- Deadlocks in last hour
DECLARE	@StartDateBlitz datetime = (SELECT DATEADD(HH,-1,GETDATE())), @EndDateBlitz DATETIME = (SELECT GETDATE())
EXEC sp_BlitzLock @EndDate = @EndDateBlitz, @StartDate = @StartDateBlitz
GO



/*  Some other things to consider

	Have the usual optimisation jobs run as expected. Stats/indexes etc
	If one proc has regressed badly, could it help to clear only that plan from the cache or to recompile it.
	EXEC sp_blitz -- Although not as handy as the others for real time issues. Better used as a health check
	EXEC sp_blitzcache -- More useful for helping you identify the resource hungry queries, allowing you 
	EXEC sp_readerrorlog 0,1, 'memory'
*/

The calm after the storm

I will dedicate a full post to this however for now, it is important to say that slow queries or resource intensive queries should be identified and added to a backlog for fixing. If one particular query is likely to go bad again, it should be treated as a priority to fix, in order that we don’t see the same issue escalating again.

You should also do a SQL Server Health Check in order to satisfy that you don’t have an sub optimal configuration causing you issues.

IT Certification Category (English)728x90

Related to: When your SQL Server is in big trouble

Locks, blocks and deadlocks in SQL Server
Zero Budget DBA – SQLBITS 2020

Filed Under: front-page, Performance Tuning, SQL Server Tagged With: firefighting, sql server troubleshooting, triage

  • « Previous Page
  • 1
  • …
  • 3
  • 4
  • 5
  • 6
  • 7
  • …
  • 20
  • Next Page »
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2022