What Are Filegroups
Produced: 09/10/2016 16:13:00
Well, let’s be honest here… in a nutshell a Filegroup is… wait for it… a group of files.

Bet you didn’t see that one coming.

However, it’s their uses which are actually worth discussion because they provide some useful functionality as a whole.

Continued...
SQL 2016 Install Sets Multiple TempDB Files
Produced: 02/10/2016 16:10:00
This is something that I’ve been hoping would make an appearance in SQL Server at some point, and also why I included my last post about tempDB contention. It’s the ability to choose the number of tempDB files during install rather than having to add them yourself at a later date.

Continued...
Contention and Multiple TempDB Files
Produced: 25/09/2016 16:07:00
We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.

However, where some people can fall down is that they don’t realise that tempDB can easily get overwhelmed and therefore can suffer badly from contention.

Continued...
Clustered Index Orders Pages Not Rows
Produced: 11/09/2016 09:01:00
This is something I’ve had mentioned to me a lot recently… that a clustered index ensures that records must be stored in order on the data pages.

But it’s just not true.

Continued...
Incremental Statistics Performance
Produced: 21/08/2016 18:33:00
Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

Continued...
The Awesomeness of Incremental Statistics
Produced: 14/08/2016 18:29:00
I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.

Continued...
How to Find Partition Range Values
Produced: 24/07/2016 18:19:00
This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

Continued...
Test for Compression Savings
Produced: 26/06/2016 19:21:00
This is actually an addendum to my last post as I referred to using SQL compression and provided a script in which you could compress a set of tables.

Continued...
Rolling Back to a Database Snapshot
Produced: 05/06/2016 22:06:00
One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

Therefore what you can do is take a snapshot, make a load of changes (whether testing new code, trying out something new etc) and then if it all goes pear shaped you can simply revert back to the snapshot and all will be well.

Continued...
Creating a Database Snapshot
Produced: 29/05/2016 22:04:00
This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)

Continued...
Delayed Durability - What is it?
Produced: 22/05/2016 16:06:00
This is one of my favourite new features of SQL Server 2014 as it can make an absolutely huge difference to your servers when performing inserts, updates, and deletes.

And best of all, this is an ALL Edition feature rather than Enterprise only.

Continued...
Be Very Careful with Sampled Statistics
Produced: 15/05/2016 16:03:00
Okay, we all know that Sampled Statistics are not perfect otherwise there would be no need for a Full Scan stats update. However, there’s an issue I encountered recently that has really bothered me in regard to these.

Continued...
2014 Cardinality Estimator Exponential Backoff
Produced: 08/05/2016 11:38:00
This is going to be a very basic explanation as to the differences seen in my previous post (about TF 9481) in which we encountered the new and old cardinality estimators providing different estimates to what looked like a very simplistic query.

Continued...
Help for Undocumented DBCC Commands
Produced: 24/04/2016 19:55:00
This episode of Fun with Flags is used in conjunction with my previous post about TF 3604.

Undocumented DBCC commands are written about all over the place and, although undocumented and therefore also generally unsupported, people seem to love them. I use them myself in all manner of places, never in production code, but they still get used. The biggest problem though, is knowing how to use them.

Continued...
Returning DBCC Messages to SSMS
Produced: 17/04/2016 19:33:00
There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

Continued...
Proof A Failed Insert Increases the Value of an Identity Column
Produced: 28/02/2016 09:59:00
This is something I’ve seen cause plenty of confusion and so I thought I would investigate. It’s actually lucky that I did before decommissioning my old SQL 2008 R2 server as the same proof can’t be run on SQL Server 2014 (I’ve not tried SQL Server 2012).

Continued...
Shrink Log Without Breaking a Backup Chain
Produced: 24/01/2016 20:39:00
This is something I see asked all over the internet and it’s also something for which there are a LOT of incorrect responses, dodgy solutions, and poor advice (including on the MSDN website). Therefore as I’ve had to resize a few transaction logs recently I thought I’d share the code and process I’ve used.

Continued...
Temp Table Caching
Produced: 03/01/2016 08:09:00
I had a discussion with someone the other day who stated that all temp tables should be dropped at the end of a procedure because otherwise they never get cleaned up.

I could understand the statement, but it’s not actually how SQL Server works because within a stored procedure temp table caching generally makes dropping redundant.

Continued...
Is SQL Server Showing Memory Pressure
Produced: 27/12/2015 07:50:00
This is something I’ve never really been able to prove but have now found, courtesy of 2 VERY good posts (Grant Fritchey and Jonathan Kehayais respectively - links to follow), that it’s actually quite simple to track and monitor in order to ascertain whether or not your SQL Server is experiencing memory pressure.

Continued...
Shrinking a Database with TRUNCATEONLY
Produced: 13/12/2015 06:51:00
I received a comment the other day on my previous post about never shrinking a database in which I was asked whether TRUNCATEONLY was safe to use as Books Online state that no data pages are moved.

Aside from the fact that I had initially misunderstood the question (my bad), I did happily concede that yes, it is a safe option to use HOWEVER, it does have its quirks and therefore I thought I would give it its own post.

Continued...
Transaction Log Tracking Inserts Updates and Deletes Over Time
Produced: 15/11/2015 11:21:00
Have you ever wanted something which will reliably keep you informed as to the number or Inserts, Updates or Deletes against table in your system? Maybe to keep an eye on the volume of modifications happening in a replicated environment?

Continued...
Looking for Inserts Updates and Deletes in the Transaction Log
Produced: 08/11/2015 10:56:00
This will form the basis of my next post but it needed covering separately in the first place… that being how you can see Inserts, Updates, and Deletes within the transaction log, and how you can tell which table they were performed against.

Continued...
Quick Note About INSERT INTO vs SELECT INTO
Produced: 09/08/2015 05:14:00
The debate about INSERT INTO vs SELECT INTO can be quite long and get people quite heated and therefore I’m not going to go into the entire thing right now, but what I did want to mention was something that I encountered the other day and which could be worth considering next time you’re deciding which approach to take.

Continued...
Instant File Initialization
Produced: 03/05/2015 18:19:00
This is actually a Windows level setting, but something that is hugely beneficial to a SQL Server and should be enabled where possible.

It basically allows database data files to instantly allocate new space (autogrowth, creating new database, restoring a database, adding datafiles etc) rather than zero initializing them.

Continued...
Wait Stats Per Query
Produced: 12/04/2015 09:35:00
This was something that I have wanted to be able to do for a while. I like having wait stats to look at (I’ll do a blog about those at some point - this is for people who already know what they are)… but until Extended Events came about these were only available, to my knowledge, at the server level. This was great, but what if you wanted to know what your specific query was doing?

Continued...
Optimize For Ad-Hoc Workloads
Produced: 05/04/2015 09:18:00
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.

So what does this setting do?

Continued...
Every Statement Is Parameterized
Produced: 29/03/2015 10:15:00
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.

Continued...
Part Populating Temp Tables
Produced: 01/02/2015 19:43:00
The other day I was going through some code looking for a reduction in reads (as they were rather higher than expected), and whilst doing so I came across a piece of code that seems logical yet is actually quite a performance killer.

Continued...
Vertical Partitioning To Reduce Reads
Produced: 30/11/2014 18:51:00
Directly following on from my posts about Reads and the 8k Page, Vertical Partitioning your tables can suddenly make a lot more sense and it’s very quick and easy to see how vast improvements can be made to the speed of your queries and their impact on your disks.

Continued...
How Many Pages In My Table
Produced: 23/11/2014 18:48:00
This is the natural follow-up to my previous post about the 8k page. It’s all well and good knowing that SQL Server works in 8k pages and that these directly influence your read figures, but we don’t want to sit there and calculate how many records there are per page in order to know how many data pages exist in our table.

Continued...
What is a Read?
Produced: 16/11/2014 12:12:00
I’ve spoken to a good few people now who didn’t actually know what a Read was inside SQL Server. They were discussing tuning of queries and, correctly, knew that less reads were advantageous and therefore that’s what they were looking for in a better performing query, but didn’t really know why.

Continued...
The 8k Page
Produced: 09/11/2014 10:25:00
This will be a relatively short post simply explaining the way in which SQL Server stores databases records on disk. The reason that I’ve included this post is because I’ve come across a good few people who didn’t realise this was how SQL Server worked and yet this is an important precursor to my next few posts.

Continued...