avoid checking parameters at where clause.

Recently , we came across an issue, when massive traffic come in, one of our store procedure will trigger very very high CPU usage,  at some point, it trigger the CPU usage spike to 90% percent, which greatly increase the database response time, and our service is timing out, also it slow down the other websites which are also connecting this database.  In our dev environment , when we look at the store procedure, it looks totally fine, we check the execution plan, it is good as well, we are using index seek , nothing is expensive at all.
Today we create a load test using Visual Studio 2010, which can simulate 1000 or 2000 concurrent online user and be able to reproduce the issue, and we have an interesting finding.

We are doing parameter check at where clause.

assume In SQL Server, if you have store procedure, which can pass in an @UserID INT, when @UserID is 0, you would like to skip this parameter, only use it when @UserID is not zero.  so the SQL Statement you can write like this.

SELECT ID,REALNAME,USERNAME FROM USERS where (@UserID=0 OR ID=@UserID)

When you looked at this statement it is fine, and when you try it , it works as well but it is problematic.
a)When you have a lot of traffic on a store procedure that is implemented in this way, you will find this statement use far more CPU resource than others.  in our case, the store procedure cause the SQL Server CPU to spike , and it reach 90% , and keep high.  after we take this statement out, CPU usage goes down to around 50 percent.
b)if you write SQL Statement this way, SQL Server will not be able to pick up appropriate index.

So be careful, if you are writing some SQL statement which will be executed on a database with high traffic, please avoid it.