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