Last week, I received an alert that the percentage of transaction log in use on one of our production databases was increasing more than it should have been. I’ll refer to this database as DB1 from here on in. It had reached 18%, which is above the normal 5-10% we like to see it at. DB1 is in an Always On Availability Group so if the log usage jumps we jump.
The first thing I checked was that the log backups were running. We use Ola Hallengren’s maintenance solution to manage our backups. The backup jobs were running without error.
The next thing to check was the log_reuse_wait_desc column in sys.databases.
The highlighted row is the row for DB1. A log_reuse_wait of 2 means there were no changes to backup since the last log backup. If that were the case then why was my transaction log slowly filling like an unattended bath?
Read on to learn more. James also has a link to a (closed/won’t-fix) Connect item.
Back in December, I published a post entitled, “How I spot not-yet-documented features in SQL Server CTPs.” In that post, I revealed a few of the ways that I get an early jump on what’s changed between CTP and/or RC builds of SQL Server. You can do those same things if you want to see what new objects or columns have been created, or which system modules have changed, between – say – SQL Server 2014 and SQL Server 2016. This will likely be a bigger list than any of the individual sets of items I’ve posted about in our SQL Server 2016 builds post, but the same concepts apply – create a linked server to the older instance, optionally create some synonyms for easy adaptation, and go to town.
There are a few other things I check as we get closer to the final release, and they can be quite revealing about what features have made it into the product. We can also get some insight into things they tried to get in but couldn’t (for example, there are error messages and Intellisense verbiage for
STRING_AGG(), which does not seem to be in the cards for RTM). I’m going to point out a few, but I’m not going to iterate through all of the things I’ve learned – this is more to serve as as a starting point so you can experiment on your own.
If you’re interested in this kind of spelunking, you can learn a lot without having to reverse engineer binaries.
In an enterprise solution, you might have 10+ perspectives – some of which might have similar names – and without a clear description it will be confusing for a user (especially new users) to know which perspective is the correct one.
A better idea is to add a description/annotation property for each perspective where a more helpful text description can be provided indicating the business process, common types of analysis, etc. This would provide a metadata hook for self-service reporting tools (e.g. Excel, Power BI) as well as enterprise data cataloging solutions such as Azure Data Catalog.
Another helpful feature would be the ability to set the visibility of a perspective – or if you’re more familiar with the Tabular vernacular: “hide it from client tools”!
While you’re reading about perspectives, fill out Bill’s SSAS survey.
What’s clear from these examples is that trying to relate what’s going on in the query to what you see in Profiler is quite tricky even for seemingly simple queries; for most real-world queries it would be almost impossible to do so with total confidence. That said, when I’m tuning queries I usually comment out large parts of the code to try to isolate problems, thus creating much simpler queries, and I hope the value of this post will lie in you being able to spot similar patterns in Profiler to the ones I show here when you do the same thing. In part 3 of this series I’ll show you some practical examples of how all this information can help you tune your own queries.
Whenever I read Profiler, my next question is “Is there an extended event which covers this?”
Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application. The application doesn’t know if it’s calling a table or a view so that’s why this works.
You should never store passwords in plaintext. You should almost never store passwords in a reversable format (i.e., encrypted). The primary case in which I can see storing passwords encrypted rather hashed is if you have automated systems (or non-automated technicians) which need passwords to authenticate somewhere. Even then, there’s a lot of value in using OAuth tokens. But if you can’t get around any of this, John’s solution does remove the really bad decision of leaving passwords in plaintext.
Much of the time there is a systems team and a DBA team, and when the DBAs need to build out a new set of SQL Servers, they request X number of virtual servers from the systems team. The servers are handed over and the DBA team works its magic, and then we have our Failover Cluster Instance or Availability Group High Availability solution. But, is it really Highly Available?
The reason I ask is twofold:
- Which physical hosts are your Virtual Machines are located on?
- Which data stores are your virtual disks are located in?
If the answer to either of these questions results in the same answer for any of your Virtual Machines in an Availability Group, or Failover Cluster Instance for that matter. Then you potentially have a massive flaw in your implementation that can affect availability.
The moral of the story is to communicate with the network administrators and SAN folks.
Before I jump into the technical details of the Service Broker architecture, I think it helps to have a real-world analogy of what Service Broker is and does. In the last installment, I used the example of ordering something from Amazon.com. This time, I’d like to use an analogy that’s somewhat timely: taxes.
Each year, we fill out that 1040 or 1040EZ form and we send it to the Internal Revenue Service. Maybe we eFile, maybe we mail it in, it doesn’t matter. That form is received by the IRS and goes into a queue, awaiting review. At some point, days, maybe weeks later, our tax return is processed. If all goes well, our return is approved and the IRS cuts us a check. That is a Service Broker application.
When I first started learning Service Broker, it seemed like there were a lot of abstract notions (mostly because I didn’t know anything about message queues). The pieces all start to come together once you get into an application.
This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.
You still need Integrated mode to read Power View reports, and John mentions a few places where Native mode falls short, so take the time to plan out which is right for you.
Tim Radney shows us a new way of clearing the procedure cache in Azure SQL Databases (and in 2016 RC0 or later):
It turns out that
DBCC FREEPROCCACHEis not supported in Azure SQL Database. This was troubling to me, what if I’m in production and have some bad plans and want to clear the procedure cache like I can with the box version. A little Google/Bing research lead me to find the Microsoft article, “Understanding the Procedure Cache on SQL Azure,” which states:SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache.
In discussing this with Kimberly Tripp after not seeing that described behavior, it does not flush the plan from cache, but it does invalidate the plan (and then the plan will be eventually aged out of the cache). While this is helpful in certain situations, this was not what I needed. For my demo I wanted to reset the counters in sys.dm_exec_cached_plans. Generating a new plan would not give me the desired results. I reached out to my team and Glenn Berry told me to try the following script:
Read on for the new command, and just like DBCC FREEPROCCACHE, be careful where you point that thing.
Typing a * sounds a lot easier than actually listing all of the columns you need. However is that really a good idea? You’ll hear lots of people saying it isn’t, but why?
Well there are several reasons.
Read on for his reasons.