Posted in Development, Performance Tuning

How to collected inventory for SQL Server using Microsoft Assessment and Planning (MAP) tool

Hi friends,

We need to collect inventory for database migration as well as platform to set up environment for migration, development and testing.

Is there any tool from Microsoft to achieve this scenario?

Yes, we have Microsoft Assessment and Planning Tool (MAP) for it.

From BOL:

The Microsoft Assessment and Planning (MAP) Toolkit is an inventory, assessment, and reporting tool that helps you assess your current IT infrastructure and determine the right Microsoft technologies for your IT needs. The MAP toolkit uses Windows Management Instrumentation (WMI), Active Directory Domain Services (AD DS), SMS Provider, and other technologies to collect data in your environment and inventories computer hardware, software, and operating systems in small or large IT environments without installing any agent software on the target computers.

In order for the MAP Toolkit to successfully connect and inventory computers in your environment, you have to configure your machines through WMI and also allow your firewall to enable remote access through WMI. In addition to enabling WMI, you need accounts with administrative privileges to access desktops and servers in your environment.

Download and Install MAP tool

You can download Microsoft Assessment and Planning Toolkit from http://go.microsoft.com/fwlink/?LinkId=313396

Once the MAP tool is installed SQL Server LocalDB gets installed by default.

Latest Version of MAP tool is 9.4

MAP 9.4 is updated to inventory, assess and report the SQL Server 2016 instances and components in the SQL Server assessment.

  1. After opening it Create inventory database which will be used to save the inventory data and statistics inside it when working with MAP tool.

1

2. Once you opened the MAP tool in the Create or Select a Database to Use dialog click Create an inventory database and type student as a new database name and click OK.

Student database is created successfully and message will also appears for this.

3. To enable the remote administration open the Command Prompt as Run As Administrator.

Type the following command and press enter, you will get the message OK in response.

netsh advfirewall set currentprofile settings remotemanagement enable

 4. This is home screen for MAP tool which allow the different options to collect the inventory data for Server, Desktop, Cloud etc.

2

MAP toolkit uses WMI, Active Directory Domain Services, SQL Server Commands, VMware web services, Oracle client, PowerShell  and SSH with remote shell commands to collect the inventory information from the target network machines. It is not required to have the MAP tool on the target machine to collect the inventory.

5. Click on Database option from left side pane

6. Now click on Collect Inventory Data link, an Inventory and Assessment Wizard window opens.

3

You can choose the scenarios on your choice to collect the inventory with various scenarios available:

  • Windows-based computers
  • Linux-based computers
  • VMware computers
  • Exchange Server
  • Active Devices and users
  • Forefront Endpoint Protection Server
  • Lync Server
  • SQL Server
  • SQL Server with database details
  • Windows Azure Platform Migration
  • MySQL, Oracle, and Sybase

7. Choose option SQL Server and SQL Server with Database Details as Inventory Scenario.

8. Click Next.

Discovery Methods tab appears which is used to discover computers. Various discovery methods available for selection of method-

  • Active Directory® Domain Services
  • Windows networking protocols
  • System Center Configuration Manager
  • Scan an IP address range
  • Manually enter computer names and credentials
  • Import computer names from a file

 

9. Select option Manually enter computer names and credentials.

4
Click Next.

10. Now in All Computer Credentials page, click Create.

11. In the Account Entry dialog, type the username for the local computer user

12. In both the Password and Confirm Password fields, type the password for the local computer user then click Save

13. In the All Computer Credentials Page, click

14. Click Next in the Credentials Order

15. In the Enter Computers Manually page, click Next.

16. Now, in Specify computers and credentials page enter your computer name and Click on Add.

You can add multiple computer names here as per your requirement.

17. Click on Save

5

18. Then Clicking on Next button will take you on Summary section.

19. In Summary section you can see the summary of your selection including any detected errors.

620. Click on Finish

21. Inventory and Assessment Windows appears on screen where you can see the progress of Data collection.

7

How to generate reports to get SQL Server DB information?

 To generate the report, we will click on SQL Server from Database scenario.

In the option section two types of reports can be generated:

  1. Generate SQL Server Assessment Report
  2. Generate SQL Server Database Detail Report

Click on any report and save to your local disk.

8

These two reports shows different type of information which are like –

  1. SQL Server Assessment Report

This report shows information about Database Instances and Components.

2. SQL Server Database Detail Report

This report provides an information of all the SQL Server database engine instances and number of databases discovered on your network.

So, in this way we can use MAP tool to collect the inventory and statistics.

Advertisements
Posted in Performance Tuning, Uncategorized

SQL Server Execution Plan Operator – Table Scan

In my previous blog we discussed about the different format of SQL Server execution plan. Now in upcoming blogs we will discussed about the most commonly seen operators we see when execution plan generates. In this post I will start the easy one that is Table Scan operator.

As with the name of operator Table Scan it indicates that it returns the rows after scanning every row of a table.

When this operator appears in execution plan?

When there is no index present on the table then query optimizer has to scan every row of table to return the result set.

tablescan

In the returned result set data is retrieved after traversing data row by row.

When a table is having fewer rows and returning maximum rows then it’s faster for query optimizer to scan all rows rather than checking indexes.

That’s all for the day !!!

 

Posted in Development, Performance Tuning, Uncategorized

SQL Server Execution Plan formats

Execution plan helps in knowing how a query gets executed and troubleshooting a query which is performing poor.

In SQL Server execution plan can be of 2 types:

  1. Estimated Execution plan
  2. Actual Execution plan

Execution plan can be viewed in 3 different formats:

  • Text format
  • XML format
  • Graphical format

We can choose the format on depending what level of details we want to see.

Let’s discuss these format one by one.

  1. Text format

Execution plan in text format can be seen using these 3 methods.

  • Showplan_ALL
  • Showplan_Text
  • Statistics Profile

Showplan_ALL : Its shows the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_ALL ON;

1

Once we analyze our query plan we can turn off this option by executing below query so that our subsequent queries will not get affected from this.

SET SOWPLAN_ALL OFF;

Showplan_Text: It’s also works with the estimated execution plan of the query. To enable it we need to execute the query:

SET SHOWPLAN_TEXT ON;

2

SET SHOWPLAN_TEXT OFF;

Statistics Profile: It works with the actual execution plan. We need to execute the below query to enable it:

SET STATISTICS PROFILE ON;

3

SET STATISTICS PROFILE OFF;

2 XML format plans

Execution plan can be seen in XML format by using following two methods:

Showplan XML

Statistics XML

Statistics XML: It is used to generate the execution plan in XML format. After clicking on XML link a graphical execution plan gets opened in a new window. We can save that graphical plan to our local disk with extension .sqlplan.

4

Showplan XML: It’s also generates the XML plan and only one result set appears in outcome with XML link.

5

3. Graphical Plan

This is the most easiest and readable option to generate the execution plan. It also has two option for generating execution plan:

Estimated execution plan

Actual execution plan

To enable the estimated execution plan we can press the shortcut key Ctrl +L. Also, we can navigate to Query tab and select Display Estimated Execution Plan.

Estimated execution plan is beneficial when we have complex query and not aware how much it will take for complete execution in such situation we can use this as this doesn’t execute the query to generate the plan.

6

Similarly, actual execution plan we can be enabled by using shortcut key Ctrl+M.

We can also activate it by selecting option Include Actual Execution Plan from Query menu tab.

That’s all for the day folks.

 

Posted in Development, Performance Tuning

SQL Server – Cleaning Procedure Cache for a particular database

HI friends, most of the time we used DBCC FREEPROCCACHE to clear the procedure cache, clears the specific plan from cache by specifying plan handle or SQL handle.

DBCC FREEPROCCACHE does not require any parameters.

To clear the plan cache we just need to execute below query –

DBCC FREEPROCCACHE

Whenever we execute this command, it will results as message like –

“DBCC execution completed. If DBCC printed error messages, contact your system administrator.”

2

If you don’t want any information message to be appear after execution then execute the query in this way –

DBCC FREEPROCCACHE WITH NO_INFOMSGS

But when we required to clean the procedure cache for a particular database then in that case we use another DBCC command – DBCC FLUSHPROCINDB.

This command work is identical to FREEPROCCACHE only difference is that it accepts the parameters and can be executed for a single database only.

Suppose I want to clear the procedure cache for my database ‘Test_kapil’ then I can do it in this way-

SELECT name as DBname, dbid as DatabaseID FROM sys.sysdatabases WHERE name like ‘%Test_Kapil%’

First we will find out the DBID from the system table using above query.

After getting DBIS from that we will pass that  DBID to command.

DBCC FLUSHPROCINDB(6)

1

This is how we can also clean the procedure cache for a particular database.

Happy Learning 🙂

Posted in Performance Tuning, Uncategorized

How Client Statistics helps in improving performance in SQL Server

Client Statistics is SQL Server data tool which is very helpful in determining the statistics that how much data received from server to the client side. In other words, client statistics helps in analyzing the traffics load like packets/bytes sent and received at client – server side. When we run a script or query in T-SQL editor, we can enable Client statistics to collect statistics like application profile, time statistics and network statistics which help in checking the efficiency of the script.

Client statistics are grouped into 3 categories:

  1. Query Profile Statistics
  2. Network Statistics
  3. Time Statistics

Let’s briefly look out about the categories of Client Statistics which gives you a picture what these categories show.

Query Profile Statistics

Query profile statistics section shows the information about the query execution like:

How many INSERT, UPDATE, DELETE and SELECT statements are executed in query editor window?

How many rows appeared in query output combining rows of multiple SELECT statements?

How many transaction occur in query window?

Network Statistics

Similarly, network statistics shows the data about size of data send and receive from server to client in form of Packets and Bytes.

Time Statistics

Time statistics shows the data about time taken by server in sending and receiving data.

How to enable Client Statistics?

When you have to work with Client Statistics first we need to enable it using SSMS.

Method 1

Press Shift + Alt + S

CS_1

Method 2

Move to the Query option in Menu Option and click on Include Client Statistics.

CS_2

Method 3 –

Right click in the query window and select Include Client statistics

CS_3

Now let’s put some extra light on Client Statistics internals. As per below screenshot I have executed two SELECT statement in a single query editor pane. As the query gets executed one more tab ‘Client Statistics’ appears in output window.

CS_4

As we can see the screenshot there are some different color Arrows signs (Black, Green and Red) which makes the looks more significant. Green sign signifies improvement in statistics while Red sign signifies degradation in statistics. Black arrow signifies that the value is unchanged from previous run.

Let’s execute the query one more time to get the picture clearer.

CS_5

Every time when a query executed a Trail tabs gets added with latest one in descending order. Average tab is the average of all Trail tab values.

There is also a row with name ‘Client Execution Time’ which shows at which time the query gets executed in T-SQL editor when Client Statistics was enabled.

Now we will see the calculation of each category one by one:

Query Profile Statistics

We don’t have any INSERT, UPDATE, DELETE statement in query window so value of ‘Number of INSERT, UPDATE and DELETE statement’ is 0.

Value of ‘Rows affected by INSERT, UPDATE and DELETE statement’ is 0 as no such statements are present.

Value of ‘Number of SELECT statements’ is 2 as you can see we have 2 select statement in query window.

Value of ‘Rows returned by SELECT statement’ is 4 which is sum of output of each individual Select statement.

Value of ‘Number of transaction’ is 0 as no transaction is happening currently in query window.

 

Network Statistics

Value of ‘Number of server roundtrips’ is 1 as only a single time we sent the request to the server and received a reply.

Value of ‘Number of TDS packets sent’ is 1 which represents the number of packets sent to server.

Value of ‘Number of TDS packet received’ is 1 which represent the number pf packets received from the server.

Value of ‘Number of bytes sent’ is 2 which represent the number of bytes sent to server in TDS packets.

Value of ‘Number of bytes received’ is 2 which indicates the number of bytes received from server in TDS packets’

Time Statistics

Value of ‘Client processing time’ is 20 which indicates the time spent by the driver in processing.

Value of ‘wait time on server replies’ is 30 which indicates the waiting time for the driver spent to get the reply from the server.

Value of ‘Total execution time’ is sum of values of Client processing time + Wait time on server replies (20 + 30 = 50).

This is simple case of client statistics. To get some interesting information now let’s disable the Client statistics from the tab and execute the query one more time. Again enable the Client statistics from any method which is mentioned above and execute the query one more time.

Now one more Trail tab is added and also Red arrows increases. You will observe that values of Trail1 and Trail2 is almost identical while value Trial3 is totally different.

CS_6

Why Trail3 values are different as we run the query only a single time after enabling the Client statistics?

The answer to this is – Trail3 value is the sum of number of execution of query when Client statistics was disabled and sum of the values when Client statistics was enabled again. Means, whenever a query gets executed it will added all the previous values till client statistics is enabled.

Values in Query profile statistics is showing the sum of previous execution value including query execution after enabling.

Value of Number of SELECT statement is 4 [2(Query executed when statistics was disabled) + 2 (Query executed when statistics enabled again)]

Same is with value of Rows returned by SELECT statement i.e. 34 [17 (When client statistics was disabled) + 17 (When query statistics enabled again)]

In Network statistics also values are showing values of sum of query execution when client statistics was disabled and sum of values when client statistics was enabled. You also see Red arrow signs which indicates the degradation in statistics when compare with Trial2.

Same calculation is with Time statistics.

How to reset/clear the Client statistics data?

Go to the Query tab >> Reset Client Statistics.

CS_7

In summary, client statistics also helps you in analyzing the script performance by monitoring the response time of query, including client server execution time, data send in packets/bytes.

That’s all folks. Hope you will like it.