Monthly Archives: June 2014

Performance tips – Check no of logical & Physical reads TIP #18

Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …

Determine current date with different option TIP #16

We can determine date with different option in SQL server Option 1:– with GETDATE() we can get current date & time Go  SELECT GETDATE()GO Option 2:- With sysDateTime()  , we can get current date & time (with nano seconds)GO   SELECT SYSDATETIME()GOOption 3:- with SYSDATETIMEOFFSET() , We can get current date & time with timezone offsetGo  …

How to determine when Last backup taken of a database n SQL Server? TIP #15

Sometime we require to know when we have taken last backup. So let me share here all the backup, log shipping related detail you can find in the MSDB database which is system database.SQL server maintain all the backup related detail in the MSDB database table As Show in the figure Now we can run …

How to update statistics ? TIP #14

b In last TIP tip#13, We learn how to find last updated statistics status. Now we know when it last updated so it may be require we need to update statistics for some of table. So to update statistics we need to write following command ( if we want to update  statistics of entire tables …

How to determine Last statistics update? TIP #13

To determine last statistics update we need to run following command. GoSELECT o.name, i.name AS [Index Name],         STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],        s.auto_created, s.no_recompute, s.user_createdFROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK)ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK)ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_idWHERE o.[type] …

How to find what is currently running on your SQL SERVER? TIP # 12

When we are keen to know that what is currently running on SQL SERVER for analysis purpose then with the help of below SQL statement we can easily find those statements, Stored procedure, status etc. GoSELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid), [User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = …