A Fine Slice Of SQL Server
Dan Blank lays down some information about saving headerless CSV files in Powershell:
Anyway, a question was posted recently using the #pshelp hashtag on Twitter. How do you replace the header line of multiple csv files at once?
I saw this and maybe cockily thought to myself, “that’s got to be an easy 2 liner, bet I can boost my ego and quickly write out a solution”.
Spoilers: it turned out to be slightly more complex.
Comments closedBrent Ozar has some interesting and mostly disheartening statistics:
One of my favorite things about my job is being able to take the pulse of how real companies are managing their databases. I don’t wanna be locked in an ivory tower, preaching down to you, dear reader, about how you should be doing five million things a day – when in reality, you’re struggling to get an hour of real work done because you’ve got so many meetings.
But sometimes I wonder – am I out of touch? When I was a DBA, I remember struggling with backups and corruption checking – has that gotten easier? Have today’s DBAs started using more automation and tools to protect their estate? Is Transparent Data Encryption catching on? Did DBAs start using Extended Events for monitoring when I wasn’t looking?
And it’s important because I wanna build the right training material and scripts for our customers. I see a problem trending, I want to be able to give people the right information to fix the problem, fast.
Standard disclaimers about potential bias in samples apply, but it’s an interesting slice of the population.
Comments closedBill Fellows has an interesting approach to bucketing tables into groups of similar size:
You need to do something to all of the tables in SQL Server. That something can be anything: reindex/reorg, export the data, perform some other maintenance—it really doesn’t matter. What does matter is that you’d like to get it done sooner rather than later. If time is no consideration, then you’d likely just do one table at a time until you’ve done them all. Sometimes, a maximum degree of parallelization of one is less than ideal. You’re paying for more than one processor core, you might as well use it. The devil in splitting a workload out can be ensuring the tasks are well balanced. When I’m staging data in SSIS, I often use a row count as an approximation for a time cost. It’s not perfect – a million row table 430 columns wide might actually take longer than the 250 million row key-value table.
Click through for the script. For the R version, this Stack Overflow post shows how to do it with cumulative sums and the cut function.
Of course, probabilities greater 1 do not make sense. That’s the reason why we prefer a “bended” graph, such as the s-type ogive in logistic regression. Let’s plot that instead.
First, we need to get the survival probabilities:
d %>% mutate(pred_prob = predict(glm1, type = "response")) -> dNotice that
type = "responsegives you the probabilities of survival (ie., of the modeled event).
Read the whole thing.
Comments closedAntonio Sanchez Chinchon builds a shortest-path portrait generator:
In this experiment I apply an heuristic algorithm to solve the TSP to draw a portrait. The idea is pretty simple:
Load a photo
Convert it to black and white
Choose a sample of black points
Solve the TSP to calculate a route among the points
Plot the route
Click through for the code. This is an interesting application of the traveling salesman problem.
Comments closedDenny Cherry looks at a new Azure SQL Database announcement:
Today Microsoft has announced that there is a new way to buy Azure SQL DB. If DTUs aren’t making sense to you, you’ll be happy to know that you can now simply select how many vCores you want for your SQL DB workload. Now this will still require that you have an understanding on your workload to use this new vCore based way to buy Azure SQL DB, but Cores are a concept that is easy for people to talk about and wrap their heads around. Now this new model is only in preview at the moment, but I’m guessing that it’ll be around for a while in preview, then it’ll go GA as this new model makes sense.
I definitely prefer this model, as IT departments already understand the idea, whereas DTUs were nebulous at best.
Comments closedDavid Fowler shows how easy it is to encrypt database backups in SQL Server:
We all go to great lengths to make sure that our databases are secure (or at least I really hope that we do), we make sure that only authorised users have access and then only to the databases and levels that they need. We’re careful that all our passwords conform to crazy complexity rules. We’re a pretty security minded lot aren’t we?
But what about our backups? Most of the time they’re just sitting on, either a local disk on the server itself or some share somewhere. Wherever they are, what happens if someone manages to get hold of our backup files? They can easily just restore them onto a server where they have permissions and voila, all our super secret data is now theirs.
They could even just open the files with a hex editor read the data, it’s really not a difficult thing to do.
By default, SQL Server makes absolutely no effort to encrypt or in any way protect our backups. That’s pretty bad in my mind.
If you’re on Standard Edition, this became available in 2014. For Enterprise Edition, it was available earlier. At this point, I don’t think there are good reasons for not encrypting backups on production systems.
Comments closedSo, how do you do it when running SQL Server in Azure Container Services?
Well there’s a couple of options available.
The first one is to change the port that SQL is listening on in the container, open that port on the container, and direct to that port from the service.
The second one is to leave SQL Server listening on the default port and direct a non-default port to port 1433 from the service.
Read on to see Andrew try out both of these methods.
Comments closedTeo Lachev has a quick note on Azure Analysis Services and parallelism:
Anyone who’s done parallel programming knows that it’s not easy. Safe access and locks need to be used to synchronize access and protect shared resources. The AS xVelocity engine (aka Vertipaq) is a multi-threaded application that accesses RAM as a shared resource. As each thread competes for a global lock for memory allocation/deallocation, scalability decreases. Because processor cores reserve memory in chunks, called cache lines, additional synchronization is required when threads access memory location that are close to each other. Before SQL Server 2016 SP1, this was a serious issue with Tabular. As we’ve learned, starting with SQL Server 2016 SP1, Microsoft switched to using the Intel Threading Building Clocks (TBB) C++ library. Specifically, Tabular now uses the TBB scalable memory allocator. Mind you that TBB is not CPU-specific extensions, so any modern Intel CPU should get these benefits. “Analysis Services SP1 uses an Intel TBB-based scalable allocator that provides separate memory pools for every core. As the number of cores increases, the system can scale almost linearly” and “The Intel TBB-based scalable allocator is also expected to help mitigate performance problems due to heap fragmentation that have been shown to occur with the Windows Heap”. Further, starting with SP1, Tabular is NUMA aware with 4-node NUMA system but I don’t know if the AAS VMs are preconfigured for NUMA.
Teo doesn’t have any firm conclusions at this point, but his initial testing looks positive.
Comments closedWhenever I’m teaching and recovering from corruption, I always stress that if the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (which I’ll just call ‘repair’ from now on) has to be used, you should do the following:
- Run another DBCC CHECKDB to make sure that repair fixed everything and no new problems were exposed (by the repair fixing something that was blocking DBCC CHECKDB‘s access to a portion of the database)
- Run DBCC CHECKCONSTRAINTS on any tables or indexes that were repaired and are involved in constraints (stated in Books Online too)
- Reinitialize any replication subscriptions that are based on tables that were repaired (stated in Books Online too)
- Reevaluate your HA/DR strategy so you don’t have to use repair again in future
One question I’m often asked is about why replication can be broken by running repair.
Read on for the answer.
Comments closed