How to use SQLdiag and Pssdiag for SQL server 2008

Dear expertist,

right now I tried to use SQLdiag and Pssdiag to identify the bottlenect of our SQL server 2008, please gives some example on how to use this 2x tools.

What is the tools to combine the result from these 2 x tools ? The SQL Nexus tool  ?

I find the web site of SQL Nexus tool is not easy to understand, please show me some example on how to use it.

I want to easily identify the bottleneck of the SQL server 2008. By this tools is enought? Any other good experience to share?

What if I have use perfmon to collect some data but the file is too large for excel 2003 to open, perfmon on Window XP seems hard to open the perfmon result from Windows 2008 R2 as all the result seems overlap and I don't know how to separate the line.

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AnujSQL Server DBACommented:
sqldiag is a utility that ships with SQL Server, this was used previously by Microsoft SQL Server tech team to troubleshoot the customer performance problems now they released it to the public, this tool captures the SQL Server and Windows Performance counter details.  Where as the pssdiag is utility that you need to download from here (also included usage).

These tools are meant for gathering performance metrics, once started they will run background and capture various performance counters of the server. The counters mentioned can be added\removed by editing the XML configuration files.

SQL Server Nexus is a GUI reporting tool that generate report based on the performance counters captured by sqldiag and pssdiag. This has a various built in templates like wait stats blocking etc to find the bottlenecks.

Though to find the basic details this tool is enough, also I found PAL very interesting this tool that can analyze the windows performance counters and gives bottleneck details based on the threshold values(that can be customized). This is very handy for doing complete health check of your servers, I would recommend you this too.

In addition to the above you can also use, Powershell, SQL Server Profiler & windows performance counter, and DMVS, activity monitor to get the same details as these tools provide.

SQL Server 2008 has new feature performance data collector that allows you to find the bottleneck on resources like disk, memory and cpu. Based on the performance data it can generate reports.

0
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

 So you mean performance data collector is not designed to run by itself like SQL Server Nexus ? I find SQL Server Nexus  is very hard to setup, any quick start example you use ?

Any jump start example of using pssdiag  and sqldiag ?

IT seems that PAL need a lot of software to be install before using, not very good on production as large company don't like thrid party tools.

DBA100.
0
AnujSQL Server DBACommented:
Performance data collector is a new feature in SQL Server 2008 that gathers performance data from the server, Based on the these data you can view reports in SQL Server.  Where as SQL Nexus will not gather performance data this allows you to generate GUI drill down reports based on the data you collected using sqldiag utility.

How to Consolide data collection with SQLDIAG and analysing it all with SQLNexus -  This is a session presented by Christian Bolton at SQLBits VII.

Reg PAL: As i am aware you don't need any software to be installed for running PAL, all you need is PAL only.
Also, you will not run any of these third party tools in your Production SQL Server, you should run this on a remote machine.

PS: I will update you if i found any link regarding pssdiag.




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

I strongly believe that you are very helpful and I will wait for more update from you while I am setting up the Performance data collector.

BTW, I don't see any informaiton on how to remove Performance data collector, any you can find?

Other than "Consolidating data collection with SQLDIAG and analysing it all with SQLNexus", anything like Consolidating data collection with SQLDIAG and Pssdiag, analysing it all with SQLNexus ? Pssdiag is one of our company's tools, I think I can make use of it.

For PAL, this is what I can see from the requirement:

- PowerShell v2.0 or greater.
- Microsoft .NET Framework 3.5 Service Pack 1
- Microsoft Chart Controls for Microsoft .NET Framework 3.5

"Microsoft Chart Controls for Microsoft .NET Framework 3.5 will install new assemblies that contain the ASP.NET and Windows Forms Chart Controls."

I don't see we have the chart control installed. Right now our test environment and production enviornment is completely separated. So I think I can only run this tools against a staging platform.

By this :
"Where as SQL Nexus will not gather performance data this allows you to generate GUI drill down reports based on the data you collected using sqldiag utility."

Do you mean Performance data collector also use sqldiag to get data? please clarify.

DBA100.

0
marrowyungSenior Technical architecture (Data)Author Commented:
by this link:http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/

it seems don't show you how to customize the performanace collection counter and how to modify the report to report this change.

Any link for this?

DBA100.
0
AnujSQL Server DBACommented:
marrowyung, my comments are in lined.

-- I don't see any informaiton on how to remove Performance data collector, any you can find?
While setting up the data collector, it will ask for the retention period by default this is one meaning the data will be automatically cleaned out every day, this can be customized.

-- Other than "Consolidating data collection with SQLDIAG and analysing it all with SQLNexus", anything like Consolidating data collection with SQLDIAG and Pssdiag, analysing it all with SQLNexus ? Pssdiag is one of our company's tools, I think I can make use of it.

I am not sure you can consolidate sqldiag and pssdiag, to be honest i never tried it.

-- I don't see we have the chart control installed. Right now our test environment and production enviornment is completely separated. So I think I can only run this tools against a staging platform.

PAL is just a reporting tool, this needs to have windows performance counter data. To use PAL the following are the basic steps.
-  Install PAL on remote machine .
- Capture Windows performance counter of Server either in Server or in Remote machine.
- Feed this Performance Log to the PAL and it will guide you to the rest of the steps.

"Where as SQL Nexus will not gather performance data this allows you to generate GUI drill down reports based on the data you collected using sqldiag utility."

Performance data collector does not use sqldiag instead it uses set of  APIs, Stored Procedures and Views, SSIS package and SQL Server Agent. This article has the answer.



0
AnujSQL Server DBACommented:
You cannot customize the Performance data collection counters, this provides you only basic details like Server Activity, Disk and Query Statistics. If you are looking for advanced counters then certainly Performance data collector is not the tool for you, you should use sqldiag\pssdiag + SQL Nexus or PAL + windows counters.
0
marrowyungSenior Technical architecture (Data)Author Commented:
from this: http://sqlnexus.codeplex.com/
"SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
"
So I expected that this tools can analysis result from both tools, isn't it ?

"You cannot customize the Performance data collection counters,"

Yes, I can't find the way either, disappointed by that. From the graphic, I can't see verticle value, can't see how much is the value, how can I know it is still health or not?



0
marrowyungSenior Technical architecture (Data)Author Commented:
when running the installatoin of SQL Nexus, I see this error, what is that mean ?

I should just run the instllation file and that's it, right?

I can't see I need to run other installation file first, right?
Nexus-error.JPG
0
AnujSQL Server DBACommented:
Download from codeplex, run sqlnexus.exe file that's all, there is no installation files in it.
0
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

That error message above is the one I got when I do what you said, please suggest. I can't see I have the right software installed .

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Do it need VS2008 to be install in advance?
0
AnujSQL Server DBACommented:
You don't need VS2008, but do have latest .Net framework installed?
0
marrowyungSenior Technical architecture (Data)Author Commented:
The tools need to create a user database on the DB server in order to operate ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I see this page: http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&referringTitle=Home

Any way to customize the couter I am going to measure?  it doens't say this.

After the script runs, the SQLdiag will create report for Nexus to execute, right ?
0
AnujSQL Server DBACommented:
The batch files will capture all the required counters, StartSQLDiagTrace.cmd will capture basic events, where as StartSQLDiagDetailed_Trace will capture almost all the events, this is a detailed one but has performance overhead as it captures all the events.

Usually there is no need to customize these batch files, but you can customize it for capturing details for a specific server. This can be done by editing the corresponding XML file

After the script runs, SQLDiag will generate a trace file and you need to import this trace file to SQLNexus for generating reports. The trace file will be saved in the location specified in XML config file.
0
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

Nice, but the point is, how to modify the xml file?  I am not developer,

Any suggest only for this case? more than one place to add/remove and how to add the counter ?
0
AnujSQL Server DBACommented:
Luckley you don't need to do any coding  in it :-), its very easy, have to do find and replace only.

Edit the config file then substitute the server name <Machine name="My Server"> and <Instance name="My Instance">

Good luck!
0
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

 I just check that out each item, like "\Lazy writes/sec", only appear once.

This mean I can only add/remove one row only. But I need to know under which group the counter belongs to, right?

DBA100.

0
AnujSQL Server DBACommented:
Each counter will appears only once, Also if you are running detailed trace you don't need to add any counters to the list all most all counters will be listed there. You are good to go.




0
marrowyungSenior Technical architecture (Data)Author Commented:
anujnb:

Yeah.. but not really, let me test it next week.

you are helpful.

merry christmas and happy new year.

DBA100.
0
AnujSQL Server DBACommented:
Happy holidays....! Merry Christmas and Happy New Year!
0
marrowyungSenior Technical architecture (Data)Author Commented:
Right now I get a task to switch from 4k to 64k disk allocation unit, what counter you remmend me to check ? from the script, it seems only logical c:\ is here, I cna't see other drive.

As a result I have to add them one by one, right? what is the drive is J:\ and L:\ ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
but it seems this tools is not going to work on SQL server 2008 with SP3 and SP4? the article seems not apply for that anymore ?

how about SQL server 2014 with sP2, this tools still work for that?

what tools you recommend to check healthy problem on SQL 2014 with sP4, the one can automate the check and have the result send to a mailbox is better.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.