A Fine Slice Of SQL Server
Kevin Eckart investigates filtered indexes not being used:
This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.
Remove the parameters and use literals. (not practical)
Use Dynamic SQL
Use OPTION(RECOMPILE) at the bottom of the query.
This is the classic issue with filtered indexes: you expect them to be used, but when you check the plan, they aren’t.
Comments closedSifiso Ndlovu shares some tips regarding installing Team Foundation Server:
Although, you can get away with using convenience names (i.e. a dot, (local), or locahost) as SQL Server identifier name during the configuration of a SharePoint server farm as shown in Figures 2 & 3, such a practice is not allowed during configuration of TFS (as shown in Figure 1).
Check this out before installing your own TFS server. Or use Visual Studio Online or GitHub or BitBucket or …
Comments closedSunil Agarwal talks about clustered columnstore index logging:
There you have it; our recommendation is to choose a batchsize of > 102400 to get benefits of minimal logging with clustered columnstore index. In the next blog, I will discuss parallel bulk import and locking optimizations.
My experience is that you really want to insert in large batches.
Comments closedMeagan Longoria wants to stack groups of measures in Power BI:
To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically. Note: this is true as of 8 Jan 2016 but may change in the future. If you agree that this should be a feature in Power BI, please make your voice heard and vote for this idea on the Power BI forum and encourage others to vote for it as well.
The answer is a little complex. Considering how frequently Power BI gets updated, hopefully they’ll make this a bit easier in the near future.
Comments closedShaun J. Stuart has a script which compresses all (compression-worthy) objects in a database:
Reader Dick H. posted a comment on my last version of this script stating that he got an error when this was run against tables containing sparse columns. Data compression does not support tables with sparse columns, so they should be excluded from this process. I’ve modified this script to correct this. I don’t have any tables with sparse columns in my environment, so thanks to Dick for pointing this out!
For instructions on using this script, look here.
This is a very useful script to have in your back pocket.
Comments closedManoj Pandey investigates row-level security:
Here in this post I will talk about the new Block Predicate option available in the CTP 3.0 release. With this option we can restrict write access for specific users.
Block Predicates block all write operations like:
– AFTER INSERT and AFTER UPDATE
– BEFORE UPDATE
– and BEFORE DELETE
I want this to perform well in a busy production environment. I really, really do.
Comments closedAndy Galbraith explains that SQL Server loves RAM:
A frequent complaint we receive comes from a client that has an in-house sysadmin monitoring tool like Microsoft SCOM/SCCM. They turn the tool on and it startsred-alerting because the Windows server hosting SQL Server is at 90%+ used RAM. The sysadmin (or local DBA) logs on to the server and finds that there is 5GB free (~4%), and the sqlservr.exe process is using 120GB of the 128GB on the server!
In my experience, VMware administrators tend to be most hung up about this concept.
Comments closedKenneth Fisher discusses how to get MSDTC working with Integration Services:
tl;dr; The MSDTC service has to be not only turned on, but configured on all of the machines involved. Including the machine running the SSIS package (possibly a workstation).
Configuring remote MSDTC, sure. Configuring local MSDTC, though, is something I hadn’t realized was important, at least if you want to use SSIS transactions. Probably goes to show how often I use SSIS transactions…
Comments closedChris Shaw tells us to optimize TempDB:
By default when you install SQL Server the TempDB database is not optimized. For SQL Servers that use the TempDB even moderately, I recommend optimizing it for the best performance. The TempDB is the storage area that SQL Server uses to store data for a short periods of time, information that can be found in the TempDB include temporary tables, and data that is being sorted for query results. The data in the TempDB is so temporary that each time the SQL Server service restarts, the TempDB is recreated.
Good advice within.
Comments closedBuck Woody shares some tips regarding learning R:
Another mistake I see a lot in beginning R students is forgetting that R cares about case. In other words, the variable “a” is a separate thing than the variable “A”.
NOTE: Package names can be case-sensitive as well.
A lot of this comes down to “learn the syntax.”
Comments closed