Foliotek Developer Blog

Identifying Unused Indexes in a SQL Server Database

One of our databases began timing out during our weekly index rebuild job, so I began to look into what indexes were getting rebuilt. After looking at the Index Usage Report, I noticed that some of them were not even being used. Some of them were very large indexes as well.

Obviously, an index that is never used doesn’t really need to be rebuilt to improve performance, so I removed a few of them. Then I began to look more closely at how I could identify other indexes that were not being used, aside from looking through every index on the SQL Report.

Unused Indexes with no reads in the Index Usage Stats Table

From research I did online, I saw that the system table sys.dmdbindexusagestats contains usage statistics for indexes. There are two key columns on this table that are of particular interest, namely userscans **and **userseeks. If both of these values are zero, then the system probably isn’t using the index.

It is probably a good idea to at least look at a few other columns as well, including userlookups, systemseeks, systemscans, and systemlookups.

I created a query that identifies unused indexes based on this criteria and outputs them in a pretty nice format. I was unable to find any other queries online that output everything in as nice of a format as this, so I hope you find this helpful.

 -- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE  
 DECLARE @MinimumPageCount int  
 SET @MinimumPageCount = 500

SELECT Databases.name AS [Database],  
 object_name(Indexes.object_id) AS [Table],  
 Indexes.name AS [Index],  
 PhysicalStats.page_count as [Page_Count],  
 CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],  
 CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],  
 ParititionStats.row_count AS [Row Count],  
 CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024)  
 / ParititionStats.row_count) AS [Index Size/Row (Bytes)]  
 FROM sys.dm_db_index_usage_stats UsageStats  
 INNER JOIN sys.indexes Indexes  
 ON Indexes.index_id = UsageStats.index_id  
 AND Indexes.object_id = UsageStats.object_id  
 INNER JOIN SYS.databases Databases  
 ON Databases.database_id = UsageStats.database_id  
 INNER JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL)  
 AS PhysicalStats  
 ON PhysicalStats.index_id = UsageStats.Index_id  
 and PhysicalStats.object_id = UsageStats.object_id  
 INNER JOIN SYS.dm_db_partition_stats ParititionStats  
 ON ParititionStats.index_id = UsageStats.index_id  
 and ParititionStats.object_id = UsageStats.object_id  
 WHERE UsageStats.user_scans = 0  
 AND UsageStats.user_seeks = 0  
 -- ignore indexes with less than a certain number of pages of memory  
 AND PhysicalStats.page_count > @MinimumPageCount  
 -- Exclude primary keys, which should not be removed  
 AND Indexes.type_desc != ‘CLUSTERED’  
 ORDER BY [Page_Count] DESC

This query includes the following helpful information on the unused indexes:

  • Table Name
  • Index Name
  • Page Count
  • Total Index Size (MB)
  • Fragmentation (%)
  • Row Count
  • Index Size/Row (Bytes)

Unused Indexes with no entry in the Index Usage Stats Table

For part of my research on other sites, I noticed that there are some indexes that somehow never get listed in the dmdbindexusagestats table. So, running this first query is not really enough. In fact, the SQL Index Usage Report doesn’t list all the indexes either. So, there is another query that I created to identify these types of indexes. I joined on several tables again to make the output more useful.

 -- GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE  
 DECLARE @dbid INT  
 SELECT @dbid = DB_ID(DB_NAME())

SELECT Databases.Name AS [Database],  
 Objects.NAME AS [Table],  
 Indexes.NAME AS [Index],  
 Indexes.INDEX_ID,  
 PhysicalStats.page_count as [Page Count],  
 CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],  
 CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]  
 FROM SYS.INDEXES Indexes  
 INNER JOIN SYS.OBJECTS Objects ON Indexes.OBJECT_ID = Objects.OBJECT_ID  
 LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats  
 ON PhysicalStats.object_id = Indexes.object_id  
 AND PhysicalStats.index_id = indexes.index_id  
 INNER JOIN sys.databases Databases  
 ON Databases.database_id = PhysicalStats.database_id  
 WHERE Objects.type = ‘U’ — Is User Table  
 AND Indexes.is_primary_key = 0  
 AND Indexes.INDEX_ID NOT IN (  
 SELECT UsageStats.INDEX_ID  
 FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats  
 WHERE UsageStats.OBJECT_ID = Indexes.OBJECT_ID  
 AND Indexes.INDEX_ID = UsageStats.INDEX_ID  
 AND DATABASE_ID = @dbid)  
 ORDER BY PhysicalStats.page_count DESC,  
 Objects.NAME,  
 Indexes.INDEX_ID,  
 Indexes.NAME ASC

When to Remove an Index

Just because an index is not being used does not necessarily mean it should be removed. The queries above ignore the clustered indexes on the tables, since there are very few times, if any, when a table should not have any index at all. There may be other reasons to keep an index around as well. Those decisions are best made by those that have a knowledge of how the data is used and will be used in the future.

Conclusion

I was able to use these two queries to get rid of a lot of dead weight in several of our databases. In one of them, in particular, I was able to remove almost 1 GB of space. That’s 1 GB less for every backup, so it adds up over time.

One last note… After removing indexes, it’s probably a good idea to run SQL Profiler, just to be safe, on the database to catch any queries that may be expensive and somehow used an index that should not have been deleted. See my other post on SQL Optimization – A Beginner’s Guide

For more tips on managing a SQL Server Database, see Managing a SQL Server Database: Tips for Beginners


jQuery Mobile Code Snippets for Visual Studio

I’ve been recently learning jQuery Mobile and am really impressed with the technology. It is a powerful and robust tool to quickly create content for mobile devices. However, with it being so new to me and with it being so robust, I find it difficult to remember all of the syntax for the many different things you can do with it. It occurred to me that code snippets could be a great way to relieve this burden, so I began creating my own to do this.

As an example, using my jqmPage code snippet (by typing

<div data-role="page" data-theme="THEME" id="PAGENAME">  
    <div data-position="fixed" data-role="header" data-theme="a">
        <h1>HEADER</h1>
    </div>
    <div data-role="content" data-theme="a"></div>
    <div class="ui-bar" data-position="fixed" data-role="footer" data-theme="a">
        <h4>FOOTER</h4>
    </div>
</div>  

Then, if I wanted to, for instance, add a slider, all I have to do is use my jqmSlider snippet (by typing

<fieldset data-role="fieldcontain">  
    <label for="SLIDER1">Slider Input:</label>  
    <input type="range" name="slider" id="SLIDER1" min="MIN" max="MAX"
           value="INITIALVALUE" data-highlight="false" data-mini="false" />
</fieldset>  

Then, to add a text field, I just have to type <jqmText + tab + tab, and I get this:

<div class="fieldcontain">  
    <label for="txtNAME1">Text Input:</label>  
    <input data-mini="false" id="txtNAME1" name="name" placeholder="PLACEHOLDER" type="TEXT" value=""></input>
</div>  

The Code Snippet Tooltip for the input type identifies the valid options.

I’ve included all my current snippet files below. They’ll probably change a little bit as I continue to learn jQuery Mobile better, and you’ll probably want to make changes of your own. Nonetheless, this is a decent start.

jQuery Mobile Code Snippets


How to Record, Edit, and Produce Videos with Text Overlays

Recently I had the opportunity to create a video to build a greater understanding of one of the products our company sells. Working for a small company I wear many hats and it turns out I needed to get a new hat called "Videographer". So I thought I would share a simple post on my process in an effort to help anyone in need of wearing a "videographer"hat to not encounter the same pitfalls that I did.

Why Build a Portfolio

ONE
I recommend making sure you have a flash based camera. It makes the transfer of the video VERY EASY. Ideally you have more than one of these cameras so you can move back and forth between two different shots. We used two Sony HDR – CX160 cameras.

Sony HDR – CX160

TWO
Make sure you have a solid script written and reviewed by a few people. It sucks to have to redo the video shoot over and over as you tweak the script. I like to use Google Docs so multiple people can tweak the file at once. Using the "comments" feature works great. Once the script is done, you can make a quick teleprompter using a laptop, MS Word, and a music stand (or some other type of stand). Make sure the screen of the laptop is almost directly behind the camera so it is fairly hard to tell whether the person is looking at the camera or reading off of a screen.

THREE
Make sure you have a good microphone for the audio. Quality audio is huge. We actually had a wireless microphone sending a signal to a receiver on the camera. This is a bit high end. If you can't swing that, then make sure you have a boom mic set up, the typical camcorder mic ain't gonna cut it.

FOUR
Transfer the video and audio to your computer. Download Adobe Premiere Pro, Adobe After Effects, and Audacity (freeware). You'll use Premiere Pro to edit the video, After Effects for the typography details, and Audacity to clean up the audio.

FIVE
Put your video(s) into Premier Pro and before you even begin editing, export your best videos audio only from Premier Pro. You can do this by exporting "media" and choose "audio only" from their drop down options as a wav file.

To only export a single audio stream you'll need to mute one of the audio channels. Then open Audacity and import your audio there. In Audacity you can clean up typical noise related junk the mic picked up. Here is a quick video on cleaning up audio in Audacity.

Audacity Noise Clean Up

SIX
Add your audio back into Premiere Pro as a second audio file. Expand both the original audio file and the additional audio file you just added and zoom in as much as possible. This will allow you to sink up the new "cleaner" audio file with the existing video. It far easier to sink with the original audio than it is trying to sync with the video. Remember to “mute” the original audio file once the new cleaner file is synced up. You don’t want a double audio sound.

SEVEN
With the two video feeds, start to place keyframes on the top video layer in obvious places where it would be good to shift from one video angle to the other. Always place two keyframes directly next to one another so you can fade in and out from 0 – 100% easier. Once all the keyframes are placed. Set all of the in/out transitions from one video feed to the other. There may be a better way to do this, but in my limited amount of learning time, this worked best for me.

EIGHT
Now the hard part. Listen to the video over and over and make a clear outline for where you think it would be good to add some text overlays. I found it better to have more overlays than less. Nobody likes to watch a talking head. After the outline is done, it's time for Adobe After Effects. This is where the real work happens.

NINE
In After Effects the best way to handle everything is in < 20 second segments. Oh and PS, you'll want as much RAM for this project as possible. I had 8 Gig with a quad core xeon processor and my machine was lagging on me :(. Now the key to After Effects is bringing in that same audio track you edited with Audacity so you can line up your text to the audio as best as possible. Now it is just a matter of building out all of the After Effect Compositions you will need to import into Adobe Premier Pro.

You'll probably spend the bulk of your time creating text effects with After Effects. Make sure you check out Adobe's "animation presets." The titles of the presets suck and you can't see a preview of any of them, but if you add them to a composition and play with them, they are easy enough to remove.

Oh, and if you don't know how to use After Effects, head to your trainer (www.youtube.com) and type in "text effects in adobe after effects," you should be good to go from there. I probably watched 1.5 hours of videos over the course of two weeks.

TEN
As you complete the Effects, render their output and dump it into your Premier Pro sequence lining up the audio just right. The final step in the processes is finding a cheap audio track with the correct licensing to rest beneath the entire video. You'll want to tweak the DBs on the music audio track you pick so they don't overpower the presenter, but that's a walk in the park compared to messing with After Effects. I found my music here

www.premiumbeat.com

FINAL
Export the video as a Media file and blamo you are done! 10 simple steps. OK… not so simple. The video above took about 40 hours of "learning" and production for a 2 min. 30 sec. snippet. I'm betting the next production takes 15 hours or less.

If you are getting ready to do some video, I'm hoping this was moderately helpful. Adobe now has monthly licensing available for their top end products. So it is possible to only spend $100 on software if you only need it for a month. Otherwise you're out 1,500k (ouch). If you are really quick, you can use their 30 day trial and get 'er all done before the trial ends. I did :)


Visual Studio Custom Start Page

Feature Driven Development

More than a year ago, the Foliotek development team moved to a Feature Driven Development Cycle (FDD). If you’ve never researched into this form of agile development I would recommend looking into it, because I won’t go into too much detail about it. Here’s a quick overview of how we use this development process here at Foliotek (assuming you have at least a basic understanding of SVN):

In each project repository, trunk is our production copy. This means that trunk needs to build without any errors, and is under a strict testing cycle. When we start work on a feature we will create a branch off of trunk. All the work for that feature goes into that branch, therefore we’re never checking in incomplete code into the production copy. This means that several developers can work on multiple features at one time, without interfering with each other. Testing can be done separately in these feature branches as well. After a feature has been approved, we reintegrate the branch into trunk, and it is ready for production.

The Problem

The problem we ran into with this cycle was the amount of time and effort that went into setting up a feature for development. We’d have to

  1. Create the branch with an ID based on the case in our project managment software.
  2. Check it out onto our computer (using TortoiseSVN)
  3. Set up an IIS entry (including virtual directories) so we could run the feature branch on our machine.
    This allows testers and developers to access the work at http://computername/task_12345

After all is said and done it takes 10-15 minutes just to set each branch up (not to mention maintain it), and some of us can have up to 5 or 6 features open at a time. This also gets really hard to manage when you’re working on multiple features for separate projects. Meanwhile other people are reintegrating their features into trunk, and in order to avoid conflicts in SVN you need to be constantly merging the latest changes in trunk into your feature branches. As anyone with merging experience in SVN can tell you, if you don’t do this reintegrating your branch can result in a ton of conflicts. It didn’t take long to realize that we needed a better way to streamline this process.

The Solution – The Foliotek Start Page!

We settled on developing a Custom Start Page for Visual Studio, that would help us keep track of all of our branches. It would also help us keep them updated, reintegrate them, and allow easier access to them. Since we’ve had so much success with this tool, we wanted to share it, in case any other teams were looking for a similar solution. This will be a hybrid introduction and setup guide. If you’re only interested in what the final product is, you can scroll to the bottom.
Note: We’ve not spent much time on the UI of this tool – you’ve been warned…

Prerequisites

  • Visual Studio 2010 (not Express Versions)
  • IIS 7
  • Tortoise SVN (1.6 or 1.7)

Before even downloading the start page, you’ll need to set up a settings xml document. Copy the contents of the following xml sample (or download the settings file) to the following location and replace all the values with your project’s details.

C:\Users\[USERNAME]\Documents\Visual Studio 2010\Settings\StartPageSettings.xml
(or whatever the path is to your Visual Studio 2010 Settings folder)

I recommend setting each Project element’s Path to an empty directory to allow the start page to start from a clean slate.

<?xml version="1.0" encoding="utf-8" ??>  
<settings>  
    <projects>
        <project>
            <name>Example Project</name>
            <shortname>example</shortname>
            <svnurl>https://svn.example.com/svn/example</svnurl>
            <path>C:\Users\USERNAME\Documents\Visual Studio 2010\Projects\Example</path>
            <webfolderpath>Web</webfolderpath>
            <solutionfolder></solutionfolder>
            <iisuser></iisuser>
            <iispassword></iispassword>
            <iisapppool>ExampleAppPool</iisapppool>
            <iisapppoolversion>4.0</iisapppoolversion>
            <virtualdirectories>
            <virtualdirectory>
            <name>Resources</name>
            <path>\\shareddirectory\resources</path>
            </virtualdirectory>
            <virtualdirectory>
            <name>Resources2</name>
            <path>\\shareddirectory\resources2</path>
            </virtualdirectory>
            </virtualdirectories>
        </project>
    </projects>
    <svnversion>1.7</svnversion>
    <username></username>
    <email></email>
    <fogbugzurl></fogbugzurl>
    <fogbugzusername></fogbugzusername>
    <fogbugzpassword></fogbugzpassword>
    <websvnurl></websvnurl>
</settings>  

Installation

After you’re finished setting up the settings document, we can download the start page and get started. Once you’ve downloaded and installed the Foliotek Start Page Extension, It will ask you to restart Visual Studio, and you’ll notice that your start page isn’t set yet. You’ll have to go to (in visual studio)

Tools -> Options -> Environment -> Startup -> Customize Start Page -> Select the Foliotek Start Page.
Setting the start page in Visual Studio 2010

After you click Ok Visual Studio will open the start page. It might give you a dialog that mentions that the directory doesn’t exist so it needs to create it, which is fine. Once the start page is loaded, if you chose a fresh directory, you’ll see this…



In order to do Feature Driven Development you’re going to need a local copy of trunk, so you click Get Trunk and the start page will open a Tortoise dialog asking you to download the trunk of this project’s repository. After you download the copy of trunk you’ll have the following options on your start page…




Ignoring the buttons on the trunk control (we’ll explain those later), let’s say we’re ready to start on one of our features. We could click on “Create Task”, and type in our branch name into the Name textbox and click add.


After we click add, the start page will create the branch (if it doesn’t already exist), then we’ll get the TortoiseSVN dialog to download the branch.


After we click Ok on the Tortoise dialog, the start page will check out the branch and create an IIS entry for the branch. You can see in the image below we have an entry for /foliotek (trunk) and /foliotektaskDemo.


Now you’re ready to start developing!! The above process is the base functionality of the start page. Before the start page, this Demo branch would have taken around 10-15 minutes to set up before I could even develop. Now I have it ready in under a minute. In addition to improving the set up process, we realized that we could improve the maintenance and management of the project with a few extra buttons.
Foliotek Start Page - Individual Task

Some of the buttons are self explanatory, some of them aren’t, so I’ll just go from left to right and explain each one.

  1. The Visual Studio button that opens the solution.
  2. The windows explorer button that opens that individual task’s folder, then the browser button which actually just opens the project in your default browser, not IE.
  3. Open the SVN Repository Url in the browser
  4. Open the WebSVN Url (if one exists). We use WebSVN because it is a nicer UI for browsing source, but the only one that’s mandatory is an SVN url
  5. Next we have a FogBugz (our bug/feature tracking software) button that opens the feature’s description.
  6. Update button grabs the latest changes from the SVN repo
  7. The local build button builds the solution without opening it in Visual Studio.
  8. The Merge from Trunk button opens a TortoiseSVN merge dialog, with the fields auto-populated, and all you have to do is click next.
  9. The Reintegrate button opens a TortoiseSVN dialog, but unfortunately it doesn’t auto populate the fields for you. In the future we hope to get that working, but for right now you’ll have to enter the reintegration fields yourself.
  10. The big red “X” will delete the folder from your computers, and also remove the IIS entry associated with the branch.

Conclusion

This start page has improved our development process immensely, and we thought if anyone else is using this development cycle, they might benefit from it as well. Even if your team isn’t using Feature Driven Development, the start page can still be useful to teams using SVN and IIS. If anyone is interested in trying it, you can get it from its extension gallery page, or you can find it in the extension manager in visual studio.

Visual Studio -> Tools -> Extension Manager-> Online Gallery -> Search for “Foliotek Start Page
Our plans for the unforeseeable future include open sourcing this extension, and also implementing an architecture that would allow people to write their own actions to perform on branches, and adding some screens to add projects and edit settings.

Let us know if you have any feedback or problems setting it up, I’d be more than happy to help.


Spectrum Colorpicker In Foliotek Presentation

Spectrum, my JavaScript colorpicker project, has gotten some good news lately!

It was adopted by the WebKit project for use in the Web Inspector, which was on a few people’s wishlists, including my own.

Foliotek Getting Involved

We decided to add (and expand on) functionality and improve the design for use in Foliotek Presentation.

Geoff Leigh and I worked together, thinking about the color picking process from scratch and came up with a good workflow, I think.

Common Palettes

The palettes on the top are commonly used colors that are ‘sticky’. These are easy ways to grab a color that looks about right, and makes it easy to reuse colors. Clicking on any element from a palette instantly chooses the color and closes the colorpicker.

Template Palette

Part of what makes Foliotek Presentation cool is that we know so much about the designs that people use by analyzing the CSS and color changes that they do in our template editor. With this information, we bind a list of colors that are being used in their template. This opens up possibilities like reusing a heading color to accent a certain word in their portfolio.

User Colors

This is a list that remembers which colors you have chosen. It uses the localStorage object. It will automatically add any color you select that isn’t in the above palettes.

Since it is automatic, users do not need to add/remove/edit the custom palette. Right now, we think this is better than presenting the user with an interface for setting custom colors. We originally had some designs that handled this management, but we decided to go with the simpler solution for now.

Going Forward

I hope to continue development and make this the best JavaScript colorpicker available, keeping these goals in mind:

  • Good UX
  • Cross Browser Compatible (IE6 and up)
  • Doesn’t use any images
  • Easy to skin via CSS
  • Simple interface / easy to integrate with app

I am very happy with the changes and excited to be working on projects like these! You can follow development of the plugin, suggest features, or report bugs here: https://github.com/bgrins/spectrum.