Archive for the ‘Tools’ Category

Find the “Unaccounted-For” Memory Using RAMMap

As a DBA, have you ever experienced or witnessed the “unaccounted-for” memory issue where memory utilization is at 100% or close to 100% and SQL Server is using only the Max Server Memory configured and the remaining majority of the memory is being used by other process or processes not showing up in the Task Manager processes tab? First of all, let me give you a warm welcome to the DBA world of “guilty until proven innocent!” Second, you need to get into the habit of being able to defend yourself from these wrongful, hurtful, and plainly ignorant accusations. These accusation will always end with “…It’s the DBAs issue. They need to fix it!” Third, be a bigger man or a more civilized woman, don’t argue with the folks doing the blame or else you will be in the same class as they are. But, share with them your technical findings with supportive evidences to prove the root causes of the issues. In this case, use RAMMap to identify or explain SQL Server is not using all the memory. It is VMWare Tools or other apps that are hogging all the memory.

You can read more details about RAMMap and download it on Yes. It is free from Microsoft. One “red tape” removed here. But, you may have other bureaucratic policies that may prevent you from installing it. Similar to other advices that I had shared with you in the past years, watch some political debates or watch the Sunday news programs to see how the professionals, the politicians, deploy diplomacy and how to get it done!

Here’s an example of “Driver Locked” using 21,811,372 KB of memory and it does not show up in Task Manager. As you click through the different tabs in RAMMap, you will be able to identify the app that’s using all these memory.

The Amazing Microsoft Assessment and Planning Toolkit (MAP)

If you are elected as president of a newly discovered country, what would be one of the most critical first things you should do to be successfully as a leader? Let’s put aside the formidable goals of declaring yourself as king or queen or absolving the term limits for a second. Most likely, the most practical first things to do is to establish a team of national geographic experts and task the team to create you a map of the country so that you will know your country’s geographical areas and utilize the resources efficiently. In a similar scenario, if you have been chosen as the guardian of the company’s data – the SQL Server Database Administrator (DBA), the very first thing you should be is to find out the SQL Server instances, their functionalities, and specifications such as version, edition, service pack, clustering, collations, databases, data files, log files, and etc… Wouldn’t it be nice if there’s a free discovery tool that have us SQL Server DBAs in mind and when executed will provide all of these useful information in professionally prepared graphs , charts, and also enables us the function to export the data to Excel and Word documents. There is and it is the Microsoft Assessment and Planning Tookit (MAP)! While it is not new, it is definitely underutilized and deserves to have more exposures in the SQL Server community.
You can read more about it here and then download it. It is a 72.3MB file and it requires .NET Framework 4.2+ to run. That’s all the pre-requisites. While you can use it as a discovery tool for other Microsoft server products such Sharepoint, Exchange, Lync, Windows Server, and etc…, our interest in this blog is to use it as a SQL Server instances discovery tool.

The following are the steps on how I had used it on my testing lab (my laptop):
1. Download and install MAP.
2. In the “All Programs” menu, open “Microsoft Assessment and Planning Toolkit” and it will prompt you to create a new inventory database. Here, I will call it MAP and then click the OK button.
Inventory database

3. On the “Overview” section, click on the link “Perform an inventory”

Perform an inventory

4. On the “Inventory and Assessment Wizard” and under the “Inventory Scenarios” section, select the checkbox for “SQL Server with Database Details” and the click the Next button.

Inventory Scenarios - SQL Server with Database Details

5. On the “Discovery Methods” section, select the methods you wish to use to discover the SQL Server in your network. Note: Usually, you will select the top two options of “Use Active Directory Domain Services (AD DS)” and “Use Windows networking protocols” when you are running MAP in your company’s network. In this testing scenario, I will select “Manually enter computer names and credentials” as I am not running my laptop inside a network. Click the Next button once you made your selections.

Discovery Methods

6. On the “All Computers Credentials” page, click the “Create…” button to supply the account that will be used to connect to the machines you will be specifying and the click the “Save” button and then click the “Next” button to continue.

Supply the account to connect ot the machines

7. On the “All Computers Credentials” page, click the “Create…” button to supply the account that will be used to connect to the machines you will be specifying and the click the “Save” button and then click the “Next” button to continue.

Credentails Order

8. On the “Enter Computers Manually” page, click on the “Create…” button and then click on the “Next” button to key in the computer names, click on the “Add” button to add the computers, select the “Use All Computers credential list” option to use the credential you had submitted in the last step, and then click on the “Save” button to continue. Click the “Next” button on the subsequent page.

Enter Computers Manually

Key in the computer name and specify the credential

9. Review the “Summary” page and the click the “Finish” button when done.

Summary Page

10. The “Data Collection” page will be shown and the collection process will be in progress.

Data Collection

11. After the “Assessment” is “Completed”, click on the “Close” button.

Assessment Completed

12. On the left hand side of the menus, click on the “Database” link and then click on the “SQL Server Products” section to view the details of the “SQL Servers”:

SQL Server Products

13. In this case, on my laptop, I have 2 SQL Server instances. One is running SQL Server 2012 and the other is running on SQL Server 2008 R2. There’s a pie chart showing the distributions of versions:

SQL Server Discovery

14. Click on the “Generate Detailed SQL Server Reports” link to export the details to Excel and Word documents.

Generate Detailed SQL Server Reports

15. The detailed reports will then get exported to the MAP folder in the “My Documents” folder:

The Reports are saved in the MAP folder

16. Review the reports as you wish.