FAQ : How to clear SQL Server cache / memory

Warning : Not to be used in production env.

In Development or tsesting env its very common that during performance tuning we do clear cache to get correct picture. It may also required that only a particular db related cache should be clear. here wo go...

(a) Clear entire procedure and databuffer cache

Checkpoint -- Write dirty pages to disk
DBCC FreeProcCache -- Clear entire proc cache
DBCC DropCleanBuffers -- Clear entire data cache

(b) Clear only a particular db procedure cache using undocumented DBCC command

Declare @DBID int
Select @DBID =db_id(‘YourDBname’)
DBCC FLUSHPROCINDB(@DBID) – Undocumented dbcc command to clear only a db proc cache

FAQ : How to move a physical file (MDF or LDF) from one location to another in SQL Server 2005

In SQL Server 2000, if you want to move a physical file of a database from one location to another , you have to detach and attach the database. In SQL Server 2005, this process has been made very simple, you need to take the database offline, alter the file path with the new one using Alter Database command and copy the database file to new location manually and finally take the database online. Simple

--Step 1 : Create Database
Create Database TestMoveFile and check the database file location
GO
Select *From Sys.master_files where database_id=db_id('TestmoveFile')
GO
--Step 2 : Alter Database and Set the db to offline
Alter Database TestMoveFile Set Offline
GO
-- Step 3 : Move the physical file to new location
--Move the file to new location using dos command or Windows GUI

--Step 4 : Alter the database file path using Alter Database command
Alter Database TestMoveFile Modify File(Name='TestMoveFile',FileName='c:\TestmoveFile.mdf')
Go

-- Step 5 : Set the database Online and check the file path
Alter database TestMoveFile Set Online
GO
Select *From Sys.master_files where database_id=db_id('TestmoveFile')

Go
Drop database TestMoveFile

FAQ : What is the difference between DELETE TABLE and TRUNCATE TABLE commands

TRUNCATE

• Less Transaction Log entry because TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log and hence TRUNCATE is fast
• TRUNCATE apply Fewer table locks
• TRUNCATE is DDL Statement
• TRUNCATE Release the tables spaces to System
• TRUNCATE Can not have WHERE Conditions
• TRUNCATE does not fire trigger
• TRUNCATE reset the identity to 0 (if table have any)
• TRUNCATE Can not be used against the tables involved in TRUNCATE transactional replication or merge replication.
• TRUNCATE Can not be used against the table used in Indexed view
• TRUNCATE can not be used against the table that Are referenced by a FOREIGN KEY constraint.
• TRUNCATE commands are not tracked by DDL trigger

Note : TRUNCATE can be rollbacked. I have seen many place where its is mentioned that it can not

DELETE

• DELETE FROM TABLE command logs each records in transaction log , hence DELETE is slow.
• DELETE apply more locks to the table
• DELETE is a DML command
• DELETE remove the records but will not release the space to the system
• DELETE can have WHERE conditions
• DELETE Fires TRIGGER
• DELETE do not RESET IDENTITY
• DELETE Can be used against table used transactional replication or merge replication
• DELETE Can be used in tables reference by a Foregin Key and tables involved in Indexed view

FAQ : Index Scan Vs Seek in SQL Server

There are five logical/physical operators in SQL Server related to Index scan ,Index Seek and Table scan.

(a) Table Scan
(b) Clustered Index Scan
(c) Clustered Index Seek
(d) Index Scan (Non- Clustered index Seek)
(e) Index Seek (Non- Clustered index Seek)

Table Scan
Table Scan retrieves all rows from the table (if you have no WHERE Conditions). Basically, before returning the rows, it traverse through all data pages related to the table. If you have where condition, though it travel through all the pages only those rows are returned which satisfy the conditions. When you do not have Clustered index on the table it does a table scan. In other words, both Clustered Index Scan (clustered index is nothing but the data itself) and Table Scan are same because in both method system traverse through all the data pages. Generally, you should avoid table scan.

Clustered Index Scan
Clustered Index Scan is nothing but horizontally traversing though the clustered index data pages. Clustered Index Scan return all the rows from the clustered index (Clustered index is nothing but Data). If you have where condition , only the satisfying rows are returned, but system traverse through all the data pages of the clustered index. Both Table scan and Clustered Index Scan are generally considered to be bad. But at times like if the table is small contains only few rows table or clustered index scan may be good also

Clustered Index Seek
Clustered Index Seek traverse vertically right down to the Data page where the requested data is stored. Basically any seek is vertically traversing though the B-Tree structure (as we all know the index is stored in B-tree structure in sql server). System does a Seek when it find a useful index and generally its done for highly selective query.

Index Scan or Non-Clustered Index Scan
As already said, Scan is horizontal traversing of B-Tree data pages. But in this case it horizontally traverse though the Non-Clustered index available. Its not same as Clustered index scan or Table Scan. In SQL Server , while reading execution plan you can find only Index Scan not Non-Clustered Index Scan. But you must read Index Scan as Non-Clustered Index Scan.

Index Seek or Non-Clustered Index Seek
As already mentioned Seek is Vertical traversing of B-Tree to the data page. But in Index seek it vertical traversing of Non-Clustered Index. Generally, its considered as the best option for high selective query.

FAQ : How to reset SA Password when there is no Buildin\Administrator role in SQL Server 2005

Check Raul Garcia's blog Entry

FAQ : Query to get all the Dynamic Management Views/Functions

Select * From Sys.All_Objects
Where Type IN ('V','TF','IF')
and Name Like '%dm_%' And
Schema_ID=4

Webcasts in Feb 2008

Check out the webcasts schedule in the month of Feb.
http://www.microsoft.com/india/webcasts/

DOs and Don’t when Running Profiler

We run profiler often for many reasons. We generally, do this on Test/UAT environment. Running profiler on production is not recommended but not completely avoidable always. You may need to run profiler/trace even on production server. But to get better result with less overload, we may need to follow some guideline. I think following are the general guideline which we can follow when we run profiler on any environment.

(a) Do not save the trace result into a table on the same database
(b) Run the trace with proper filter. Monitor only the required events otherwise the trace may overload the system.
(c) Run the trace from any other machines other than which you are monitoring. You can connect from Test server or any other workstation which acts as a monitor to the actual server.
(d) If you are running the trace for workload then run the Trace for the whole business day when activities are high to get exact picture.
(e) Create a template with the events you need and save it to re-use.
(f) You may save the result to a table so that you can run sql query against it.

Note : It is always recommended to run T-SQL Storedprocedure that define the trace rather than running Profiler GUI.

A dream come true... you can call me MVP

I have no words... I have been honoured by Microsoft and I am a MVP now... From a Soldier to a MVP, probably first MVP from Indian Defence Services , its been a great new year for me

SQL Server 2008 Virtual Lab

TechNet Virtual Lab: Administering Servers by Using Declarative Management Framework (DMF) Policies
TechNet Virtual Lab: Authoring Reports Using SQL Server 2008 Reporting Services
TechNet Virtual Lab: Implementing Change Data Capture (CDC) in SQL Server 2008
TechNet Virtual Lab: Learn How to Build Occasionally Connected Applications with SQL Server 2008
TechNet Virtual Lab: What's new in SQL Server 2008 for Database Administrators
TechNet Virtual Lab: Working with the new DATE data type in SQL Server 2008