The Bit Bucket

Invoke-SqlCmd4 - A Superior SQL Commandlet

Recently, I wrote about one of the issues with the Invoke-SqlCmd commandlet where it sets the ApplicationName when you use the parameter that should set the host.

Fellow MVP Ben Miller sent me a copy of the Invoke-SqlCmd3 that they were using. It was much better.

I then realized that there were many other options missing from these commandlets (such as options for Database Mirroring and Availablity Groups) and so I set about improving it.

2015-04-03

Optimizing Dynamic Search Screens – Fixing the Performance of Common Code Paths

One of the common challenges that arises in many applications is around how to optimize the SQL Server performance of dynamic search queries. For example, we’ve all seen applications with screens like this:

clip_image002

Often, there will be many, many more fields to be searched. What I often hear from developers is that there is no way to optimize the query because you don’t know in advance which options the user will choose. While this is partly true, it assumes that human behaviour is much more random than it really is. Let me explain:

2015-04-01

Power BI Designer March Update includes Google Analytics Connector

The Power BI team have released details of their March update to the standalone Power BI designer.

The first thing I noticed is just how much faster the tool operates. The blog post mentioned performance enhancements but I really, really noticed them.

One particular enhancement  that I wanted to call out was the additional of a connector for Google Analytics. I’ve been trying that this morning and have found it really easy to use. All the standard categories appear as available sets of data:

2015-03-27

Powershell Invoke-Sqlcmd –Hostname Parameter sets the Application Name instead of the Host Name

Two of the parameters in SQL Server connections are the Application Name and the Host Name. You can see these in SQL Server if you execute the following command:

image

I’ve always been a fan of having applications identify themselves in their connection strings. It makes tasks like tracing much easier. The tools supplied with SQL Server do a reasonable job of that as you can see above. But many other tools don’t do such a good job.

2015-03-19

Rebuild Partitioned Clustered Columnstore Indexes

A short time back, I provided an updated script that can be used to rebuild clustered columnstore indexes when necessary.

One thing that wasn’t covered by that script was the ability to only individual index partitions. Below, I’ve provided an updated version of the script that will rebuild only affected partitions if the clustered columnstore index is partitioned.

-———————————————————————

-- Rebuild clustered columnstore indexes when necessary

--

-- Dr Greg Low v2.2

2015-03-05

Suggest Compression Strategies for Tables and Indexes

I make no secret of the fact that I think that table compression was the best thing added to SQL Server 2008.

It seems like every week that I’m talking to clients about it. They have Enterprise Edition but they weren’t using it because they were familiar with O/S level disk compression and they think that compression will mean smaller and slower, yet this often couldn’t be further from the truth for customers whose databases are currently I/O bound or who don’t have enough memory in their buffer cache for it to work correctly.

2015-03-05

Lots of (Mostly Free) eBooks from Microsoft Virtual Academy

I know that many of my readers appreciate finding out when free eBooks become available.

Microsoft Virtual Academy has been releasing quite a few lately.

In particular, SQL Server and database folk might want to take a look at the following:

  • Introducing Microsoft SQL Server 2014
  • Introducing Windows Server 2012 R2
  • Introducing Microsoft Azure HDInsight
  • Microsoft Azure Essentials: Fundamentals of Azure
  • Microsoft Azure Essentials: Fundamentals of Azure
  • Introducing Windows Azure for IT Professionals
  • Rethinking Enterprise Storage: A Hybrid Cloud Model

2015-02-20

Interested in Winning a Trip to the Ignite Conference?

This is the first year for Microsoft’s new Ignite conference. Many people are keen to go but figure it would break the bank.

image

Here’s another option:

The Microsoft Virtual Academy have their Heroes program running again. You get one point in the draw for just signing up. Even better though, take some courses and both learn something worthwhile and increase your chances of winning the competition:

image

Sign up and enter here now: SIGN UP           (NOTE: Australians Only for this one sadly)

2015-02-16

Clustered columnstore index rebuild script–updated

Recently I published a series of blog posts that provided scripts for rebuilding clustered columnstore indexes.

Niko Neugebauer sent me a suggested update to the latest version of those scripts and it makes sense. The change will help to filter out row groups that are not compressed and Niko noted that as Microsoft have started to include more objects (like Tombstone values)  into sys.column_store_row_groups in Azure SQLDatabase, he believes that will help to prevent future SQL versions having a problem with the scripts.

2015-01-31

PASS BA Analytics Conference for 2015

I’m always telling people that the thing I love about BI projects is that they tend to appeal to the people who pay the bills. This is a good place to be doing project work.

When I look at large companies like Amazon, I can imagine that the people who do the IT work that relates to order processing, invoicing, shipping, etc. do a great job. But I’ll bet that their life is full of budget cutbacks, headcounts, increased pressure to improve productivity, etc. By comparison, I’ll bet the projects that fund parts of their site that make recommendations for possible purchases, etc. are funded at an entirely different level. And that’s because these projects can more directly impact the profitability of the organization, and do so in a more visible way.

2015-01-28