Clear SQL Server Cache

While working on performance tuning sql queries or procs, the query which took a considerable amount of time to execute the first time will take very less time to execute the second time. The reason behind this was the data getting cached and sql server uses this cached data to publish the results.

To free this cached data use the commands DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.

DBCC DROPCLEANBUFFERS clears all data from the cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE clears the stored procedure cache.

SQL Server 2005 management studio not getting installed in XP – Solution

I was setting up my system with Windows XP SP2, Visual Studio 2008 and SQL Server 2005 Developer version. After the installation of SQL Server I found that the management studio was missing from my startup menu options. I tried to reinstall the SQL Server but with no luck. I tried searching Google for a solution and found similar problems with lot of guys.

Thanks to the blog by Pete Orologas http://blogs.neudesic.com/blogs/pete_orologas/archive/2006/10/12/416.aspx on the same issue, I was able to find a work around for the issue. I tried to follow Pete’s method to get my management studio installed on my system by renaming my Tools folder and executing the SqlRun_Tools.msi file from the installation CD. But to add to my disappointment I got the warning from SQL Server setup “A component that you have specified in the ADD_LOCAL property is already installed. To upgrade the existing component, refer to the template.ini and set the UPGRADE property to the name of the component.” and the setup closed without completing the installation process.

Issue:

The visual studio installation comes with a SQL Server express edition along with it. During installation of Visual Studio it creates a folder in C:\Program Files\Microsoft SQL Server\90. During the installation of SQL Server 2005, it will look for the folder C:\Program Files\Microsoft SQL Server\90. If the folder exists the setup will skip the section for installing the tools.

Solution:

• Rename the Tools folder in C:\Program Files\Microsoft SQL Server\90 folder and run the SqlRun_Tools.msi file from the Tools folder in the installation CD.
• If the setup throws an error.
• Open Add/Remove programs from control panel.
• Select Microsoft SQL Server 2005. Click Change.
• Check whether WorkStation Components are installed.
• If yes, remove the workstation components by selecting the WorkStation Components and uninstalling that.
• Rename the Tools folder in C:\Program Files\Microsoft SQL Server\90 folder and run the SqlRun_Tools.msi file from the Tools folder in the installation CD.
• After installation you can find the Management Studio in your start menu under the Microsoft SQL Server folder.

Hope this may help someone while installing SQL Server.