The next round of SQL Saturdays is rapidly approaching: Tampa on 1/23/2010 followed by Boston and Richmond on 1/30/2010. I received notification that two of my sessions were accepted so now comes the time when I need to get back into the swing of all-things-SQL and switch out of Holiday Mode to get some things accomplished in the way of generation of content for the sessions that were accepted.
The sessions I’ll be presenting are entitled Operationalize With Templates andInsight Into Your Indexes With DMVs. The DMV presentation will focus on returning information on:
- Index fragmentation (and how to rectify issues presented.)
- Unused indexes
- Missing indexes
This is accomplished through the use of the sys.dm_db_index… DMVs (and if you order now I’ll throw in a DMF for FREE!) This is a favorite topic of mine. One that I rely on daily to keep my databases running as smoothly as they can on non-optimal disk configurations, when many of them run alongside databases that may (or may not) have been developed by Lesser Mouse Lemurs (not to be confused with Greater Dwarf Lemurs who would have at least known better than to use GUIDs in their primary keys.)
There will be none of this “I’ll get the scripts posted up to my blog in a couple days” talk either. The attendees will be able to return to work on Monday with all the tools necessary to:
- Identify and remedy highly-fragmented indexes in their databases
- Locate (and drop if appropriate to do so) indexes that are incurring write overhead, but never used for system or user scans, lookups, or seeks.
- Obtain recommendations on indexes that, based upon cached activity, may be of use if created.
Operationalizing With Templates will explain what templates are, how you create and store them for use, and how they can be used to make your routine administration tasks easier. Think of templates as database agnostic, non-persisted stored procedures. These objects are native to SQL Server Management Studio (which means they’ve been around since the release of SQL Server 2005) and are accessible through a window (aka Explorer) that can be toggled on and off within SSMS. The templates are by default, stored locally on the user’s workstation – which can and should be changed to a drive that is backed up regularly, and if in an office with multiple DBAs should be placed on a shared network volume that all DBAs have access to. What is the coolest thing about templates is that any Transact-SQL script can be converted easily to a template with little effort. (See previous post on Templates at thesqlagentman.com.) I recommend using templates whenever a task is going to occur on a semi-regular basis (therefore not appropriate to the confines of a scheduled SQL Agent Job) that you will be executing manually andmay be executed against multiple (or at least different) databases at different times, with different values. Some may say this is the job for parameterized stored procedures hosted in either the master database or a dedicated database on each of your SQL Server instances. I think the argument could be made that the answer to using a template v. stored procedure is… wait for it… It Depends.
I hope to see you there. Registration is online – secure a spot while registration is still open!