Looking at the actual execution plan is one of the most used performance troubleshooting techniques. Having information on elapsed CPU time and overall execution time, together with session wait information in an actual execution plan allows a DBA to use showplan to troubleshoot issues away from the server, and be able to correlate and compare different types of waits that result from query or schema changes.
A few months ago we had introduced exposed in SSMS some of the per-operator statistics, such as CPU and elapsed time per thread. More recently, we have introduced overall query CPU and elapsed time tracking for statistics showplan xml (both in ms). These can be found in the root node of an actual plan. Available using the latest versions of SSMS v17, when used with SQL Server 2012 SP4, SQL Server 2016 SP1 and SQL Server 2017. For SQL Server 2014 it will become available in a future Service Pack.
Also be sure to check out Geoff Patterson’s Connect item asking that the execution plan results show the top ten waits in descending order rather than ascending order. That’s the appropriate ordering in my mind: show me the most important things first.
Local Debug enables you to debug your C# code behind, step through the code, and validate your script locally before submitting to ADLA.
Use command ADL: Start Local Run Service to start local run service and set a breakpoint in your code behind, then click command ADL: Local Debug to start local debug service. You can debug through the debug console and view parameter, variable, and call stack information.
Click through to see the other improvements.
OStress is a Microsoft tool comes with RML utilities package and it uses to stress SQL Server. This is especially useful when you want to troubleshoot SQL Server while SQL Server is under heavy load.
It is a free tool for SQL Server developers and DBAs. It is designed to assist with performance stress testing of T-SQL queries and routines. The tool automatically collects metrics to help you determine whether your queries will perform under load, and what kind of resource strain they put on a server. In short, it also allows putting a serious load on your database.
OStress isn’t the easiest thing in the world to set up, but it works well.
Meet Bhagdev has a couple MacOS-related announcements for SQL Server. First, the SQL Server team has released command line tools:
We are delighted to share the production-ready release of the SQL Server Command Line Tools (sqlcmd and bcp) on macOS El Capitan and Sierra.
The sqlcmd utility is a command-line tool that lets you submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing.
The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
Second, there’s a new ODBC driver available:
Azure AD support – You can now use Azure AD authentication (username/password) to centrally manage identities of database users and as an alternative to SQL Server authentication.
Always Encrypted support – You can now use Always Encrypted. Always Encrypted lets you transparently encrypt the data in the application, so that SQL Server will only handle the encrypted data and not plaintext values. Even if the SQL instance or the host machine is compromised, an attacker gets ciphertext of the sensitive data.
Table Valued Parameters (TVP) support – TVP support allows a client application to send parameterized data to the server more efficiently by sending multiple rows to the server with a single call. You can use the ODBC Driver 13.1 to encapsulate rows of data in a client application and send the data to the server in a single parameterized command.
Multi-platform is the catchword of the day. If you’re a MacOS user, this might be a portent of things to come.
Community has completed the translations for VScode SQL Server extension for six languages: Brazilian, French, Japanese, Italian, Russian, and Spanish.
We still need help with other languages. If you know anyone with language expertise, refer them to the Team Page.
Your contributions are valuable and will help us improve the product in your languages. We hope to continue working with the community in future projects.
Hit up the Team Page link to learn more about how to contribute.
There is graph support in the next version of SQL Server. The private preview page states
SQL Graph adds graph processing capabilities to SQL Server, which will help you link different pieces of connected data to help gather powerful insights and increase operational agility. Graphs are well suited for applications where relationships are important, such as fraud detection, risk management, social networks, recommendation engines, predictive analysis, dependence analysis, IoT suites, etc.
Initially, SQL Server will support CRUD graph operations and multi-hop graph navigation, and the following functionality will be available in the private preview:
- Create graph objects, that is, nodes to represent entities and edges to represent relationships between any 2 given nodes. Both Nodes and Edges can have properties associated to them.
- SQL language extensions to support join free, pattern matching queries for multi-hop navigation
Kennie Pontoppipidan wrote a great blog post on where to find out more information.
Click through for more links to interesting resources.
After I dusted off my PowerShell 2.0 documentation, I got my script written and started testing. I processed several folders and their files before I received the following error while running my PowerShell script:
Invoke-Sqlcmd : String or binary data would be truncated.
The statement has been terminated.
At line:127 char:36
+ … MyResults = Invoke-Sqlcmd -ServerInstance $ServerName `
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Interesting. I added some Write-Host statements for troubleshooting and found the offending entry. Like any good programmer, I tested my stored procedure call in SQL Server Management Studio (SSMS) to make sure it really was a SQL Server error and guess what? It worked just fine! No errors what so ever. WTH?! This is where my tunnel vision sets in. If it works in SSMS but not in PowerShell, then PowerShell must be the problem, right? Well, sort of.
Read on for the solution.
The heart of his critique is this: data science is changing very fast, and any tool that you learn will eventually become obsolete.
This is absolutely true.
Every tool has a shelf life.
Every. single. one.
Moreover, it’s possible that tools are going to become obsolete more rapidly than in the past, because the world has just entered a period of rapid technological change. We can’t be certain, but if we’re in a period of rapid technological change, it seems plausible that toolset-changes will become more frequent.
The thing I would tie it to is George Stigler’s paper on information theory. There’s a cost of knowing—which the commenter notes—but there’s also a cost to search, given the assumption that you know where to look. Being effective in any role, be it data scientist or anything else, involves understanding the marginal benefit of pieces of information. This blog post gives you a concrete example of that in the realm of data science.
New SQL command line tools for Linux: We’ve created Linux-native versions of your favorite SQL command line tools such as sqlcmdand bcp and sqlpackage and also added the new mssql-conf tool that lets you configure various properties for the SQL Server instance on Linux (e.g., SA password, TCP port and collation).
New versions of SSMS, SSDT and SQL PowerShell: We have released updated versions (v17.0 RC1) of our flagship SQL Server tools including SQL Server Management Studio (SSMS), Visual Studio SQL Server Data Tools (SSDT) and SQL PowerShell with support for the SQL Server v.Next on Windows and Linux.
They also have a plugin for Visual Studio Code, which can be helpful if you’re running on Linux.
With Windows Server 2016 just been released, now is the perfect time to build an Azure VM with SQL Server 2016 on Windows Server 2016. In a matter of minutes you can be playing and learning both platforms. Below I will document the steps I took to build the VM along with the additional software I installed. This is a fully-loaded VM that I use for demo’s and to build small projects:
(Software updates as of 11/4/2016)
There’s a lot of good software here. And Java.