A Fine Slice Of SQL Server
Kenneth Fisher thinks about when to test:
To test or not to test. That is the question. Or is it? Shouldn’t we always test? That was the question posted here. It’s a big question so let’s break it down a bit.
My short answer is: test as much as necessary to keep two conditions from occurring: first, that you get fired (or worse); second, that the inevitable bug somebody else discovers in your code is too embarrassing. Kenneth’s answer is better.
Comments closedErik Darling shows missing index requests while creating indexes:
So there I was, creating some indexes. Since I was in demo mode, I had execution plans turned on. I’m actually terrible about remembering to turn them off. There have been times when I’ve run loops to create some REALLY big tables, and left them turned on. SSMS crashed pretty quickly. Apparently generating execution plans isn’t free! Keep that in mind when you’re timing/tuning queries.
Since they were there, I took a look at them. They were basically what I expected. The only way to index data is to read it and sort it and blah blah blah. But there was a little something extra!
Yo dawg, I heard you like indexes…
Comments closedLast 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.
Comments closedBack 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.
Comments closedBill Anton walks through perspectives in Analysis Services:
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.
Comments closedChris Webb has part 2 of his SSAS multi-dimensional monitoring series:
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?”
Comments closedJohn Morehouse shows you how to fix plaintext password storage when you can’t fix the application:
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.
Comments closedJohn Martin looks at combining Availability Groups with a virtualized environment:
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.
Comments closedColleen Morrow gives explanations for various Service Broker components:
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.
Comments closedJohn White shows that the Sharepoint Integrated vs Native mode has shifted in SQL Server 2016:
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.
Comments closed