Maintenance

A simple way for consistency TIP #106

  Whenever we develop application of maintain application we define sets of rules or policies  like naming convention , data type, database & SQL SERVER properties (like which property should be unable or disable)  but the problem is to cross check or to enforce these properties is very tedious. To enforce the policies SQL SERVER …

Easy way to diagnose SQL Server “sp_Who2” TIP #84

  Why the SQL Server is running slow ? What are the processes running currently on SQL SERVER instance ? Many other like the above which might help us to understand our current SQL Server instance health can be answered by a simple command “sp_who2”. “sp_Who2”  is an Undocumented command. You can utilize this command …

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

  When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space . Now what you need to do to get free space from each drive ? Just create a simple job which run on daily basis which send you space …

Find last statistics updated date detail ?–Maintenance TIP #59

  Problem:- One of the pain point in any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow. One of the possible reason is your maintenance.   You don’t know when statistics last updated and take further step if those are not updated Solution:- Here we have simple …

Maintenance of fragmented Heap table–TIP #55

  Friends, In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so. Now our task is to run maintenance of this highly fragmented table. So you have to run following command “ALTER TABLE  TABLENAME REBUILD; For example  I run the Rebuild …

A hidden feature sp_MSforeachdb TIP #52

  As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB  by the name it …

Performance tips – How to determine last statistics update in table ? TIP #46

Hi, For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats. To determine this we need to run the following scripts SELECT OBJECT_NAME(s.object_id) AS [ObjectName]      ,s.name AS [StatisticName]      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]FROM sys.stats sINNER JOIN sys.objects …

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