RoboCopy and RichCopy

Here are two microsoft tools which extend the copy functionality.I have used the Robocopy earlier.I tested RichCopy  recently and liked this GUI.

Multiple options to Clear query cache and buffer pool

While tuning queries it becomes important to run the existing query and obtain initial execution time.

Changes are then made to the query or underlying indexes or statistics. The modified query is executed and the execution time is compared with the initial value.

The query gets compiled the very first time it is executed and the query plan is cached. Similarly the results of the first execution are stored in the buffer pool.

As a result the second time the test query is executed, unless the query is recompiled the execution time would be faster and undependable.

With every change the query should be executed with the exact same set of conditions as the initial execution. How can this be done?

This can be achieved by clearing the plan cache and buffer pool using these commands

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Note: Do not run these commands on a production server. These commands will cause  queries to be recompiled and it may adversely impact performance. This changes clear plan cache and buffer pool for the entire instance.

DBCC FREEPROCCACHE

Removes all cached query plans and execution contexts from the plan cache,

Removes a specific plan from the plan cache by specifying a plan handle or SQL handle or

Removes all cache entries associated with a specified resource pool.

Example:

SELECT * FROM Person.Address;

GO

SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE text LIKE N'SELECT * FROM Person.Address%';

GO

dbcc freeproccache(0x060005123RGCA270E306E5EF00200000001000000000000000000000000000000000000000000000000000000)

DBCC FREESYSTEMCACHE

Removes all plans from all caches or from a specified Resource Governor pool.

Example: DBCC FREESYSTEMCACHE('SQL Plans')

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

DBCC FLUSHPROCINDB( db_id )

Removes all cached plans from the plan cache for a particular database.

Example:

       Declare @dbid  int = DB_ID()

       DBCC FLUSHPROCINDB(@dbid)

DBCC DROPCLEANBUFFERS

Removes all clean buffers from the buffer pool. Issue a checkpoint to write all dirty pages to disk and then execute the command.

SQL Server Ebook collection

I found Performance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson really really helpful.

I have the paperback edition and I was looking for the online version to avoid typing the sample queries.The entire book and many more are available for free download.Happy reading!

SSMS Tool Pack

Checkout the features of SSMS Tool pack. SSMS2012 needs a license.Prior versions are free.

MySQL Database on HP Cloud (free private beta)

Check this out.You can host an MySQL database on HP Cloud now.

HP Cloud Relational Database for MySQL uses an open source distribution of MySQL built upon OpenStack™ technology. This is offered on-demand as a pay-per-use service (offered for free during our private beta period for this service)

Host your database on Amazon cloud for FREE

You could setup your database on the amazon cloud here for free.The database can have a 20GB capacity with 10 million I/Os.

It is easy to signup for an account, create an instance, provide access and launch it.For step by step guide view this link.

Rename Logical Filename

What would be a good reason to change the logical name of a database?

Well, every time there is a requirement to create a database for development,the DBA  provides a name that would be most applicable to that database at that point in time.In most cases an abbreviation of the project.

Couple of months/years later,this database has to be setup for System test,Technical test and so on.A backup of the development database would be restored on to the new environment.While the data and log files are named based upon the destination environment,the logical names remains the same.In most cases, by the time the product is delivered the logical and physical names are way different.

The logical name of the database can be changed using the below syntax.

ALTER DATABASE AdventureWorks2012
  MODIFY FILE ( NAME = AdventureWorks2012_Data ,
                                               NEWNAME = AdventureWorks2012_lob_data )

Though the logical name can be changed post restore for each environment,I would steer clear of this approach.Instead a better way would be to avoid environment names in the logical file name.

A sample template could be to just use

            <Projectname>_data/log or <Projectname>_<purpose>_data/log
  Eg. Proj_LOB_Data/Log.

This would make sure that the filename does not have to be modified at a later stage and the initial name remains vaild throughout the lifecycle of the project.