I find myself just now sitting down to write this as the deadline for TSQL Tuesday 24 fast approaches.  Fortunately I started planning out what I wanted to cover several days ago.

I could have tried writing this last night – but I ended up falling asleep at the keyboard.  Drowsy typing can be a very dangerous thing.  So it is a good thing I didn’t start then.

Alas, I digress.  This month, a TSQL guru (BRAD SCHULZ), is at the helm.  He has challenged us to write about procs and functions.  We get decide which direction we take it.

I thought about this and figured there could be a few functions and paths to take on this.  So let’s get started and see where this bird-walk can take us.

Function for which I am thankful

I decided that this is a topic that must be discussed.  It is the month of Thanksgiving afterall.

For this topic, I want to give a shout out to Jeff Moden for a function that he wrote about and published.  This function has proved useful to solve some performance issues.

In a recent case where this function helped, I was a bit reluctant because it just didn’t seem possible that the function already in place could have that big of an impact.  Guess what, it did.  The function that Jeff did was his string splitter.  The strings in question were short and each value was not very big.  Despite that, the string splitter was chewing up resources.

After implementing the new string splitter, we saw immediate performance gains.  Using this function has paid off big dividends and has saved me a grundle of time.  With time being more valuable as time passes, I am very thankful to Jeff for the work he did on his function.

Function I can’t live without

That might be overstating it a bit.  The reality is, there are some functions that I use far more in my scripting and troubleshooting than others.  One function, above all others, seems to come into use more frequently.

This function is a dynamic management object and is called dm_db_index_physical_stats.  There are several parameters to this function and there are several uses for it as I have seen and demonstrated on my blog.

To learn more on the parameters, read this.  As MSDN states, this function “Returns size and fragmentation information for the data and indexes of the specified table or view.”  So you can ascertain quickly that the most likely use of this index is in index fragmentation analysis.

I have written about this function for index analysis, table size analysis, BLOB index analysis, and finding ghost records.  I even referenced it when doing a table compression analysis.

You can check out all of those articles here.

There is just so much good info that can be gleamed from this function and so many ways to use it.  That is why I find myself coming back to it time and time again.

Funcs for Fun

There are many functions that are useful.  Some are more interesting than others.  Some, I use because I find it fun delving into the internals of SQL Server.  If the function can provide useful information and I learn something along the way, then it is pretty fun.

Here are some of those functions that I have enjoyed using.

sys.fn_physloccracker

sys.fn_virtualfilestats

sys.dm_exec_query_plan

sys.dm_exec_sql_text

sys.dm_io_virtual_file_stats

You can find good information on most of these in MSDN.  For sys.fn_physloccracker, you may want to rely on this from Paul Randal.  You can even read what little I wrote about it a few months ago.  I came across this little function while working on a pet project (that I still need to finish – d’oh).

Conclusion

Functions are very handy.  They can also prove to be the cause of poor performance (such as was the case with that string splitter).  When used appropriately, you can provide well-performing SQL as well as some very handy information.