SQL Server Blog

Since finding out about this option it has been an sp_configure setting that I’ve generally recommended everyone turns on within their SQL Server.



It’s not essential that you do so, just a recommendation that I give, alongside a full explanation of course.

This is a common misconception that I hear from devs and DBAs alike… people seem to believe that SQL Server will parameterize every statement it runs and store the plan for re-use later.



However, this is not necessarily the case.

This is just a quick post as it’s something I came across the other day and I thought it warranted a note and word of caution.



Basically I was alerted that replication had ground to a halt and was suffering badly after a developer had performed an update. Their logic was “it was only 100,000 rows, it should be able to cope with that”.

This is a common requirement in all companies… servers must be baselined otherwise how do you know if changes are detrimental or beneficial? How do you know where to look if something suddenly goes wrong?

This was something I created due to being fed up with constantly having to set up Profiler, having it “forget” my custom traces, and also wanting the data in SSMS so that I could query it if necessary.

This is something that’s really useful to know. It threw me for a little while when I first thought of doing it, but now it’s something I use quite frequently and to good effect. It’s really simple to set up once you know what you’re doing.



What you need to do is to create a table of connection strings and then you’ll get the package to read through the table and use each connection in turn via a “forEach” container.

This is something that I found tricky when I first started to work with SSIS packages and something that I regularly see asked on the internet, therefore I figured it was something worth documenting.

I’ve been using a lot of SSIS recently and whilst doing so I figured I should really add a few basics to the blog in case anyone either hasn’t used it before or simply hasn’t used it for a while and wants a few pointers.



Therefore this is the first of a few posts I’ll try to do covering some simple SSIS stuff.

We know that sp_spaceUsed is a great way to obtain a few simple figures about a table such as row count, table size, and index size… but these are all cumulative. Therefore what happens if you want to know the specific size of an individual index?