{"id":39131,"date":"2020-04-24T06:00:56","date_gmt":"2020-04-24T13:00:56","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/premier-developer\/?p=39131"},"modified":"2020-04-20T10:33:55","modified_gmt":"2020-04-20T17:33:55","slug":"a-how-to-guide-for-pssdiag-and-sql-nexus-creating-a-pssdiag-package","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/a-how-to-guide-for-pssdiag-and-sql-nexus-creating-a-pssdiag-package\/","title":{"rendered":"A How-To Guide For pssdiag and SQL Nexus: Creating a pssdiag Package"},"content":{"rendered":"<p>In this post, Sr. Consultant <a href=\"https:\/\/www.linkedin.com\/in\/kendalvandyke\/\">Kendal Van Dyke<\/a> explores pssdiag and SQL Nexus, free tools from Microsoft that help you capture and analyze SQL Server performance data.<\/p>\n<hr \/>\n<p>This is part 2 in a series of blog posts about pssdiag and SQL Nexus, free tools from Microsoft that help you capture and analyze SQL Server performance data.<\/p>\n<p>The previous blog post provided a general overview the process for using pssdiag and SQL Nexus. In short, the steps are:<\/p>\n<ul>\n<li>Step 1: Create a pssdiag package<\/li>\n<li>Step 2: Run pssdiag capture<\/li>\n<li>Step 3: Import into SQL Nexus<\/li>\n<li>Step 4: Reporting\/Analysis<\/li>\n<\/ul>\n<p>This blog post covers Step 1: Creating a pssdiag package. As a reminder, this step runs on the SQL Nexus Server.<\/p>\n<h2>Creating a pssdiag package<\/h2>\n<p>The first step to running a pssdiag capture is to create a pssdiag package using the Pssdiag Configuration Manager. Start by <a href=\"https:\/\/github.com\/microsoft\/DiagManager\/releases\">downloading the latest release from Github<\/a>. There is no installer; simply extract the contents of the ZIP file and double click DiagManager.exe to launch the utility.<\/p>\n<h2>Enter Instance Information<\/h2>\n<p>First, enter the source server\u2019s machine name, instance name, platform, and version of SQL Server:<\/p>\n<p><img decoding=\"async\" width=\"1254\" height=\"781\" class=\"wp-image-39132\" src=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-40.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-40.png 1254w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-40-300x187.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-40-1024x638.png 1024w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-40-768x478.png 768w\" sizes=\"(max-width: 1254px) 100vw, 1254px\" \/><\/p>\n<p>Use the following charts to determine which values to use for Machine Name and Instance Name based on where you plan to run pssdiag:<\/p>\n<h3>Standalone Instance<\/h3>\n<table>\n<tbody>\n<tr>\n<td><strong>pssdiag location<\/strong><\/td>\n<td><strong>Machine Name<\/strong><\/td>\n<td><strong>Instance Name<\/strong><\/td>\n<td><strong>Instance<\/strong><\/td>\n<td><strong>Notes<\/strong><\/td>\n<\/tr>\n<tr>\n<td rowspan=\"3\">SQL Server<\/td>\n<td rowspan=\"3\">.<\/td>\n<td>*<\/td>\n<td>All<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>mssqlserver<\/td>\n<td>Default<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>{instance name}<\/td>\n<td>Named<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td rowspan=\"3\">Remote machine<\/td>\n<td rowspan=\"3\">FQDN for SQL Server<\/td>\n<td>*<\/td>\n<td>All<\/td>\n<td rowspan=\"3\">Trace\\Xevent data written to local disk on SQL<\/td>\n<\/tr>\n<tr>\n<td>mssqlserver<\/td>\n<td>Default<\/td>\n<\/tr>\n<tr>\n<td>{instance name}<\/td>\n<td>Named<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3>Failover Cluster Instance<\/h3>\n<table>\n<tbody>\n<tr>\n<td><strong>pssdiag location<\/strong><\/td>\n<td><strong>Machine Name<\/strong><\/td>\n<td><strong>Instance Name<\/strong><\/td>\n<td><strong>Instance<\/strong><\/td>\n<td><strong>Notes<\/strong><\/td>\n<\/tr>\n<tr>\n<td rowspan=\"6\">Active Node<\/td>\n<td rowspan=\"3\">.<\/td>\n<td>*<\/td>\n<td>All<\/td>\n<td rowspan=\"3\">Captures data from all nodes\n(this may capture more than intended!)<\/td>\n<\/tr>\n<tr>\n<td>mssqlserver<\/td>\n<td>Default<\/td>\n<\/tr>\n<tr>\n<td>{instance name}<\/td>\n<td>Named<\/td>\n<\/tr>\n<tr>\n<td rowspan=\"3\">SQL Server Network Name<\/td>\n<td>*<\/td>\n<td>All<\/td>\n<td rowspan=\"3\"><\/td>\n<\/tr>\n<tr>\n<td>mssqlserver<\/td>\n<td>Default<\/td>\n<\/tr>\n<tr>\n<td>{instance name}<\/td>\n<td>Named<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><em>Note: pssdiag should always run on the active node for Failover Cluster Instances<\/em><\/p>\n<h2>Choose Scenario(s)<\/h2>\n<p>Next, select one of more checkboxes in the Scenario section. Enabling a checkbox turn on different options in the tabs to the right of the screen. Scenarios can be combined to capture more events.<\/p>\n<p><img decoding=\"async\" width=\"1247\" height=\"781\" class=\"wp-image-39133\" src=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-41.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-41.png 1247w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-41-300x188.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-41-1024x641.png 1024w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-41-768x481.png 768w\" sizes=\"(max-width: 1247px) 100vw, 1247px\" \/><\/p>\n<p>If you\u2019re not sure where to start, <strong>General Performance<\/strong> is usually a good place. This will enable an Extended Events Session or Profiler Trace (depending on what version of SQL you\u2019re targeting) capture of batches and RPC calls (+ additional events), perfmon counters related to SQL Server and Windows, and scripts which capture snapshots of DMVs and query activity.<\/p>\n<h2>Customize What to Capture<\/h2>\n<p>After selecting one or more scenarios, you can enable or disable capture of specific events by using the tabs on the right side of the screen.<\/p>\n<p><img decoding=\"async\" width=\"1244\" height=\"782\" class=\"wp-image-39134\" src=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-42.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-42.png 1244w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-42-300x189.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-42-1024x644.png 1024w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-42-768x483.png 768w\" sizes=\"(max-width: 1244px) 100vw, 1244px\" \/><\/p>\n<p><strong>*Important*: <\/strong>If you\u2019re not familiar with what each scenario does, make sure to pay close attention to what\u2019s enabled \u2013 some pssdiag scenarios can add a lot of overhead on an instance and potentially bring things to a screeching halt. Before running pssdiag against a production instance, try a test run on a non-production system so you know what the capture configuration you\u2019re creating does to performance.<\/p>\n<p><strong>Tip: <\/strong>For an extended duration (e.g. 1 hour or more) capture of pssdiag a good starting place is the \u201cGeneral Performance\u201d scenario plus these customizations:<\/p>\n<ul>\n<li>UNCHECK the capture checkboxes on the XEvent and Profiler Trace tabs.<\/li>\n<li>On the perfmon tab change the polling interval to 20 seconds.<\/li>\n<li>Leave everything else at the defaults<\/li>\n<\/ul>\n<h2>Save Capture Configuration<\/h2>\n<p>Click the save button and save your pssdiag package as a .ZIP file.<\/p>\n<p><img decoding=\"async\" width=\"1241\" height=\"777\" class=\"wp-image-39135\" src=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-43.png\" srcset=\"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-43.png 1241w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-43-300x188.png 300w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-43-1024x641.png 1024w, https:\/\/devblogs.microsoft.com\/premier-developer\/wp-content\/uploads\/sites\/31\/2020\/04\/word-image-43-768x481.png 768w\" sizes=\"(max-width: 1241px) 100vw, 1241px\" \/><\/p>\n<p>This completes the pssdiag package configuration steps.<\/p>\n<h2>Next: Run pssdiag capture<\/h2>\n<p>The next blog post in this series will cover how to run the pssdiag package you\u2019ve configured to capture SQL Server and Windows performance data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this post, Sr. Consultant Kendal Van Dyke explores pssdiag and SQL Nexus, free tools from Microsoft that help you capture and analyze SQL Server performance data.<\/p>\n","protected":false},"author":582,"featured_media":37840,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[8,128,129,1],"tags":[9725,12],"class_list":["post-39131","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data","category-performance","category-premier","category-permierdev","tag-databases","tag-sql-server"],"acf":[],"blog_post_summary":"<p>In this post, Sr. Consultant Kendal Van Dyke explores pssdiag and SQL Nexus, free tools from Microsoft that help you capture and analyze SQL Server performance data.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/39131","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=39131"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/39131\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/37840"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=39131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=39131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=39131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}