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/)

DMV-7 : Find Queries waiting for memory ?……..sys.dm_exec_query_memory_grants


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

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

This DMV helps in finding queries that are waiting (or recently had to wait) for a memory grant. This particular DMV works with SQL Server 2005, 2008, and 2008 R2. There were some new columns added for SQL Server 2008 and above.

You should periodically run this query multiple times in regular intervals and need to look for rows returned each time. If you do see a lot of rows returned each time, then it could be an indication of internal memory pressure. It will help you to identify queries that are requesting relatively large memory grants, perhaps because they are poorly written or they’re missing indexes that make the query more expensive.

Query 1 : Details of queries required memory to execute

SELECT DB_NAME(ST.DBID) AS [DATABASENAME],

MG.REQUESTED_MEMORY_KB ,

MG.IDEAL_MEMORY_KB ,

MG.REQUEST_TIME ,

MG.GRANT_TIME ,

MG.QUERY_COST ,

MG.DOP ,

ST.[TEXT],

QP.QUERY_PLAN

FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP

ORDER BY MG.REQUESTED_MEMORY_KB DESC ;

Remarks

1. 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-6 : How well my store procedure doing ?……..sys.dm_exec_procedure_stats


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

Returns aggregate performance statistics for cached stored procedures. The view con­tains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the cor­responding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

This DMV is new to SQL Server 2008 so you can use it only in SQL Server 2008 onwards. You can get similar data out of sys.dm_exec_cached_plans, which will work on SQL Server 2005. This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.

Query 1 : Details of cached procedures

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,*

FROM SYS.DM_EXEC_PROCEDURE_STATS

Query 2 : Details of procedure with total & average CPU, logical reads , logical writes & physical reads

SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME

,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]

,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]

,CACHED_TIME

,LAST_EXECUTION_TIME

,EXECUTION_COUNT

,TOTAL_WORKER_TIME / EXECUTION_COUNT AS AVG_CPU

,TOTAL_ELAPSED_TIME / EXECUTION_COUNT AS AVG_ELAPSED

,TOTAL_LOGICAL_READS

,TOTAL_LOGICAL_READS / EXECUTION_COUNT AS AVG_LOGICAL_READS

,TOTAL_LOGICAL_WRITES

,TOTAL_LOGICAL_WRITES / EXECUTION_COUNT AS AVG_LOGICAL_WRITES

,TOTAL_PHYSICAL_READS

,TOTAL_PHYSICAL_READS / EXECUTION_COUNT AS AVG_PHYSICAL_READS

FROM SYS.DM_EXEC_PROCEDURE_STATS

ORDER BY AVG_LOGICAL_READS DESC

Remarks

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

2. This DMV will capture the details of 3 objects types :

a. SQL_STORED_PROCEDURE

b. CLR_STORED_PROCEDURE

c. EXTENDED_STORED_PROCEDURE

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

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