Press "Enter" to skip to content

Author: Kevin Feasel

Compess An Entire Database

Published 2016-01-11 by Kevin Feasel

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

Published 2016-01-11 by Kevin Feasel

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

Published 2016-01-11 by Kevin Feasel

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

Published 2016-01-08 by Kevin Feasel

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

Published 2016-01-08 by Kevin Feasel

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

Starting Query Tuning

Published 2016-01-08 by Kevin Feasel

Tim Radney has in introduction on how he tunes a SQL Server instance:

Typically the common compliant when someone’s stating they need to tune a SQL Server is that it’s running slow. What does slow mean? Is it a certain report, a specific application, or everything? Did it just start happening, or has it been getting worse over time? I start by asking the usual triage questions of what the memory, CPU, and disk utilization is compared to when things are normal, did the problem just start happening, and what recently changed. Unless the client is capturing a baseline, they don’t have metrics to compare against to know if current stats are abnormal.

Tuning is about method and tools (in that order).  I like the way Tim does both.

Comments closed

Why Use SSDT?

Published 2016-01-08 by Kevin Feasel

Ed Elliott has a three-part series on database projects in SQL Server Data Tools.

Part 1:  What is SSDT?

The SSOX or SQL Server Object Explorer is a cool utility that lets you connect to a live database and do things to it like debug stored procedures or update individual objects. It also lets you see a view of you projects after all references have been resolved so if you use “Same Database” references you can see how your end project will end up – really useful.

Part 2:  Deploying projects

Using the DacServices via whatever method you want (schema compare, sqlpackage, powershell, something else?) really makes it simple to spend your time writing code and tests rather than manual migration steps. It constantly amazes me who well rounded the deployment side of things is. Every time I use something obscure, something other than a table or procedure I half expect the deployment to fail but it just always works.

Over the last couple of years I must have created hundreds if not thousands of builds all with their own release scripts across tens of databases in different environments and I haven’t yet been able to break the ssdt deployment bits without it acyually being my fault or something stupid like a merge that goes haywire (that’s one reason to have tests).

Part 3:  the .Net APIs

The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.

The second way it can be used is to take objects and create T-SQL.

I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn’t guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table’s deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.

This is a great series with a lot of informative links.

Comments closed

Always Encrypted

Published 2016-01-07 by Kevin Feasel

Warner Chaves has a video introducing Always Encrypted:

This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.

Always Encrypted strikes me as something that will be incredibly useful for 2-3% of the population, somewhat painful for 3-5% of the population, and completely ignored by the rest.  I’m currently on the fence about whether, three years from now, I will consider “completely ignored by the rest” to be a shame.

Comments closed

Anglicize Values

Published 2016-01-07 by Kevin Feasel

Dave Mattingly shows an easy way to anglicize values:

If your customer’s name is “José” but you search for “Jose”, you won’t (by default) find him.

Here’s a simple way to take care of that in your SQL database, without changing the data that you have.

If a particularly system only needs to support one language (e.g., English), this can be helpful, at least until somebody throws in Chinese or Hebrew characters.  That said, supporting Unicode is the best move when available.

Comments closed