MS SQL Server

ByDr. SubraMANI Paramasivam

Explore DMVs

You can explore DMV’s within SQL Server Management Studio. A sample picture is provided below.

Explore_DMV

Also you can right click on any DMV’s and then select top 1000 rows how you do like every other table in your local databases. Below image provides information on selecting DM_OS_BUFFER_DESCRIPTORS DMV.

DMV_OS_Buffer_01

ByDr. SubraMANI Paramasivam

Dynamic Management Views

Microsoft have released many DMV & DMF for every release of its new SQL Server release. This has helped every administrator and developers a VERY BIG HAND in sorting out many problems. The days in writing our own scripts are now over and these DMV’s also provide support for almost all areas no matter whether it is to do with sessions, tables, indexes, Operating system, I/O. All are now taken care. Below I am providing some of the DMV’s that might be helpful for you.

Execution Related

• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_cached_plans
• sys.dm_exec_query_plans
• sys.dm_exec_sql_text
• sys.dm_exec_query_stats

Index Related

• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns

SQL Server Operating System

• sys.dm_os_performance_counters
• sys.dm_os_schedulers
• sys.dm_os_nodes
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats

I/O Related

• sys.dm_io_virtual_file_stats
• sys.dm_io_pending_io_requests
• sys.dm_io_cluster_shared_drives

ByDr. SubraMANI Paramasivam

Change Database Recovery Model

In this post, I am going to provide a simple demo on how to change the Database Recovery Model. For this example I have chosen the AdventureWorkdDW2008R2 database and you can download it from here.

Once you have opened your SQL Server Management Studio, expand the databases and right click on our AdventureWorkdDW2008R2 database and choose properties. The below image will appear. In here go to the Options tab from the left and then you can see “Recovery Model” options which has Full, Bulk-logged, Simple. You can simply select the required recovery model and click on OK button at the bottom of the pop-up box. That’s it, your recovery model will be changed.

ChangeRecoveryModel

ByDr. SubraMANI Paramasivam

Differential Database Backup

This post is to cover differential database backup in Microsoft SQL Server. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.

In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…

FullDatabaseBackup_01

Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and selected “Differential”. If you click on the drop down box, you will then see options for other types of backups.

Differential_DatabaseBackup_01png

In “Backup set”, I am giving a name for the differential backup. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the default location.

Also you can use below T-SQL scripts to take backups instead of using GUI interface.

BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N’F:\\AdventureWorksSimpleRM.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N’AdventureWorksDW2008R2-Differential Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

NOTE: Full Database backup should be taken before taking differential backup.

Give a try at your end.

 

ByDr. SubraMANI Paramasivam

Full Database Backup

This post is to cover some of the backup options that we have. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.

In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…

FullDatabaseBackup_01

Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and you see “Full” is selected. If you click on the other options you will then see options for other types of backups to be chosen.

FullDatabaseBackup_02

Then I have highlighted “Backup set”, which means I am giving a name for it. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the provided default location.

You can also use below T-SQL Scripts to do the same manually instead of using GUI screens.

 

–Syntax for Full Database Backup
BACKUP DATABASE databasename
TO backup_device [ ,…n ] –Device and Location
[ WITH with_options [ ,…o ] ] ;

–Simple Script to take Full Database Backup
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\AdventureBackup.bak’
GO

–Craete a Full database backup by using WITH FORMAT to overwrite any exisiting backups and Create a new media set
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\\AdventureWorksSimpleRM.bak’
WITH FORMAT;
GO

Happy going.

ByDr. SubraMANI Paramasivam

SQL Server Memory Issue

Every one running SQL Server has always had the same issue of memory. Whatever the extra memory is given to windows, it is all eaten by the SQL Server alone. No matter what the process is going on, the memory takes much resources for all related to SQL Server. How can we control the memory issue. Well there are some possibilities from SQL Server that we can restrict the server to occupy the server memory.

–To check for the existing memory usage

use master

EXEC sp_configure ‘max server memory (MB)’

By default the server won’t display the advance options to enable this service. So follow the steps below to view the memory usage.

–To display advance option

USE master

EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE WITH OVERRIDE

After this you can run the below query to see the usage.

use master

EXEC sp_configure ‘max server memory (MB)’

Then use this to change the server memory.

–To change the maximum server memory

USE master

EXEC sp_configure ‘max server memory (MB)’, 6144 RECONFIGURE WITH OVERRIDE

Then again you can check the usage using the query which we have see earlier.

ByDr. SubraMANI Paramasivam

MICROSOFT SQL SERVER

  • Business Intelligence
  • Database Development
  • Database Administration
  • SQL Server Administration & Management
  • SSIS / SSAS / SSRS
  • Reporting & Dashboards
  • Performance Tuning
  • High Availability
  • Custom solutions
ByDr. SubraMANI Paramasivam

SQL Server 2008 New Features

With SQL Server 2008 version there are quite a number of new features are added in. Those are listed below. (I know this is pretty old update, but new when SQL 2008 was released).

  1. Installation of SQL Server 2008 with more options
  2. Compressed Backups
  3. Enhanced configuration
  4. Management of Audits with CDC (Change Data Capture)
  5. New Table Value Parameter
  6. Filestream Data types
  7. Sparse Columns
  8. Encryption Enhancements
  9. High Availability
  10. Performance
    1. Performance Data Management
    2. Resource Governor
    3. Freeze Plan
  11. Declarative Management Framework (DMF)
  12. Development Improvements
    1. LINQ Supports
    2. MERGE Statement
  13. Spatial Data type
  14. Analysis Services Improvements
  15. ETL Enhancements
  16. Few on Reporting Services
    1. No longer use of IIS
    2. Better Graphing
    3. Export to Word Support
  17. Some of the Deprecated & Discontinued Features