Agent Jobs Using SSIS

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: No Comments
Published on: March 15, 2012

This is a short script to help the DBA with documentation purposes.  This would come in handy especially in those cases where you are consulting or you have taken on a new job.

Suppose you want/need to find out what SQL Agent jobs are running SSIS packages.  This script will help to quickly identify those jobs.

SELECT DISTINCT j.name
	FROM msdb.dbo.sysjobs j
		INNER JOIN msdb.dbo.sysjobsteps js
			ON j.job_id = js.job_id
	WHERE js.subsystem = 'SSIS'
		OR js.command LIKE '%DTEXEC.exe%'

Table Size and Missing FK Indexes

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 14, 2012

I am bringing an oldie back with another twist.  I recently ran into the need to correlate information between a couple of different queries that I like to use.  The two scripts returned different pieces of data about tables in a database.  This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size.  The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database.  We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'SAMPLED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		GROUP BY a.OBJECT_ID
	), TotalUnused AS (
				SELECT SUM(FreeDataSpace) AS UnusedSpace
					FROM SummaryInfo
		)
	SELECT TableName,ChildTableColumn AS MissingFKIndexColumn,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
			,DataUsedSize = DatabaseSize - TU.UnusedSpace
			,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
		FROM SummaryInfo SI
			CROSS APPLY TotalUnused TU
			Cross Apply (SELECT
								OBJECT_NAME(FK.parent_object_id) AS FKtable
								,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable
								,OBJECT_NAME(FK.constraint_object_id) AS FKName
								,C.name ChildTableColumn
							FROM sys.foreign_key_columns FK
								INNER JOIN sys.columns C
									ON FK.parent_object_id = C.OBJECT_ID
									AND FK.parent_column_id = C.column_id
								INNER JOIN sys.objects O
									ON FK.parent_object_id = o.OBJECT_ID
								LEFT OUTER JOIN sys.index_columns ic
									ON FK.parent_object_id = ic.OBJECT_ID
									AND FK.parent_column_id = ic.column_id
							WHERE ic.OBJECT_ID IS NULL) FK
		WHERE Fk.fktable = SI.TableName
		ORDER BY PercentofDB DESC
END

The change is not overly much.  I added a subquery via a cross apply to get the missing foreign key info.  Then I return the pertinent columns back to the Select query.

T-SQL Tuesday #028 – Jack of All Trades, Master of None?

Comments: 2 Comments
Published on: March 13, 2012

Another month and another opportunity to write about an interesting topic.  This month hosting TSQL Tuesday is Argenis Fernandez (Blog | Twitter).

This month, Argenis has invited us to talk about demons from our past.  Ok, not necessarily demons but at least share why you might be a Jack of All Trades or a Master of something or nothing.

Thinking about the topic, I thought of some very good stories.

Jack of All Trades

Back in the day, I worked in a one-man IT shop.  On any given day, my duties involved configuring SOHO routers and firewalls as well as higher end Cisco equipment.  I was also responsible for Active Directory, pc maintenance,printer repair, Exchange, domain registrations and all things SQL.

My least favorite duty was that of Janitorial Engineer.  It was amongst my duties to ensure the restrooms were stocked and that the toilets were free-flowing.  I can’t necessarily say that this skill helped advance my career.  I can’t say that it was even helpful at home.

I can say that this duty did help me make the decision to specialize more in SQL Server – though I was already headed in that direction.

Master, erm…

Like Argenis said in the TSQL Tuesday announcement, I don’t much consider myself an expert or master of anything.  I do think I am rather proficient and I do recognize many shortcomings within the vast technology, we love, called SQL Server.

I aligned myself with this technology because of the constant challenge and opportunity to learn.  I enjoy working with SQL Server.  I still do not find as much pleasure in plumbing as I do in SQL Server.

What is a Favicon?

Categories: Blogging, News, Professional, SSC
Comments: No Comments
Published on: March 8, 2012

If I were to show you the tabs in the following pic without a description, would you recognize the websites to which they belong?

I would dare say that you would likely recognize them.  Not due to the labels on the tabs, but quite possibly due to the icon on the tab.

Sometimes these icons are the logo for the respective site.  Sometimes, it is just something that might be fun.  In either case, it is something that helps brand the site in a fashion suitable to you.  These little icons are called “favicons” and they are pretty easy to put in place.

If you haven’t considered doing it, I think it would be worth the effort to create one.  It is a means to brand your site.  Here is a quick tutorial on how to do it.

Tutorial

I used a free website tool to create my favicon.  You can find it here.  The tool is appropriately called favicongenerator.

 Prior to being asked how to create one, I had no clue what those little pics were called.  Nor did I know how easy it was to create one.  It took me a few minutes searching the internet to figure out what it was called (I think I started by searching for “tab icon internet”).  Once I learned that it was called a favicon, it was easy to find instructions on how to create it.  Now it should be just a bit easier.

Physical or Virtual

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 2 Comments
Published on: March 7, 2012

Today I get to share something that has been on my someday list for quite some time.  I have planned on getting a solution that involves TSQL to help determine if you are running SQL Server on a physical machine or if it happens to be a virtual machine.  Now, I am prepared to share two means that involve using TSQL to achieve that goal.

I get to cover the spectrum with these two solutions.  At one end, we have something that is relatively simple.  It only works in SQL 2008 R2 and above though.  At the other end of the spectrum, we get to use something that involves more of a sledgehammer.

Covering these two extremes is useful.  As I said, the simple solution at the simple end of the spectrum is not going to work if you are running SQL Server 2008 or older.  So, if you are running SQL 2005, for example, you would need something a bit less delicate.  I will leave it to you to determine if it is worth it to use the “sledgehammer” approach.

Sledgehammer

I’m calling this the sledgehammer approach because it is not a 100% TSQL solution.  Some may not like the idea due to the use of xp_cmdshell – making the sledgehammer more like a bull in a china shop.

Up front, this solution utilizes tools that are readily available.  Those tools are PoSH, WMI and TSQL.

Let’s first look at the WMI.  I knew I could find the information I wanted if I could query WMI.  All that was needed was a means to get to the Win32_ComputerSystem class.  From there I could get the information for manufacturer and model for the machine.  Virtual machines tend to have a manufacturer such as the the following “VMWare”, “innotek”, and “Microsoft Corporation” – to list some of the more popular options.

Next, we can get to the WMI very easily via PoSH or vbscript.  I was having a devil of a time trying to figure out a sane method of doing it via TSQL only.  That is fine, because I was able to utilize PoSH quite nicely in this case.  After, having found a reference for what I wanted to do from here, I was able to create the script that I needed.  Here is that script.

Param ([string]$HostName="localhost",$Option=1)
 
function ExecQry {
    param ([string]$CmdStr)
         $cmdStr
    }
 
if ($Option=1){
    $x = ExecQry "gwmi Win32_ComputerSystem -computer `$hostname" | iex
    $x | fl manufacturer,model
 
}

Nothing too terribly fancy there.  I am only querying for the two attributes that I really want to accomplish my goal.  Those attributes being: manufacturer and model.

Next comes the difficult part.  Running all of this from within TSQL and capturing useful results.  If you execute that PoSH script, you will notice that the presentation of the results is really lacking.  First though I needed to get the script to execute from within SSMS.

I started out miserably with getting that to run.  The script just kept hanging and would never even cancel out.  This is what I started out with in trying to get it to run.

xp_cmdshell 'powershell -noexit "& ""C:\Admin\PoSH Scripts\GetMachineInfo2.ps1"""'

If I extracted the powershell command and ran it from a command prompt, it would run flawlessly.  From within SSMS – crash and burn every time.  Then, I decided to try removing the -noexit and see if I could get different results.  Voila – fantastic results.  I now had the basis for getting this running in SSMS.

In addition to the use of xp_cmdshell, I felt it prudent to use a string splitter to help tidy up the presentation.  The string splitter I like to use (delimited split function) can be found here.  I also felt it necessary to use Pivot – again to help tidy up the results in the presentation.

I know, you’re itching to see the script now, so here it is.

DECLARE @processing TABLE (ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1),Results VARCHAR(256))
DECLARE @PartTwo TABLE (ID INT, subID INT, Somevalue NVARCHAR(100))
 
DECLARE @PathtoPS1			VARCHAR(256)
		,@CommandtoExec		VARCHAR(512)
 
SET @PathtoPS1 = 'your file system path\GetMachineInfo2.ps1'
SET @CommandtoExec = 'powershell "& ""' + @PathtoPS1 +'"""'
;
 
INSERT INTO @processing(Results)
	EXEC xp_cmdshell @CommandtoExec
 
;
 
INSERT INTO @PartTwo (ID,subID,Somevalue)
	SELECT pr.ID,ss.ItemNumber,LTRIM(RTRIM(ss.Item)) AS Somevalue
		FROM @processing pr
		CROSS APPLY AdminDB_Test.dbo.stringsplitter(pr.Results,':') ss
 
;
SELECT Manufacturer,Model
		,CASE
			WHEN Manufacturer like '%Vmware%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%innotek GmbH%'
				THEN 'ISVIRTUAL'
			WHEN Manufacturer like '%Microsoft Corporation%' AND Model like '%Virtual%'
				THEN 'ISVIRTUAL'
			ELSE 'PhysicalMachine'
			END AS VirtualorPhysical
	FROM (SELECT pt.SomeValue AS Property,p2.somevalue AS PropertyVal
			FROM @PartTwo pt
				INNER Join @PartTwo p2
					ON pt.id = p2.id
					And pt.subid <> p2.subid
			WHERE pt.somevalue in ('manufacturer','model')
			) B
	PIVOT (MIN(PropertyVal) FOR Property IN (manufacturer,model)) AS P

Unless you have xp_cmdshell disabled, the only change you will need to make is for the @PathtoPS1 variable.  Save the PoSH script on your file system with the name GetMachineInfo2.ps1, and you will be all set.

Elegance

Now that you have seen the hard way of doing it, here is what we can do in SQL Server 2008 R2 (must have SP1 applied at a minimum – thanks to Nic Cain for that info).

SELECT @@SERVERNAME AS SRVName,virtual_machine_type
	FROM sys.dm_os_sys_info

The virtual_machine_type attribute is a new addition to this DMV as of SQL 2008 R2.  There are three possible values: 0,1, or 2.  The value of 0 means that the machine is physical.  Any other value means that it is a virtual machine.  You can read more about that from MSDN.

There you have it.  Two methods within SSMS that you can extrapolate where a Server is physical or virtual.

page 1 of 56»
Calendar
March 2012
M T W T F S S
« Feb    
 1234
567891011
12131415161718
19202122232425
262728293031  
Follow me on Google+
Jason Brimhall

In 266 people's circles

Add to circlesi
Content
Categories

Categories

Now Reading

Now Reading

Planned books:

Current books:

  • ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running

    ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer

  • Advanced Marathoning ? 2nd Edition

    Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas

  • SQL Server MVP Deep Dives

    SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg

  • A World Without Heroes (Beyonders)

    A World Without Heroes (Beyonders) by Brandon Mull

Recent books:

View full Library

SQLHelp

SQLHelp


Welcome , today is Thursday, March 15, 2012