This returns a single row, single column result set that contains an XML value that is clickable in the SSMS output grid. Here is a sample query that shows how it works:ĬONVERT ( nvarchar ( max ), p. It is based on a trick that I learned from SQL Server MVP Adam Machanic ( blog). Luckily, there is a simple workaround that, somewhat ironically, uses XML support in the SQL Server query engine. So far, so good.īut when I tried to copy the plan from the SSMS grid output and paste the XML into a new window, I quickly found that for larger plans, the XML text becomes truncated. This avoids some problems with using the xml data type. Even though the plan is stored in its XML representation, the data type of this column is nvarchar(max). The plans are exposed in the sys.query_store_plan view, in the query_plan column. Among other things, it captures and stores query plans for queries that execute on a SQL Server instance, so that they can be reviewed later, i.e. This also allows you to send the empty notebook to a remote client, ask to run one or more queries and send back the notebook including the results for you to review.Query Store is a great new feature in SQL Server 2016 and SQL Azure Database. #Azure data studio execution plan codeThis is nice way to incorporate the code and results in a single file, that you can review offline later. What you obtain is a Jupyter Notebook that you can open in Azure Data Studio: notebookOutputPath "diagnostic-notebook.ipynb" diagnosticScriptPath "C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql" ` Once you decide which file to use, you can pass it to the script: SQLServerDiagnosticQueries_AzureSQLDatabase_201901.sql SQLServerDiagnosticQueries_2019_201901.sql SQLServerDiagnosticQueries_2017_201901.sql SQLServerDiagnosticQueries_2016_201901.sql SQLServerDiagnosticQueries_2016SP2_201901.sql SQLServerDiagnosticQueries_2014_201901.sql SQLServerDiagnosticQueries_2012_201901.sql SQLServerDiagnosticQueries_2008_201901.sql SQLServerDiagnosticQueries_2008R2_201901.sql Get-ChildItem "$dbatoolsPath\bin\diagnosticquery" | Select-Object NameĬ:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777 $dbatoolsPath = Split-Path -parent (Get-Module -ListAvailable dbatools).path You will find all available scripts in the module directory of dbatools: Dbatools includes the latest version of the diagnostic scripts already, so you just need to choose which flavor you want to use. In order to use the script, you need to provide the path to the file that contains the diagnostic queries and the path where the new Jupyter Notebook should be generated. # Parses the diagnostic script and breaks it into individual queries,įunction Invoke-DbaDiagnosticQueryScriptParser " | Out-File –FilePath $notebookOutputPath –Append # create-diagnostic-notebook.ps1 -diagnosticScriptPath "C:\Program Files\WindowsPowerShell\Modules\dbatools\0.9.777\bin\diagnosticquery\SQLServerDiagnosticQueries_2019_201901.sql" -notebookOutputPath "diagnostic-notebook.ipynb" # and generate a Jupyter Notebook to run in Azure Data Studio # Purpose: take the diagnostic queries from Glenn Berry The function returns a list of queries that I can use to generate the Jupyter Notebook: #Azure data studio execution plan downloadThe part that can help me is not a public function available to the user, but I can still go to GitHub and download the internal function Invoke-DbaDiagnosticQueryScriptParser for my needs. Unsurprisingly, dbatools comes to the rescue: André Kamman added a cmdlet that downloads, parses and executes Glenn Berry’s diagnostic queries and added the cmdlet to dbatools. Fortunately, PowerShell is my friend and can do the heavy lifting for me. Obviously, I don’t want to create such a Notebook manually by adding all the code cells one by one. For this particular usage scenario, the first thing that came to my mind is running the diagnostic queries by Glenn Berry in a Notebook. It helps when you have to establish a workflow in your demos that the attendees can repeat at home when they download the demos for your presentation.įor troubleshooting scenarios, the interesting feature is the ability to include results inside a Notebook file, so that you can create an empty Notebook, send it to your client and make them run the queries and send it back to you with the results populated. This is a very interesting feature that opens new possibilities, especially for presentations and for troubleshooting scenarios.įor presentations, it is fairly obvious what the use case is: you can prepare notebooks to show in your presentations, with code and results combined in a convenient way. The March release of Azure Data Studio now supports Jupyter Notebooks with SQL kernels.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |