Home » Posts tagged 'SQL'

Tag Archives: SQL

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

DMV-5 : Queries runing are adhoc or proc , single or multi use ?……..sys.dm_exec_cached_plans


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

Returns a row for each query plan that is cached by SQL Server for faster query execu­tion. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

sys.dm_exec_cached_plans provide execution related details like no. of queries comes under adhoc or proc section. It help is understanding the query type & performance improvisation by looking into heavily running adhoc queries.

Query 1 : Memory used by cache plan of each database

SELECT

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

SUM(CASE WHEN CP.USECOUNTS <= 1 THEN 1 ELSE 0 END) AS SINGLE_USE_COUNT,

SUM(CASE WHEN CP.USECOUNTS > 1 THEN 1 ELSE 0 END) AS MULTI_USE_COUNT,

AVG(CASE WHEN CP.USECOUNTS > 1 THEN CP.USECOUNTS ELSE NULL END) AS MULTI_USE_AVG_USE_COUNT,

(SUM(CASE WHEN CP.USECOUNTS <= 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS SINGLE_USE_SIZE_IN_MBYTES,

(SUM(CASE WHEN CP.USECOUNTS > 1 THEN CP.SIZE_IN_BYTES ELSE 0 END) / (1024 * 1024)) AS MULTI_USE_SIZE_IN_MBYTES,

(SUM(CP.SIZE_IN_BYTES) / (1024 * 1024)) AS TOTAL_SIZE_IN_MBYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

OUTER APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

GROUP BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

ORDER BY

CP.CACHEOBJTYPE,

CP.OBJTYPE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END

Query 2 : Find single-use, ad-hoc queries

SELECT ST.[TEXT] ,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CP.SIZE_IN_BYTES

FROM

SYS.DM_EXEC_CACHED_PLANS AS CP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

WHERE CP.CACHEOBJTYPE = ‘COMPILED PLAN’

AND CP.OBJTYPE = ‘ADHOC’

AND CP.USECOUNTS = 1

ORDER BY CP.SIZE_IN_BYTES DESC

Remarks

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

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

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

DMV-4 : Find top most expensive cached queries ?……..sys.dm_exec_query_stats


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

Returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

sys.dm_exec_query_stats provides a wealth of performance statistics for cached query plans. It’s a very useful DMV to get cached query details for performance & server load analysis.

Query 1 : Top 10 total CPU consuming queries

SELECT TOP 10

QT.TEXT AS STATEMENT_TEXT,

QP.QUERY_PLAN,

QS.TOTAL_WORKER_TIME AS CPU_TIME

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT

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

ORDER BY TOTAL_WORKER_TIME DESC

Query 2 : Top 10 average CPU consuming queries

SELECT TOP 10

TOTAL_WORKER_TIME ,

EXECUTION_COUNT ,

TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] ,

QT.TEXT AS QUERYTEXT

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT

ORDER BY QS.TOTAL_WORKER_TIME DESC ;

Query 3 : Top 10 I/O intensive queries

SELECT TOP 10

TOTAL_LOGICAL_READS,

TOTAL_LOGICAL_WRITES,

EXECUTION_COUNT,

TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

QT.TEXT AS QUERY_TEXT,

DB_NAME(QT.DBID) AS DATABASE_NAME,

QT.OBJECTID AS OBJECT_ID

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0

ORDER BY [IO_TOTAL] DESC

Query 4 : Execution count of each query

SELECT QS.EXECUTION_COUNT,

QT.TEXT AS QUERY_TEXT,

QT.DBID,

DBNAME= DB_NAME (QT.DBID),

QT.OBJECTID,

QS.TOTAL_ROWS,

QS.LAST_ROWS,

QS.MIN_ROWS,

QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

ORDER BY QS.EXECUTION_COUNT DESC

Remarks

1. Statistics in the view are updated when a query is completed.

2. 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-3 : What is currently going on ?……..sys.dm_exec_requests


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

Returns information about each request that is executing within SQL Server.

sys.dm_exec_requests DMV is used to get details of currently running sessions. This DMV help us is getting details like used database, transaction isolation level, open transaction count, status, blocked session, percentage of task completed etc.

Query 1 : Complete details of currently running queries

SELECT

R.SESSION_ID,

R.REQUEST_ID AS SESSION_REQUEST_ID,

R.STATUS,

S.HOST_NAME,

C.CLIENT_NET_ADDRESS,

CASE WHEN S.LOGIN_NAME = S.ORIGINAL_LOGIN_NAME THEN S.LOGIN_NAME ELSE S.LOGIN_NAME + ‘ (‘ + S.ORIGINAL_LOGIN_NAME + ‘)’ END AS LOGIN_NAME,

S.PROGRAM_NAME,

DB_NAME(R.DATABASE_ID) AS DATABASE_NAME,

R.COMMAND,

ST.TEXT AS QUERY_TEXT,

QP.QUERY_PLAN AS XML_QUERY_PLAN,

R.WAIT_TYPE AS CURRENT_WAIT_TYPE,

R.LAST_WAIT_TYPE,

R.BLOCKING_SESSION_ID,

R.ROW_COUNT,

R.GRANTED_QUERY_MEMORY,

R.OPEN_TRANSACTION_COUNT,

R.USER_ID,

R.PERCENT_COMPLETE,

CASE R.TRANSACTION_ISOLATION_LEVEL

WHEN 0 THEN ‘UNSPECIFIED’

WHEN 1 THEN ‘READUNCOMITTED’

WHEN 2 THEN ‘READCOMMITTED’

WHEN 3 THEN ‘REPEATABLE’

WHEN 4 THEN ‘SERIALIZABLE’

WHEN 5 THEN ‘SNAPSHOT’

ELSE CAST(R.TRANSACTION_ISOLATION_LEVEL AS VARCHAR(32))

END AS TRANSACTION_ISOLATION_LEVEL_NAME

FROM

SYS.DM_EXEC_REQUESTS R

LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = R.SESSION_ID

LEFT OUTER JOIN SYS.DM_EXEC_CONNECTIONS C ON C.CONNECTION_ID = R.CONNECTION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) ST

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(R.PLAN_HANDLE) QP

WHERE

R.STATUS NOT IN (‘BACKGROUND’,’SLEEPING’)

Query 2 : How much task is completed in percentage? It’s my one of the favorite query while doing backup, restore check DB or index rebuild.

SELECT

A.SESSION_ID,

DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,

A.START_TIME,A.COMMAND,

A.CPU_TIME,

A.PERCENT_COMPLETE,

A.ESTIMATED_COMPLETION_TIME,

B.TEXT

FROM SYS.DM_EXEC_REQUESTS A

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B

WHERE SESSION_ID > 50

Remarks

1. All Possible status of sessions :

· Background

· Running

· Runnable

· Sleeping

· Suspended

2. Percentage of work completed can be viewed for the following commands:

· ALTER INDEX REORGANIZE

· AUTO_SHRINK option with ALTER DATABASE

· BACKUP DATABASE

· DBCC CHECKDB

· DBCC CHECKFILEGROUP

· DBCC CHECKTABLE

· DBCC INDEXDEFRAG

· DBCC SHRINKDATABASE

· DBCC SHRINKFILE

· RECOVERY

· RESTORE DATABASE,

· ROLLBACK

· TDE ENCRYPTION

3. Exceptions for Blocking Session ID :

· -2 = The blocking resource is owned by an orphaned distributed transaction.

· -3 = The blocking resource is owned by a deferred recovery transaction.

· -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions

4. All possible Transaction isolation level of the session, on basis on integer values :

· 0 = Unspecified

· 1 = ReadUncomitted

· 2 = ReadCommitted

· 3 = Repeatable

· 4 = Serializable

· 5 = Snapshot

5. Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

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

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

Cumulative Update – 10 for SQL Server 2008 Service Pack 3 Is Now Available !


The 10th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 10 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

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

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

KB Article For CU10 of SQL Server 2008 SP3

· CU#10 KB Article: http://support.microsoft.com/kb/2814783

Previous Cumulative Update KB Articles of SQL Server 2008 SP3:

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

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

Follow

Get every new post delivered to your Inbox.

Join 216 other followers