Press "Enter" to skip to content

Day: December 17, 2015

Table Sampling

Published 2015-12-17 by Kevin Feasel

Ginger Grant shows a couple of techniques for sampling from tables:

The random sample that TABLESAMPLE provides is based on the number of data pages, not the number of records. If you want the number of rows to be specifically limited you will need to use Top(n) as well. I’ve written all my samples based upon AdventureWorksDW so you can run them for yourself later. I’ve listed the variety of ways to call TABLESAMPLE and shown the number of records returned.

TABLESAMPLE is useful for spelunking, but is somewhat limited otherwise.

Comments closed

Data Compression

Published 2015-12-17 by Kevin Feasel

Corey Beck on data compression:

Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage.  Since page compression includes row compression, we will start with row compression and the estimated savings.

EXEC sp_estimate_data_compression_savings
‘Person’,’Person’,null,null,’row’

In practice, data compression is extremely valuable and in most circumstances, the benefits outweigh the costs.  In certain workloads, you might even see CPU usage go down.

Comments closed

Watch Named, Nested Transactions

Published 2015-12-17 by Kevin Feasel

Gail Shaw finishes her outstanding series on transactions:

The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can’t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and/or the transaction log to grow.

It’s not just that someone in the future might call the code from another stored proc within a transaction, it’s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction

Read the whole thing.

Comments closed

Set SQL Server Startup Parameters With Powershell

Published 2015-12-17 by Kevin Feasel

Mike Fal has a function for managing SQL Server startup parameters:

Looking back at the previous blog post, changing the the startup parameters through the SMO is pretty easy with the ManagedComputer class. In some ways, it is too easy. As Shawn calls out, you could easily overwrite the full string and remove the startup locations for your master database (and breaking your instance). This is where tool building can be such an aid, because by wrapping the change code in a function, we can build some safety mechanisms to protect us (or others) from doing harm when trying to make this sort of change. The function I wrote is not terribly long, but I’ll spare you the whole thing by letting you view it on GitHub. We’ll use our time better by going over how I constructed it while focusing on some of my tool building principles.

Thanks to Mike for making that available to the community.

Comments closed