Search Results for: indexes

Troubleshooting Transactional Replication in SQL Server

4th February 2016 By John McCormack Leave a Comment

This might make me the odd one out but I actually really like replication. It took me a while to get comfortable with it but when I did and when I learned how to troubleshoot transactional replication confidently, I became a fan. Since I exclusively use transactional replication and not snapshot replication or merge replication, this post is only about transactional replication and in particular, how to troubleshoot transactional replication errors.

In the production system I work on, replication is highly reliable and rarely if ever causes the DBA’s headaches. It can be less so in our plethora of dev and qa boxes, probably down to rate of change in these environments with regular refreshes. Due to this, I’ve had to fix it many times. As I explain how I troubleshoot replication errors, I assume you know the basics of how replication works. If you don’t, a really good place to start is books online. It describes how replication uses a publishing metaphor and describes all the component parts in detail.

[Read more…]

A successful performance tuning project

5th June 2020 By John McCormack Leave a Comment

Performance tuning project

I’m coming to the end of what has been a successful performance tuning project for SQL Server. I wanted to share some of the things that made it a success.

Corporate buy in

The company had a goal to improve the page load times of a number of key pages within our application. This was to improve the user experience for our customers. They acknowledged that the database code, both indexes and stored procedures needed optimsation but so too did aspects of the application code. It was good to see this acknowledged as I’ve been in many situations where the database takes all the blame.

The company approved a considerable amount of resource in terms of personnel to work solely on this stability and optimisation project. It included senior developers, testers and a project manager. I was brought in to look at the database performance. Whilst some business as usual (BAU) priorities did come in from time to time, a large core of the team was protected from this and allowed to get on with the work of making the system go faster, thus improving the customer experience.

Daily standups

We held daily standups where we covered what we had done since the last meeting, what we were working on and if anything was blocking our progress. These were kept short so as to not get in the way of the development work, but allowed everyone an overview of what the other team members were working on. Often, as a result of these, side conversations spun up and team members helped out others who were looking for a bit of assistance. (Or simply to bounce ideas around)

Collaboration

The team were willing to help each other. When Pull Requests (PRs) were submitted, these were swiftly approved where there were no objections, or challenged in a positive way which helped get the best overall result. When the API calls were showing as slow, but nothing was obvious on the SQL server, heads were put together to use the tools at our disposal to get to the root cause. This often included Azure App Insights which I had not previously used, and this helped us get the end to end transaction details. We could pull out the SQL for any areas which were slow and work on making it perform better.

Measuring improvements

The Azure Instance class for the SQL Server had previously been doubled so there was no appetite to scale it up again. The hope was that we may eventually be able to scale back down after a period of stability.

The system previously had issues with blocking, high CPU and slow durations so I wanted to reduce page reads, CPU and duration for all of the SQL calls I was working on. I wouldn’t consider a PR if at least 2 of these metrics were not improved. My main focus was on reducing duration of calls but I didn’t want to improve one thing, and make others worse as a consequence. In my own tickets, I always made sure to document the before and after metrics from my standalone testing so as to give confidence that they would be included in upcoming releases.

We also used Apdex which is a standardised way of measuring application performance. It ranks page views on whether the user is satisfied, tolerating or frustrated. The more we move users out of the frustrated and tolerating groups, and in to satisfied, the higher the Apdex score will be. As our project moved through release cycles, we were able to see steady increases in our Apdex scores. Apdex also allowed us to identify what was hurting us most and create tickets based on this information.

Top Quality Load Test Environment

We had a top quality load test environment which used production masked backups for the databases. I set up the availability groups to match production, the servers were all sized the same as production and had the same internal settings such as tempdb size, sp_configure settings and trace flags etc. We were able to replay the same tests over and over again using Gatling, and our testers made really useful reports available to help us analyse the performance of each hotfix. If it was a proven fix, it was promoted to a release branch, if it wasn’t, it was binned.

End Game

This intensity was kept up for the almost 2 months and it was ultimately transformative for the business. Whilst there are still many further improvements that can be made, the specialised squad is being disbanded and team members are being reallocated to other squads. Performance should be a way of life now, rather than an afterthought or another performance tuning project.

We can be happy that we improved the Apdex scores, sped up a huge number of regularly used SQL transactions, and removed the large CPU peaks that dominated our core business hours.

If you enjoyed this, you may also enjoy some of these other posts.

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.

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.

https://youtu.be/oWSj_Jy0YMY

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:

sp_whoisactive – https://github.com/amachanic/sp_whoisactive
sp_blitzfirst – https://www.brentozar.com/first-aid/
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:

The plan cache has been recently erased
You have high CPU utilization from a program other than SQL Server
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.

Related to: When your SQL Server is in big trouble

https://johnmccormack.it/2020/12/locks-blocks-and-deadlocks-in-sql-server/

https://johnmccormack.it/2020/10/zero-budget-dba-sqlbits-2020/

T-SQL Tuesday #143 Wrap Up

19th October 2021 By John McCormack 2 Comments

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

https://johnmccormack.it/2021/10/t-sql-tuesday-143-short-code-examples/

I learned so much by hosting this and made sure I gave due care to reading every post. It was also a lot of fun and allowed me to interact with people in the community that I haven’t met before. If you haven’t hosted T-SQL Tuesday before, please contact Steve Jones as we are always looking for new hosts.

Wrap Up

Rob Farley – Short and to the point like I asked for, Rob details a quick way to find objects. And he was happy to clear up for the reader that I didn’t mean GOTO as in the old BASIC syntax you could run on your commodore 64. (For me it was an Amstrad CPC464)
http://blogs.lobsterpot.com.au/2021/10/12/go-to-scripts/

Koen Verbeek – Koen shows us numbers tables, tally tables and a dates table. These are really useful constructs for allowing your queries to go “set based”. Essential reading for anyone who cares about performance. https://sqlkover.com/t-sql-tuesday-143-short-code-examples/

Aaron Bertrand – Aaron shows us how he “bulletproofs” his answers for dba.stackexchange and Stack Overflow. db<>fiddle was new to me. I love some of Aaron’s demo database names like [master (Restoring…)]. I ran the create command on my test instance and had to drop the DB right away as it was giving me the chills.
https://sqlblog.org/2021/10/12/t-sql-tuesday-143-worst-metadata

Deborah Melkin – Deborah shows us a really useful debugging trick when creating stored procedures that use dynamic sql. Many of us have been lost in dynamic sql at some point, and this snippet is great at helping you see where you are.
https://debthedba.wordpress.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Kenneth Fisher – Kenneth shares a compendium of previous posts which all require some serious reading. My favourite was “all jobs that ran during a given time frame”
https://sqlstudies.com/2021/10/12/code-examples-t-sql-tuesday-143/

Jeff Hill – Jeff shared 4 great PowerShell snippets. True to this month’s request, they are short and incredibly useful. Want to know what version of Windows you are running on your Server or when it was last rebooted, look no further.
https://sqladm.in/posts/tsql-tuesday-143/

Chad Baldwin – Chad is a newcomer to T-SQL Tuesday and chipped in with a stellar first post. I must admit, I’ve never given much thought to how to format a result set as I’ll usually do it in the client, but when you need to; it is possible as Chad shows. But that’s only the start. There’s too much to discuss in this digest as he also covers tally tables, random numbers and overcoming the divide by 0 problem. Did I mention he also covers docker, monitoring/filtering log files and setting aliases. Cap doffed.
https://chadbaldwin.net/2021/10/12/tsql-tuesday-short-code.html

Andy Yun – Random numbers, random delays (I wonder if Scotrail use this script) and random strings. Thanks Andy for a great post. There are great scripts on their own and for building into more complex ones.
https://sqlbek.wordpress.com/2021/10/12/t-sql-tuesday-143-random-fun/

Kevin Chant – Kevin discusses just how to get the most out Glenn Berry’s diagnostic scripts, specifically in relation to missing indexes. He also shows a create table syntax and highlights how effective it has been for him in his training sessions around Dev Ops.
https://www.kevinrchant.com/2021/10/12/t-sql-tuesday-143-two-of-my-personal-go-to-scripts/

Andy Mallon – Andy stores all of his useful scripts in a DBA database. It’s a popular approach and I was hoping someone would mention this. Andy goes beyond this though and has converted a lot of scripts into Stored Procedures. Whilst having a local scripts folder is great; if you can put your code into a stored procedure in a database which you deploy to all the servers you manage, there is no need to panic and find the scripts when the pressure is on. I must admit I love this approach and I’ll be downloading Andy’s database to look further into it.
https://am2.co/2021/10/t-sql-tuesday-143-my-favorite-short-scripts/

Jason Brimhall – Jason talks about all things endpoints here and I found the code examples so handy. I’ve already used them. Not only can you use them to validate your endpoints, but you can also use them to fix some issues as well. On a personal note, just what I needed.
http s ://bit.ly/3lAOMmF

Tom Zika – Tom shares loads of useful snippets including regex and t-sql. Wow – one regex snippet shows us how to find table variable declarations and turn them into temp tables. You could make a killing selling this one trick to consultants. Tom also shares a mega handy way to check permissions using impersonation as well as a great method to find referencing objects.
https://straightforwardsql.com/posts/short-code-examples/

Mikey Bronowski – Mikey shows us a handy way to execute multiple queries including dynamic ones and also tells us about agent_datetime() function. I have to admit I’ve never used that function but it looks so useful for when you are interrogating those msdb agent job tables. I will definitely be adding it my list. Finally, he shares a useful query for pulling back table data with his added enhancement (however a nice little plug for DBATools hints he now has a better way of approaching this).
https://bronowski.it/t-sql-tuesday-143-short-code-examples/

Todd Kleinhans – Todd focuses on Python and he is the only person to do so. I won’t give away his one liner but it’s just the sort of thing I was looking for. Are you feeling Zen?
https://toddkleinhans.wordpress.com/2021/10/12/t-sql-tuesday-143-import-this/

Mala Mahadevan – Mala shares some top class queries for interrogating query store. Query store has so much useful data that knowing how to get started querying it will be a big win for some.
https://curiousaboutdata.com/2021/10/12/tsql-tuesday-143-short-code-examples/

Chad Callihan – Chad mentioned 3 handy t-sql snippets and then shared a gem for keeping Brent Ozar’s First Responder Kit up to date. (Hint he uses DBATools). DBATools and FRK are amongst the most essential free tools for any DBA and beyond. If you run anything like sp_blitz or sp_blitzcache, it’s worth keeping it up to date and this method shows how to do it in only a few lines of code.
https://callihandata.com/2021/10/12/t-sql-tuesday-143-handy-short-scripts/

Deepthi Goguri – Deepthi shares some of the best of the rest by highlighting some of her favourite community scripts. From help with migrations to troubleshooting replication, it just goes to show that there’s no need to reinvent the wheel when there’s a perfectly good script out there that meets your needs.
https://dbanuggets.com/2021/10/12/t-sql-tuesday-143-short-code-examples/

Steve Jones – Did you know that you could get a tally table with just 4 key strokes? Steve shows you how, leveraging on the power of SQL Prompt by Redgate. This is taking snippets to a new level.
https://voiceofthedba.com/2021/10/13/t-sql-tuesday-143-short-code/

Jess Pomfret – Aloha to Jess who squeezes her entry in on time due to the Hawaiian time loophole. Want to find out if certain accounts are local admins on remote servers? Jess shares a quick and efficient method for finding this out. Being Jess, of course she is using PowerShell to make her life easier. I for one will be stealing this.
https://jesspomfret.com/t-sql-tuesday-143/

Eitan Blumin – Eitan takes the opportunity to link to some of his past blog posts which are full of useful code however he doesn’t stop there. With a new entry for T-SQL Tuesday, Eitan shows us how to move database files to a new location in Always On Availability Groups without breaking HADR. Ok at 374 lines, it’s a bit more than a snippet but it’s really great code so we’ll let that one slide.
https://eitanblumin.com/2021/10/13/t-sql-tuesday-143-powershell-move-db-files-alwayson-availability-groups/

Shane O’Neill – Shane also mentions agent_datetime(). It’s a cool function for converting the very user unfriendly ms format that we see in msdb tables. Shane points out it might not be the most efficient function however when you don’t have much data to bring back, it’s much quicker than rewriting the thing. Shane being Shane (Big Powershell fan) also points out a few great PowerShell commands for formatting and sorting and shows how they can be used in conjunction with other commands that yield really useful results.
https://nocolumnname.blog/2021/10/12/t-sql-tuesday-143-short-code-examples/

P.S. I’ve taken every bit of care to check my comments and on twitter but if I have missed your post, please let me know and I’ll include it immediately.