Well, just because the database is mirrored and the drive is out of space, doesn’t mean there is nothing that can be done without impacting significantly the primary server – nor the mirror.

SQLCat has a nice write-up on what can be done.  I have used this method a few times, and felt that it needs to be brought up.  You can read the SQLCat article here.

In short (here are the steps from that article), you can do the following:

1. On the secondary server, Use ALTER DATABASE MODIFY FILE to move the log file.

ALTER DATABASE <db_name> MODIFY FILE (NAME = LOG_FILE, FILENAME = ‘new location’).

2. Stop the SQL Server Service for the instance which has the mirrored (secondary) database.
3. Move the log file to the new location specified in the Modify File script already run.
4. Start the SQL Server Service for the instance which has the mirrored (secondary) database.

Performing these steps can be just the trick needed to save the day.  Performing this move this way has saved me an outage on more than one occasion.  Also, this has saved me hours of work that could come along with having to break and rebuild the mirror.

If you have been hiding under a rock, you have probably not heard that SQL 2012 has launched.  With it, some really cool features are now available to use in our SQL database environments.

Some of these cool new features include some new DMVs, the AlwaysOn feature, and columnstore indexes.

But did you know that there are some new recommended best practices?

Today, I want to share with you a new best practice that involves table naming schemes.

With the release of SQL 2012, it is now advised that tables be named in such a manner that the name is obfuscated.  The reason for this is to slow down any would be attacker from quickly finding which table holds the most sensitive data.  Currently it is not too far fetched for an attacker to look in a database and figure out where the orders or customer confidential information may be placed.  After all, it is fairly common to name those tables in such a manner that the name describes the data.

Now though, the best practice is pushing in a new direction.  And no longer is it even a good idea to use legible words.  The drive is for full obfuscation of the table name.  In an effort to demonstrate, here is a quick script that will create several tables that are obfuscated in name.

Code block

IF not exists (SELECT 1 FROM sys.databases WHERE name = 'Fun')
BEGIN
	CREATE DATABASE Fun
END;
GO
 
USE Fun;
Go
 
SET NOCOUNT ON;
GO
 
DECLARE @rocket CHAR(100)
DECLARE @boost TINYINT
DECLARE @tiers TINYINT
DECLARE @maxtiers TINYINT
DECLARE @SQL	VARCHAR(MAX)
DECLARE @overall	TABLE	(rocket VARCHAR(MAX));
DECLARE @somevar	VARCHAR(MAX)
		,@someint	INT = 0
 
SELECT @rocket = ' ', @tiers = 1, @boost = 1, @maxtiers = 4
PRINT @rocket
 
INSERT INTO @overall (rocket)
	VALUES (@rocket);
 
WHILE @tiers < @maxtiers
BEGIN
	SELECT @boost = POWER(@tiers,2)
	WHILE @boost < 12*@tiers
	BEGIN
		SET @rocket =  STUFF(@rocket, (DATALENGTH(@rocket)/2)-(@boost/2), @boost,REPLICATE('*', @boost))
			UPDATE @overall
				SET rocket = rocket +CHAR(10) + @rocket
			SET @SQL = 'Create Table ['+ @rocket + '_'+ CONVERT(VARCHAR,@someint) +'] (someint int);'                 PRINT @SQL
		SET @boost = @boost+2
		SET @someint = @someint + 1
 
	END
 
	SET @tiers = @tiers+1
END
 
SELECT @somevar =  rocket
	FROM @overall
 
PRINT 'The following Tables have been created:' +CHAR(10)+CHAR(13)
PRINT @somevar

I like this new standard.  Now, I will just design images into my tables that describe the database in whole.  As you can see, this particular database would be useful for maybe a space case or maybe a hobbyist with a high enthusiasm for rockets.  Or maybe it just works really well for any demo involved with the SQL 2012 Launch.  Other than a neat little picture, the tables are completely obfuscated.

Conclusion

Keep best practices in mind whenever designing a database.  Maintaining best practices can help protect the data you were hired to manage and protect.  In addition to that, make sure you forget every other word of this post.  It is complete and utter hogwash.  Happy April Fools.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

Code block

SELECT TableName = OBJECT_NAME(s.OBJECT_ID),IndexName = i.name, i.index_id
        ,TotalUserWrites = user_updates
        ,TotalUserReads = user_seeks + user_scans + user_lookups
        ,[DIFFERENCE] = user_updates - (user_seeks + user_scans + user_lookups)
		,TotalSystemReads = s.system_seeks + s.system_scans + s.system_lookups
		,TotalSystemWrites = s.system_updates
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
	INNER JOIN sys.indexes AS i WITH (NOLOCK)
		ON s.[OBJECT_ID] = i.[OBJECT_ID]
		AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
	AND s.database_id = DB_ID()
	AND user_updates > (user_seeks + user_scans + user_lookups)
	And (user_seeks + user_scans + user_lookups + s.system_seeks + s.system_scans + s.system_lookups) = 0
	AND i.index_id > 1
ORDER BY [DIFFERENCE] DESC, TotalUserWrites DESC, TotalUserReads ASC OPTION (RECOMPILE);

In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.

Requirements

Given a set of characters, you must be able to sort according to the following:

1. !
2. “
3. $
4. ?
5. @
6. ^
7. {
8. >
9. ASCII values

Setup

To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.

Code block

CREATE TABLE #Chars
(
	MyChar CHAR(1)
)
 
INSERT INTO #Chars (MyChar)
VALUES
  ('!'), ('"'), ('$'), ('?'), ('@'), ('^')
, ('{'), ('>'), ('1'), ('2'), ('3'), ('4')
, ('5'), ('6'), ('7'), ('8'), ('9'), ('A')
, ('B'), ('C'), ('D')

Solution

Code block

SELECT *
	FROM #Chars
	ORDER BY MyChar COLLATE SQL_Latin1_General_Cp1251_CS_AS

Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

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

Code block

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%'

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.

Code block

/* 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.

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.

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.

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.

Code block

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.

Code block

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.

Code block

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).

Code block

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.

Today, I am following up on a topic I mentioned in an earlier post.  In case you haven’t read the post on finding your linked servers, you can read it here.

It is in that post that I spoke of altering a script I had just written for another client for another requirement.  The requirements were very similar in nature.

The client needed to discover which, if any, stored procedures in the database referenced any of the thousands of views that had been created.  (Spoiler – not a single view was being used by any stored procedure.)

My options at the time were to either write something that could do the work for me (rapidly) or manually investigate every stored procedure (there were thousands of those too).  I opted for the faster approach.

I already had a script handy to search for particular words or key phrases throughout all of the procs.  So, all I needed to do was work out the routine to search for all of the views.  The only approach I felt would work across the board was to write a cursor to do it.  For a job such as this, I think the tool is still appropriate.

Since you have already been exposed to a variation of the script, here is the version that works great for searching for the view usage in stored procs.  This script is simpler in nature than the one I shared in the Linked Server article – only because I do not query that SQL Agent jiobs for ad-hoc queries that use any views.

Code block

DECLARE @VName VARCHAR(256)
DECLARE FindView CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
SELECT name
	FROM sys.objects
	WHERE type = 'v'
		And is_ms_shipped = 0
 
OPEN FindView;
FETCH NEXT FROM FindView INTO @VName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT OBJECT_NAME(OBJECT_ID)
		FROM sys.sql_modules
		WHERE Definition LIKE '%'+@VName +'%'
		AND OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1 ;
 
	FETCH NEXT FROM findview INTO @VName;
END
 
CLOSE FindView
DEALLOCATE FindView

Put this in your toolbox if you like.  It did take it quite a while to run (20 or so minutes) when I ran it against a database with thousands of views and thousands of procs.  Just be warned .