How to Check Index Fragmentation on Indexes in a SQL Database

Firstly I would like to point out that the following article was copied from here so all credit goes to them. I found it while tying to find out why one of our databases was incredibly slow!

Issue

SQL Queries taking longer than normal to complete.

Environment

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

When a database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.

If database indexes are fragmented, the SQL Server query optimizer may chose a non-optimal execution plan when using an index to resolve a query.

This will affect the overall query performance and you may notice a query behaving slower than normal.

Resolution

**Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.**

The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the ‘where’ clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.

For heavily fragmented indexes a rebuild process is needed, otherwise index reorganisation should be sufficient.

The following info summarises when to use each one:

Reference Values (in %): avg_fragmentation_in_percent > 5 AND < 30
Action: Reorganise Index
SQL Statement: ALTER INDEX REORGANIZE

Reference Values (in %): avg_fragmentation_in_percent > 30
Action: Rebuild Index
SQL Statement: ALTER INDEX REBUILD

Mailchimp’s CSS Inliner Tool

Formatting HTML emails to look great in email clients can sometimes be a real pain. Some email clients strip out <head> and <style> tags from emails, so it’s best to have your CSS written inline within your markup. We all know that writing inline CSS is time consuming and repetitive, so Mailchimp built a conversion tool to automatically inline your email’s CSS.

Mailchimp’s CSS Inliner Tool

You just paste your email’s HTML, click Convert, and you’ll get a more email-friendly version that’s ready to send. If your email’s CSS includes media queries for responsive styling, don’t worry – the inliner tool will leave those rule sets alone.

Thanks Mailchimp 🙂

Redirect non www to www in IIS using web.config

One common use of URL Rewrite is redirecting http://yourdomain.com to http://www.yourdomain.com. The method below will perform that redirect as well as set a permanent 301 redirect.

Rule 

<rule name=”Redirect to WWW” stopProcessing=”true”>
<match url=”.*” />
<conditions>
<add input=”{HTTP_HOST}” pattern=”^yourdomain.com$” />
</conditions>
<action type=”Redirect” url=”http://www.yourdomain.com/{R:0}” redirectType=”Permanent” />
</rule>

 

Full Web.Config with Rule

<configuration>
<system.webServer>
<rewrite>
<rules>
<rule name=”Redirect to WWW” stopProcessing=”true”>
<match url=”.*” />
<conditions>
<add input=”{HTTP_HOST}” pattern=”^yourdomain.com$” />
</conditions>
<action type=”Redirect” url=”http://www.yourdomain.com/{R:0}” redirectType=”Permanent” />
</rule>
</rules>
</rewrite>
</system.webServer>
</configuration>

If you dont already have it you can download URL Rewrite from http://www.iis.net/expand/URLRewrite.

How to boot in to Safe Mode on Windows Server 2012 R2

If you have worked on Windows 8 or newer, you will know that Microsoft has removed the F8 boot functionality. They did this to decrease boot times. What is odd is that they applied this logic to servers. Regardless the F8 Safe Mode functionality is not truly removed, it is just turned off.

To turn F8 boot on in Server 2012 you need to get to an elevated CMD prompt.

If your server is functional that is easy. If your server won’t boot (hence the need for F8) then you can boot off of a Windows Server install DVD and select REPAIR YOUR COMPUTER option, the Troubleshooting, then Command Prompt.

Once you have a Command Prompt (as an administrator) open enter the following commands which are self explanatory:

bcdedit /set {bootmgr} displaybootmenu yes
bcdedit /set {bootmgr} timeout 10

Reboot your machine and enjoy pressing F8 and getting back into SAFE MODE or LAST KNOWN GOOD to work out your problem.

This will also work on Windows 8 and Windows 10.

Original article can be found here

Automatically chop up audio and save as mp3 – Adobe Audition

Do you have a long mp3  file (or any other audio format) that you’d like to chop up into smaller pieces and save as individual files? I did and found the following article on how to do this in Adobe Audition which has the ability to batch export marker ranges as individual files.

This feature is most useful for splitting a long recording into several individual assets, but it can also be used to create unique copies of the sections of audio that are most important within any open audio file. Here’s an example of how this works.

  1. Open an audio file into the Edit view of Audition.
  2. Go to Window > Marker List to open the Markers Panel.
  3. Select a section of audio that you would like to export as its own file.
  4. Click the F8 key on your keyboard or click the “Add Marker” button in the Markers Panel.
  5. Repeat steps 3 and 4 for each section of audio you wish to export as a new file.
  6. When you are done marking each section, go to the Markers Panel and give each marker its own name (Label) or leave them with the default generic marker labels.
  7. Select the marker ranges you wish to export (CTRL or SHIFT + click each marker).
  8. Click the “Batch Export Marker Regions” button which is at the bottom of the Markers Panel with a floppy disk icon. If this button is disabled it means you either do not have any markers selected or you have individual point markers chosen and not ranges (range markers will always have a begin and end time).
  9. When the Batch Process Marker Ranges dialog appears you can choose to either “Save to files”, or add a certain amount of silence to the start and end of each marker. For the purpose of this walk-through, choose “Save to files”.
  10. Next, you can choose if you want to use the Marker label as the filename, or set your own Prefix and Sequence Start number for all exported files.
  11. Once you have the naming convention chosen, choose the destination of where the individual files will be saved and set your export format and options.
  12. Click OK to export your new audio files.
  13. Browse to the folder you exported your new files to and take the next steps to burn them to CD, email them to clients, archive them for later or continue editing each asset in Audition.

Source: http://blogs.adobe.com/audition/2010/06/using_audition_to_save_section.html