Home » 2013 » April

Monthly Archives: April 2013

DMV-9 : Digging out details of CLR Tasks……..sys.dm_clr_tasks

sys.dm_clr_tasks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-in/library/ms177528.aspx

Returns a row for all common language runtime (CLR) tasks that are currently running. A Transact-SQL batch that contains a reference to a CLR routine creates a separate task for execution of all the managed code in that batch. Multiple statements in the batch that require managed code execution use the same CLR task. The CLR task is responsible for maintaining objects and state pertaining to managed code execution, as well as the transitions between the instance of SQL Server and the common language runtime.

sys.dm_clr_tasks DMV is applicable to you if you have enabled the CLR on your SQL Server instance, and you are using at least one CLR assembly loaded in any of one user databases on your SQL Server instance.

You should look for rows having forced_yield_count column value above zero or that have a last_wait_type of SQLCLR_QUANTUM_PUNISHMENT. This will point that the task previously exceeded its allowed quantum & caused the SQL OS scheduler to intervene and reschedule it at the end of the queue. Value of Column forced_yield_count shows the number of time that this has happened.

If you noticed this, you should talk to your developer for this. This could cause issue for you SQL Server.

How to enable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,1

GO

RECONFIGURE

GO

How to disable CLR?

EXEC SP_CONFIGURE ‘CLR ENABLED’,0

GO

RECONFIGURE

GO

Query 1 : FIND LONG RUNNING SQL/CLR TASKS

SELECT

OS.TASK_ADDRESS,

OS.[STATE],

OS.LAST_WAIT_TYPE,

CLR.[STATE],

CLR.FORCED_YIELD_COUNT

FROM SYS.DM_OS_WORKERS AS OS

INNER JOIN SYS.DM_CLR_TASKS AS CLR

ON (OS.TASK_ADDRESS = CLR.SOS_TASK_ADDRESS)

WHERE CLR.[TYPE] = ‘E_TYPE_USER’;

Remarks

1. To use this DMV, User required VIEW SERVER STATE permission on the server.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

DMV-8 : Check Space consumed by Database……..sys.dm_db_file_space_usage

sys.dm_db_file_space_usage DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms174412.aspx

Returns space usage information for each file in the database.

It’s most commonly used DMV to check total used & available free space in database. Before SQL Server 2012, it applicable only to the tempdb database

Key Columns :-

  • database_id – identifies the database on basis on database id
  • unallocated_extent_page_count – Total number of pages that are in unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Unused pages in used extents (having any active data) will not be included in this total.
  • version_store_reserved_page_count – Number of pages reserved to support snapshot isolation transactions
  • mixed_extent_page_count – Number of extents that have pages of multiple types (user objects, internal objects, version store, Index Allocation Map (IAM) pages, etc.)

Query 1 : Calculate total, used & unused in databases

SELECT

DB_NAME(SU.DATABASE_ID) DBNAME,

MF.PHYSICAL_NAME,

SU.ALLOCATED_EXTENT_PAGE_COUNT*8/1024 ALLOCATED_EXTENT_SIZE_MB,

SU.TOTAL_PAGE_COUNT*8/1024 TOTAL_SIZE_MB,

SU.UNALLOCATED_EXTENT_PAGE_COUNT*8/1024 UNALLOCATED_EXTENT_SIZE_MB

FROM

SYS.DM_DB_FILE_SPACE_USAGE SU

JOIN SYS.MASTER_FILES AS MF

ON MF.DATABASE_ID = SU.DATABASE_ID

AND MF.FILE_ID = SU.FILE_ID

Query 2 : Calculate Free space in TempDB

SELECT

UNALLOCATED_EXTENT_PAGE_COUNT/128 [FREESPCAE
(MB)]

FROM

SYS.DM_DB_FILE_SPACE_USAGE

Remarks

1. User required VIEW SERVER STATE permission on the server.

2. sys.dm_db_file_space_usage did not include the LDF file size whereas SP_Spaceused include LDF file size while calculating total database size.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 7 for SQL Server 2012 RTM Is Now Available !

The 7th cumulative update release for SQL Server 2012 RTM is now available. Cumulative Update 7 contains all the hotfixes released since the initial release of SQL Server 2012 RTM.

 

 

Those who are facing severe issues with their environment, they can plan to test CU7 in test environment & then move to Production after satisfactory results.

KB Article For CU7 of SQL Server 2012

· CU#7 KB Article: http://support.microsoft.com/kb/2823247

Previous Cumulative Update KB Articles of SQL Server 2012

· CU#6 KB Article: http://support.microsoft.com/kb/2728897

· CU#5 KB Article: http://support.microsoft.com/kb/2777772

· CU#4 KB Article: http://support.microsoft.com/kb/2758687

· CU#3 KB Article: http://support.microsoft.com/kb/2723749

· CU#2 KB Article: http://support.microsoft.com/kb/2703275

· CU#1 KB Article: http://support.microsoft.com/kb/2679368

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 6 for SQL Server 2008 R2 Service Pack 2 Is Now Available !

The 6th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 6 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP2.

 

 

Those who are facing severe issues with their environment, they can plan to test CU6 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2008 R2 SP2

· CU#6 KB Article: http://support.microsoft.com/kb/2830140

Previous Cumulative Update KB Articles:

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 12 for SQL Server 2008 R2 Service Pack 1 Is Now Available !

The 12th cumulative update release for SQL Server 2008 R2 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 12 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP1.

 

 

Those who are facing severe issues with their environment, they can plan to test CU12 in test environment & then move to Production after satisfactory results.

For CU12 of SQL Server 2008 R2 SP1

· CU#12 KB Article: http://support.microsoft.com/kb/2828727

Previous Cumulative Update KB Articles:

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: