Sql agent maintenance job run duration query

Run this across all registered servers for dates your server runs optimizations on.
You will get total time it took for the jobs to run in the two weeks. Drop the values in excel and plot graph if required. Courtsey Ken Simmons, he ran this in our production environment to show how reindexing only fragmented index took a lot of time out of optimization schedule. How he changed the optimizaiton plan is a different blog post.

select
STUFF(STUFF(RIGHT('00000000' + CAST(run_duration as varchar(10)) ,6),3,0,':'),6,0,':') AS
run_duration,
run_date
from msdb.dbo.sysjobs A Join msdb.dbo.sysjobhistory B
ON A.job_id = B.job_id
WHERE A.name like '%opt%' and B.step_id = 2 and (run_date = 20110212 or run_date = 20110205)

SSIS error running executable/batch file from network

So, I had this issue for sometime now, where an SSIS scheduled from a sql agent job, would hang at a particular step and not run at all. We figured it was due to the exe and bat file in SSIS which needed a user prompt confirmation to go to next step. I had looked online if there was an option to suppress it inside of SSIS but dint find one.

Finally my manager pointed me to this solution:
"The publisher could not be verified" prompt running executable from network
http://www.annoyances.org/exec/forum/winvista/t1151260847

Problem:
Running XP SP2 or higher, you try to run an executable located on another machine
on your network. Your accosted with a prompt: "The publisher could not be verified".
You are forced to confirm that you wish to run this program... every time you run
it.

Solution:
Run gpedit.msc

Go to User Configuration >> Administrative Templates >> Windows Components >> Attachment
Manager

Add "*.exe;*.bat;" to the "Inclusion list for moderate risk file types" setting.

which I configured on the server that runs the package (as above)
and that fixed it! No more prompts.

Sql agent Jobs (owner,lastrun,runstatus)

select * from msdb.dbo.sysjobs a
inner join sys.server_principals b
on a.owner_sid=b.sid where b.name like '%%'

SELECT j.[name],
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status,b.name as jobowner
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id AND jh.step_id = 0
inner join msdb.dbo.syscategories sc
on j.category_id = sc.category_id
inner join sys.server_principals b
on j.owner_sid=b.sid
--WHERE sc.[name] like '%DTS%'
GROUP BY j.[name], jh.run_status,b.name

Using registered servers tab in ssms

Register all sql servers under registeres servers in ssms,either by adding manually or using a regsrvr file

1) Right click local databases and import-->

2)Select the exported file which has a list of already existing servers exported from someone else's. It is in .regsrvr format.

Once all servers are registered a query can be run across all of them at one.

Very useful to see:
Exec sp_who2
Exec sp_whoisactive
server configurations etc

privilages to non-admin to connect to SSIS

To configure rights for remote users on Windows Server 2003 or Windows XP

1. Very important

If the user/group is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2. Give user/group remote Launch and activate permissions, so that they can connect from there machines to the SSIS server.

Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5.
Right-click on MsDtsServer and select Properties.

6.
In the MsDtsServer Properties dialog box, select the Security tab.

7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9.
Click OK to close the dialog box.

10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

11.
Close the MMC snap-in.

12.
Restart the Integration Services service.

13. If you cannot add a package or migrate it to server. Try adding user to msdb database and give read and write permissions.

Some other things to keep in mind are replace . by servername\instance in the file MsDtsSrvr.ini.xml on that server

Query optimization articles reference

http://www.serverwatch.com/tutorials/article.php/2175621/How-to-Optimize-Queries-Theory-an-Practice.htm

http://www.sql-server-performance.com/articles/per/query_tuning_p1.aspx

Codename "Denali"

Always on high availability feature
Columnstore index
File storage and creating file system and searching thru file text like usual file
Better compression
Parallel data warehouse PDW solution > 30TB
Faster queries, better query time in power pivot