A Fine Slice Of SQL Server
Koos van Strien discusses warehouse testing:
Case: we’ve integrated two sources of customers. We want to add a third source.
Q: How do we at the same time know that our current integration and solutions will continue to work while at the same time integrating the new sources?
A: Test it.
Q: How do we get faster deployments and more stability?
A: Automate the tests, so they can run continuously.
This is an interesting concept; do read the whole thing.
Comments closedTomaz Kastrun shows how to save R plots in SQL Server FileTable:
FileTable has been around now for quite some time and and it is useful for storing files, documents, pictures and and binary files in a designated SQL Server table – FileTable. The best part of FileTable is the fact one can access it from windows or other application as if it were stored on file system (because they are) and not making any other changes on the client.
And this feature is absolutely handy for using and storing outputs from Microsoft R Server. In this blog post I will focus mainly on persistently storing charts from statistical analysis.
I can see this being quite useful for things like automatically sampling data for quality control.
Comments closedKendra Little answers a question about indexing foreign key constraints:
We recently had a SQL Server performance assessment. It remarked on two things that made me think:
1 ) tables with foreign keys but missing supporting index
2 ) tables with indexes that are not usedBut in our case the remark in Case 2 was on a index that supports a foreign key!
Curtain down!
Now to my question, in which cases should you as a rule create indexes to support a foreign key?
I think Kendra elaborates the pros and cons well. I’d lean against automatically creating indexes because I’ve worked with scenarios in which you don’t drive from the Parent to the Child; instead, you drive from Child to Parent or Something to Child to Parent and those indexes go unused.
Comments closedAllison Tharp has a script to create SSMS snippets:
One issue with SSMS’s Snippet Manager is that the code has to be stored in a .Snippet file in a specific location on your local machine. The .Snippet files are in XML format, which is a little inconvenient since we don’t write SQL in XML format. This means that in order to add a new snippet, you need to create a new .Snippet file in the correct format, add the SQL to it, and save it in the right location. After that, you must import the file through SSMS.
I have had ideas for snippets, but it is enough of an inconvenience to sometimes prevent me from going through the motions of making an actual snippet file. I wanted to write some kind of code that would allow me to save a SQL file into a specific folder and let an automated program convert it into the Snippet format. I’ve also been wanting to learn PowerShell, so I thought this would be a good opportunity!
The next step would be turning that into a cmdlet.
Comments closedWe know that the problem is remote applications being unable to connect to SQL Server. I want to simplify this and get the gateway out of the picture.
A lot of times we can become fixated on the app we are looking at and ignore other possibilities. You may know that locally works and remote does and start looking at why the gateway specifically isn’t working. Maybe you start to think something on the Power BI side is broken, or maybe we can’t talk to the Azure Service Bus. This is why I like to remove the gateway from the picture. Or, if you had a custom application, let’s remove that from the picture. Simplify as best you can. This helps to exclude a lot of potential complexity.
How do we do that? You can use the same test that you used locally! Use something like a UDL file or another tool to see if it can connect. Because it is SQL Server, I’d really like to try Management Studio. Management Studio uses .NET as does the gateway. To start, it doesn’t necessarily have to be the same machine as the gateway. If you have a different machine that has Management Studio installed, use that! If that works, then we may want to do the test specifically from the gateway machine.
This is a good troubleshooting guide, dealing with some of the more likely causes before moving on to the esoteric issues.
Comments closedSo How Do I Get To The Execution Plans?
So when I look at a deadlock graph, I can see there are sql_handles. Given that, I can grab the plan_handle and then the query plan from the cache, but I’m going to need to collect it automatically at the time the deadlock is generated. So I’m going to need
XML shredding skills
Ability to navigate DMVs to get at the cached query plans
A way to programatically respond to deadlock graph events (like an XE handler or a trigger)
If you don’t have the funding to get a third-party tool in place which collects this information, this could be a good fit.
Comments closedPaul Randal gives a very special case in which heap pages can be (temporarily) linked together:
Now the pages are linked together!
Note that this is an OFFLINE rebuild, which is the default. What happened is that the offline ALTER TABLE … REBUILD operation uses the part of the underlying functionality for an offline ALTER INDEX … REBUILD operation that builds the leaf level of the index. As that functionality builds a doubly-linked list of pages, the newly rebuilt heap initially has a doubly-linked list of pages! This doesn’t happen for an ONLINE rebuild of the heap, which uses a totally different mechanism.
Although the pages appear doubly-linked, that’s just an artifact of the mechanism used to build the new heap – the linkages aren’t used or maintained.
In other words, you get zero benefit from this. Click through to see Paul’s test script.
Comments closedPaul Popovich gives tips on how to speed up restores from a DataDomain device:
Recently we had to restore our 5TB prod database to rebuild an AG node. Here’s some things we learned along the way to hopefully help you speed up your restores.
Backup your VLDB to multiple files. We found 12 to be the sweet spot in our setup. Make sure you’re going to 10gig NICs on both ends of the transfer.
In terms of folder directories on that thing we learned to go wide and small. Let me explain. Our setup is this:
\\datadomain\sql\sqlbu\\\Full or Tlog
Click through for more tips. This is independent of optimizing your restore scripts themselves.
Comments closedMax Trinidad explains how to get Powershell running with Visual Studio Code on Ubuntu:
Using VS Code Debug
First, we are going to use VS Code debug option to run PowerShell Out-Of-The-Box. This way we can be use debug to execute and step thru the PowerShell script code.
Open the folder were the scripts are going to be stored. The first time using the Debug it will ask to create the “launch.json” which is needed in order to execute and debug the script. Accept the default values as you can change them later if needed.
Read on; it’s a whole new world…
Comments closedJames Anderson uses ReadyRoll to perform database deployments:
Migration Scripts
ReadyRoll automatically generates and adds migration scripts to our project in Visual Studio. This means almost all the manual work of writing (or generating with compare tools) migration scripts is done by ReadyRoll. ReadyRoll can also organise these scripts, using semantic versioning, into a logical folder structure within our project.
This post is a continuation in his database deployment automation series; this post talks mostly about what ReadyRoll is and how to install it.
Comments closed