SQL Server Blog

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.



The concept itself is incredibly basic… you are literally taking one wide table and breaking it up into “regularly returned columns” and “infrequently returned columns”. Sounds unnecessary, but in respect of my reads and 8k page posts your cogs should already be turning and seeing the implications this change can have.

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.

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.

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.

This came about due to incredibly bizarre request I received which happened to throw me a little. Basically I was told by a business that they actually had no idea how many SQL Servers they had or on which machines they could be running. They asked if I knew a way to find them.

This is just a little something to look out for when administrating a database as I’ve seen it catch a few people out.



Basically it’s all about making sure you have the correct fill factor for your indexes so that you don’t unnecessarily bloat your databases as a whole.

This is another bug-bear of mine… it’s something I believe should always be set at the time of creating a database and then periodically checked and altered where necessary... and this applies not only to the data file but to the log file as well.



I keep seeing instance upon instance in which the growth settings have not been altered from the defaults provided by the Model database after installation. I don’t know if it’s because people don’t understand the consequences of these settings, or if it’s simply an oversight, but either way it’s something I will check on any new instance I see.

This is a massive misconception that has been going around for a long while and it needs to be wiped out.



Basically this is the belief that if you have a database in FULL recovery mode and take a full backup, then it truncates the log.



This is NOT true.

I was looking for some sample code to help me with a personal C# application I was coding and in looking around I saw a perfect example as to what not to do when manipulating a database.



To be honest the C# code itself was pretty ropey, but aside from that I could see what the guy was trying to achieve but the t-SQL he was using would likely let him down at some point due to his misuse of “UNION”.