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.
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.
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.
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.
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.
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.
With sp_BlitzFirst & sp_WhoIsActive in a SQL Agent job with scheduled execution for every 10-15 minutes, you can look back in time in terms of What was running, its execution stats, file stats, wait stats and Perfmon counters. This would help you to answer anybody as why your server was slow at a particular point in time.
Click through for the post and check out the GitHub repo as well. Baselining is extremely important for proper administration.
Solomon Rutzky is a month early with this:
In the event shown directly above, towards the bottom, in the final “<Data>” element that starts with “
\\?\C:\ProgramData...“, that entry does point to a folder containing a Report.wer file. It is a plain text containing a bunch of error dump info, but nothing that would seem to indicate where to even start looking to fix this. And, nothing useful for searching on, at least not as far as my searching around revealed.Conclusion
There you have it: a nearly untraceable way to prevent SQL Server from starting.
Read on to see how what Solomon did.
The next piece of code helps fix orphaned users by reconnecting them to logins that have precisely the same name, but a differing SID. This code is a variant of the above code that dynamically creates
ALTER USERstatements. A statement is created for each orphaned user where there is a match-by-name in the list of server logins. Once the list of dynamically createdALTER USERstatements are compiled, the commands to fix orphaned users are automatically executed.
Click through for the scripts.
Randolph West wants you to disable priority boost if you have it turned on:
It turns out that on one of the benchmarks, Microsoft was able to achieve higher throughput by setting the SQL Server process to
HIGH_PRIORITY_CLASS, and the thread priority level over and above that toTHREAD_PRIORITY_HIGHEST. While it was extremely helpful to beat artificial benchmarks, it came at the cost of giving SQL Server higher execution context on the CPU than almost every other process on Windows.
And that’s the only good use of priority boost ever. If you’ve never heard of priority boost, just keep on ignoring it.
Kevin Feasel
2019-03-21
Administration, Cloud
No Comment