Foliotek Development Blog

First off – I am not a DBA. What follows is just some general tools and tips I’ve found to help write better queries, find queries that are performing poorly, and find some ideas on how you might improve performance.

Here’s the general procedure I use to find and fix the poorly performing “low hanging fruit” in an SQL Server driven database application.

1. Do you know a query you want to improve?  If so, skip to step 3.
2. Use SQL Profiler to identify candidate queries
   1. Fire up SQL Profiler.  You can find it under ‘SQL ….->Performance Tools’ in your start menu, or you can open it in the ‘Tools” menu of SQL Management Studio.
   2. Start a new trace.   Use the event selections tab to filter for queries you care about.
      1. I usually check ‘Stored Procedures->RPC:Completed’ and ‘TSQL->SQL:BatchCompleted’
      2. I also use the column filters to limit to the ApplicationName/DatabaseName/LoginName/HostName I care about and the performance characteristics I’m looking for  (a good place to start is >100 cpu, >50 duration or >5000 reads.  Adjust as necessary to find the worst performing in each category)
   3. Run the trace until you see some queries you are interested in.  Pause or stop it and select the query text from the detail window.
3. Copy your identified query into SQL Management Studio.  Right click to turn on ‘Execution Plan’ and ‘Client Statistics’ if they aren’t on already.  Assuming you’ve selected a SELECT query that won’t change data, run it to get a feel for the amount of time you want to improve.  Look at the execution plan and the client statistics to see what parts of the query cost most – you might identify ways to rewrite the query to improve performance.   Common ways to improve are to shrink the amount of data you are returning by limiting columns returned, etc.
4. If you can’t find a way to improve performance with a rewrite (or want to improve it even more) you can often improve performance by adding SQL Statistics or Indexes.  You might be able to guess appropriate indexes based on the execution  plan/client statistics – but it’s just as easy to let SQL identify candidates for you with SQL Tuning Advisor.
   1. Highlight the query, right-click, and select Analyze Query in Database Engine Tuning Advisor
   2. Click the play button ‘start analysis’
   3. Look over the recommended statistics and indexes, especially the size column.  Indexes are a size/speed tradeoff – you want to make sure you are getting enough benefit out of the extra index space.  Statistics generally have a lower impact to both speed and storage space (they are stored in a blob in an internal table).  NOTE: I’ve generally found the ‘Estimated improvement’ number useless.  I’ve applied an estimated 2% improvement that realized a 2x speed benefit.
   4. Uncheck any you don’t want, and then click ‘Save Recommendations’ to create a .sql file you can apply to the server to create the indexes and statistics.

Today, after loading up a publish script and running it, I got a strange error:  '*** @echo' is not recognized as an internal or external command, operable program or batch file. It then continued to run each line individually, throwing errors on basically every line.

After playing around with creating a brand new empty file, and getting the same error – I realized the problem. The encoding was set to UTF-8, which the Windows command line failing on.

To fix it, just set your encoding to ANSI. Here is a screenshot of how to do that with Notepad++:

And now, everything works wonderfully and I can get back to work:

Today, after loading up a publish script and running it, I got a strange error:  '*** @echo' is not recognized as an internal or external command, operable program or batch file. It then continued to run each line individually, throwing errors on basically every line.

After playing around with creating a brand new empty file, and getting the same error – I realized the problem. The encoding was set to UTF-8, which the Windows command line failing on.

To fix it, just set your encoding to ANSI. Here is a screenshot of how to do that with Notepad++:

And now, everything works wonderfully and I can get back to work:

Have you ever wondered why the box to change the name or “Alias” on an application or virtual directory is greyed out (see screenshot below)? I found a way to change the name without recreating all your settings. It uses the built in administration commands in IIS7, called appcmd.

Renaming Applications In IIS7

1. Open a command prompt to see all of your applications.

   C:> %systemroot%\system32\inetsrv\appcmd list app
   
   APP "Default Web Site/OldApplicationName"
   APP "Default Web Site/AnotherApplication"
   

2. Run a command like this to change your “OldApplicationName” path to “NewApplicationName”. Now you can use http://localhost/newapplicationname

   C:> %systemroot\%system32\inetsrv\appcmd set app "Default Web Site/OldApplicationName" -path:/NewApplicationName;
   
   APP object "Default Web Site/OldApplicationName" changed
   

Renaming Virtual Directories In IIS7

1. Open a command prompt to see all of your virtual directories.

   C:> %systemroot%\system32\inetsrv\appcmd list vdir
   
   VDIR "Default Web Site/OldApplicationName/Images" (physicalPath:\serverimages)
   VDIR "Default Web Site/OldApplicationName/Data/Config" (physicalPath:\serverconfig)
   

   We want to rename /Images to /Images2 and /Data/Config to /Data/Config2. Here are the example commands:

   C:> %systemroot%\system32\inetsrv\appcmd set vdir "Default Web Site/OldApplicationName/Images" -path:/Images2
   
   VDIR object "Default Web Site/OldApplicationName/Images" changed
   
   C:> %systemroot%\system32\inetsrv\appcmd set vdir "Default Web Site/OldApplicationName/Data/Config" -path:/Data/Config2
   
   VDIR object "Default Web Site/OldApplicationName/Data/Config" changed
   

Have you ever wondered why the box to change the name or “Alias” on an application or virtual directory is greyed out (see screenshot below)? I found a way to change the name without recreating all your settings. It uses the built in administration commands in IIS7, called appcmd.

Renaming Applications In IIS7

1. Open a command prompt to see all of your applications.

   C:> %systemroot%\system32\inetsrv\appcmd list app
   
   APP "Default Web Site/OldApplicationName"
   APP "Default Web Site/AnotherApplication"
   

2. Run a command like this to change your “OldApplicationName” path to “NewApplicationName”. Now you can use http://localhost/newapplicationname

   C:> %systemroot\%system32\inetsrv\appcmd set app "Default Web Site/OldApplicationName" -path:/NewApplicationName;
   
   APP object "Default Web Site/OldApplicationName" changed
   

Renaming Virtual Directories In IIS7

1. Open a command prompt to see all of your virtual directories.

   C:> %systemroot%\system32\inetsrv\appcmd list vdir
   
   VDIR "Default Web Site/OldApplicationName/Images" (physicalPath:\serverimages)
   VDIR "Default Web Site/OldApplicationName/Data/Config" (physicalPath:\serverconfig)
   

   We want to rename /Images to /Images2 and /Data/Config to /Data/Config2. Here are the example commands:

   C:> %systemroot%\system32\inetsrv\appcmd set vdir "Default Web Site/OldApplicationName/Images" -path:/Images2
   
   VDIR object "Default Web Site/OldApplicationName/Images" changed
   
   C:> %systemroot%\system32\inetsrv\appcmd set vdir "Default Web Site/OldApplicationName/Data/Config" -path:/Data/Config2
   
   VDIR object "Default Web Site/OldApplicationName/Data/Config" changed