get row count for a table SQL Server DMVs

In SQL 2000 you can get this informartion from sysindexes system table.
But we should not sysindexes in because it will be deprecated in future

map login with users

When we restore backups to new server then users in the database are not mapped with their login in the new server
Its because sid is not matching.In this case we will are used to with sp_change_users_login
But this procedure will be deprectated in future version . So instead of this we should use alter user


alter user username
with
= new username,
,


So for mapping a user test with testlogi  we will use


ALTER USER test WITH login = testlogin

DMVs instead of DBCC Opentran

Those who are using SQL Server must be aware of dbcc opentran
After 2005 edition , SQL Server  is providing reach information in DMV.
For transactions 2 DMVs are available in SQL Server

sys.dm_exec_procedure_stats DMV to get cached proc stats

We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.

Diana Lite , a good utility to study Execution Plan

I came across a utility Diana Lite by  Daniel Zrihen . It is developed by nobhillsoft. Its a good utility and I think its fun to work with it and its a freebie :-)

Use of resource governor to limit CPU usage for backup compression

Resource Governor

As many of you have gone through my previous article on compress backup
Since backup compression takes higher cpu it affect other processes

Backup Compression

Backup compression
Its a new feature in SQL Server 2008 Enterprise Edition.
Its support to compress a backup , for which we had to use third party tools earlier.

compression part 2

Compression in partitions

We can set compression property for individual partition .

Below are the points that we have to take consideration for parition operation.

Compression Part 1

If you are using Enterprise or Developer edition of SQL Server  2008 you can use compression on your server

estimate data saving by compression

Today I got a mail for how to get space saving estimation using compression


Well compression is supported in SQL Server 2008 Enterprise and Developer Edition.

Merge Statement - SQL Server 2008

I am going to tell you about the new feature in Sql server 2008 "MERGE"statement.

It is used to perform insert , update, delete statement in a single query on a target table by joining to a source table