A Fine Slice Of SQL Server
Shane O’Neill looks at a few Connect items relating to indexed views:
First contender: Inserting to an indexed view can fail
What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.
Click through for more bugs.
Comments closedLouis Davidson plucks an old Connect item out for a new look:
The concept is very similar to a DEFAULT constraint, with two differences:
1. Will work on an UPDATE operation, without specifying DEFAULT
2. Could be configured to disallow the user from entering a value. My proposed syntax was pretty simple:
AUTO [WITH OVERRIDE] (scalar expression)
Now I realize that 10 years ago, I didn’t take terribly long to consider that WITH was a terrible thing to add to the syntax, and AUTO is a keyword already, so I am going to rename it: AUTO_DEFAULT (scalar expression, [option]). Since I have thought a bit more about this in the years since writing it, I realized there were a few more options that would be nice. I was terrible in college doing syntax parsing, but the syntax itself is not important. Temporal in SQL Server 2016 has syntax that is similar to this for the new temporal columns which I got really excited about the first time I saw it: SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL. Maybe in vNext?
Read the whole thing. Then check out the related Connect item Adam Machanic submitted. I’d love to see that functionality, given how frequently I create these metadata columns.
Comments closedAdam Machanic peruses the top-rated Connect items:
Magic numbers! That sounds cool. Except it’s not. It’s a horrible drain on readability and a pox on the manageability of your code. But we need them, because every database out there has “lookup tables” with specific values that we need to predicate on throughout the code base. SQL Server could solve this problem once and for all by allowing users to promote these values to named enumerators. How cool would that be?!? 220 votes, nine years.
And finally, one more from Mr. Ben-Gan, a clever idea for improving TOP with the OVER clause. I love the OVER clause. I love TOP. Their offspring would be beautiful indeed… 180 voters over the past nine years have shared in this sentiment.
This is an interesting list.
Comments closedRob Farley discusses a successful Connect item’s implementation:
I opened it up, and sure enough, no sign of that 7,276 value. It looks just the same as the estimated plan I just showed.
Getting plans out of the cache is where the estimated values come into their own. It’s not just that I’d prefer to not actually run potentially-expensive queries on customer databases. Querying the plan cache is one thing, but running queries to get the actuals – that’s a lot harder.
With SQL 2016 SP1 installed, thanks to that Connect item, I can now see the Estimated Number of Rows to be Read property in estimated plans, and in the plan cache. The operator tooltip shown here is taken from the cache, and I can easily see that Estimated property showing 7,276. This is shown from Management Studio because Plan Explorer doesn’t yet call out this property explicitly:
If you’re looking to use SQL Server 2016 SP1, read the whole thing; this will make query tuning without running those horribly expensive queries a bit easier.
Comments closedArun Sirpal would like to see database-level wait stats:
Wait Stats is my “go-to” thing, when you want to dig into performance issues everyone knows you will probably end up using sys.dm_os_wait_stats. You cannot use this in the Azure world, you have to use a DMV that is scoped to the database level. I think this would be a nice idea to have with the “earth” based SQL Servers – the ability to return information about all the waits encountered by threads that executed at the database level.
The connect item can be found at this link: https://connect.microsoft.com/SQLServer/feedback/details/2647332/sys-dm-db-wait-stats-dmv?tduid=(262281c4c73a682498780643b77e80d1)(256380)(2459594)(TnL5HPStwNw-KjxCoz0pGWobbq7q1MQTIw)()
I like this idea.
Comments closedMelissa Connors would like to control stack order for a stacked chart in Power BI:
Currently, it’s displaying the stacked column in alphabetical order from the bottom up (coyote/red on the bottom, fox/black in the middle, and wolf/gray on the top). I want the ability to display it by the count for each type, not the name. In this example, I want the order of coyote, wolf, and fox. I believed that there must be a way to control the order within a stacked column, so I clicked all over the UI and fired up the Google.
Read the whole thing and vote for this Power BI idea if it’s interesting.
Comments closedJames Anderson would like a query hint to force parallelism:
I would like to see a hint that causes the optimiser to consider a parallel plan no matter the cost of the query. It’s possible to get this behaviour with trace flag 8649 but it’s unsupported by Microsoft and therefore unfit for production use.
I only tend to use query hints as a very last resort. It’s almost always better to allow the optimiser to make these decisions and continue to reevaluate these decisions as your data changes but sometimes they can be a get out of jail free card.
I’ve written about this before and so has Paul White. Paul created this connect item for something similar. Please vote it up if you think it could help with your query tuning.
Click through for the full argument, and then hit the Connect item if you agree.
Comments closedLori Edwards wants to fix one of the most annoying error messages in SQL Server history:
The Connect item that I’m promoting today is Connect #339410 : Please fix the “String or binary data would be truncated” message to give the column name. This Connect item was opened in 2008 and has 1,328 upvotes. If we can create virtual realities, we should be able to add a column name to an error message. Help us Microsoft, you’re our only hope.
This error message is incredibly annoying, particularly because it seems the engine should know which record has an error and on which column that error is. We finally got information on which row caused a primary or unique key constraint violation, and that was helpful; this would be at least as helpful.
Comments closedAaron Bertrand has a series of Connect items of interest:
There are so many active Connect items, it’s difficult to choose just one, but after spending some time looking at the
generate_seriesfunction in PostgreSQL, the one at the top of my list right now is this suggestion from Erland Sommarskog (blog):Connect #258733 : Add a built-in table of numbers
I think it would be cheap and easy for SQL Server to provide a defaut table of numbers, always in memory, just like any other catalog view or DMV. This thing can use compression now, regardless of edition, so even a table with 2 billion rows will only take 13 MB, and should be easy to populate either at start-up or on first use. I could list out all of the potential uses for a numbers table, but they’re fairly well documented in the following sources (and most use cases don’t need anywhere near 2 billion values, so maybe the range could be defined using a
sys.configurationsor database-levelSCOPED CONFIGURATIONsetting)
Click through for additional Connect items, and please vote on any Connect items which catch your eye.
Comments closedJens Vestergaard shows how to take backups of Analysis Services cubes:
I have not met a setup where applying compression was not an option, yet. Obviously this has a penalty cost on CPU while executing the backup, and will affect the rest of the tasks running on the server (even if you have your data and backup dir on different drives). But in my experience, the impact is negligible.
This may not be the case with the encryption option, as this has a much larger foot print on the server. You should be using this with some caution in production. Test on smaller subsets of the data if in doubt.
Another thing to keep in mind, as always when dealing with encryption, do remember the password. There is no way of retrieving the data other than with the proper password.
My goal is to be able to rebuild any cube from the relational database, but even with that goal in mind, it is smart to have backups.
Comments closed