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 

Powershell Advocacy for consuming .NET Programs

Powershell is more than a shell. Programs initially not written to be used using shell can also be involved into Powershell script, or cmdlets. Example is my “Get List of File Associated with multiple Changesets” which could also be written in a simple c# program, that too in more user kissing manner with GUI. But compromising on user interface brings more advantages which a developer loves to have for its program, especially for the ones used by developers. Here is why:

1. Faster to write: A power shell script written in 4-5 line in above example may take around 20-100 lines or so to write in C# program. Once you are familiar with the syntax of cmdlet scripting, I think development is very much faster.
   
2. Faster to modify: If you want to change output in any manner, it’s damn easy to understand the script and change it.
   
3. Easy to Run: Once u have Powershell setup, it’s just like running ipconfig or ping!! 
   
4. Easy to extend the output: Output formatting provided inside Powershell is a great deal. Also, easy to write output in files and so easy to use your favorite text editor (mine is scintilla based SciTe). 
   
5. Easy to Reuse: Pipe and reuse the script which u already wrote for some purpose. Or just use script inside script.
   

Secure Your Wireless Internet

Note: Tips applicable for home networking only.
Here are some tips to secure your Internet sharing using Wi-Fi, securing not just for namesake but for real.

Tips for Securing router:
1. Make sure you change user name & password of your router. Remember, any obvious password or password that can be cracked by simple brute force techniques.
2. Restrict “Remote Access” of your router by which people can access your router without being part of your network.
3. You will get many option for Wi-Fi security out of which WEP (Wired Equivalent Privacy) being default. Don’t go on name, WEP is the security which only dumb cannot crack. Use any of WPA (Wi-Fi Protected Access). WPA can also be cracked if the password is weak.
4. Last but not least, don’t lure hackers. Switch off your modem and router when not in use.

Suggestion for strong password:
I know this need not to be a part of this discussion but if you use weak password, no security can help you. A good brute force with proper dictionary can crack these easily.
Do:

1. Mixture of Capital and Small letter, special character
2. Password length should be at least 8 character
3. For longer password, use 256 bit encryption technique
4. You are lucky if you are from non-English speaking country as you can have words which does not exist in common world list for bruit force attack. Use these words freely.
5. Try to have less used letter i.e. ‘x’ or ‘f’ or ‘q’ etc

Don’t:

1. Part of a password with complete English word. e.g. say no to ‘EightNineTen’
2. Password having any (key) sequence e.g. say no to ‘xxx1234’ or ‘xxx!@#$’ or ‘asdf’
3. Password having any obvious personal information e.g. say not to password with birth year
4. Password having repeating words i.e. say no to “IndiaIndia11”

Get List of File Associated with multiple Changesets

I wrote a cmdlet for powershell by which you can get list of file associated with list of changesets.

param(
     [string] $changesets = $(throw 'Usage: Get-Changeset-Files 'changesets')
)

process
{
     # get TFS object
     $tfs = get-tfs ;
   
     # build up list of file items    
     $fileItems = @();
   
     $changesets.Split(',') | foreach {
       $tfs.vcs.GetChangeset($_).Changes | foreach {
           if($fileItems -notcontains $_.Item.ServerItem) {
               $fileItems += $_.Item.ServerItem;
           }
       }
     }
   return $fileItems | Sort-Object;
}

Steps:
1. Create a file Get-Changeset-Files.ps1 and copy above code in the file (preferably where get-tfs resides). Also add this directory into your $path
2. Open Powershell, go to Directory where above file recides
3. Run 'Set-ExecutionPolicy unrestricted' so that you can run above cmdlet
4. Everything is ready.. just run for example: Get-Changeset-Files('10, 20, 30')

See here for get-tfs.ps1

not-my get-tfs

#This is very popular file you may get at many places. Change $serverName's default value

param(
[string] $serverName = 'http://tfs-server:port/'
)

begin
{
# load the required dll
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")

$propertiesToAdd = (
('VCS', 'Microsoft.TeamFoundation.VersionControl.Client', 'Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer'),
('WIT', 'Microsoft.TeamFoundation.WorkItemTracking.Client', 'Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore'),
('CSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.ICommonStructureService'),
('GSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.IGroupSecurityService')
)
}

process
{
# fetch the TFS instance, but add some useful properties to make life easier
# Make sure to "promote" it to a psobject now to make later modification easier
[psobject] $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($serverName)
foreach ($entry in $propertiesToAdd) {
$scriptBlock = '
[System.Reflection.Assembly]::LoadWithPartialName("{0}") > $null
$this.GetService([{1}])
' -f $entry[1],$entry[2]
$tfs | add-member scriptproperty $entry[0] $ExecutionContext.InvokeCommand.NewScriptBlock($scriptBlock)
}
return $tfs
}

Idiotic Software (TFS)

User Experience is where Microsoft TFS loses the ground. Here was a day when I struggled to merge around 30 changesets across branches. The user interface doesn't have any filter by which I can see only my changeset (out of 40 membered team's chagesets from the day we separated the branch). I cannot merge multiple changeset at once untill the are in sequence (no CTRL + select). It seems Team Foundation Server assumes the team is made of 1 person.

What NHibernate Lacks

There were situations when I had to make sure that I Get (or Load) fresh entity object from database. But with NHibernate, until I change configuration forever, there is no other way to do so except to use UnitOfWork.Refresh method.
This is expensive when I the object is not in cache at frist place. It makes 2 database hit.
What I need is something like this,

UnitOfWork.GetFresh(object pk)

OR

if(UnitOfWork.IsCached(object pk) && !UnitOfWork.IsDirty(object pk)
{
//Refresh
}
else
{
//Get
}

Vertical Text Selection

You may need to do sometime vertical selection. For example, in example below, I want to select and remove all “SolidX”.


I need to do vertical selection for this. Selecting text vertically not commonly used but an excellent functionality provided by all serious text editors.

This is how to do it (Windows specific): Hold ALT and SHIFT key and select the text vertically and horizontally. It is not important whether you select first vertically or horizontally but you MUST press and hold ALT key before SHIFT key.

You can do this on all modern Code editors, good text editors like Scintilla based SciTE, TextPad etc. Once you know about vertical text selection selection, you will find its use at more places than what you can think about when unaware.

Blog Popularity

This blog is near to cross 500 unique visitor per month :) finger crossed

How to Get Calling Assembly

I work on a product which has more than 80 assemblies call around each other. We have build with pdb file specially distributed to developers for debugging purpose but all are not so fortunate :)
It is obvious that you cannot be sure about which assembly you will need to compile in order to get full call stack. But with : System.Reflection.Assembly.GetCallingAssembly()

This way you get the calling assembly so you can compile that assembly. There are a lot of goodies to explore in System.Reflection.Assembly class for debugging.

Happy Debugging!!

Change-Set Rollback using TFS Power Tool (tfpt.exe)

Have you made a booboo by checking in something which you shouldn’t have? If you are using TFS and TFS Power Tools (tfpt in short) installed on your system, you are still good to roll the s#%t back.
Here are the steps that you will need to follow:
1. Install TFS Power Tool. This is recommended as it provides a lot of goodies without which you may see hell some day.

2. ‘Shelve’ all changes, as you will have to ‘undo’ all your changes.

3. Open cmd prompt. Make sure your $path environment variable is configured to find tfpt.exe

4. Change directory to the workspace

5. Give this command: rollback /changeset:{Change-Set Number}
(For example rollback /changeset:39356)

6. It will take latest of all your workspace. This is what I don’t like as it doesn’t work otherwise. It takes some but works…

7. After above step, tfpt has taken older version and all those file will be ready to be check-in with older viersion.. Check-in those and take a tea.

Tip [02/24/2009]: Step 6 may kill you if you are storing huge amount of data into TFS. I advice you to create a new workspace with only required files downloaded. For Example, if your all changes are in '$codebase/folder/subfolder/" then create workspace for $codebase/folder/subfolder/ mapped to local directory C:\for_rollback\ Due to this, step 6 will become very fast.

Powertool help and website: http://msdn.microsoft.com/en-us/tfs2008/bb980963.aspx

NO to Extension Methods

Extension Methods are the coolest features of C# 3.0. Although it looks very power full, it is to be used stingily. There are multiple reasons behind that with Maintainability being the first.

• You have to search the whole code base to find where you extension method is implemented.
• There are complex rule based on which overriding of extension method works
• Little more complex methods and you may put yourself into cyclic reference problem
• For me and other users of Rhino Mocks beware as you cannot mock (fake) out extension method yet.

So you may ask me ‘When to use?’ If you come on conclusion that the usage of extension method in your case is die hard and alternatives are way too ugly, perffer oldschool otherwise… Happy Coding!!

TFS Command Line

I do this get list of files of any shelveset into a text file:
Here is list of commands that you can use: http://blogs.msdn.com/buckh/articles/CommandLineSummary.aspx

Idiotic Software

Due to some issue I had to uninstall a Rating software. After uninstall is finished it put my PC into lull.. most of my customized commands went. Then it came out that the software uninstallation program removes all your $Path! How stupid! How can someone write a software like that? I don’t know how safe is my PC with that software. But as my work involves that software so I have deal with all these. It’s really frustrating!

Track down TransientObjectException

Problem:

TransientObjectException is hibernate (nhibernate in my little world) exception which occurs when you flush.

Identify the code which causes it:

It is simple. Put nhibernate session.IsDirty() underwatch. Execute your program stpe by step. You are 'ok' till IsDirty give you 'true' or 'false'. You can identify the culprit step when IsDirty raises any exception.

Cause and Solution:

No one can you tell you exact solution as this may be caused by variety of mistakes. The root cause is that you are trying to save an object into database which has transient objects (not existing in database in our context) attached to it. Solution is to save the transient objects before flush or just remove the "cascade" attribute from the HBM.

GAC Browser blogs separated

GAC Browser blogs will no more be posted here, we have got a separate location for it... visit: http://gacbrowser.blogspot.com/2008/03/gac-browser-introduction.html

GAC Browser – An Introduction

Audience: Microsoft .NET Developer, Support people of huge .NET softwares

Problem: When you are dealing (installation, un-installation, development or support) with huge software with multiple assemblies installed in Global Assembly Cache (GAC), you will have a glance of “hell” while searching the assemblies through in-build assembly viewer on windows explorer. You will be introduced to full feature hell when you get into problem of uninstalling a traced referenced assembly which is not directly possible by popular ‘gacutil’ command. And, you will be deep into hell on occasion of accidental removal of an assembly without any backup. Surprisingly, Microsoft doesn’t seem to care about these features even when the world is in verge of hailing 5^rd major release of .NET

Scope of the software: I felt a need of software to ease/eliminate above problem. Objective and vision of the software is to deal with GACed assembly completely for GAC search, software un-installation, development and support purpose.

Features: GAC Browser is powerful tool to search and remove assembly. It is must have tool for anyone dealing with assemblies in GAC. On an average, if you have visual studio 2005 installed on your PC, you may see more than 1000 assemblies on you PC. There is no good way of searching assembly in you GAC unless you have GAC Browser with you. Also, visibility in GAC is increased with more info like when it was last GACed. Double click any assembly to de-assemble the assembly in ILDASM. All in one! That’s why I said it a “must have”!

At the time, when this is written, GACBrowser supports,

·
· View All Assemblies form GAC.
· Search assemblies based from part of assembly name or public key.
· Sort the searched list based on any assembly attribute.
· Select one or more assemblies and remove them. Application can remove assemblies which is installed by any installer.
· Refresh search assemblies.
· Double Click on row to open assembly in IL Disassembler (ildasm). See picture given below to know where to clik..Note that you have to set proper $path environment variable so that "ildasm" can work.

How to get it? You are advised to download and install MSI for x86 CPU architecture from here: http://sourceforge.net/project/showfiles.php?group_id=214315

NHibernate flush

Note: This discussion is only limited to first level cache provided at ISession level in NHibernate.
Flush(): Unlike its name is suggesting, purpose of Flush() is not to clear cache. It synchronizes the cached objects with the database.In short, you cache and database gets in sync with each other. All that you retrieve using Load(), Get() and List() is entitled to stay in cache. When you do 'save', 'update' or 'delete', operations of NHibernate, not all operations are immediately performed to database. It is when you use 'flush' of NHibernate session, all 'yet to be done' change of database are done. Note that 'flush' is transaction safe. Means, all the operation done in a transaction by 'flush' can be rolled back with other things of transaction.

When to use flush? When you are interested in dirty reading of data, Flush() refreshes your modified data into database (and obviously, does “undo” if the Nhibernate transaction is rolled back) .
What to do to clear cache? Use “Evict” to clear individual object or collection of objects, Isession.Clear() removes all objects.

.NET Assembly uninstall problem

Have you ever encountered this error when you try to uninstall a .NET assembly?

Unable to uninstall: assembly is required by one or more applications Pending references:

Most probably yes!

Cause:

It is because that assembly is installed by an installer (anyone.. including standard .net MSI) . Installer specifies a traced reference count on the assembly being installed so when you try to delete assembly using GACUTIL, you get above error. I couldn’t uninstall some assembly while I was manually uninstalling a software.

Work Around:

Method 1:

Use GACBrowser software to remove such assembly (uses 3^rd approach specified below). It’s Easy, search and select assembly to remove. And removed!!

Download from here: http://sourceforge.net/project/showfiles.php?group_id=214315

GAC Browser is powerful tool to search and remove assembly. It is must have tool for any developer dealing with assemblies. On an average, if you have visual studio 2005 installed on your PC, you may see more than 1000 assemblies on you PC. There is no good way of searching assembly in you GAC unless you have GAC Browser with you. Also visibility in GAC is increased with more info like when it was last GACed. Double click any assembly to de-assemble the assembly in ILDASM. All in one! That’s why I said it a “must have”!

Method 2:

Windows maintains trace reference count in registry at two places.

[HKCU\Software\Microsoft\Installer\Assemblies\Global]

[HKLM\SOFTWARE\Classes\Installer\Assemblies\Global]

So you may want to go to this registry and remove the entry of the assembly that you want to uninstall. Use GACUTIL to remove assembly from GAC.

Method 3:

Go to Assembly folder using command prompt.
1. Open CMD
2. Go to assembly directory (for most of us: C:\Windows\assembly), If you type ‘DIR’, you may find different folders (important are: GAC, GAC_MSIL, GAC_32)
3. Search your assembly folder from this folder, go to appropriate version folder and remove the folder.

Conclusion:

You may want to have GAC Browser software as well has use method 2 for clean operation. GAC browser is soon coming up with in build method 2.

Customizing Visual Studio Compare and Merge Utility

Are you missing your favorite file compare utility because TFS ships diffmerge utility? No doubt the it has solid merge utility but compare only shows horizontal comparison and leaves us without any clue about what’s changed in the highlighted (until we manually find the change in highlighted line).

I have many time tired gvim to compare 2 files. Here is a trick by which you can put your ‘favorite utility’ on fire.

Go to Visual Studio -> Tools -> Option -> Source Control -> Visual Studio TFS. Click on ‘Configure User Tools…”. Here you can configure your ‘Compare’ and ‘Merge’ tools separately.

This is my configuration:

Extension: .cs
Operation: Compare
Command: C:\Program Files\Vim\vim71\gvim.exe (you have to fully qualify the path. Specific for GVim, add C:\Program Files\Vim\vim71 to your $Path)
Arguments: -dR %1 %2

Also, you can pass various arguments for existing diffmerge utility. Here is example to pass ‘ignorespace’ argument.

Extension: .cs
Operation: Merge
Command: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\diffmerge.exe
Arguments: /ignorespace /merge %1 %2 %3 %4 %6 %7

Although /ignorespace is not a recommended but you may want that to do in some specific case.

See complete tutorial here... http://blogs.msdn.com/jmanning/articles/535573.aspx

“generator” element in .hbm file

Recently I was stuck in a peculiar problem where I couldn’t insert a new record in a table using NHibernate. It came out to be the .hbm file which was giving problem. Because our DO generators are written so well, we looked for the bug there at last.

NHibernate class generator always creates class thinking that the primary key will be ‘auto-generated’. But that table doesn’t have auto-generated primary key. And so the “generator” element was incorrectly set to ‘native’ (i.e. auto-generated) for that table.

About “generator” element:

If the value of this is set to ‘native’, the key is auto-generated. Set ‘specified’ to set the primary key yourself in code. See link below for description of all values that generator can have. Note that ‘specified’ is the default in case “generator” element is absent in hbm file.

Link: http://www.roseindia.net/hibernate/hibernateidgeneratorelement.shtml

Throw Exception

You might have figured out ‘Find 10 difference’ column in from your favorite news paper. Here comes it again with finding one difference that affects your code:

try
{
}

catch (Exception)
{
throw;
}

and

try
{
}

catch (Exception ex)
{
throw ex;
}

Answer: In case of ‘rethrowing’ exception, 1^st case would still save the stack trace. Re-throwing the same exception in 2^nd case will result into losing your stack trace.