Recently I was working on a proof of concept and I had imported a bunch of data into a SQL database and was running a stored procedure which runs queries against a view and does some other work. Everything was running pretty well.

I then deployed a new database and imported the data into the new database and suddenly the queries were not performing as well. Actually they were a lot worse.

Being lazy as its just a POC I was thinking of just dialing up the database to the next tier and getting on with things.

I suddenly thought, “I wonder how often this happens in the real world”.

There are really 2 key differences between today and the past from the application development perspective:

  1. Back on on-premise days you couldnt just dial up the database
  2. There was often a separation of responsibilities between a developer and a DBA. These lines are a lot more blurred these days in the cloud

I decided rather than just dial the knob I would actually take a more serious look at whats the difference between the 2 scenarios which should perform the same. I followed some of the normal troubleshooting approaches and took a look at SQL rather than just at the Azure portal.

First off I ran the below query on my database.

SELECT
  end_time,
  avg_cpu_percent,
  avg_data_io_percent,
  avg_log_write_percent,
  avg_memory_usage_percent,
  xtp_storage_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

This tells me about some of the key metrics recently. I could see some differences here straightaway.

Database 1:

Database 2:

The key observation here is that there is a significant amount of IO happening on database 2.

The first thing I did was run the below SQL

EXEC sp_updatestats;

This updates the statistics used to drive the execution plans of my queries.

Immediately the query in database 2 was now performing really fast. It dropped from > 5 minutes to < 2 seconds.

How did this happen

I think what happened to lead to this was:

Database 1:

  1. Start Import of data
  2. Import Completes
  3. Run query = Its good

Database 2:

  1. Start Import of data
  2. Mike ran query a few times before import completes
  3. Import Completes
  4. Run query = Its NOT good

In this case, on database 2 the statistics were evaluated on a very small subsection of the data being imported and the execution plan was optimized for that scenario.

Lessons learned

Eventually once more data was imported etc the database would have reevaluated the statistics itself but that had not happened yet even after a couple of days.

In my case here there resolution was fairly simple, but it made me think about real world cloud solutions and how often similar scenarios might come up and how they might be handled by the organization.

The lesson for me was rather than just taking the lazy approach of dialing up the database checking all of the basics like would have done by default on premise addressed the issue. It also found a few other optimizations I could implement.

In this case id let technology drive my thinking too much and just trusted that cloud was doing it for me, I had reduced the importance of the people and process angle where I should have checked my database myself.

FinOps Takeaway

When I put my FinOps hat on, the question here is how you handle your upgrade and downgrade decisions. I think there are probably a lot more cases out there where a team just dials up the database in the cloud without having a DBA really evaluate the database thourghly and workout:

  • Is there any inefficiency
  • What is driving the usage
  • Can we tune rather than upgrade

Upgrading should really be the last resort, but now we dont have to physically buy a new piece of tin upgrading has probably become the first option with tuning as an after thought.

Some of the questions id be thinking about include:

  • How does database tuning fit into your application lifecycle now
  • Is the DBA role a cross cutting function or are they part of application delivery streams
  • How often do you evaluate existing databases
  • How often are query performance improvements recommended which arent driven by a user experience issue

All of these things will help develop a culture of good database management should then lead to a lower chance of inefficiency higher up your application stack which will then translate to higher costs.

 

Buy Me A Coffee