A Fine Slice Of SQL Server
Manoj Pandey tells us that SQL Server 2016 CTP 3.1 is now available:
–> Following are enhancements in some areas:
1. New In-Memory OLTP improvements, including Unique indexes, LOB data types, and Indexes with NULLable key columns.
2. The COMPRESS and DECOMPRESS functions convert values into and out of the GZIP algorithm.
The list goes on, but item #1 is intriguing.
Comments closedMark Broadbent has a few nuggets of information regarding using variables in SSIS script components:
Notice that in the example above the assumption is that the SSIS variable datatype is compatible with the script variable type.
Once you have finished writing your code block you may save your code and close the Script Editor. All that is left is to click the OK button to close the Script Task Editor and run your package!
Getting variables to work in script components isn’t terribly difficult, but Mark shows that there are quite a few steps to the process.
Comments closedAndrea Allred shows a nice trick using the Object Explorer Details window in SSMS:
One of the tips that I was super surprised that many people didn’t know is the Object Explorer Details. It allows you to delete multiple objects at once, script out multiple objects at once and just do some really cool stuff. How do I access this magic you are asking? When in management studio, click on View>>Object Explorer Details.
For those one-off jobs where you need to script out a dozen objects, this is very helpful.
Comments closed
David Klee uses the registry to generate blue screens:
Voila! Use the USB keyboard registry key. Set it and reboot the machine. To trigger it, hold right-control and hit scroll lock twice.
BOOM! Immediate manually-initiated BSOD. Neat, huh?
Me, I just need to update my video card drivers; that gives me all the blue screens I want…
Comments closedEd Elliott has another nice tool in his SSDT Dev Pack:
This new tool for the SSDT Dev Pack adds a menu item (Tools–>SSDT Dev Pack –> Find Duplicate Indexes) what it does is scan all the projects in the solution (it doesn’t follow “this database” references to examine them, maybe a future version) and then print to the output window a list of all the duplicate indexes based on the table, the columns and included columns – I don’t check anything else so you might actually want a duplicate in some circumstances but these should be very few and far between.
If you double click on the index it will take you to the location in the code where it actually is so you can delete it 🙂
A very useful tool gets even more useful.
Comments closedSean McCown goes into fixing one example of the “Cannot Generate SSPI Context” error:
Now, this was just a quick tutorial on how to manage SPNs. This hole can go pretty deep. Here’s a decent link on MSDN for troubleshooting SPNs. I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc. If I remember correctly it’ll also help you choose the right SPN.
This is a classic example of a bad Microsoft error. In this case, it’s bad because there are multiple root causes for the same error and because the message itself is unhelpful.
Comments closedSteve Jones shows us the Test-Path cmdlet:
One of the enhancements I wanted to make was check if the file exists, and if not, then download it. However, if it does exist, then I’ll skip the file. I know this means I don’t get updated files if schedules change, which is possible, but in that case, I can just delete the file from my desktop and go from there.
I made a quick search, and found a few links to the Test-Path cmdlet. Essentially you give this a file path and it returns true or false. Almost exactly what I need.
Test-Path is small but helpful, and a vital part of scripts which check files.
Comments closedMatt Allington walks us through solving a problem using DAX:
Average of best 8 scores from last 20 rounds
So the requirement is to find the average of the best 8 scores from the last 20 rounds of golf for each player. So if you think about that problem, there are quite a few layers to it – perfect for a blog on how to break a problem into pieces so you can solve it in DAX.
Even if you do nothing with DAX, read over the post because the problem-solving technique Allington uses is generally applicable.
Comments closedKendra Little shows us something awesome about Query Store and OPTION(RECOMPILE):
YAY! For all my queries that were run with RECOMPILE hints, I can see information about how many times they were run, execution stats, their query text and plan, and even information about compilation.
And yes, I have the execution plans, too — the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works.
This is great news. Query Store is going to be a big feature for DBAs.
Comments closedNiko Neugebauer, in part 73 of his ongoing columnstore index series:
This result was observed right after the finish of the loading script, where we can clearly see 4 Delta-Stores for 10 Million Rows. 3 of the Delta-Stores are Closed and 1 Delta-Store is Open, which is an absolutely impossible combination if we think about Clustered Columnstore Indexes, where one would expect to have 10 Compressed Row Groups or 10 Delta-Stores (9 Closed & 1 Open).
If you take a more detailed look at the associated sizes of the closed Delta-Stores, you will see that they increase each time a new Delta-Store is being used. For example, the first one is capped at 1.048.567 Rows, the second one is capped at 2.097.152 and the last closed Delta-Store is set to 4.193.904 Rows – meaning that the size is being constantly doubled.
I’d like to see this as the first step toward expanded sizes for compressed rowgroups.
Comments closed