Unicode strings and implicit conversions

Cross post with http://aka.ms/sqlserverteam Recently we had an interesting customer question about a seemingly strange behavior (and perhaps not widely known) on implicit conversions to Unicode. Imagine you declare a non-unicode string variable, and when concatenating strings that seem to fit the variable declaration, you get a result that is trimmed, although the sum of…


When do conversions generate conversion warnings? And why are these bad?

Hello, A few days ago a colleague asked me why was he seeing implicit conversions in the execution plan, but no warnings of such sort were being issued. Warnings are seen in the plan if the conditions they affect occur, and then are surfaced in the <Warnings> element of SHOWPLAN, either as affecting Cardinality Estimates…

0

SQL Swiss Army Knife #13 – Exploring the plan cache – Part 2

Hello all, This is long overdue, but here it is, the follow up on plan cache exploration queries part 1 post. There are many “hidden” gems inside a query plan XML that allow us to know our workloads in greater and better detail. Here are a few more xqueries snippets for various purposes. These are…


SQL Swiss Army Knife #13 – Exploring the plan cache – Part 1

Hello all, In the last few months I’ve been creating a few snippets of code to extract as much useful information from the plan cache as possible. Why to do this? We at Microsoft GBS PFE deliver a type of service called a SQL Performance Tuning and Optimization Clinic, which is directed at finding and…


Table variables and row estimations – Part 2

Hello again, On my previous post, we covered how the Query Optimizer handles row estimation when using Table variables under specific conditions. Following up on that, I will demonstrate other scenarios where the Query Optimizer must try to optimize queries when no statistics and histograms are available. In the 2nd example of the series I…


Table variables and row estimations – Part 1

Hello all, The subject of estimated rows vs. actual rows in plan execution has a lot of impact in query performance, and the source of these skews can be quite diverse, from outdated statistics, to incorrect sampling, or the inability of the query processor to know the value of certain variables at compile time, just…

0

SQL Swiss Army Knife #11.1 – Locking, blocking and active transactions

Latest update: 12/2/2016 (Check change log) Download on GitHub Hello all, Following the series “SQL Swiss Army Knife”, here is another version of this script that I’ve been using and tweaking for years now. This way I can get a quick overview of query execution on a given SQL Server instance, from SQL Server 2005…

3

The SQL Swiss Army Knife #3 – View I/O per file – Updated

Hello all, Here is another one focusing on SQL scripts that may help on everyday DBA tasks, following the series “SQL Swiss Army Knife”. This script will return, very promptly, the overall I/O statistics for all databases in your server, ordered by stalled I/O, and is based on the sys.dm_io_virtual_file_stats DMV. This DMV returns I/O…


A case of seeks and scans

Hello all, A couple weeks ago while I was onsite, I was looking at some queries that had performance issues. To the customer, one in particular had become a conundrum that could not be easily answered with the usual approach of good indexing, simply because there was already a good covering index for that highly…

2

T-SQL Misconceptions – JOIN ON vs. WHERE

Hello all, Last week while I was onsite, I was discussing with a customer about some T-SQL querying misconceptions, one particularly caught his interest. In the subject of JOINs, I was asked if there are there any known issues using search arguments in the ON predicate instead of a WHERE predicate? It depends! If we…