Dynamic Management Objects/ SQL/ Syndicated/ Templates/ Tips and Tricks

SQL Templates – Part 1

2 Comments

I really don’t know why it took me so long to embrace the use of templates in SQL Server Management Studio.  Really, they’re right there in plain sight.  They even have their own Explorer for cryin’ out loud!  Even the DMVs don’t have that and look how much press they get!  Unless one of the templates is caught wearing “mom jeans” or is in the process of doing something really cool, but falls out of its top in front of the paparatzi they would continue to sit their quietly on your local workstation (or anywhere else you install SSMS) just waiting for you to realize how great they are. 

“Templates?” you say.  “What are templates?  Sounds like work!”

Well that It Depends.

The only work involved with use of an existing template is the entering of parameters.  Which is actually one of the events of the up-coming Festivus Holiday, after The Airing of Grievances.  But you’re probably wondering what I’m talking about.  After all, parameters are in stored procedures; what’s a template?

Maybe it’s simply easier to show you an example of what I’m taking about.  Here is one of the dozens of templates that ships with SSMS 2008: 

DROP LOGIN <login_name, sysname, login_name>
GO

As you’ll quickly notice, it is the Transact-SQL command for dropping a login from a SQL Server instance.  By supplying a value for the login_name parameter (via either the Menu Bar/Query/Specify Values for Template Parameters or by using the Cntl+Shift+M keyboard combination) you can disregard the intricacies of the code required and instead focus on function and values.  As SQL Server becomes a broader product we DBAs can’t take the time and effort to remember everything and templates give us the ability to issue a checkpoint in tha brain of ours and free up space to learn and retain new things.  

In this most-simplistic of examples though you may lose the beauty of the construct.  Matter-of-fact it looks like more work than less.  That is why I am going to supply you with something a little more valuable and at the same time tie in my love of the Dynamic Management Objects.  In the next example I’ll give you a template for querying sys.dm_db_index_physical_stats() in order to return fragemented indexes of a specific database, for any indexes encountering fragmentation of N% and of a size greater than M pages.  You simply need to supply the database name, minimum fragmentation percent, minimum page count, and scan type parameters:

 USE <DB_NAME,sysname,Northwind>;SELECT 
   object_schema_name(DDIPS.OBJECT_ID) AS [schema],
   OBJECT_NAME(DDIPS.OBJECT_ID) AS [object_name], 
   I.name AS [index_name], 
   DDIPS.partition_number, 
   DDIPS.index_type_desc, 
   DDIPS.alloc_unit_type_desc, 
   DDIPS.avg_fragmentation_in_percent, 
   DDIPS.fragment_count, 
   DDIPS.avg_fragment_size_in_pages,
   DDIPS.page_count,
   DDIPS.avg_page_space_used_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID('<DB_NAME,sysname,Northwind>'), NULL, NULL, NULL, '<scan_type,,limited|detailed>') DDIPS
   INNER JOIN sys.indexes I 
       ON DDIPS.index_id = I.index_id AND DDIPS.OBJECT_ID = I.OBJECT_ID
WHERE DDIPS.[index_id] > 1 --no heaps allowed
   AND DDIPS.avg_fragmentation_in_percent > <index_frag_percent_min,tinyint,20> 
   AND DDIPS.page_count > <page_count_min,tinyint,100>
ORDER BY OBJECT_NAME(DDIPS.OBJECT_ID), I.index_id;

 

The scan type parameter of limited will not return information for the avg_page_space_used_in_percent. 

Looking at this example and referencing the first one , you’ll see the parameter patterning of <variable_name,data_type,default_value>.  The only required portion of this syntax is the punctuation and the variable_name.  The data_type is informational only, and the default value can be left blank as well.  When SQL parses the parameter values, it replaces everything between the < and > for the specific parameter, with the value you provided.  This means that if you want to reference a database name in a USE statement the parameter can be placed inside of brackets ([]) or just left as-is (so long as your database name does not include spaces and if it does then a pox on you and your loved ones!)  The same parameter then can be reused in a command where the database name is to be used as a string without requiring two parameters.  This is observed later in the second template example in the DB_ID() call.  In short, place the parameter between the required punctuation and let SQL do the heavy lifting.  If you supply a parameter value for the database name as ‘FOO’ instead of FOO the single-quotes will be passed in as a part of the parameter value.

Oh, curious to know where you can find the MS-shipped templates that I made mention of?  Simply go to SSMS’s View menu and select Template Explorer.  Creating templates?  Stay tuned for that…