Press "Enter" to skip to content

Category: Administration

Deleting In Batches

Published 2019-04-04 by Kevin Feasel

Andy Mallon explains why deleting in batches is a good idea:

Yesterday, I wrote about the importance of planning your data purges. Today, let’s look at the simplest requirement for purging data: Keep data for X days after it’s created. There are tons of cases where this is the rule. Log data, telemetry data, even transactional data is often retained based on X days since it is created.

Given how common this scenario is, let’s talk in a bit more detail about I like to go about deleting it. Deleting data is easy, right? DELETE t FROM dbo.Transactions AS t WHERE CreateDate <= DATEADD(dd,-90,GETDATE());

This part of the series covers the why; the next part will cover the how.

Comments closed

Row Versioning and 14 Bytes

Published 2019-03-27 by Kevin Feasel

Kendra Little explains why enabling row versioning adds 14 bytes per row:

I love it when someone sends me a repro script, but in this case I didn’t need to run it. The first thing I did was to look at the two numbers given for row size, and to subtract the smaller one from the larger one: 724 – 710 = 14 bytes of difference.

That bit of information alone gave me an immediate guess of what was going on.

Click through for the solution as well as a more detailed explanation of one of the trickier scenarios.

Comments closed

Putting TempDB Files On Azure IaaS D Drive

Published 2019-03-26 by Kevin Feasel

John McCormack tries out using the temporary drive on Azure VMs for tempdb:

Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.

TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.

John ended up seeing much bigger gains than I did when I tried this, but with a difference that big, it’s definitely worth using the temporary drive for tempdb.

Comments closed

Sizing Azure SQL Database

Published 2019-03-21 by Kevin Feasel

Arun Sirpal takes us through finding the right size for Azure SQL Database:

Do you want to identify the correct Service Tier and Compute Size ( was once known as performance level) for your Azure SQL Database? How would you go about it? Would you use the DTU (Database Transaction Unit) calculator? What about the new pricing model vCore? How would you translate you current on-premises workload to the cloud?

It can be a form of trial and error especially if you are new to this but I really do recommend trying out the PowerShell script that you can access once you have installed  DMA – Database Migration Assistant.

Read on to see how to run this tool and potentially save some money.

Comments closed

Cleaning Up After Yourself in Azure Data Factory

Published 2019-03-21 by Kevin Feasel

Rayis Imayev shows how you can automatically delete old files in Azure Data Factory:

File management may not be at the top of my list of priorities during data integration projects. I assume that once I learn enough about sourcing data systems and target destination platform, I’m ready to design and build a data integration solution between two or more connecting points. Then, a historical file management process becomes a necessity or a need to log and remove some of the incorrectly loaded data files. Basically, a step in my data integration process to remove (or clean) such files would be helpful. 

Click through to see how to do this.

Comments closed

Retaining a Few Tables From a Large Set

Published 2019-03-18 by Kevin Feasel

Jana Sattainathan has a Powershell-based solution to eliminate all but a few tables in a database:

Recently, I received a request to backup a dozen tables or so tables out of 12 thousand tables. I had to retain all the indexes, statistics etc. The goal was to hand this over to the vendor for analysis as a database backup.

I could have copied the selected tables over to a new database using the PowerShell function I had published earlier and backed that up but since the tables to backup were quite large, I skipped that route

Read on to see Jana’s solution.

Comments closed

Fixing High VLF Counts

Published 2019-03-15 by Kevin Feasel

Ajay Dwivedi shares a technique for optimizing VLF counts on log files:

DBAs! I guess everyone know that huge number of Virtual Log Files (VLFs) in SQL Server can cause Backup/Restore & Database Recovery process slow. Even in rare cases, it can introduce slowness at transaction level.
https://sqlperformance.com/2013/02/system-configuration/transaction-log-configuration

Even we all are aware of it, it has been still a challenge to remove High VLF counts from SQL Server log files since it involved log of manual effort with Shrinking and re-growing the log files.

This is where my ‘Space-Capacity-Automation‘ open source project comes to your rescue. It has a parameter option @optimizeLogFiles that can help you optimize your log files by below below tasks:-

Click through to see Ajay’s technique.

Comments closed

Beware CPU Oversubscription with SQL Server

Published 2019-03-08 by Kevin Feasel

Monica Rathbun shares a tale of terror:

Recently I had a client complain of chronic high CPU utilization. The performance of their SQL Server had degraded, and it appeared to be related to higher than normal CPU utilization in conjunction with symptoms of unresponsive user queries.  The root cause was twofold—a third party hosting provider had overallocated virtual processors on the physical host where the virtual machine (VM) running SQL Server was residing, as well as a recent upgrade from a version of VMWare that was not patched for Spectre and Meltdown. The host had 16 physical cores and was hyperthreading (making it effectively 32 cores) until the hosting provider patched from VMWare 5.5 to a newer release (we believe 6.5) which was required for Meltdown and Spectre processor vulnerabilities. This patch disabled hyperthreading from the hypervisor to mitigate the security risk from speculative execution. Note, this patch is over a year old and a critical security risk; most software vendors (including VMWare) put this out as an immediate requirement after the announcement of the vulnerabilities.

Given this was a virtual machine, it shared a physical host with many other VMs; this is a very common configuration. However, this host was VERY overallocated.  As mentioned above, there were 16 cores–however 61 additional vCPUs had been allocated to other machines. That’s 4.3 times the number of CPUs available for allocation.  The screenshot below shows this singular Host, highlighting the vCPUs allocated.

So, uh, that’s a bad thing. Monica explains in detail why exactly it’s a bad thing, which is helpful when you’re trying to explain to the server admin why it’s a bad thing. CPU oversubscription can work for things like dev boxes or web servers, where they typically aren’t anywhere near 100% utilization. It does not work at all for busy database servers.

Comments closed

When Deletes Increase Data Size

Published 2019-03-07 by Kevin Feasel

Brent Ozar shows a case where indexes can grow in size as you delete data:

Here’s one way that deletes can cause a table to grow:
– The rows were originally written when the database didn’t have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
– RCSI or SI was enabled, or the database was added into an Availability Group
– During the deletions, a 14-byte timestamp was added to each deleted row to support RCSI/SI/AG reads

Click through for a demo and takeaways.

Comments closed