Clear MSSQL Server Query Cache

NOTE: Strictly not for production servers and I mean it.

If you are first time optimizing query or choosing right index then you must know that SQL Server gives result from it's caches if query was already fired once. This is very useful feature but this behavior may tempt you to take wrong decision if this happens outside your knowledge.

You must see:

1. DBCC DROPCLEANBUFFERS -- Cleans temp buffers and dirty data.  You may want to use CHECKPOINT before this command. http://msdn.microsoft.com/en-us/library/ms187762.aspx

2. DBCC FREEPROCCACHE -- Use this to clear execution plans http://msdn.microsoft.com/en-us/library/ms174283.aspx 

I've not given FREEPROCCACHE with parameters which you may or may not be interested in but you should check out the link of microsoft site given there.

NOTE: Strictly not for production servers and I mean it.

"which" command for Windows using Powershell

"Code Assassin" has given a great way to use 'which' command (very popular in Unix and absent in Windows CMD prompt) in Windows using Powershell. If you are too lazy to go to his blog, here is the command,

($Env:Path).Split(";") | Get-ChildItem -filter sqlwb*

Here sqlwb is the command for which you want to know 'which' one will be used when executed, in short, location(s) of the command.

Surely there are better queries than this exists at the same place but above one simply works... rather works simply.

(at occations it gives more result than acutally it should give.. but you can easily figure out unwanted)

For other direct commands see: http://blog.stevex.net/powershell-cheatsheet/
It has nice collections for DOS commands and equivalent Powershell command.

Database Indexes: tips

Database Indexes are sometime tricky. But it's easy to fall into traps by following simple advices. Here are some common tips (specific to t-sql and it may or may not apply to other RDBMS) listed here:

Sample Database Table:

Customer_Id	Primary Key, Clustered Index, Numeric
Customer_Name	Part of multi-column index on "Customer_Name, Customer_Mobile, Customer_Location", Varchar
Customer_Mobile	Indexed, Varchar
Customer_Location	Part of multi-column index on "Customer_Name, Customer_Location"

1. Index is not used, when columns is in function

Example of Bad:
Select * from customer
where IsNull(Customer_Name,'!true') = IsNull(@CustomerName, '!true')

Good query:
Select * from customer
where Customer_Name= @CustomerName
OR (Customer_Name is null AND @CustomerName is null)

2. On DataType mismatch,

Example of Bad:
Select * from customer
where Customer_Mobile = 9900114477

Select * from customer
where Customer_Id = '2'

Good query:

Select * from customer
where Customer_Mobile = '9900114477'

Select * from customer
where Customer_Id = 2

3. Using Like on Wrong End:

Example of Bad:

Select * from customer
where Customer_Name LIKE '%ram'

Good Option:

If you are gonna use put % always at first, use reverse Indexing

4. : Multi-column Index is created with wrong sequence of columns

Example of Bad: 

Select * from Customer 

Where Customer_Name = 'CN' and Customer_Location = 'CL'

Good Option:

On Index is on columns: 

1. Customer_Name

2. Customer_Mobile 

3. Customer_Location

Which is only used when one of these is in your where clause:

1. Customer_Name

2. Customer_Name and Customer_Mobile 

3. All three, Customer_Name, Customer_Mobile and Customer_Location

So only option is to modify Index Or Add new index on Customer_Location