I’m a huge fan of Query Store, which regular readers may know, but there’s a need to write a bit more about Query Store best practices. This isn’t a “you must use this feature” post, this is a “here is what you must know if you want to use this feature” post.
I have a lot of content about Query Store, but maybe what’s really important gets lost amongst everything else. Glenn prompted me to write this, after I worked with two customers last week that ran into issues related to their configuration of Query Store. Listed below are the things you must know before you enable Query Store. If you already have Query Store enabled and running, I recommend reviewing the information to ensure you are following best practices.
Click through for the full set of practices and links to additional details.
Variables with a regression coefficient equal to zero after the shrinkage process are excluded from the model. Variables with non-zero regression coefficients variables are most strongly associated with the response variable. Therefore, when you conduct a regression model it can be helpful to do a lasso regression in order to predict how many variables your model should contain. This secures that your model is not overly complex and prevents the model from over-fitting which can result in a biased and inefficient model.
Read on for demonstrations.
To kick off the Big Data Cluster “Default configuration” creation, we will execute the following Powershell command:
mssqlctl cluster create
That will first prompt us to accept the license terms. Type y and Enter.
Mohammad takes us through the default installation, which requires only a few parameters before it can go on its merry way.
Ok, simple enough to fix: we just need to do what the error message says and SET IDENTITY_INSERT ON for both tables:
SET IDENTITY_INSERT dbo.User_DEV ON; SET IDENTITY_INSERT dbo.StupidQuestions_DEV ON;
And… it still didn’t work:
IDENTITY_INSERT is already ON for table 'IdentityTest.dbo.User_DEV'. Cannot perform SET operation for table 'dbo.StupidQuestions_DEV'.
Click through for the ramifications and your alternative.
Because deadlocks happen when two task permanently block each other, without a deadlock, both process will simply block forever. Of course this could never be good in a production system. It is important that these situations be identified and dealt with in some manner. This is where SQL Server database engine steps in, it is frequently searching the lock manager looking for deadlocks.
Click through for the answer.
At this point, extracting the first row from Results which would contain the string to produce in the report, is sufficient. TOPN is the function extracting that row, but there is a major drawback here: in case of a tie, TOPN returns all values involved.
In case TOPN returns multiple rows, it is necessary to show them one after the other so to make it clear that the result is not unique. CONCATENATEX is an iterator that concatenates strings and produces a single string out of a table.
This is a good demonstration of a useful function.