Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

It’s a very simple DMV used to see all pending I/O requests & there description.

You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You should run this query multiple times to check if the same files or drive letters consistently coming up on the top. If this is the case that means you facing I/O bottlenecks for that file or drive letter.

Query 1 : Details of I/O pending requests against each DB file

SELECT

DB_NAME(MF.DATABASE_ID) AS [DATABASE],

MF.PHYSICAL_NAME,

IPIR.IO_TYPE,

SUM(IPIR.IO_PENDING) TOTAL_PENDING_IO,

SUM(IPIR.IO_PENDING_MS_TICKS) TOTAL_PENDING_MS_TICKS,

SUM(VFS.NUM_OF_READS) TOTAL_READS,

SUM(VFS.NUM_OF_WRITES) TOTAL_WRITES

FROM

SYS.DM_IO_PENDING_IO_REQUESTS AS IPIR

INNER JOIN

SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL) AS VFS

ON IPIR.IO_HANDLE = VFS.FILE_HANDLE

INNER JOIN

SYS.MASTER_FILES AS MF

ON VFS.DATABASE_ID = VFS.DATABASE_ID

AND VFS.FILE_ID = MF.FILE_ID

GROUP BY MF.DATABASE_ID, MF.PHYSICAL_NAME, IPIR.IO_TYPE

ORDER BY SUM(IPIR.IO_PENDING)

Remarks

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

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.

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.