SQL Server Performance Troubleshooting -6 Collect Performance Data Using SQLDiag Tool

Hi,

I will continue to explain how to Troubleshoot Performance problems of SQL Server in this Article Series.

SQLDiagCofiguration

 

Read previous article before this.

 

There are various tools to identify the slowness and problems that occur in SQL Server Databases. Some of these tools come with SQL Server, while some of the tools you need to install extra. These tools are as follows.

  1. SQL Server Profiler
  2. Performance Monitor ( Perfmon )
  3. SQLDIAG
  4. PSSDIAG
  5. RML Utilities
  6. Performance Analysis of Logs (PAL)
  7. SQL Nexus

 

I will collect performance data on a sample database that I created with SQLDIAG tool. Well, What performance data can we collect with SQLDIAG?

With SQLDIAG you can collect the following data.

 

  1. TOP SQL according to CPU, Memory and IO
  2. Windows and SQL Server Counter (Disk, Memory read / write) values.
  3. SQL Server Instance Configuration.
  4. Windows Event logs and SQL Server Logs.

 

Now let’s go to create a database named TestDatabase and create a table named testtable on on my Local computer, in my instance named MYTESTINSTANCE. I will create sample transactions in my TestDatabase database.

The sample script of the TestDatabase database and the TestTable table are as follows.

 

use master
go
create database TestDatabase
go
use TestDatabase
create table testtable(
col1 int identity(1,1) not null,
col2 datetime null
)
use TestDatabase
alter table testtable add constraint DF_testtable_col2 default (getdate()) for col2
go



  

When you run this script successfully, the database named TestDatase and the table named testtable should be created in the local instance MYTESTINSTANCE as follows.

 

1

 

Create a parameter file called Configuration.xml as input for the SQLDIAG tool to read the required performance values.  In this parameter file, I specified the addition of the above parameters with arrow as follows. You can download SQLDiag Configuration Tool by clicking this link.

 

 

3-1

 

 

I will appoint this Configuration.xml file as an input to the SQLDIAG tool, so I move this file under C: \ Program Files \ Microsoft SQL Server \ 100 \ Tools \ Binn.

The configuration.xml file has the same path as the SQLDIAG.exe tool as follows.

 

3

 

Now, with the SQLDIAG tool, I will assing the Configuration.xml file as input and collect performance data for the TestDatabase database.

Open Command Prompt and go to the path where the SQLDIAG tool is located.

 

4

 

After going to the path where the SQLDIAG path tool is,  start the process of collecting performance data for 15 minutes, giving the Configuration.xml file as input and the output file to D: \ SQL_DIAG_OUTPUT path like following.

 

SQLDIAG Code:

SQLdiag /I Configuration.xml /O "D:\SQL_DIAG_OUTPUT" /E +00:15:00

5

 

 

When you run the above code, after waiting for a while it needs to indicate that process is started Data collection as follows

 

6

 

I have run some example Transaction like following to see in SQLDiag.

8-2

 

When you go to the D: \ SQL_DIAG_OUTPUT path, where you output the output file as follows, you can see that many trace files have been created as follows:

 

12

 

When you open the Command prompt after 15 minutes, you can see that data collection is completed as follows.

 

 

11

 

In my next article, I will read the data that i gathered in this article with the RML Utility tool and display costly queries and general information about the database in a report.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *