cjsommer.com

Maybe you’re new to SQL Server or maybe you’re just expanding into performance tuning a little more. Query tuning can be a very daunting task and sometimes it’s hard to decide where to look first. In this post I am going to show you a couple of my go-to methods for troubleshooting slow performing queries in SQL Server. This is not a “ZOMG my SQL Server is slow” list. This post is more geared to when I have been tasked to dig into a very specific query. This isn’t a be-all-end-all list, but its a good place to start and has served me well.

As you work with SQL Server look around you. Is your environment Naughty or Nice? If it is Naughty what’s wrong with it? What would you do to fix it? Do you have a scrooge that is giving you the Christmas chills? Perhaps you have servers of past, present, and future haunting you. Maybe you are looking at SQL Server 2016 like some bright shining star in the east.

“Hey DBA’s, I have a thought about a really creative solution to this really hard problem. I have two tables in my database and each has an identity column, each of which is currently incrementing by 1. I’d like to change each to increment by 2. Also, one of the tables will need to only contain ODD numbered values, and the other will need to only contain EVEN numbered values. This will help me solve my really hard problem."

Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling is handled differently for the .NET Framework data providers.

Opening connections is a very expensive and time consuming operation. Connection pooling helps alleviate that by providing a reusable “pool” of connections that your application can share. Connection pooling is usually a good thing. Unfortunately I have found that it’s sometimes not very well understood. This blog post starts with a short story…

For many years I didn’t really think about the implications of how I was retrieving data from my SQL Servers in PowerShell. I was just happy that I was able to retrieve the data! As I learned more about SQL Server I started to think of things like SQL injection and using parameterized queries to promote plan reuse.

I went back and looked at some of the old PowerShell scripts that I had written and found that I was way off! Most of the old scripts would be prime candidates for SQL Injection. I wasn’t really concerned early on because I was the one running my scripts and passing in the parameters, but as my scripts became more automated and database driven, they became more vulnerable to SQL Injection. And parameterized queries? What the heck is a parameterized query? I had no clue when I first started out.

I was doing a database code deployment the other night and the first step in the release plan was “Take a full database backup in case we have to roll back the deployment”. The database in question was about 100 GB in size, so not huge, but not small either. On our current hardware a full backup takes anywhere from 15-20 minutes on average. That’s 15-20 minutes longer than it needed to be because I knew there was a better way to offer that same rollback protection. The database snapshot.

Thanks to MidnightDBA (b/t) for hosting this month’s #tsql2sday.

Strategies for managing an enterprise.

What do you think of when you hear the word enterprise? I used to think mainframes and big iron. I would think of sprawling server rooms with dozens of server racks. I used to think monstrous storage arrays and backup tape libraries as far as the eye could see. I used to think of a large employee base. For me the word enterprise used to correlate to size, but I don’t think that any more…

PowerShell stores errors that it encounters in a circular error buffer variable named $Error. $Error also contains a count property to count the number of errors in the buffer. To figure out how many errors we could hold was pretty straight forward. All I had to do was create a script that generated an error and see how high $error.count got. At this point I didn’t even know if there was a maximum or not.

I have nothing but wonderful things to say about the SQL Saturday 386 leadership and the army of volunteers they brought along. It was a very well run event. The venue was nice, everything was very well organized, the people were amazingly helpful and friendly, and it made the perfect place for me to turn my presentation up a notch. It’s amazing how much of a calming effect can be felt when you are surrounded by such an inviting and amazing group of people. That’s really something that I love about the SQL Server community in general.

Here is a link to the official #tsql2sday invitation from Andy Yun’s Blog. This month’s subject is “Just Say No to Defaults”. If you’ve read my blog at all you will know that I have a slight obsession with SQL Server and PowerShell. As far as I am concerned PowerShell is the new gold standard when it comes to scripting and automation in the Windows environment. Add a sprinkle of SQL Server with the SQL Server PowerShell module (SQLPS) and you have a deadly combination as a DBA.