How to open SQL Profiler from command prompt–TIP # 23

  To open SQL Profiler (right now I am talking about SQL Server 2012) you need to write Profiler.exe on run command as shown in below figure for earlier version you can use profiler90.exe. You can find various parameters option using Profiler.exe –? command When you run this command you will find below screen. Although …

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] …