SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

Using the default system_health Extended Event

The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml.

However deadlocks are not the only thing captured. An overview of what is inside system_health  can be found here:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

I going to single out errors (error_reported) and provide a query to parse the data. Note I have commented out the additional where filters, but have left them in to show how this can be done.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM (SELECT @target_data) [target_data]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1
--AND x.y.exist('.//data[@name="severity"]/value/text()[. = "20"]') = 1
--AND x.y.value('(@timestamp)[1]','DATETIME') = '2015-10-21 16:29:00.000'

The above query I have used a variable to store @target_data, this could obviously be removed and the queries combined. When I am writing these xml queries I tend to keep a static copy of target_data so as not too impact the instance more than needed.

DECLARE @target_data XML
SELECT
CAST([target_data] AS XML) [target_data] INTO #target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'
SELECT @target_data = [target_data] FROM #target_data

Finally for completeness, as well as the ring_buffer there is also another target for system_health and that is to file.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
SELECT 
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM
(
	SELECT 
	CAST([event_data] AS XML) as [target_data],*
	FROM sys.fn_xe_file_target_read_file('*system_health*.xel',NULL,NULL,NULL)
) e CROSS APPLY [target_data].nodes('/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1

Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

Msg 1205, Level 13, State 45, Line 13
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

Samsung Gear VR2

Samsung VR for S6 Edge Plus in the UK is here

***** https://shop.samsung.com/uk/samsung-gear-vr-lite-286321/ *****

Update 2 new link
http://www.samsung.com/uk/consumer/mobile-devices/wearables/gear/SM-R322NZWABTU

Original Moan below:

There may be a few of us geeks in the UK looking for the latest VR offering from Samsung, which appears to be pre-releasing everywhere but here.

vrfocus are running updates as and when they occur. Their post linking to Amazon UK looks to be what we are waiting for although annoyingly no product details. http://www.amazon.co.uk/gp/product/B017WA5VI6

From what I can gather it the newer VR units are referenced as SM-R322NZWAxxx and then a 3 character country code. (The older headsets are SM-32x). Therefore the reference SM-R322NZWABTU looks good for the UK based on BTU being used before. I am not holding my breath for a release this year tho. Amazon quoting 1-3 Months… ho ho ho. Not exactly the best product launch from Samsung…

The new headset is “Compatible with Galaxy Note 5, S6 edge+, S6 and S6 edge.”

Note that the older headsets are prefixed as follows:
Gear VR Innovator Edition for S6 SM-R321.
Gear VR Innovator Edition for Samsung’s Galaxy Note 4 SM-R320.

Links to vrfocus articles:
http://vrfocus.com/archives/25783/amazon-launch-uk-gear-vr-pre-orders/
http://vrfocus.com/archives/25467/gear-vr-now-listed-australia-new-zealand-sweden-korea/

Links to Samsung:

Sweden
http://www.samsung.com/se/consumer/mobile-devices/wearables/gear/SM-R322NZWANEE

Germany
http://www.samsung.com/de/consumer/mobile-devices/wearables/gear/SM-R322NZWADBT

New Zealand
http://www.samsung.com/nz/consumer/mobile-devices/wearables/gear/SM-R322NZWAXNZ

Australia
http://www.samsung.com/au/consumer/mobile-phone/wearables/gear/SM-R322NZWAXSA

South Korea
http://www.samsung.com/sec/consumer/mobile-tablet/gear/gear-series/SM-R322NZWAKOO

US
http://www.samsung.com/us/mobile/wearable-tech/SM-R322NZWAXAR

Bits, Bytes and the Binary Decimal confusion

Quick Binary Bit background

In computing we use a unit of information with two values 0 or 1, which is called a bit. Now with either a 0 or 1, how do we go about representing the number 6 or 9? We could write 111111 and 111111111111, but you will see that this will require a lot of space to show larger number. If we use Binary (base-2), then the position of the 0/1 will relate to a corresponding value which in binary are 1,2,4,8,16,32,64,128 etc. Note that this is 2 to the power n where n is the position of the value starting from 0. The positions would be read from right to left, so Pos 7, Pos 6, Pos 5,Pos 4, Pos 3, Pos 2, Pos 1 and finally Pos 0. Perhaps therefore the decimal number values would be more easily understood if written as 128,64,32,16,8,4,2,1.

2 to Power 0 = 1
2 to Power 1 = 2
2 to Power 2 = 4 (2×2)
2 to Power 3 = 8 (2x2x2)
2 to Power 4 = 16(2x2x2x2)
2 to Power 5 = 32(2x2x2x2x2)
2 to Power 6 = 64(2x2x2x2x2x2)
2 to Power 7 = 128(2x2x2x2x2x2x2)

The number 6 is therefore represented by setting 1 in position 1, and position 2. Which would look like this 0 0 0 0 0 1 1 0. (Position zero is set to 0 so even number)
The number 9 is therefore represented by setting 1 in position 0, and position 3. Which would look like this 0 0 0 0 1 0 0 1. (Position zero is set to 1 so odd number)

Time for a quick Byte?

Now I have not chosen to show 8 bit positions randomly, as this group of 8 is referred to as a Byte. Where this gets complicated is that from this point on there are two ways to group the data units, which are either binary (base-2) or decimal (base-10).

As numbers get larger we assign a prefix denoting that it is to be multiplied by X to get the actual value. In decimal (base-10) a prefix is added for multiplies of 1000, in binary (base-2) however this is multiples of 1024.

So how much is a kilo byte? Well do we mean a binary or decimal?

1024 bytes = 1 binary kilo byte
1000 bytes = 1 decimal kilo byte

Confused? The easiest way to relieve this confusion is to use an appropriate prefix which denotes this difference. A multiple of 1000 in decimal is referred as k or kilo, where as in binary a multiple of 1024 is stated as Ki or kibi.

A more comprehensive list can be found online with a quick search but below is the first few. https://en.wikipedia.org/wiki/Binary_prefix

k kilo Ki kibi
M mega Mi mebi
G giga Gi gibi
T tera Ti tebi

So RAM in a computer is referred to as 4 GB or 4 Gigabytes, it would be more accurately described as 4 GiB or 4 GibiBytes as it is denoting a binary value. A 1000 BASE Ethernet card refers to decimal value, therefore the card is 1 GiBits. Storage is often labelled by hardware vendors as decimal multiples.

Useful Specifications

Here is a useful link for data transfers: https://en.wikipedia.org/wiki/Data_rate_units

USB https://en.wikipedia.org/wiki/USB
Mode Gross data rate Introduced in
Low Speed 1.5 Mbit/s USB 1.0
Full Speed 12 Mbit/s USB 1.0
High Speed 480 Mbit/s USB 2.0 (60 MB/s)
SuperSpeed 5 Gbit/s USB 3.0 (625 MB/s)
SuperSpeed+ 10 Gbit/s

PCI Express https://en.wikipedia.org/wiki/PCI_Express

v1.x: 250 MB/s (2.5 GT/s)
v2.x: 500 MB/s (5 GT/s)
v3.0: 985 MB/s (8 GT/s)
v4.0: 1969 MB/s (16 GT/s)

SATA Decimal Prefix Units https://en.wikipedia.org/wiki/Serial_ATA
SATA 1.0 – 1500 Mbit/s – 150 MB/s
SATA 2.0 – 3000 Mbit/s – 300 MB/s
SATA 3.0 – 6000 Mbit/s – 600 MB/s
SATA 3.2 – 16 Gbit/s – 1969 MB/s

Appendix: Throwing new hardware at an old server?

Is it a good idea to fully populate an R710 with 8 x expensive consumer grade SATA SSD? (This is an 11 Generation Dell PowerEdge Server from 2009.)

Well the server itself supports SATA 2.0 so that means each connected drive could potential push 300 MB/s, and most SSD will do more than that. So bottleneck one is that a modern SSD will want to go faster than SATA 2.0. So buy cheap SSD, as max we are going to start with is 300 * 8 = 2400 MB/s.

Next thing we hit is the controller card. A PCI Express x8 card with only 4 lanes wired Gen 1. 250 MB/s * 4 = 1000 MB/s.

 

Mmm, it’s really not going to take much to saturate this server using modern storage. Good idea to add SSD? No, unless you have them laying about.

Dell Documentation for the R710:

http://www.dell.com/downloads/global/products/pedge/en/server-poweredge-r710-tech-guidebook.pdf

11.1 Overview
The PowerEdge R710 has two PCI Express risers: Riser 1 and Riser 2. Each riser connects to the planar through a x16 PCI Express connector.
* Riser 1 consists of two x4 slots and a third x4 slot dedicated for internal SAS storage through the PERC 6i or SAS 6/iR.
* The default Riser 2 consists of two x8 PCI Express connectors.
* There is also an optional x16 Riser 2 that supports one x16 PCI Express card.

11.2 PCI Express Risers
The two PCI Express risers provide up to four expansion slots and one slot dedicated for the
integrated storage controller card. The slots meet the following requirements:
* Two x8 and two x4 PCI Express Gen2 slots, each connected to the IOH
* One x4 PCI Express Gen1 slot for internal storage connected to the IOH

12.4.1 SAS 6/iR
The R710 internal SAS 6/iR HBA is an expansion card that plugs into a dedicated PCI Express x8 slot (four lanes wired). It incorporates two four-channel SAS IOCs for connection to SAS or SATA hard disk drives. It is designed in a form factor that allows the same card to be used in the PowerEdge R610 and PowerEdge T610.

 

New Lab Environment 2015 and vpro VNC blackscreen

Now when I purchased nuc5i5mybe it was with the Intel® vPro Technology in mind. This technology requires no software for you to install and can be very useful for management purposes. There are lots of cool tools that can be used, but one of the most common is going to be the inbuilt vnc solution, which gives the ability see exactly what is happening and even login remotely.

Now the one stumbling block with the vnc solution which I did not initially realize is that a headless system (one without a monitor) may return a black screen when trying to logon.

https://support.realvnc.com/knowledgebase/article/View/261/2/problems-viewing-vnc-server-on-headless-devices

The solution that I found easiest was to get a EDID dongle which is basically a plugin dummy monitor device about the size of small thumb drive. If you do search for “edid headless plug” you should be able to get one for around £10 pounds or less. This device will then pretend to be a monitor and solve the issue of a black screen on the remote desktop session.

Most Recent SQL Query

Here is another query that is useful to have when you need to find out what everyone is up to. Or in some cases when we need to recover what was on someones ssms before they accidentally closed without saving work. 🙂

--http://jongurgul.com/blog/most-recent-sql-query
SELECT
 DB_NAME(est.[dbid]) [DatabaseName]
,es.[session_id] [SessionID]
,est."text" [StatementText]
,es.[host_name] [ConnectionHostName]
,es.[login_name] [ConnectionLoginName]
,es.[program_name] [ConnectionProgramName]
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.[session_id] = ec.[session_id]
OUTER APPLY sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) est

SQL Server Locks

Here is a simple script to get some more detailed information about locking, which I have added to over the last few years.

--http://jongurgul.com/blog/sql-server-locks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
 dtl.[request_session_id] [SessionID]
,DB_NAME(dtl.[resource_database_id])  [DatabaseName]
,dtl.[request_status] [Status]
,dtl.[resource_type] [Resource]
,CASE 
WHEN [resource_type] =   'OBJECT'      THEN OBJECT_NAME(dtl.[resource_associated_entity_id],dtl.[resource_database_id])
WHEN [resource_type] =   'DATABASE'    THEN DB_NAME(dtl.[resource_database_id])
WHEN [resource_type] IN ('KEY','PAGE','RID') THEN pin.[ObjectName]
ELSE CAST(dtl.[resource_associated_entity_id] AS VARCHAR(MAX))
END [ResourceName]
,dtl.[resource_description] [ResourceDescription]
,dtl.[request_mode] [Mode]
,pin.[type_desc] [Type]
,QUOTENAME(pin.[ObjectSchemaName]) [ObjectSchemaName]
,QUOTENAME(pin.[ObjectName]) [ObjectName]
,QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT [IndexName]
,dtl.[resource_description] [ResourceDetail]
,CASE 
WHEN [resource_type] IN ('KEY','RID')
THEN N'SELECT * FROM '+QUOTENAME(DB_NAME(dtl.[resource_database_id]))
+'.'+QUOTENAME(pin.[ObjectSchemaName])
+'.'+QUOTENAME(pin.[ObjectName])
+N' WITH(NOLOCK'+
+COALESCE (',INDEX('+QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT +')','')
+') WHERE %%LOCKRES%% = '''
+RTRIM(dtl.[resource_description])+''''
WHEN [resource_type] IN ('PAGE') THEN 'DBCC PAGE('''+DB_NAME(dtl.[resource_database_id])+''','+RTRIM(REPLACE(dtl.[resource_description],':',','))+',3) WITH TABLERESULTS'
ELSE NULL
END COLLATE DATABASE_DEFAULT [Row/Page]--Performance will be poor if the table is large.
--,es.[original_login_name],es.[login_name]
FROM   
(
	SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
	,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
	,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
	FROM sys.partitions p 
	INNER JOIN sys.indexes i
	ON p.[object_id] = i.[object_id]
	AND p.[index_id] = i.[index_id]
	CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID()) d
		UNION ALL
		SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
		,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
		,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
		FROM tempdb.sys.partitions p 
		INNER JOIN tempdb.sys.indexes i
		ON p.[object_id] = i.[object_id]
		AND p.[index_id] = i.[index_id]
		CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID('tempdb')) d
) pin
RIGHT OUTER JOIN sys.dm_tran_locks dtl ON pin.[database_id] = dtl.[resource_database_id]
AND
((pin.[hobt_id] = dtl.[resource_associated_entity_id] OR pin.[object_id] = dtl.[resource_associated_entity_id]))
--LEFT OUTER JOIN sys.dm_exec_sessions es ON dtl.[request_session_id] = es.[session_id]
WHERE 1=1
AND dtl.[request_mode] <> 'Sch-S'
AND dtl.[request_mode] <> 'S' 
ORDER BY pin.[type_desc],dtl.[request_mode]