How can I know when was my Stored Procedure last executed?
This information is stored in a DMV in #SQL Server. But this information goes as far as the last server restart. i.e, after a SQL Server restart, this information is cleared.
USE DBName GO SELECT O.name, PS.last_execution_time FROM sys.dm_exec_procedure_stats PS INNER JOIN sys.objects O ON O.[object_id] = PS.[object_id] GO
P.S. Please replace the DBName with the actual name of the Database.
For more information on the wait conditions firstly please refer this post
The below mentioned are basic explanations of each of the major wait types. Appearance of any of these wait types high up will certainly help direct your subsequent investigations.
CXPACKET
Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem, associated with one of the other high value wait types in the instance
SOS_SCHEDULER_YIELD
The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure
THREADPOOL
A task had to wait to have a worker bound to it, in order to execute. This could be a sign of worker thread starvation, requiring an increase in the number of CPUs in the server, to handle a highly concurrent workload, or it can be a sign of blocking, resulting in a large number of parallel tasks consuming the worker threads for long periods.
LCK_*
These wait types signify that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using the information in the sys.dm_db_index_operational_stats
PAGEIOLATCH_*, IO_COMPLETION, WRITELOG
These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server. PAGEIOLATCH_* waits are specifically associated with delays in being able to read or write data from the database files. WRITELOG waits are related to issues with writing to log files. These waits should be evaluated in conjunction with the virtual file statistics as well as Physical Disk performance counters, to determine if the problem is specific to a single database, file, or disk, or is instance wide.
PAGELATCH_*
Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.
LATCH_*
These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.
ASYNC_NETWORK_IO
This wait is often incorrectly attributed to a network bottleneck. In fact, the most common cause of this wait is a client application that is performing row-by-row processing of the data being streamed from SQL Server as a result set (client accepts one row, processes, accepts next row, and so on). Correcting this wait type generally requires changing the client-side code so that it reads the result set as fast as possible, and then performs processing.
Good article on Performance Tuning SQL Database Performance Tuning for Developers
Another good article for Developers MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools
Keeping a watch on drive space on all Servers that you handle is a very integral part of DBA work. Once SQL Server runs out of disk on the underlying host, then everything obviously comes to a crashing halt. While proactively sizing data and log file sizes is the best technique to use when it comes to database sizing, there are still plenty of situations where some databases might need to be allowed to auto grow in many cases. As such, the last thing you want is for databases to grow to such a point that they run out of disk.
Below is a script that is very handy to avoid such catastrophic situations. The below script has to be run in master database and a job has to be created to schedule running this procedure in whatever frequency that is acceptable to you.
This script checks for drive space that is below 5% and fires an alert to the specified email address if the drive space is below 5%.
USE [master] GO /****** Object: StoredProcedure [dbo].[up_diskspace] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- EXEC up_diskspace CREATE PROCEDURE [dbo].[up_diskspace] AS SET NOCOUNT ON DECLARE @emailBody NVARCHAR(MAX) , @emailSubject VARCHAR(50) , @emailDate VARCHAR(10) , @serverName VARCHAR(100) , @runStatusChar VARCHAR(20) , @OK_serverList NVARCHAR(MAX) , @serverOK VARCHAR(100) , @sendEmail INT , @jobRunDateChar CHAR(8) , @listStart TINYINT , @hr int , @fso int , @drive char(1) , @odrive int , @TotalSize varchar(20) , @MB bigint , @freeSpace VARCHAR(10); SET @MB = 1048576 SET @listStart = 1 SET @emailSubject = 'UAT - Low Drive Space Alert!' SET @serverName = @@serverName SET @sendEmail = 0 CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL, TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS=0 BEGIN EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive END CLOSE dcur DEALLOCATE dcur EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso SELECT drive, FreeSpace as 'Free(MB)', TotalSize as 'Total(MB)', CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' INTO #freeSpace FROM #drives ORDER BY drive --SELECT * FROM #freeSpace DELETE #freeSpace WHERE [Free(%)] > 5 SET @sendEmail = (SELECT count(*) FROM #freeSpace) IF @sendEmail > 0 BEGIN --GET DRIVES WITH LESS THAN 5% DISK FREE SPACE DECLARE l CURSOR FORWARD_ONLY FOR SELECT drive, [Free(MB)] FROM #freeSpace OPEN l -- The keyset is copied to tempdb FETCH l INTO @drive, @freeSpace WHILE (@@FETCH_STATUS = 0) BEGIN IF @listStart = 1 BEGIN SET @OK_serverList = @drive + ' (' + @freeSpace + ' MB)' SET @listStart = 0 END ELSE SET @OK_serverList = @OK_serverList + ', ' + @drive + ' (' + @freeSpace + ' MB)' FETCH l INTO @drive, @freeSpace END CLOSE l DEALLOCATE l -- RESET @listStart SET @listStart = 1 SET @OK_serverList = @OK_serverList + '.' -- PRINT LIST SET @emailBody = CHAR(13) + CHAR(13) + 'The following drive(s) on ' + @serverName + ' are below 5% free space: ' + ISNULL(@OK_serverList, '') --- EMAIL REPORT EXEC msdb.dbo.sp_send_dbmail @recipients = 'SANTHOSHVISWANATHAN@GMAIL.COM' , @body = @emailBody , @subject = @emailSubject , @importance = 'HIGH' END DROP TABLE #drives DROP TABLE #freeSpace RETURN
Please check the link for Mail alerts for new added record.
Today one of my colleagues asked me if it is possible to identify all stored procedures referring a particular table. So I thought about writing this post.
The answer is yes. It is possible. There are many ways to achieve the desired result. The query given below is one of the simplest ones.
To elaborate a bit on this, we use OBJECT_DEFINITION (Transact-SQL). What it does is it returns the Transact-SQL source text of the definition of a specified object.
--Quick way to check which all stored procedures uses a particular table SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Table_Name%'
As a person who manages SQL Databases in your organisation you would certainly have had many queries to tune. I would like to point out some basic things one should follow when we a developer asks us to tune a query. Collect these feedback before starting a performance tuning activity.
SQL DBA Blog 