SQLCwby - Chris Woods

Logical vs. Physical Processing: What’s Next?

Remember, this is just how SQL Server will logically process this query; this is not how it will physically process it. That will be determined by the query optimizer and will involve any number of operators depending on how SQL Server thinks it will best be able to get you the data.

Why Did My Copied Object Change? (The Problem and the Fix)?

In Powershell, when you have a custom powershell object and execute $variableA = $variableB, it is just a pointer/link to the same spot in RAM. You are not actually putting the data from $variableB into $variableA like you do in TSQL. This kind of problem will not occur with all variable types in Powershell.

To Adjust Fill Factor or Not To Adjust?

Many newbie DBAs will automatically adjust the default fill factor to something less than 100 on all indexes to avoid page splits. This over-reaction can actually cause more problems than it solves. Lets talk about specifics on what the DBAs are trying to avoid when they do this… If you remember the Defragmentation post from last week, you’ll recall I talked about adding a new record into the middle of the table.

What is wrong with non-default Fill Factor values?

How Fill factor impacts DiskIO: If you recall from I want to be a SQL Engine when I grow up article, you learned that SQL stores (reads and writes) data in 8kb pages. Regardless of how full that page is it still reads the full 8kb. So having empty space just causes you to have your data spread across more pages, causing more reads and thus taking longer to run if the data isn’t already in memory.

What should be our initial baseline?

The default setting in your DBs and for all indexes should be 100. If you find you are having issues, then you can adjust from there. But you really need to understand your data. You need to know the patterns of the inserts. You also need to be aware of how how much