T-SQL – Where Is a Table Used?

Another “put it in my blog so I don’t lose it” T-SQL nugget.

Nicer than just a raw text-based search, a where-used search for a named table.

select distinct [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
  where (o.name = 'Cow') 
  order by sp.Name

Handy T-SQL Merge

Inserts records into target that aren’t in source, deletes records that are no longer in source.

MERGE CowTarget AS t
USING CowSource AS s
ON (t.TargetCowId = s.CowId)
WHEN NOT MATCHED BY TARGET THEN
    INSERT (TargetCowId, TargetName, TargetMotto)
	VALUES (CowId, Name, Motto)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

String Properties in ADO.NET Entity Data Model

(NOTE: This is Model First development in Entity Framework–start with a data model in the Entity Framework Designer and then generate database schema from the model).

When you add a scalar property of type string to an entity data model, you can fill in the values of several other properties within Visual Studio.

  • Fixed Length – (None), True, False
  • Max Length – (None), value, Max
  • Unicode – (None), True, False

stringprop

What does a value of (None) mean for these properties (when generating SQL Server Database from model)?

  • Fixed Length – (None) equivalent to False
  • Max Length – (None) equivalent to Max
  • Unicode – (None) equivalent to True

Here’s how choices for these properties map to SQL Server data types:

  • Fixed=True, Max=20 – nchar(20)
  • Fixed=(None), Max=20 – nvarchar(20)
  • Fixed=False, Max=20 – nvarchar(20)
  • Fixed=(None), Max=(None) – nvarchar(max)
  • Fixed=True, Max=(None) – nchar(4000)
  • Fixed=False, Max=(None) – nvarchar(max)
  • Fixed=(None), Max=Max – nvarchar(max)
  • Fixed=True, Max=Max – nchar(4000)
  • Fixed=False, Max=Max – nvarchar(max)
  • Fixed=(None), Max=20, Unicode=True – nvarchar(20)
  • Fixed=(None), Max=20, Unicode=False – varchar(20)

This means that if you just pick String as the type and set nothing else, you’ll get nvarchar(max) as the SQL Server type.

NOTE: The value of 4,000 is being used because the maximum length of a fixed length string in SQL Server is 4,000 characters (8,000 bytes when encoded as UTF-16).

 

Count Total Rows while Paging and Avoid Two Queries

This is well covered in many other places, but I’m posting on my own blog so that I can more easily find this trick.

You can do paging in SQL Server 2012 and later using the OFFSET-FETCH syntax. This is useful for returning a single page’s worth of records from a larger (sorted) set of records. To count the total number of records, however, you’d normally do a second query. The T-SQL below shows how to do a total count as part of the query that returns the page in question. The count is repeated for every record, but it avoids having to do two queries.

select FirstName, LastName, COUNT(*) over () as TotalCount
  from Person
  where LastName like 'mc%'
  order by LastName, FirstName
  offset 500 rows
  fetch next 100 rows only

Here’s what the output looks like, returning records 501-600 out of 1,968 records having a last name that starts with “Mc”.

161109-1

SQL Server -Dump Info on Keys

Here’s a short stored procedure that could be useful when trying to understand a SQL Server database.

Let’s say that you have a simple schema like the one below. Person has GroupID as a foreign key (Group.GroupID), meaning that a person belongs to a single group. A person can also have multiple contact records, so PersonContact has a foreign key indicating which person the contact is for.

Keys

In a simple schema like this, you can quickly see the two relationships. You can also see some of this by looking at the Person table in Management Studio, under Columns and Keys. In the diagram below, we see that GroupID is a foreign key and, based on the naming convention, we infer that the corresponding primary key is in the Group table.

MgmtStudio

This notation relies on the naming convention used for the FK_Person_Group relationship. Also, while looking at the Person table, we’re unable to see which tables might contain foreign keys that refer to the primary key in the Person table.

Below is a simple stored procedure that dumps out all relationships that a specified table participates in. That is, given a table, it tells you:

  • All foreign keys that reference the specified table’s primary key
  • All foreign keys in the specified table and where the corresponding primary key is located
-- Given specified table, show all PK/FK relationships.  Show:
--   1) All foreign keys that reference this table's primary key
--   2) All foreign keys in this table and which table contains primary key
create procedure uKeys
(
	@TableName varchar(255)
)
as
begin
select tfk.name as PKTable, cfk.name as PKColumn, tpk.name as FKTable, cpk.name as FKColumn from sys.foreign_key_columns fk
  inner join sys.tables as tpk on fk.parent_object_id=tpk.object_id
  inner join sys.columns as cpk on fk.parent_object_id=cpk.object_id and fk.parent_column_id=cpk.column_id
  inner join sys.tables as tfk on fk.referenced_object_id=tfk.object_id
  inner join sys.columns as cfk on fk.referenced_object_id=cfk.object_id and fk.referenced_column_id=cfk.column_id
  where (tpk.name = @TableName) or (tfk.name = @TableName)
  order by PKTable, PKColumn, FKTable, FKColumn
end;

With this stored proc in place, you can now do the following in a query window, to ask about the keys for the Person table.

ukeys 'Person'

You’ll get some nice output that shows information about all of the relationships for the Person table.

Output

This is a simple example, but this procedure is considerably more useful when you’re dealing with very large databases, where you have a large number of tables and it’s not easy to see all of the relationships for a particular table.

Addendum: There’s already a built-in stored procedure in SQL Server that does basically the same thing (though perhaps in not as clean a format). sp_helpconstraint will dump out the same sort of information, as shown below.

sphelpconstraint

That Conference 2016 – What’s that Smell? It’s your Entity Framework!

That Conference 2016, Kalahari Resort, Lake Delton, WI
What’s that Smell? It’s your Entity Framework! – Russ Thomas

Day 2, 9 Aug 2016

Disclaimer: This post contains my own thoughts and notes based on attending That Conference 2016 presentations. Some content maps directly to what was originally presented. Other content is paraphrased or represents my own thoughts and opinions and should not be construed as reflecting the opinion of the speakers.

Executive Summary

  • Entity Framework often gets a bad rap when it appears to generate very poor T-SQL
  • You can generate far better T-SQL by paying attention to how you construct queries in LINQ
  • This talk describes several “code smells” that lead to poorly performing T-SQL and that can easily be improved
  • [Sean: Most of this also applies to LINQ to SQL]

All communication with SQL Server is via T-SQL

  • T-SQL is the only way to talk to the server
  • From T-SQL to database storage
  • Goes through Optimization (indexes, etc.)
  • There’s no such thing as a technology that bypasses T-SQL
  • Entity Framework adds 4th piece
    • EF -> T-SQL -> Optimization -> Database Storage
  • EF makes things easier because it quickly scaffolds T-SQL
  • Goal today–work towards higher performance in EF

Code smell #1 – too many columns

  • EF can bring back too many columns from database
  • Demo
    • Retrieve data from People table, do something with LastName, Firstname
    • Using simple LINQ statement, just do select p (e.g. from person table)
    • Then foreach on results, pull off LastName, FirstName
  • Look at what EF sends
    • Start up Profiler (yes, should use Extended Events)
    • Reminder: never do the trace in production
    • If writing this query manually in T-SQL, we’d do simple query
    • Look at trace from EF version of this
    • Query is selecting large list of columns–far more than we’re using
    • Can also look at Reads metric in Profiler
  • Recommendation
    • Specify exactly the columns that you want
    • Use Projection
    • instead of: select p,
    • use: select new {p.LastName, p.FirstName}
    • Goes from 544 reads to 4 reads
    • Query now looks like what we’d write ourselves

Code smell #2 – Non Set Based Work

  • Demo–Retrieving e-mails
  • Write a couple of loops, loop through People p, then loop through p.EmailAddresses (implicit join)
  • LINQ query is still returning entire People table
  • And then code is iterating through everything to get e-mail addresses
  • This results in a new T-SQL statement for each pass through the loop
  • Horrible performance
  • Don’t retrieve data procedurally
  • Relational DB works better if you retrieve using set-based query
  • Solution
    • In T-SQL, you’d just do a join
    • In LINQ
      • from people in db.People.Include(“EMailAddresses”)
      • Tells EF that we’ll be coming back later for e-mail address
    • Even better way than .Include
      • Do join in LINQ
      • Then only a small iteration on final results

Code first discussion and demo

  • Most people prefer model-first, but code-first is slick
  • Can write code and then generate DB from code
  • Discussion
    • By default, it set column type to nvarchar(max) for string
    • And decimal(18,2) for double (e.g. a salary field)
  • Conclusion
    • IF you’re going to do code-first, go further than just column names and types
    • SQL Server does care about max string length, makes educated guesses on storage
  • Better way
    • Override OnModelCreating in DbContext, use fluent syntax on modelBuilder to set stuff up
    • Set various attributes for each column, e.g. maximum
    • Re-gen model, can see better attributes on columns
  • Thoughts
    • Code-first, often starts as simple hobby project
    • But you really do need to set proper attributes ahead of time

How to see what EF is doing under-the-covers

  • Profiler
    • Don’t run on production server
  • So how do we see what’s going on in EF without using profiler?
    • SqlLogger class, IDBCommandInterceptor
    • Requires EF 6
    • Can write out to text file, capturing TSQL statements
    • You implement different methods to capture different kinds of things
    • Can do xxxExecuting or xxxExecuted
    • Then: DbInterception.Add(new MyCommandInterceptor());
  • Stuff is intercepted client-side
    • So doesn’t impact performance on server
  • [Sean: Can also set context.Database.Log property]
  • Other stuff you can do with interceptors
    • Intercept and change statement, e.g. “soft deletes” where they don’t actually delete but just set IsDeleted column
    • No trigger !

Other comments

  • There are other optimizations you can do that have nothing to do with SQL Server

Wrapup

  • Will put zipped up demo on sqljudo.com

That Conference 2016 – Tell SQL Server Profiler to Take a Hike

That Conference 2016, Kalahari Resort, Lake Delton, WI
Tell SQL Server Profiler to Take a Hike – Jes Borland

Day 1, 8 Aug 2016

Disclaimer: This post contains my own thoughts and notes based on attending That Conference 2016 presentations. Some content maps directly to what was originally presented. Other content is paraphrased or represents my own thoughts and opinions and should not be construed as reflecting the opinion of the speakers.

Executive Summary

  • SQL Server Profiler has been deprecated
  • We should start using Extended Events (XE)

1998

  • SQL Server 7.0, introduced trace
  • Profiler was GUI for trace

Profiler

  • 2012–deprecated
  • Replaced by Extended Events

Extended Events

  • Started out bad, but much more usable now

Why use

  • More thorough
  • Less resource intensive
    • Profiler hits system hard, can bring server to its knees
  • Covers new features
    • Can no longer use profiler for some features
    • Same # events in profiler forever; but always adding XE
  • Azure
    • Can’t use Profiler, but can use XE

Where data is gathered

  • Post-processing vs. Pre-processing
  • Start session, Gather ino, Output
  • Profiler
    • Filters after gathering information
  • XE
    • Stuff filtered before going into buffer

Common Profiler uses

  • Main uses
    • Capture queries executed, their performance
    • Capture deadlocks
    • Capture errors and warnings
  • Can use Extended Events for all the same things

Capturing all queries with Extended Events

  • Management | Extended Events
  • Sessions–already running sessions
  • system_health always running
  • Rt-click: New Session… (don’t use wizard)
  • Can start when server starts
  • Events
    • degree_of_parallelism – help capture every event on an object
    • sp_statement_completed, sql_statement_completed,
  • Must click Configure to pick database
  • Events have fields (properties)
    • e.g. cpu_time
    • Clicking on event, you see properties
  • Global fields
    • Properties that all events have in common
    • Common: client_app_name, database_name, query_hash, sql_text
  • Filter / Predicate
    • Select all and then filter applies to everything
    • e.g. database_name
    • duration > 5 sec
  • Targets
    • ring_buffer
      • Uses memory
      • Set # items
    • event_file
      • Saves to directory on server

Viewing data

  • New guy under sessions
  • Rt-click | Start
  • Expand, see targets
  • Rt-click | View Target Data
  • Choose Columns
    • Pick what you want to see
  • Sort
    • Don’t do on >1,000 rows
  • Can view data
    • Export to XEL (XML), CSV

Can script out the session

  • Readable text that looks similar to TSQL

XEL file

  • One copy per session, per run

sa permissions?

  • Don’t need
  • Profiler: Need ALTER TRACE
  • XE 20012+: ALTER ANY EVENT SESSION
  • XE is a bit more secure–can capture events but not necessarily read

Start/stop at particular time

  • Use SQL Server Agent

Existing traces

  • Can convert to XE sessions
  • Can find equivalents
  • Mapping