Tips, Tricks, and Advice from the SQL Server Query Optimization Team

A colleague of mine has just introduced me to one of the best blogs I have seen in along while. The Tips, Tricks, and Advice is brought to you by the SQL Server Query Optimization Team.

Here are some of my favourite posts on the performance features of SQL 2005 Query Optimizer. Check out the following:

• Why should I create an index?
  
• I Smell a Parameter!
  
• Fun for the day - Automated Auto-Indexing!

Free Tools that have made my SQL life a bit better

I normally don’t recommend tools on this blog. But over last month I have been working with couple of tools that are worth a mention.

The first tool on the list is SQL Prompt by Red-Gate. I have been a fan of red gate bloggers for a while now at http://www.simple-talk.com/. SQL Prompt is without a doubt one of the most useful ad-ins for SQL Management Studio and Query Analyzer. This little tool is Intellisense for SQL Server. This tool dynamically looks up common functions and syntax as you type. It’s a free download until September 2006.

Check out: SQL Prompt

The second tool was recommended to me by Chris Webb. It’s a replacement for SQL Manager for stoping and starting all of SQL Server 2000/2005 services created by Jasper Smith at SQLDBA.com

Check out: SQL Manager

The last tool on my list is a new migration script by Marco Russo. If you were like me and followed the DTS best practices guide that MS put out and used UDL data sources. You will find that are unable to migrate your DTS packages. Marco has put together a great tool for converting UDL and other DTS objects that do not migrate.

Check out: A tool to improve DTS to SSIS migration

SQL Management Studio Short Cuts

I have been working with SQL Management Studio (SMS) a fair bit lately. Like most developers I always look for the standard short cuts that I used on Query Analyser. Below are a few of my favourites.

Commenting

I use these short cuts all the time to comment large sections of code. The comment style I prefer the most is dash form which I prefer too the slash style. -- Dash Comment /* Slash Comment */

• To comment in SQL management studio you must now do Ctrl+K, Ctrl+C instead of Ctrl+Shift+C in Query Analyzer.
  
• To uncomment in SQL management studio you must now do Ctrl+K, Ctrl+U instead of Ctrl+Shift+R in Query Analyzer.

Formatting

• To indent a block of code Select the code you would like to indent and press TAB to indent
  
• Shift+TAB to un-indent your code
  
• Word Warp long strings with Ctrl+E, Ctrl+W
  
• Make Uppercase Ctrl+Shift+U
  
• Make Lowercase Ctrl+Shift+L

Bookmarks

Ok so you are working on a massive script that travels for miles. Well then you better be using book marks to navigate that code. Bookmarks are great for moving through scripts and trouble shooting. Try out the following the next time you are working on a big script.

• Toggle Book Mark on/off and Crtl+K, Ctrl+K
  
• Enable all book marks Crtl+K, Ctrl+A
  
• Move to next bookmark Ctrl+K, Ctrl+N
  
• Move to previous bookmark Crtl+K, Ctrl+P
  
• Move to next bookmark folder Ctrl+Shift+K, Ctrl+Shift+N
  
• Move to previous bookmark folder Ctrl+Shift+K, Ctrl+Shift+P
  
• Clear book marks Ctrl+K, Ctrl+L

Avoiding Microsoft SQL Server 2005 Deployment Pitfalls

January has not been a good month for blogging. It seems the reality of coming back to work has taken over my time a bit. Plus I am currently training for a Mountain bike race in March. I came across this interesting installation guide on installing SQL Server 2005. It’s worth a read if you have not installed SQL 2005 before.

Check out: Avoiding Microsoft SQL Server 2005 Deployment Pitfalls

It raises an interesting point, what is the minimum spec to locally develop SQL 2005 applications on. Currently I am running a HP nc6000 with 2 Gig of Ram. It’s the minimum I would recommend to anyone starting development on a laptop.

Any one looking at serious development should always have a development server. A basic development server I would start with a 2 way dual core 64x with 4 Gig minimum. Of course you will still need to size your dev server based on project requirements.

SQL Server 2005 Services Manager

So its 2006, No more CTPs or Launches to look forward too; Instead it's back to the reality of doing projects with SQL Server 2005.

Like most developers I miss SQL Server Services Manager to start and stop SQL. Mainly because I have a laptop and I use it for everything else as well as development.

To get round this I have created two DOS batch files on my desktop for stoping and starting required SQL services. There are three main services that I have in my batch files:

• MSSQL - The services name for SQL Server 2005,
• MSOLAP -The services name for Analysis Services 2005,
• ReportServer - The services name for Reporting Services.

The Syntax for starting services is NET START command. To start a default instance of SQL from the command line use the following:

NET START MSSQL

For a Named Instance use the following syntax.

NET START MSSQL$InstanceName

Strangely enough to stop Services you use the NET STOP Comand. Listed below are the two batch scripts I use to stop and start SQL Server , Analysis Services and Reporting Services on my Laptop. Feel free to save them in notepad with the .bat extention.

Start Serivces down batch script.

CDREM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services START Script
REM -----------------------------------------------------------------
NET START MSSQL$SQL2005
NET START MSOLAP$SQL2005
NET START "IIS Admin"
NET START w3svc
NET START ReportServer$SQL2005

Stop Serivces down batch script.

CD\
REM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services STOP script
REM -----------------------------------------------------------------
NET STOP MSSQL$SQL2005
NET STOP MSOLAP$SQL2005
NET STOP w3svc
NET STOP "IIS Admin"
NET STOP ReportServer$SQL2005