Query to findout JOBs information for a period of time

I hope this requirement is very common in DBA work life, as DBA needs to findout how the JOBs are performing currently and for a long time. I too faced the requirement. I have searched most of the pages from google and I couldn't found the solution. Finally I written the following query spending almost 3 hours.

Hopefully it will useful for all SQL Server DBAs.


Syntax :
Select b.Name JobName, a.step_name,a.run_date,run_time,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End run_time,
run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Duration], a.message
from sysjobs b inner join sysjobhistory a on a.job_id = b.job_id
where b.name like '%Reindex%' AND step_name not like 'Notification'
and step_name not like '%Job Outcome%' order by b.Name


For the above query you can add filter conditions as per your requirements. By getting the result of above query you can have an idea of how the jobs are involved and making the analysis you can increase the performance of the server.

Query to findout list of Objects count from all databases in SQL Server :

This is very useful query on DBA daily work environment. I have struggled to findout the information before. Finally I developed following query to get the information.

Select 'select ('''+name+''') Database_name,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''u'') Table_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''v'') View_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype in (''fn'', ''tn'')) func_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''p'') proc_Count,
(Select count(*) from '+name+'.dbo.sysobjects where xtype = ''tr'') Trig_Count union all'
from sysdatabases where dbid > 4


Instructions :

* you need to take the result script of above query and execute in other window. And you need to remove 'UNION ALL" from end statement.
* dbid > 4 = it will not show for sysdatabases



Result Query :

select ('TEST') Database_name, (Select count(*) from TEST.dbo.sysobjects where xtype = 'u') Table_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'v') View_Count, (Select count(*) from TEST.dbo.sysobjects where xtype in ('fn', 'tn')) func_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'p') proc_Count, (Select count(*) from TEST.dbo.sysobjects where xtype = 'tr') Trig_Count

Statistics in Synchronization and Asynchronization mode to gain the performance.

In SQL Server we have Statistics to improve the performance on tables. Following are some of the concepts about Statistics in Synchronization/Asynchronization mode to gain the performance from tables.

1. Statistics Asynchronization mode was introduced in SQL Server 2005. In earlier versions only synchronizations mode was available. But the default level is synchronization only.

2. Synchronization /Asynchronization options are at database level, not individual object level. There is single option we can enable or disable this for entire database. We can do this by using alter database statement.
Syntax : ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC OFF

3. To checkup this option on existing databases.
Syntax : Select name,is_auto_update_stats_async_on from sys.databases

1 = enabled
0 = disabled

When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set. When the setting is on, the query does not need to wait as the statistics update are handled by a background process. Mainly it is using to improve the performance on the tables.

So, prior to utilize the statistics aynchronous option at database level, the following process should do at object level.
1. Create statistics on indexed columns. It is suggestable that statistics must create on composite indexed columns.

2. All the created statistics must be updated regularly. When we update the statistics on regular basis, we can gain the performance from tables.

3. This options will work on tables which are having statistics and updating regularly will improve the performance compare to synchronization mode.