A Fine Slice Of SQL Server
Paul White expands upon NOEXPAND:
There is another consequence of not using the
NOEXPANDhint, which I mentioned in passing a couple of years ago in my article, Optimizer Limitations with Filtered Indexes:The
NOEXPANDhints are needed even in Enterprise Edition to ensure the uniqueness guarantee provided by the view indexes is used by the optimizer.
If you use indexed views in your environment, read this article.
Comments closedJack Li shows how to enable a trace flag without sysadmin or changing any application code:
The initial thought is to enable the trace flag at session level. We ran into two challenges. First, application needs code change (which they couldn’t do) to enable it. Secondly, dbcc traceon requires sysadmin rights. Customer’s application used a non-sysadmin user. These two restrictions made it seem impossible to use the trace flag.
However, we eventually came up with a way of using logon trigger coupled with wrapping the dbcc traceon command inside a stored procedure. In doing so, we solved all problems. We were able to isolate the trace flag just to that application without requiring sysadmin login.
This is the very edge of an edge case. In normal practice, change the code.
Comments closedKenneth Fisher asks if you check TRUSTWORTHY settings on your databases:
I wasn’t surprised (although a little disappointed) that out of the 9 people the answered only one person was, and of the rest 5 didn’t even know what TRUSTWORTHY is. I even had one person ask me later. That’s somewhat scary because under the right circumstances if you give me a database with TRUSTWORTHY turned on I can take over your instance. I’m NOT going to show you how but it isn’t terribly difficult.
I’ll admit that I have been a bit non-chalant about TRUSTWORTHY in the past, but turning it on is the smart move.
Comments closedCody Konior looks at slipstreaming SQL Server 2012:
There was a blog post by Boris Hristov which had some good images of the various places to look in the GUI to make sure that the updates have been picked up correctly. Using these and through experimentation I was able to answer those questions.
Interesting questions and good answers.
Comments closedDavid Alcock looks at a few common “fixes” which end up causing their own problems:
I’m seeing lots of CXPACKETS waits, how do I fix these?
Bad Advice = Set the maximum degree of parallelism to 1, no more CXPACKET waits!
I’m seeing index fragmentation occur quite frequently on some of my indexes, what should I do?
Bad Advice = Set fill factor on the server to 70, no more fragmentation problems!
I’m worried about TempDB contention. What should I do?
Bad Advice = Set the number of files to the number of cores, no more contention issues!
Read the post for better advice.
Comments closedJan Mulkens has started a series on combining Power BI and R.
Fact is, R is here to stay. Even Microsoft has integrated R with SQL Server 2016 and it has made R scripting possible in it’s great Azure Machine Learning service.
So it was only a matter of time before we were going to see R integrated in Power BI.
From the previous point, it seems that R is just running in the background and that most of the functionality can be used.
Testing some basic functionality like importing and transforming data in the R visual worked fine.
I haven’t tried any predictive modelling yet but I assume that will just work as well.
So instead of printing “Hello world” to the screen, we’ll use a simple graph to say hello to the world.
First we need some data, Power BI enables us to enter some data in a familiar Excel style.
Just select “Enter Data” and start bashing out some data.
I’m looking forward to the rest of the series.
1 CommentMeagan Longoria shows us how to use a variable to populate a lookup query in SSIS:
I already had my data flow populated with the lookup for MSA. I set it to full cache and entered a query in the connection to initially populate the fields that would be returned (simply my lookup query without the where clause).
Next, I opened the data flow properties, located Expressions and clicked on the ellipses to open the Property Expression Editor. I found the SQLCommand property for my MSA lookup and set it to my package variable that contained my query.
I had issues in the past with full cached lookups and variables. Fortunately, you can get around a lot of problems with expressions.
Comments closedAndy Mallon looks at the costs and benefits of data compression:
The obvious benefit is that compressed data takes up less space on disk. Since you probably keep multiple copies of your database (multiple environments, DR, backups, etc), this space savings can really add up. High-performance enterprise-class storage is expensive. Compressing your data to reduce footprint can have a very real benefit to your budget. I once worked on an SAP ERP database that was 12TB uncompressed, and was reduced to just under 4TB after we implemented compression.
My experience with compression is that the benefit vastly outweighs the cost. Do your own testing, of course.
Comments closedYou will also notice this is the end of your road for this installation without getting a success on this rule since the ‘Next’ button is grayed out.
Sure, you could go back and just create a new instance in the process to install SSRS on this node, but there has to be another way, right? If only we could skip this rule in the installation….
We can using command prompt!
Knowing how to install SQL Server from the command line (or Powershell) is important; this is just one reason why.
Comments closedMeagan Longoria shows us type 6 dimensions with BIML:
In my previous post, I provided the design pattern and BIML for a pure Type 2 Slowly Changing Dimension (SCD). When I say “pure Type 2 SCD”, I mean an ETL process that adds a new row for a change in any field in the dimension and never updates a dimension attribute without creating a new row. In practice, I tend to create more hybrid Type 2 SCDs where updates to some attributes require a new row and others update the value on the existing rows. A similar pattern that I find I implement more often than a pure Type 2 is a Type 6 SCD. A Type 6 SCD builds on the Type 2 technique by adding current attributes alongside the historical attributes so related measures can be grouped by the historical or current dimension attribute values. The only difference between what I call a hybrid Type 2 and a Type 6 is that in the Type 6, there are no Type 1 attributes in the dimension that do not also have a Type 2 version in the dimension to capture the historical values.
Dear Mr. President: there are too many types these days. Please eliminate three. I am NOT a crackpot.
Comments closed