Parameter Sniffing

Sometimes maybe you find out your SP (Store Procedure) doesn't response in reasonable time despite Select statement, which you used in that SP, has good performance and result is shown you immediately. What's wrong with your SP?
There are different answers for that problem but when I faced on this problem, I realized it's strongly related to Parameter Sniffing.

You can solve Parameter Sniffing easily with follow this example:
If you have a SP such as GetOrderForCustomers that has Parameter Sniffing issue:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
As
Begin
Select * from orders
Where customerid = @CustID
End

To solve problem you should change your SP similar:

Create procedure GetOrderForCustomers (@CustID Varchar (20))
As
Begin
Declare @LocCustID Varchar (20)
Set @LocCustID = @CustID
Select * from orders
Where customerid = @LocCustID
End

Good luck.

How to Speed up SQL Server Management Studio in SQL Server 2005?

Have you ever launched SQL Server Management Studio (SSMS) and had to wait over 60 seconds before you could even log in? I have - all too often! It's annoying - and thankfully there's an easy fix.

1. Launch Internet Explorer
2. Browse to Tools -> Internet options -> Advanced tab
3. Uncheck the check box under Security called "Check for publisher's certificate revocation"

Now you'll be able to launch SSMS and it will be fast. BTW - this works particularly well for offline workstations/laptops. When you are connected to the internet and SSMS checks for this info (every 15 friggin' seconds) then it's fine - but when you aren't online, it has to wait for a timeout!

Increase your VS compile speed

To increase your VS compile speed:

• Install Microsoft hotfix 935225.
• Install Microsoft hotfix 947315.
• Use a true multicore processor (ie. an Intel Core Duo 2; not a Pentium 4 HT).
• Use 3 parallel builds. In Visual Studio 2005, you will find the option in Tools > Options... > Projects and Solutions > Build and Run > maximum number of parallel project builds.
• Defragment your hard drive regularly.
• Disable virtual memory.

Visual studio IDE

Visual studio is a pretty awesome IDE, but sometimes you just wish it would go faster.

Here's a list. All of these can be accessed on Tools->Options menu:

1. Disable F1. (Environment->Keyboard) This is probably the best advice that I found somewhere.
2. Disable "Animate environment tools" (Environment->General).
3. Disable Start Page (Environment->Startup).
4. Disable "Track Active Item in Solution Explorer" (Projects and Solutions).
5. Disable Navigation Bar (Text Editor->C#). I think this is available for every language.
6. Set "AutoToolboxPopulate" to false (Windows Forms Designer).
7. You can set the Code view as the default view when viewing Windows Forms. Just right-click on the cs file and select "Open With...".
8. Open Visual Studio using the command line (devenv) rather than using the Start menu. I don't know why but I notice it loads faster.
9. Turn off Track Changes. (Text Editor->Track changes)

Basically I just customized John Lam's settings. It's very minimal.

And also you can use ScottGu's Blog tips.