Computer Virus – RDN/YahLover.worm!055BCCAC9FEC Infection

Background

Parents texted me the message shown below.

It has to do with a pestering window that has been occurring on their machine.

 

RDN/YahLover.worm!055BCCAC9FEC Infection

Image

Explanation

  1. Helpline
    • 888-225-2540

Virus Removal Steps

  1. Download & Run Malware Removal Tools
    • Option 1
      • Malware Bytes
        • Download from here
    • Option 2
      • Spybot – Search & Destroy
        • Download from here
    • Option 3
      • ToolsLib
        • Download from here

 

Wale, J Cole, No I.D. AND Friendship

Introduction

Spent most of yesterday locked into my laptop trying to learn something new.

Never far from YouTube or Gossip Columns.

One of the click baits I clicked on is ‘Jay-Z Opens Up About Alleged Feud With Kanye West and Reveals “What Really Hurt” Him‘.

In the segment he brought up “No I.D.”, Ernest Dion Wilson.

 

Lyrics

Nas, J. Cole, No I.D.

In his track “Let Nas Down“, J. Cole spoke about No I.D. upping the ante …

Dion called me when it dropped, sounded sad but sincere
Told me Nas heard your single and he hate that …
Said, “You the one, yo, why you make that …?”

 

J. Cole & Wale

In his track “False Prophet”  on the second verse, J. Cole spoke about his good friend, Wale.

I got a homie, he a rapper and he wanna win bad
He want the fame, the acclaim, the respect that’s been had
By all the legends, so every time I see him, he stressin’
Talkin’ ’bout, niggas don’t fuck with him, the shit is depressin’
And I know he so bitter he can’t see his own blessings
Goddamn, nigga, you too blind to see you got fans, nigga
And a platform to make a classic rap song
To change a nigga life, but you too anxious livin’ life
Always worried ’bout the critics who ain’t ever fuckin’ did it
I write what’s in my heart, don’t give a fuck who fuckin’ with it
But in a sense I can relate, the need to be great
Turns into an obsession and keeps a nigga up late
Writin’ words, hopin’ people observe the dedication
That stirs in you constantly, but intentions get blurred

 

Wale’s Response

I’m on my way to Raleigh so grateful for that inspiration
I’ll see you at the game bro
Ralph

 

One way too aggressive nemesis, and these never-ending spells with bipolar depression all, the while progressing
Maybe I mind my business and count my blessings
Be a living testament, a walking embodiment for
Everybody that’s been endlessly tested
And maybe resent the sentiment that they rarely respected
Maybe I mind my business and count my blessings

 

I’m just gonna get my black ass in this booth, cut loose so I can buy papaya juice for my beautiful black baby
And I love you Zyla, but if this game kills your father
Know we started off as a passenger, put some passion in it
And some non-passive aggressiveness

 

Just know he toured the world with Ross and gave him his first platinum plaque that he ain’t even had to adlib, rap or even talk on
Nah this Folarin with the art of sad song that TT wrote and bad girl RiRi

 

While I watch these discount niggas buy their way in with their lay-away effort
And lay waste to the very game I did my best in

 

Battling my label
One way too aggressive nemesis, and these never-ending spells with bipolar depression all, the while progressing
Maybe I mind my business and count my blessings
Be a living testament, a walking embodiment for
Everybody that’s been endlessly tested
And maybe resent the sentiment that they rarely respected
Maybe I mind my business and count my blessings
Everyday is groundhog’s day
So what you say I anticipate, cause I heard it forever and ever and ever

 

Ain’t no way in hell the devil gon’ stop my endeavors, yes
Forever underrated ’til Undertaker makes his presence
And as we know, the utmost praise for those who cannot feel it

 

So maybe I mind my business and provide for my Zyla, her mama and my niggas
And one day buy a house for Big I, Emilola, Roberto, Diane and Jordan
So they can enjoy the journey I’ve endured that’ll probably kill me

 

I mind my business
I mind my business
Nigga mind your business
And in this dark time, if we don’t find a reason to smile
We ain’t never gon’ shine nigga

 

J. Cole & Cozz

Look, I never admit it, tryna get my brother acquitted
Yo’ tell the judge I be happy to pay him off if he’s with it
My flesh and blood, hauled off to the cell
I search for heaven as I see him getting lost in this hell
Headed for coffin or jail, to never try is the ultimate fail
But love is wanting more for someone than they want for themselves
Deep, I guess I love a lot
Because the more I do my thang the more I feel the guilt and shame that my brother’s not
Reminisce as we was kids screaming “Fuck a cop”
Guess he took it serious, for me that shit was just for props

 

Videos

  1. Wale
    • Letter Ft. John Mayer
      • YouTube
        Published On :- 2009-oct-26th
        Link
    • Groundhog Day
      • YouTube
        Published On :- 2017-Feb-2nd
        Link
    • Family Affair
      • “Family Affair” is a music video by Wale that is featured on the upcoming “Inside A Change” Soundtrack. 
      • YouTube
        • Link
          Published On :- 2017-May-26th

Jay Z

The Podcast is title “RAP RADAR / EPISODE 10 – JAY-Z PART 1”.

And, here are some links to the podcast.

  1. jay-z-kanye-west-444-tidal-rap-radar-podcast-interview
    link

 

Summary

Words come in so many different ways.

Some are public, while others are private.

With time we learn whether they are to hold us back.

Or whether they are to encourage us.

Prayerfully don’t mistake words to “Grow On” with ones “To Miss“.

Letting Cole have the last words…

But love is wanting more for someone than they want for themselves

 

References

  1. J. Cole
    • J. Cole – Let Nas Down
      • Lyrics
    • False Prophets
      • Lyrics
        • Google Play Music
          Link
  2. Wale
    • GroundHog Day
      •  Lyrics
        • Genius
          • Groundhog Day (Poem)
            Genius – Poem
            Link
          • Groundhog Day
            Link
        • Google Play
          Link
  3. J Cole & Wale
    • Wale and J. Cole Hang Out at a Basketball Game Together
      Link

SqlServer – ColumnStore Index – AdventureWorksDW

Background

A couple of weeks ago I touched on the fact that I was interested in Columnar Databases.

In SQL Server, the Columnar Engine is integrated into the traditional RDBMS Row Engine.

 

Use Cases

Went online to search out use cases for Columnar Databases.

Dennis Forbes

The best I found is Dennis Forbes work and it is availed here.

 

DataSets

Wanted to find suitable datasets and explored Sample databases courtesy of Microsoft’s AdventureWorks.

AdventureWorks / AdventureWorksDW

The AdventureWorks dataset are packaged as AdventureWorks & AdventureWorksDW.

GitHub Microsoft SQL Server Sample Databases

The main project site for Microsoft’s sample DB is here.

Per SQL Server v2016, Adventure Works appears to be deprecated in favor of Wide World Importers.

The 2014 Version specific files for Adventure Works are available here.

 

Download & Avail

Please download and avail the backup file for AdventureWorksDW.

 

Tables

Tables in a Data Warehouse database can be viewed through the lenses of Dimension or Fact.

Dimension being what is being measured, and Fact been the value measured.

Table Dictionary

Type Table Description
Dimension
DimAccount  Account
DimCustomer  Customer
DimDate  Date
DimEmployee  Employee
DimProduct  Product
Fact
FactInternetSales  Direct Customer Sales
FactResellerSales  Sales  to resellers

 

List of Tables

Here are the tables that we will be using.

A traditional Clustered Index table, a Clustered Column Index, and a Non-Clustered Column Index.

Table Index
FactResellerSales Row Store – Clustered Index
FactResellerSales.ColumnStore Column Store – Clustered Column Index
FactResellerSales.ColumnStore.NC Row Store with Non-Clustered Column Index

 

Table Metadata

Index Column Names
SQL

use [AdventureWorksDW2014]
go

; with cteTable
as
(
	select 'DimAccount' as [table]
	union 
	select 'DimCustomer'
	union 
	select 'DimDate'
	union
	select 'DimEmployee'
	union
	select 'DimProduct'
	union
	select 'FactInternetSales'
	union
	select 'FactResellerSales'
	union
	select 'FactResellerSales.ColumnStore'
	union
	select 'FactResellerSales.ColumnStore.NC'
) 

, cteTableIndexColumn
as
(

	select 
			[table]
				=  tblSS.[name]
					+ '.'
					+ tblSO.[name]

			, [indexID]
				= tblSI.[index_id]
						
			, [indexType]
				= tblSI.[type_desc]
							
			, [index]
				= tblSI.[name]

			, [column]
				= tblSC.[name]

			, [indexColumnKeyOrdinal]
				= tblSIC.key_ordinal

			, [indexColumnID]
				= tblSIC.index_column_id

	from   sys.schemas tblSS

	inner join sys.objects tblSO

		on tblSS.schema_id = tblSO.schema_id

	inner join sys.indexes tblSI

		on tblSO.object_id = tblSI.object_id

	inner join sys.index_columns tblSIC

		on  tblSI.object_id = tblSIC.object_id
		and tblSI.index_id = tblSIC.index_id

	inner join sys.columns tblSC

		on  tblSIC.object_id = tblSC.object_id
		and tblSIC.column_id = tblSC.column_id

	inner join cteTable cteT
		on tblSO.[name] = cteT.[table]

	where  tblSS.[name] = 'dbo'

)

SELECT 
		[table]
		  = cteTIC.[table]

		, cteTIC.[indexID]

		, cteTIC.[index]

		, cteTIC.[indexType]

		, [column]
		  = STUFF
		  (

			(
				
				select ', ' 
						+ [column]
						+ case
							when indexColumnKeyOrdinal =0 then '^'
							else ''
						  end

				from   cteTableIndexColumn cteTIC_Inner
				
				where  cteTIC.[table] = cteTIC_Inner.[table]
				
				and    cteTIC.[indexID] = cteTIC_Inner.[indexID]

				and    cteTIC.[index] = cteTIC_Inner.[index]

				order by
						  cteTIC_Inner.indexColumnKeyOrdinal
						, [cteTIC_Inner].indexColumnID
							
				FOR XML PATH('')

			)
			, 1 ,1 ,''

		 )

from   cteTableIndexColumn cteTIC

group by
		  cteTIC.[table]
		, cteTIC.[indexID]
		, cteTIC.[index]
		, cteTIC.[indexType]

order by
		  cteTIC.[table]
		, cteTIC.[indexID]
		, cteTIC.[index]

 

Output

Number of Records
SQL

; with cteTable
as
(
	select 'DimAccount' as [table]
	union 
	select 'DimCustomer'
	union 
	select 'DimDate'
	union
	select 'DimEmployee'
	union
	select 'DimProduct'
	union
	select 'FactInternetSales'
	union
	select 'FactResellerSales'
	union
	select 'FactResellerSales.ColumnStore'
	union
	select 'FactResellerSales.ColumnStore.NC'

) 
select 
		[table]
			=  tblSS.[name]
				+ '.'
				+ tblSO.[name]

		, [indexName]
			= tblSI.[name]
					
		, [indexType]
		  = tblSI.[type_desc]

		, [numberofRecords]
			= sum(tblSP.[rows])

		, numberofPagesData
			= sum(tblSPS.in_row_data_page_count)

		, numberofPagesLOB
			= sum(tblSPS.lob_used_page_count)

		, usedPageCount
			= sum(used_page_count)

from   sys.schemas tblSS

inner join sys.objects tblSO

	on tblSS.schema_id = tblSO.schema_id

inner join sys.indexes tblSI

	on tblSO.object_id = tblSI.object_id


inner join sys.partitions tblSP

	on tblSI.object_id = tblSP.object_id
	and tblSI.index_id = tblSP.index_id


inner join sys.dm_db_partition_stats tblSPS

	on tblSP.object_id = tblSPS.object_id
	and tblSP.index_id = tblSPS.index_id
	and tblSP.partition_id = tblSPS.partition_id


inner join cteTable cteT
		on tblSO.[name] = cteT.[table]

where  tblSS.[name] = 'dbo'

group by
		  tblSS.[name]
		, tblSO.[name]
		, tblSI.index_id
		, tblSI.[name]
		, tblSI.[type_desc]

order by
		  tblSS.[name]
		, tblSO.[name]
		, tblSI.index_id

Output

Tabulated
Type Table #Records # of Pages # of Pages LOB # of Used Pages
Dimension
DimAccount  99  2  0  4
DimCustomer  36968  978  0  996
DimDate  7304 57  0  59
DimEmployee  296  29  635  666
DimProduct  1212  251  498  751
Fact
FactInternetSales  60398  1236  0  1254
FactResellerSales  60855  1672  0  1690
FactResellerSales.ColumnStore ( Clustered )  60855  0  242  240
FactResellerSales.ColumnStore ( Non Clustered – Heap)  60855  1681  42  1682
FactResellerSales.ColumnStore ( Non Clustered – INDX_COLUMNSTORE_NONCLUSTERED_SalesAmount)  60855  0  28  28
Explanation
  1. Column Store Indexes
    • Stored in LOB Data pages

 

Space Used
SQL

use [AdventureWorksDW2014]
go

exec sp_spaceused '[dbo].[FactResellerSales]'

exec sp_spaceused '[dbo].[FactResellerSales.ColumnStore]'

exec sp_spaceused '[dbo].[FactResellerSales.ColumnStore.NC]'


Output

 

Explanation
  1. Table Sizes
    • Row Stored Clustered :- 13.063 MB
    • Column Stored – Clustered :-  1.875 MB
    • Row Stored  Heap – Non Clustered Column Index :- 13.352 MB
  2. Compare Row Store To Column Store
    • Column Store is 15% of Row Store

Scenario

Let us ask a fairly straightforward question.

Get top 5 Sales from [dbo].[FactResellerSales].

Table

dbo.FactResellerSales

SQL

select top 5
		  --  ProductStandardCost * OrderQuantity
		  [salesAmount]
			= tblFRS.SalesAmount

from   [dbo].[FactResellerSales] tblFRS

order by
		tblFRS.SalesAmount desc

Query Plan

Query Plan – Warning
Textual

The query memory grant detected "ExcessiveGrant", which may impact the reliability.
Grant size: Initial 1024 KB, Final 1024 KB, Used 16 KB.

Image

 

Query Plan – Explanation
  1. [dbo].[FactResellerSales]
    • The [dbo].[FactResellerSales] table has a lone index, PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber
    • The columns  been SalesOrderNumber and SalesOrderLineNumber
  2. We are looking for top Sales Amount ( SalesAmount)
    • We are not going to be helped by the Primary Key Column

 

dbo.[FactResellerSales.ColumnStore]

Objective

We will create a tracking table, [dbo].[FactResellerSales.ColumnStore].

The table will track the [dbo].[FactResellerSales] table.

The only difference between the two tables is that [dbo].[FactResellerSales] will have a Clustered Index and [dbo].[FactResellerSales.ColumnStore] will have a Clustered Column Store Index.

 

Create Table
SQL

DDL is posted on GitHub and here is the Link.

Index - ColumnStore - Clustered
SQL

CREATE CLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_CLUSTERED]
ON [dbo].[FactResellerSales.ColumnStore]

Explanation
  1. Unlike a Row Store Index:
    • The column list is not explicitly stated
    • All columns are noted as so called “Included Columns
  2. Like traditional Row Store Clustered Index
    • All columns are stored in the Clustered Index
  3. Singularity
    • When a Clustered Column Index exists on table, no other indexes can be defined on that table
Populate Data
SQL

USE [AdventureWorksDW2014]
GO

set nocount on
go

truncate table [dbo].[FactResellerSales.ColumnStore]
GO

insert into [dbo].[FactResellerSales.ColumnStore]
select *
from   [dbo].[FactResellerSales]
go

alter index [INDX_COLUMNSTORE_CLUSTERED]
	on [dbo].[FactResellerSales.ColumnStore] rebuild;
go

update statistics [dbo].[FactResellerSales.ColumnStore] with FULLSCAN;
go

Get Top 5 Records from ColumnStore – Clustered Index
SQL

select top 5 

		  [salesAmount]
			= tblFRS.SalesAmount

from   [dbo].[FactResellerSales.ColumnStore] tblFRS

order by
		tblFRS.SalesAmount desc

Output
Explanation
  1. Columnstore Index Scan ( Clustered )
    • 2%
  2. Sort
    • 98%

 

 

dbo.[FactResellerSales.ColumnStore.NC]

Objective

We will create a new tracking table, [dbo].[FactResellerSales.ColumnStore.NC].

The table will also track the [dbo].[FactResellerSales] table.

Again, the difference between the two tables is that [dbo].[FactResellerSales] will have a Clustered Index and [dbo].[FactResellerSales.ColumnStore] will have a Non Clustered Column Store Index.

 

Create Table
SQL

The SQL for creating the dbo.[FactResellerSales.ColumnStoreNC] is here.


Index – ColumnStore – Non Clustered
SQL

CREATE NONCLUSTERED COLUMNSTORE INDEX [INDX_COLUMNSTORE_NONCLUSTERED_SalesAmount]
ON [dbo].[FactResellerSales.ColumnStore.NC]
(
   [SalesAmount]
)
Populate Data
SQL

USE [AdventureWorksDW2014]
GO

alter index [INDX_COLUMNSTORE_NONCLUSTERED_SalesAmount]
	on [dbo].[FactResellerSales.ColumnStore.NC] disable;

--truncate table [dbo].[FactResellerSales.ColumnStore.NC]

delete from [dbo].[FactResellerSales.ColumnStore.NC]
GO

insert into [dbo].[FactResellerSales.ColumnStore.NC]
select *
from   [dbo].[FactResellerSales]
go

alter index [INDX_COLUMNSTORE_NONCLUSTERED_SalesAmount]
	on [dbo].[FactResellerSales.ColumnStore.NC]	 rebuild;

update statistics [dbo].[FactResellerSales.ColumnStore.NC] with FULLSCAN;

Get Top 5 Records from ColumnStore – NonClustered Index
SQL

select top 5 

	  [salesAmountFormatted]
		= cast(tblFRS.SalesAmount as money)

from   [dbo].[FactResellerSales.ColumnStore.NC] tblFRS

order by
	tblFRS.SalesAmount desc

Output
Explanation
  1. Columnstore Index Scan ( NonClustered )
    • 2%
  2. Sort
    • 98%

 

Compare Queries

Let us compare queries targeting our tables.

Query Plan

Image

Explanation
  1. Original Table
    • Query Cost :- 39%
    • Warning :- The query memory grant detected “ExcessiveGrant”, which may impact the reliability.
      Grant size: Initial 1024 KB, Final 1024 KB, Used 16 KB.
  2. ColumnStore – Clustered
    • Query Cost :- 31%
    • Columnstore Index Scan (Clustered)
  3. ColumnStore – NonClustered
    • Query Cost :- 31%
    • Columnstore Index Scan (NonClustered)

Statistics IO

v-2014
Image – v2014

Explanation – v2014
  1. dbo.FactResellerSales
    • Scan count :- 1
    • Reads
      • Logical reads :- 1682
      • Physical reads :- 3
      • Read Aheads :- 1720
    • LOB Reads
      • Logical reads :- 0
      • Physical reads :- 0
      • Read Aheads :- 0
  2. dbo.[FactResellerSales.ColumnStore]
    • Scan count :- 1
    • Reads
      • Logical reads :- 0
      • Physical reads :- 0
      • Read Aheads :- 0
    • LOB Reads
      • Logical reads :- 38
      • Physical reads :- 1
      • Read Aheads :- 60
  3. dbo.[FactResellerSales.ColumnStore.NC]
    • Scan count :- 1
    • Reads
      • Logical reads :- 0
      • Physical reads :- 0
      • Read Aheads :- 0
    • LOB Reads
      • Logical reads :- 13
      • Physical reads :- 1
      • Read Aheads :- 24
v-2016
Image – v2016

Explain – v2016
  1. Non-Clustered Column Index
    • It is curious that we have both a workfile and worktable reference on Non-Clustered Column Index Search

Time Taken

Image
Image – v2014
Explanation – v2014
  1. The original query took 33%
  2. The query that targeted the Clustered Column Index was at 15%
  3. And, the query that targeted the Non Clustered Column Index was at 52%

 

Image – v2016

Explanation – v2016
  1. The original query took 37%
  2. The query that targeted the Clustered Column Index was at 18%
  3. And, the query that targeted the Non Clustered Column Index was at 43%

 

GitHub

Commited to Github.

The repository is here

Dedication

Paying back Dennis Forbes.

Summary

Admittedly, our dataset is very small.

A Clustered Column Store is much, more smaller than a Row Store table.

This is due to the compression algorithms that lends themselves particularly to a Column Store.

It appears that a Clustered Columnar Index might reduce our query time.

In comparison to a Clustered Column Index, the Nonclustered Column Index is relatively slower.

Additionally in v2016, it has both workfile and worktable references in the Statistics IO.

 

References

  1. SQL Server Dynamic Management Views ( DMVs )
    • sys.dm_db_partition_stats (Transact-SQL)
      Link
    • sys.index_columns (Transact-SQL)
      Link
  2. Thomas LaRock
    • 05 MAY SIZE MATTERS: TABLE ROWS AND DATABASE DATA PAGES
      Link
  3. Martin Schoombee
    • ADVENTUREWORKS DW: GENERATING MORE FACTS
      Link

Sapien – WMI Explorer – Reporting Services Configuration Manager

Background

Wanted to utilize another WMI Query tool in addition to Microsoft’s own WMI tools.

 

Lineage

Posts

SQL Server Reporting Services (SSRS) – Error – “Invalid Class”

In the “SQL Server Reporting Services (SSRS) – Error – Invalid Class” post, we bemoaned the difficulty of fully espousing our wilderness experience based on screenshots from a single tool; that tool being Microsoft WMI Explorer.

The post is here.

Microsoft’s WMI Explorer is available at Codeplex and here is the specific URL.

 

Sapien

We wanted to try out one more tool and the one we chose is Sapien WMI Explorer.

Download

Please download WMI Explorer from here.

The current version is 2.2.74.

Requirements

  1. OS Version
    • Desktop :- Windows 7 / Windows 8 / Windows 8.1 / Windows 10
    • Server :- Windows Server 2008 R2 / Windows Server 2012 / Windows Server 2012 R2
  2. OS Bitness
    • 32 and 64 bit
  3. Powershell
    • Version :- Powershell Version 3.0
  4. Visual Studio 2012 Runtime

 

Install

Installation is straightforward.

Register

Connect to the Vendor’s web site and request a trial key.

 

Usage

Launch Sapien’s WMI Explorer and navigate the Class Browser tree.

As one chooses a specific Namespace, the classes and corresponding properties and methods for that class are shown in the right window.

We are interested in SQL Server Namespace and specifically the ReportServer space.

Please click on the Query button on the Ribbon Tab to view and edit queries.

Image

Pasted below are the images captured from our journey.

Hierarchy

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>
Instance – Default

Instance – DATACAP

 

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>
Instance – {RS_DATACAP }\ Version {v12}

Instance – {RS_DATACAP }\ Version {v12} – Custom Query

Instance – {RS_DATACAP }\ Version {v12} – Query Results

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>\Admin
Instance – {RS_DATACAP }\ Version {v12} \ Admin – Custom Query

Instance – {RS_DATACAP }\ Version {v12} \ Admin – Query Results

 

WMI – Reporting Services Configuration Manager – Event Viewer

Background

When troubleshooting Reporting Services Configuration Manager WMI calls it can be useful to see whether WMI activities are occurring.

 

TroubleShooting

Event Viewer

Enable Log

WMI calls are logable in the Event Viewer.

But, they are not logged by default.

To enable logging please follow the steps listed below:

Obtaining WMI Events Through Event Viewer
Link

  1. Open Event Viewer. On the View menu, click Show Analytic and Debug Logs
  2. Locate the Trace channel log for WMI under Applications and Service Logs | Microsoft | Windows | WMI Activity
  3. Right-click the Trace log and select Log Properties. Click the Enable Logging check box to start the WMI event tracing
  4. WMI events appear in the event window for WMI-Activity. Double-click an event in the list to see the detailed information. You can view an event in XML View or in Friendly View format.

Logged Events

Tabulate

# Event
1 GroupOperationId = 159602; OperationId = 159602; Operation = IWbemServices::Connect; ClientMachine = QADB; User = dadeniji.adeniji; ClientProcessId = 5024; NamespaceName = \\QA\root\Microsoft\SqlServer\ReportServer
2 GroupOperationId = 159603; OperationId = 159604; Operation = Start IWbemServices::CreateInstanceEnum – __NAMESPACE; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer
3 GroupOperationId = 159605; OperationId = 159605; Operation = IWbemServices::Connect; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\QADB\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
4 GroupOperationId = 159606; OperationId = 159607; Operation = Start IWbemServices::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
5 ProviderInfo for GroupOperationId = 159606; Operation = Provider::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ProviderName = ReportingServicesWMIProvider; ProviderGuid = {0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF}; Path = C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Explanation

  1. Event #1 :- IWebServices Create Instance Enum
    • NamespaceName :- \\QA\root\Microsoft\SqlServer\ReportServer
  2. Event #2 :- IWebServices Create Instance Enum
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer
  3. Event #3 :- IWebServices Connect
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  4. Event #4 :- IWebServices Instance Enumerate
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  5. Event #5 :- Com Object Instantiated
    • COM Object ID :- 0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF
    • COM File :- C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Summary

From logging WMI Calls we are able to see the inner workings of the WMI Provider class and IWbemServices interface.

In the case of Sql Server Reporting Services (SSRS) it is an version specific dll ( C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll ).

Microsoft Access – Querying SQL Server Table – Schema Stability Lock

Background

A quick follow-up to a post over the weekend.

The forwarding post is titled “SQL Server – Index Rebuild – Blocked / Blocking” and it is here.

In that post we spoke of how a scheduled Business Back Office Job was hung and could not proceed.

Using Adam Mechanic’s sp_whoIsActive, we discovered we had were indeed experiencing session blocking, but not an actual deadlock, which in fact would have triggered a vote as to which session to abort

We stopped the blocking database maintenance job which is an Index Defrag job.  The Index Defrag job simply calls Ola Hallengren’s IndexOptimize Stored Procedure.

TroubleShooting

TroubleShooting – Day 1

Adam Machanic

dbo.sp_WhoIsActive

Code

exec  [dbo].[sp_WhoIsActive]

Image
Session

SQL Text

Explanation
  1. Sessions
    • Login :- app
      • We are familiar with the app account
      • The sessions are the bottom two
    • SQL :- OpenRowSet
      • A SQL Server Profiler Trace we initiated to track the ongoings
    • Status :- suspended // wait_info :- ASYNC_NETWORK_IO
      • used_memory
        • 10, 000 KB ( 10 MB)

 

TroubleShooting – Day 2

Microsoft

Dynamic Management Views

sys.dm_exec_sessions
SQL

declare @appNameMSFTODBC sysname
declare @appNameMSFTOffice sysname
declare @appNameMSFTSSMS sysname
declare @appNameMSFTJavaJDBC sysname

declare @clienInterfaceName sysname

set @appNameMSFTODBC = 'Microsoft® Windows® Operating System'
set @appNameMSFTOffice = 'Office'

set @appNameMSFTSSMS = 'Microsoft SQL Server Management Studio - Query'
set @appNameMSFTJavaJDBC = 'Microsoft JDBC Driver for SQL Server'

set @clienInterfaceName = 'Microsoft JDBC Driver 4.0'

select 
		  tblDES.session_id
		, tblDES.program_name
		, tblDES.transaction_isolation_level
		, tblDES.open_transaction_count
		, tblDES.host_name
		, tblDES.client_interface_name
		, tblDES.client_version
		, tblDES.[status]
		, tblDES.[row_count]
		, tblDES.[prev_error]
		, tblDES.reads
		, tblDES.last_request_start_time
		, tblDES.last_request_end_time
		, [timeSinceLastCommunicationInMinutes]
			= datediff
				(
					  minute
					, tblDES.last_request_end_time
					, getdate()
				)

from  sys.dm_exec_sessions tblDES

where  tblDES.session_id >= 50

and	   (
			(
						
				   ( tblDES.program_name like @appNameMSFTODBC )
				or ( tblDES.program_name like '%' + @appNameMSFTOffice + '%' )

			)
			and
			(

				       ( tblDES.program_name != @appNameMSFTSSMS )
				   and ( tblDES.program_name != @appNameMSFTJavaJDBC )
			)

	   )


Output

 

Microsoft SQL Server Profiler

Image

Explanation

Here is the conversation captured:

  1. SQL:BatchStarting
    • SELECT Config, nValue FROM MSysConf
  2. SQL:BatchStarting
    • SELECT “dbo”.”oe_dep_audit”.”oe_dep_emp_location”,”dbo”.”oe_dep_audit”.”oe_dep_emp_ssn”,”dbo”.”oe_dep_audit”.”oe_year”,”dbo”.”oe_dep_audit”.”oe_dep_no”,”dbo”.”oe_dep_audit”.”oe_dep_session_ID”,”dbo”.”oe_dep_audit”.”oe_dep_record_type”,”dbo”.”oe_dep_audit”.”oe_dep_record_flag” FROM “dbo”.”oe_dep_audit”
      • Gets all the records in table
  3. SQL:BatchStarting
    • SELECT CASE DATABASEPROPERTYEX( DB_NAME(), ‘Updateability’) WHEN ‘READ_ONLY’ THEN ‘Y’ ELSE ‘N’ END
  4. RPC:Completed
    • SQL
      • declare @p1 int
        set @p1=1
        exec sp_prepexec @p1 output,N’@P1 char(2),@P2 char(9),@P3 char(4),@P4 int,@P5 char(10),@P6 char(1),@P7 char(1)’,N’SELECT “oe_dep_emp_location”,”oe_dep_emp_ssn”,”oe_year”,”oe_dep_no”,”oe_dep_session_ID”,”oe_dep_record_type”,”oe_dep_record_flag”,”oe_dep_name”,”oe_dep_birthdate”,”oe_dep_relationship”,”oe_dep_SSN”,”oe_dep_sex”,”oe_dep_disabled”,”oe_dep_medical”,”oe_dep_dental”,”oe_dep_optical”,”oe_dep_legal”,”oe_dep_PCP”,”oe_dep_current_patient”,”oe_dep_deleted” FROM “dbo”.”oe_dep_audit” WHERE “oe_dep_emp_location” = @P1 AND “oe_dep_emp_ssn” = @P2 AND “oe_year” = @P3 AND “oe_dep_no” = @P4 AND “oe_dep_session_ID” = @P5 AND “oe_dep_record_type” = @P6 AND “oe_dep_record_flag” = @P7′,’01’,’000000000′,’2012′,1,’a733167067′,’B’,’ ‘
        select @p1
    • Prepares a fetch Statement
  5. RPC:Completed
    • SQL
      • exec sp_execute 2,’01’,’00140′,’2013′,3,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,3,’a08938′,’O’,’A’,’01’,’00140′,’2013′,3,’a57784′,’O’,’A’,’01’,’00140′,’2013′,3,’a94593′,’O’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’O’,’A’,’01’,’00140′,’2013′,4,’a577848305′,’O’,’A’,’01’,’0014′,’2013′,4,’a94593′,’O’,’ ‘,’01’,’00154′,’2005′,100,’R50001′,’O’,’ ‘,’01’,’00154′,’2006′,1,’N6000′,’B’,’ ‘

 

Microsoft Network Monitor

Filter

//IP Address
(

    ( IPv4.Address == 10.1.20.182 )

)
and 
(

	(
          not ( Conversation.ProcessName == "Ssms.exe")
    )

	and
        ( 
             not ( Conversation.ProcessName == "PROFILER.exe")
        )
)

Traffic
Image

Explanation
  1. Using a Network monitor tool we can see that there is quite a bit of ongoing Network Activity between the client node running MS Access and the Database Server
  2. The protocols are plain TCP and TDP
  3. And, the ports are the default SQL Server Port of 1433 and the ephemeral ports from the Source Node

 

Summary

When the table queried from MS Access is reasonably large, the database connection is kept opened.  And, the DB table is locked with an object stability lock.

It is a designed behavior by MS Access and it reduces the amount of local resources on the client host.