A Fine Slice Of SQL Server
Chris Webb explains how to show hidden Analysis Services cubes in Management Studio:
Unfortunately this doesn’t make any objects in the cube that are not visible, like measures or dimensions, visible again – it just makes the cube itself visible. However, if you’re working on the Calculations tab of the Cube Editor in SSDT it is possible to make all hidden objects visible as I show here.
Read on for the command and watch out for that caveat.
Comments closedImportant Notes:
Start-dfs.shwill start NameNode, SecondaryNamenode, DataNode on master and DataNode on all slaves node.Start-yarn.shwill start NodeManager, ResourceManager on the master node and NodeManager on slaves.- Perform
Hadoop namenode -formatonly once otherwise you will get an incompatible cluster_id exception. To resolve this error clear temporary data location for datanode i.e, remove the files present in $HADOOP_HOME/dfs/name/data folder.
If you’d like to set up your own Hadoop cluster rather than using one of the big vendors (Hortonworks, Cloudera, MapR) or a PaaS solution like HDInsight or ElasticMapReduce, this will give you a head start.
Comments closedGlenn Berry explains that the Enterprise Edition of SQL Server is still important for enterprises:
If you are using Columnstore indexes, you get the following performance benefits automatically, when you use Enterprise Edition:
Aggregate Pushdown: This performance feature often gives a 2X-4X query performance gain by pushing qualifying aggregates to the SCAN node, which reduces the number of rows coming out of that iterator.
Index Build/Rebuild: Enterprise Edition can build/rebuild columnstore indexes with multiple processor cores, while Standard Edition only uses one processor core. This has a pretty significant effect on elapsed times for these operations, depending on your hardware.
Local Aggregates: Enterprise Edition can use local aggregations to filter the number of rows passing out of a SCAN node, reducing the amount of work that needs to be done by subsequent query nodes. You can confirm this by looking for the “ActualLocallyAggregatedRows” attribute in the XML of the execution plan for the query.
Glenn’s focus is around columnstore indexes and DBCC CHECKDB, but there are additional benefits as well, with the separator being improved performance rather than different feature surface areas.
Comments closedRegardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.
The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.
Click through for the code. I enjoy asking this as an interview question. It’s a non-trivial problem with a non-trivial solution and isn’t a trick question.
Comments closedMichael Swart has a story on blocking due to an edge case scenario:
SQL Server was struggling to compile the procedure in time and the application wouldn’t let it catch its breath. The query optimizer was attempting to create statistics automatically that it needed for optimizing the query, but after thirty seconds, the application got impatient and cancelled the query.
So the compilation of the procedure was cancelled and this caused two things to happen. First, the creation of the statistics was cancelled. Second, the next session in line was allowed to run. But the problem was that the next session had already spent 28 seconds blocked by the first session and only had two seconds to try to compile a query before getting cancelled itself.
The frequent calls to the procedure meant that nobody had time to compile this query. And we were stuck in an endless cycle of sessions that wanted to compile a procedure, but could never get enough time to do it.
There are two important lessons here: how Michael solved the problem and also a reminder that plan cache entries are dependent upon specific application settings.
Comments closedJames Anderson continues his ReadyRoll series:
Right clicking the rows in the grid allows us to:
- review the generated script
- view revert scripts which can be used to reverse the changes
- view the object differences
If you click “view differences”, be aware that ReadyRoll opens a tab in Visual Studio but doesn’t switch focus to it automatically. Clicking the “Import and Generate Script” button will apply the changes to our ReadyRoll project.
Check out the entire series if you’re new to database deployment.
Comments closedKenneth Fisher rounds up one of the larger T-SQL Tuesdays I’ve seen:
Because let’s face it whole books are written on the subject and yet it’s one of the very first things a DBA should learn. Because it is one of those subjects everyone has to learn one way or another I had a large number of responses (which explains my delay in getting this rollup out, sorry about that). However, the large number of responses makes this list an excellent course on backup and recovery. It’s by no means comprehensive but if you read each of these posts you will have a great start into what’s necessary and what’s possible.
Click through for links to 25 blog posts on the topic.
Comments closedJason Brimhall explains an error message which might be confusing at first:
Here is the error message that is quite possible to encounter while creating principals.
Msg 15006, Level 16, State 1, Line 6
‘SomeDOmain\jason’ is not a valid name because it contains invalid characters.At first look, this error makes absolutely no sense. The error states there is an invalid character somewhere in the string “SomeDomain\jason”, yet every character in that string is supported and normal for the collation. This can be a head-scratcher for sure.
Read on for the answer.
Comments closedIt’s Christmas Day Observed, so no curation today. Instead, here’s a classic paper which has shaped the industry: Amazon’s Dynamo paper. Abstract:
Reliability at massive scale is one of the biggest challenges we face at Amazon.com, one of the largest e-commerce operations in the world; even the slightest outage has significant financial consequences and impacts customer trust. The Amazon.com platform, which provides services for many web sites worldwide, is implemented on top of an infrastructure of tens of thousands of servers and network components located in many datacenters around the world. At this scale, small and large components fail continuously and the way persistent state is managed in the face of these failures drives the reliability and scalability of the software systems.
This paper presents the design and implementation of Dynamo, a highly available key-value storage system that some of Amazon’s core services use to provide an “always-on” experience. To achieve this level of availability, Dynamo sacrifices consistency under certain failure scenarios. It makes extensive use of object versioning and application-assisted conflict resolution in a manner that provides a novel interface for developers to use.
This paper shaped Amazon’s Dynamo system, their DynamoDB offering on AWS, and a host of competitors, including Riak and Cassandra. That makes it worth reading.
Comments closedTom Zeng shows howt o install Jupyter Notebooks on Amazon’s ElasticMapReduce:
By default (with no --password and --port arguments), Jupyter will run on port 8888 with no password protection; JupyterHub will run on port 8000. The --port and --jupyterhub-port arguments can be used to override the default ports to avoid conflicts with other applications.
The --r option installs the IRKernel for R. It also installs SparkR and sparklyr for R, so make sure Spark is one of the selected EMR applications to be installed. You’ll need the Spark application if you use the --toree argument.
If you used --jupyterhub, use Linux users to sign in to JupyterHub. (Be sure to create passwords for the Linux users first.) hadoop, the default admin user for JupyterHub, can be used to set up other users. The –password option sets the password for Jupyter and for the hadoop user for JupyterHub.
Installation is fairly straightforward, and they include a series of samples you can get to try out Jupyter.
Comments closed