Category Archives: Database Administration

The Analysis, Migrate and Report tool (AMR) is part of SQL Server Management Studio 2014 and it aims at helping you evaluate your workloads and how effective/time consuming would the transformation to in-memory be.

Under the covers the tool is nothing more than couple of reports in SSMS, integrated as part of Management Data Warehouse feature. So before you start using it, you must enable the MDW and start the collection jobs.

In SQL Server 2014 they are completely rewritten and now instead of having the well-known Server Activity, Disk Activity and Query Activity collections, we have Stored procedure analysis and Table usage analysis collectors:

Once this is implemented, the ARM reports are accessible through the MDW reports in SSMS:

Note: MDW is my Management Data Warehouse database.

Now let me tell you a bit more about the “tool” – there are three reports, that are useful and that aim helping you identify all tables and procedures you can plan transform in to in-memory OLTP.

The Transaction Performance Analysis Overview report:

This report is simply the starting point where you can start your exploring :)

The Stored Procedure analysis report:

This report shows you the most expensive stored procedures in terms of worker time and those are suggested to be the best candidates for migration to in-memory OLTP.

The Table Usage report:

This report is one of my favorite ones – it shows you graphically the best options for tables to transform to in-memory OLTP. On the left axis you have the “Gain” i.e. how much will you gain if you transform. On the bottom axis you have the work that needs to be done in order to transform. So – the best candidates for in-memory OLTP are the top-right ones!

The Table contention analysis report:

This one is basically the same as the previous one, but it shows you the tables that are mostly affected or part of contention. Again you have migration work and gain on the axis.

Well, that’s the AMR tool – nothing more than couple of reports and an activated MDW.

I am now really interested in the logic behind the reports and I’ll start investigating – especially how MS decided how much will it take to migrate a table/SP and what the gain would be. I suppose there are some magic numbers here as well :) But that is for another post ;-)

If you need to check if there is any database on your SQL instance, related to replication, you have couple of different options – from manually checking through SSMS to using different options with T-SQL. Now I will share a simple script that you can use if you need to quickly check if your databases are part of a replication.

The script itself is exposing information in sys.databases, so you will need proper permissions to do that (I guess you are a sysadmin already :) )

SELECT 
	name as [Database name],
	CASE is_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Published],
	CASE is_merge_published 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Merge Published],
	CASE is_distributor 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Distributor],
	CASE is_subscribed 
		WHEN 0 THEN 'No' 
		ELSE 'Yes' 
		END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

Once you have the output you will have to dig up a little bit to see the details about the replication your database(s) is part of. The script is just pointing you the direction. Here is an output:

Note that you have two different columns for the publishing part – one for merge and one for transactional/snapshot replication.

If you want to write your own version of the script, you can also use DATABASEPROPERTYEX function – http://technet.microsoft.com/en-us/library/ms186823.aspx

Activity monitor is something that I do not use very often – it is just easier for me to work with the DMVs and the rest of the tools out there that show you what really is happening on your server. Although I am not using it, there are DBAs and support guys, who count really much on the Activity Monitor in SQL Server Management Studio. And as every DBA should do, they are all trying to access it remotely instead of RDC-ing the database server with Terminal Services. So far so good. If you are a member of the Local Administrators on the DB Server and sysadmin on the particular instance you want to monitor – everything is just great – you just open a management studio, connect to the instance remotely and start your Activity Monitor. What are you going to do if you are not a Local Administrator and you are still a junior DBA, not blessed with sysadmin permissions over the SQL Server instances? Well here comes the interesting stuff. Here is how to enable Activity Monitor for remote connection for non-admins.  Read More →

During one of my latest investigations on SQL Server MDW, I had to learn a few things the hard way – there are several stored procedures, that run on the central MDW server, such as the sp_purge_data which are designed to give you a constant update on what are they doing, but you need to manually enable it. Most of the stored procedures have PRINT/RAISEERROR statements in their code and in the case of the purge SP, I wanted to see what is going on behind the scenes. The sp_purge_data procedure is called via a SQL Agent job, called mdw_purge_data. So in my case I was lucky – what I needed to do is to simply set an output of the job step (called Step 1) like this:

Then just save the job and run it – the log is immediately created and now you can see what is going on:

A month ago I noticed a very neat post about finding if a given SQL Server database has been upgraded or not. The original post shows what to use and where to look for the info. In short, DBCC DBINFO reveals two fields – dbi_createversion and dbi_version, which can ultimately help us understanding if our database has been upgraded or not.

However, imagine if you need to do this for all your databases (usually a lot more than 5-6 :) ). This would not be an easy task, so I took the script I am using for checking the last successful run of DBCC (original post here) and modified it a bit, so now the output is a lot more readable.

The entire script you can find below:

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(
    SELECT [Value],
ROW_NUMBER() OVER (ORDER BY ID) AS rn1 
FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], 
ROW_NUMBER() OVER (ORDER BY ID) AS rn2 
FROM #temp WHERE Field IN ('dbi_createVersion'))
	,CHECKDB3 AS ( SELECT [Value], 
ROW_NUMBER() OVER (ORDER BY ID) AS rn3 
FROM #temp WHERE Field IN ('dbi_version')
)      
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS DatabaseCreateVersion
		, CHECKDB3.Value AS DatabaseCurrentVersion
		, CASE 
			WHEN CHECKDB2.Value < CHECKDB3.Value THEN 'Yes'
			ELSE 'No' 
			END as DatabaseUpgradedStatus
FROM CHECKDB1 
INNER JOIN CHECKDB2
	ON rn1 =rn2
INNER JOIN CHECKDB3
	ON rn2=rn3

DROP TABLE #temp

Probably you wonder why do you need to know this info – to be honest, so far I do not have  a reason besides that it is cool and geeky. Have fun!