A Fine Slice Of SQL Server
Grant Fritchey digs into an interesting combination of settings:
While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled?
This is my favorite kind of question because the answer is simple: I don’t know.
Click through to join Grant in learning what happens.
Comments closedAlex Whittles wants to show a month-by-month comparison including last December:
I came across an interesting MDX challenge this week; within a cube’s Date dimension, how to show December twice, once where it should be and again as the opening value for the following year. i.e. for each year I need to show Dec (prev yr), Jan, …, Nov, Dec.
Why? Well if you consider the following pivot chart, you can clearly see growth from Jan to Feb, Feb to Mar, etc., but it’s very difficult to see the growth between Dec and Jan.
The solution is easier than I would have expected.
Comments closedKevin Hill gets into Powershell:
What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.
Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉
One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.
1 CommentDenis Gobo shows how to specify a database when connecting to an instance using Management Studio:
One of our database on the development went in suspect mode today. This database was the default for a bunch of logins. These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.
I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.
Connecting to the default database is usually fine, but sometimes you need to specify one. Fortunately, Management Studio makes it pretty easy.
Comments closedArun Sirpal explains the READPAST hint:
So, as the name of the hint suggests, it literally does “readpast” it.
I’ve never used READPAST in any production code. I suppose there are good reasons to use it, but I haven’t run into any yet.
Comments closedAaron Nelson is advocating improvements to Powershell cmdlets around Analysis Services:
Frequently when developing updates to an SSAS cube I want to deploy my schema and process the dimension. Sometimes several of dimensions process successfully and then fails on one. At this point I go and correct the error, deploy the new schema, and then I only want to process all of my dimensions except the dimensions which did process successfully the first time. Sometimes this is really easy, but if you have a large number of dimensions this can become cumbersome since the only way to know which dimensions had been processed successfully or to right-click each dimension one at a time and find out, or to have memorized which dimensions had processed successfully on the earlier attempt. There can be a better way, and of course, PowerShell is one of those options. J The only problem is that as things currently stand, PowerShell is not as easy as it could be; the Invoke-ProcessDimension cmdlet doesn’t accept [direct] pipeline input. What is one to do when PowerShell isn’t as easy as it could be? File a Connect item of course!
Check out the Trello board. It’s been instrumental in helping Microsoft developers get the leverage they need to dedicate time to improving particular aspects of the product.
Comments closedWei Wang and Haimo Liu announce Hortonworks Data Flow version 2.1:
In the release of HDF 2.1, data flow administrators within the enterprise can identify that in order for certain potential processors to be added to a working data flow system, additional authorization would be required.
In addition, HDF 2.1 supports over 180 processors including newly introduced Connect/Listen/PutWebSocket, Put/FetchElasticsearch5, ValidateCsv, etc.
HDF is Hortonworks’s big play on simplifying streaming operations in Hadoop.
Comments closedAndrew Peterson shows how to start, stop, and restart the SQL Server service on Linux:
Start Service
sudo systemctl start mssql-server
He also shows how to do a status check. This is for distributions which use systemd, which includes the Red Hat distribution set (Fedora, CentOS, Red Hat Enterprise). If you’re on Ubuntu, there’s no support quite yet, but you can use start and stop.
Comments closedRob Farley has a set of questions you should ask yourself regarding your backups:
Does your disaster testing include a situation where a well-meaning person has taken an extra backup, potentially spoiling differential or log backups?
Does your disaster testing include random scenarios where your team needs to figure out what’s going on and what needs to happen to get everything back?
Something which might be helpful would be to catalog the reason why you restored a particular backup (or when somebody asks you for a backup but you can’t do it), and then have a plan to handle that scenario in the future.
Comments closed