'Being able to understand and analyze execution plans is an important and beneficial skill for SQL Server database administrators and developers alike. An execution plan documents the estimated cost of a query, the indexes used, and the operations performed. All of this information is vitally important in attempting to speed up a slow performing query.
This article is part of a three part series on graphical execution plans. Part 1 explains what execution plans are and discusses the differences between estimated and actual plans. Part 2 shows how to create both estimated and actual execution plans. Lastly, Part 3 takes a look at a simple graphical execution plan, and discusses several of the most common operators used in a query.'...
http://www.sqlservercentral.com/articles/Execution+Plans/105771/?utm_source=SSC&utm_medium=pubemail
'One question constantly comes up; What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to?
What Do Your Plans Cost?
I have a question right back at you. What do your plans currently cost? Let’s say, for argument’s sake, that all your plans have an estimated cost (and all plan costs are estimates, let’s please keep that in mind, even on Actual plans) value of 3 or less. Do you need to adjust the cost threshold in this case? Probably not. But the key is, how do you look at the costs for your plans? Unfortunately, there isn’t a property in a DMV that shows this value. Instead, we have to query the XML...'...
http://www.scarydba.com/2017/02/20/estimated-costs-queries/
'Today during the Visual Studio launch event, we announced that we’ve partnered with Redgate to include Redgate Data Tools in Visual Studio 2017. Redgate Data Tools includes three components that extend DevOps practices to SQL Server and Azure SQL databases and increase your productivity while doing database development.
Here’s a brief overview of each of them:
-
ReadyRoll Core allows you to develop, source control, and safely automate deployments of database changes alongside application changes. ReadyRoll Core is available in the Enterprise edition of Visual Studio 2017.
-
SQL Prompt Core offers advanced code completion for SQL. SQL Prompt Core is available in the Enterprise edition of Visual Studio 2017.
-
SQL Search lets you find SQL objects fast and easily explore across databases. SQL Search is available in all editions of Visual Studio 2017.'...
https://blogs.msdn.microsoft.com/visualstudio/2017/03/07/redgate-data-tools-in-visual-studio-2017/
'Problem
Have you ever had the need to get an inventory of all of the logins that have been created on a bunch of SQL servers? I have and it wasn't pretty. I was trying to deploy the System Center Operations Manager client across a datacenter and if you're familiar with that product it needs a SQL login to monitor the databases in a SQL instance. After connecting to each of these servers with SQL Server Management studio, clicking the Logins box and noting which logins existed on all of these servers I gave up and decided to script it with PowerShell.
Solution
To query any SQL Server database from PowerShell you need to have the SQLPS module installed along with a few supporting packages to make your life easier. I'm going to be focusing on the SQL Server 2012 packages in this tip. You will need...'...
https://www.mssqltips.com/sqlservertip/4181/inventory-sql-logins-on-a-sql-server-with-powershell/
'In-Memory OLTP, also known as Hekaton, can significantly improve the performance of OLTP (Online transaction processing) database applications. It improves throughput and reduces latency for transaction processing, and can help improve performance where data is transient, such as in temporary tables and during ETL (Extract Transfer and Load). In-Memory OLTP is a Memory-optimized database engine that is integrated into the SQL Server engine, and optimized for transaction processing.'...
https://www.simple-talk.com/sql/database-administration/migrating-disk-based-table-memory-optimized-table-sql-server/
'This post was authored by the Windows Server and SQL Server Product Teams.
Today we are announcing the availability of Windows Server Premium Assurance and SQL Server Premium Assurance. With Windows Server Premium Assurance and SQL Server Premium Assurance, you receive critical and important security updates and bulletins for an additional six years after the end of Extended Support. This means you can get up to 16 years of total support, an extension from the usual five years each of Mainstream and Extended support.
These new offerings are designed to provide flexibility by enabling legacy applications to continue running without disruption as you modernize applications and infrastructure, or move to cloud. Some of the key benefits include:
-
Sustain critical and legacy applications for six additional years
-
Stay compliant with regulatory requirements
-
Maintain security on systems that may not be ready to update'...
https://blogs.technet.microsoft.com/hybridcloud/2017/03/01/now-available-windows-server-premium-assurance-and-sql-server-premium-assurance/?WT.mc_id=AID522292_EML_4926145
'One of the most sought after blog is around Wait Stats when it comes to performance troubleshooting. The place to bookmark would be – SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28. I get queries from a number of you and have been fortunate in the past for sending me such information because it becomes a great learning experience for me too. At the same time, your problems are getting solved too. It becomes a great win-win situation. Let us learn about Extended Event wait_info in this blog post.
In a recent troubleshooting exercise, one of the senior DBA inside a company asked me how to understand and know the waits related to a single session or a query they are running. This was a great challenge and I sent them a script a week later. I thought it is also worth to share here as part of the blog for future reference.
I wondered where to start and realized the best way to find this information is used Extended Events. Here is the script that I came up with:'...
https://blog.sqlauthority.com/2016/05/28/sql-server-finding-waits-session-extended-event-wait_info/
'SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.
But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].
Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like...''...
https://sqlwithmanoj.com/2016/11/19/now-create-or-alter-stored-procedure-function-view-trigger-with-sql-server-2016/