Press "Enter" to skip to content

Month: January 2016

Filtered Indexes Are Tricky

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.

  1. Remove the parameters and use literals. (not practical)

  2. Use Dynamic SQL

  3. 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 closed

Installing TFS

Sifiso 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 closed

Power BI Matrices

Meagan 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 closed

Compess An Entire Database

Shaun 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 closed

Row-Level Security

Manoj 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 closed

SQL Server Wants Your RAM

Andy 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 closed

MSDTC + SSIS

Kenneth 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 closed

Setting Up TempDB

Chris 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 closed