Count of rows per table in SQL Server

A lot of times I have to study a new database and count of rows per table has been a great source of info. There are many ways to get number of rows per table, but I’m sick of looking it up every time. So here goes one I prefer:

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.NAME AS TableName,
    Sum(p.[Rows]) as [RowCount],
    'select top 1000 * from ['+SCHEMA_NAME(t.schema_id) +'].['+t.name+']'
FROM sys.tables t 
    INNER JOIN sys.partitions p ON t.[object_id] = p.[object_id]
WHERE 
    t.NAME NOT LIKE 'dt%' 
GROUP BY 
    t.NAME,t.schema_id
ORDER BY 
    Sum(p.[Rows]) desc

Here is a discussion with more ways to do the same thing.

Performance Tuning of Entity Framework Requests

Most of my large projects are using Entity Framework. And Entity Framework is known to sometimes create crazy sql requests. Or rather it easily allows developers to create crazy queries. It is really easy to throw couple .Include() into your query and then return objects as they come from EF:

var products = dbContext.Products.Include(p => p.Tags)
                                 .Include(p => p.Reviews)
                                 .ToList(); // first query
foreach(var prod in products)
{
    var reviewers = prod.Reviews.Select(r => r.Owner).ToList(); // N+1
}

The above code is fictional, but most likely will produce N+1 issue. N+1 issue is when you don’t just issue a SELECT query, but follow up every returned row by another SELECT query to retrieve related entries. This is caused by EF feature called Lazy Loading. Read better explanation about Lazy Loading problem here. Basically think that your first query for Products returns 1000 records. Then in case of N+1, operation inside of foreach loop will produce another thousand queries to your database.

Continue reading

Execute Large SQL Script from command line

For my own future reference, when I get a large SQL file, large enough that SSMS freaks out about lack of memory, I need to use Sqlcmd tool. And here how it is done:

sqlcmd -S localhost\sqlexpress -d MyDatabaseName -i .\InputFileName.sql

Note that localhost is important to put there, if you try to connect to .\sqlexpress, you will only get a connection error

How to export data from Excel into SQL Server

TL;DR: Go here for a code sample.

This is long overdue post – I’m too busy these days with contract work and other commitments.

By the nature of my work, I have to do data migrations on a regular basis. Some of the migrations from one database into another, but most of the migrations are from Excel spreadsheets into a database.
A database to database migration is somewhat nerve racking – usually the structure is broken, data is dirty, fields re-purposed and all other sort of terrible things you read about on The Daily Wtf. The best so far I’ve seen are along the lines of person middle names stored in field called CompanyName2; employee information stored in table called tbl_Jobs and info about actual jobs in table tbl_Job – see the difference here? That is all to avoid the confusion!

Database-to-database migration are unique in their own kind. But I found a lot of similar things in a process of export from Excel into SQL Server. So I’ll write down these things here – for my own future reference and you, my dear reader, might find this useful as well.

Continue reading

How We Do Database Integration Tests With Entity Framework Migrations

There is a follow-up article about integration tests in 2016. Recommended for reading if you are using VSTS and looking at Cake build system

Unit tests are fine and dandy, but if you persist your data in database, persistence code must be tested as well. Some possible bugs will never be picked up only by unit tests. One of the issues I’ve been burned by is entity validation, another is a missing foreign key relationship.

Validation issues can burn you very easy. Look at this bit of code:

public class Person 
{
    [Key]
    public Guid PersonId { get; set; }

    [Required]
    public String FirstName { get; set; }

    [Required]
    public String LastName { get; set; }
}

public void CreatePerson(string firstName, string lastName)
{
    var newPerson = new Person();
    newPerson.FirstName = firstName;

    // dbContext was injected via constructor
    dbContext.Persons.Add(newPerson); 
    dbContext.SaveChanges();
}

NB: This is just a sample code. Nowhere in my projects we do such things.

Continue reading

Moving from Entity Framework 5 to Entity Framework 6 and back or “Could not determine storage version”

Recently we have upgraded our project to run on EF 6. And this is still in beta in Deb branch. So this has not been deployed to most of our customers. And today I had to solve a bug for older version of our system that is still on EF5.

Long story short: I’ve tried to connect to DB that already had migrations from EF6, but I was connecting with EF5. And cryptic error messages emerged, just like EF loves to throw cryptic error messages, this time it was no exception, excuse the pun.

This time the exception is saying: The provider did not return a ProviderManifest instance. with internal exception saying Could not determine storage version; a valid storage connection or a version hint is required. Which made no sense at that point. Until you spend couple hours trying to figure out what is happening.

Let me go into EF migration system details slightly. EF stores database state information in __MigrationHistory table that looks like this for EF5:

EF5_MigrationHistory

For EF6 the structure of the table have slightly changed:

EF6_MIgrationHistory

Notice on the second screenshot there is another column for DbContext type name used for the migration. This is a new feature in EF6 – allows more than one context to live in one project and you can run migrations for both of them. Have not tried this one yet, but time will come pretty soon!

Also once we have run migrations in EF6, the version recorded next to the migration record have changed from 5.xx to 6.xx – this is also visible on the last screenshot.

And if you try to connect to this database with older version of EF, table structure throws the system off it’s tracks. I’ve tried updating version number in data, but it did not change anything, do don’t bother.

I have not found the solution to this issue, other than re-create database migrations in EF5. So be careful when you update to EF6 and run migrations – there is no way back to EF5.

Can not connect to SQL Server from web-site on IIS?

I remember I had to fix this issue about a year ago. And I completely forgotten how to do this. And the same problem strikes again.

The issue was with a web-site (MVC5, 4.5.1 .Net) running on IIS. And I had an issue when trying to get data from SQL Server.
The connection did not go through whatever I did.

The fix was quite simple. All I needed to do is to change the AppPool underlying user to be “NetworkService” instead of “ApplicationPoolIdentity”:

  1. In IIS, in list of Application Pools find the one which works with your site.
  2. In Advanced Settings (link on right) for that AppPool find Section Process Model and Identity setting first
  3. Change Identity to be NetworkService
  4. Restart AppPool and the web-site.

This is possibly one of the reasons that can cause the issue. There is a million of other reasons this can happen, but I have checked most of them before started semi-randomly poking about with app pools and IIS settings.

SVN Auto-commit files

Many times you would like to have a version control on your database. Here is a simple recipe how to set this up on you SQL Server datbase. And sometimes you need to set up automatic commits to SVN (or other version control system). Here is how to do it:

REM Script out database: all the tables, stored procedures, functions, etc.
REM then add missing files to the SVN version control
REM and commit all the changes to the repository.
REM Provide a comment for the commit as a parameter to this batch.
REM This is used as a scheduled task job - once a week do an automatic commit.

scriptdb -con:"Data Source=myServer;Initial Catalog=NorthenLights;user id=sa;pwd=password" -outDir:scripts -nocollation
cd d:\SQL_versioning\
"c:\Program Files\TortoiseSVN\bin\svn" add --force * --auto-props --parents --depth infinity -q
"c:\Program Files\TortoiseSVN\bin\svn" commit -m %1%

I hope I don’t need to tell you that you need to have TortoiseSVN installed, along with ScriptDB. If you are using SQL Server 2008, you can use my build for ScriptDb as the provided executable file does not work with SQL Server 2008 and 2012. Also don’t forget to modify the connection string for your database.

SP_SENDMAIL_DBMAIL How it Works, Stuck Emails and Purge InternalMailQueue.

I’m an enemy to myself. I’m writing a huge stored procedures and send emails in a loop. And sometimes that loop is infinite.

Anyway, today I had to figure out how to deal with SQL Server mailing functionality and sp_sendmail_dbmail. Every time you send email via sp_sendmail_dbmail, your message is going through the a huge number of checks and verifications. (Check out the source of sp_sendmail_dbmail procedure, it is on msdb database, inside System Stored Procedures folder.)

After all the checks you eventually will find insert statement: INSERT sysmail_mailitems ... Now look into sysmail_mailitems table. You’ll see there a list of all the emails that have been sent, will be sent or failed. There are few views to aid your viewing experience that aggregate data from sysmail_mailitems into something useful.

Looking at emails is fun Not this bit is a bit of black magic for me, but that is how I understand it. I could be wrong, don’t take it as a reference.

  • Whenever sp_sendmail_dbmail is executed, info about this email is inserted into sysmail_mailitems table and into InternalMailQueue which is not a table, but a queue. (Note for myself, lookup queues in SQL Server).
  • That queue is used by a Broker, and whenever there are records in the queue, Broker runs DatabaseMail.exe program.
  • The queue holds some information about new mail message and a reference to the record in sysmail_mailitems. So DatabaseMail.exe pops a record from the queue, reads respective message from sysmail_mailitems and sends an email as requested.
  • Now black magic part is almost over. When I have created a 3 million message queue with emails (infinite loop, remember?), all 3 millions records were in sysmail_mailitems and in InternalMailQueue. There are helper procedures to remove emails from the pending list:

    exec msdb.dbo.sysmail_delete_mailitems_sp null, ‘unsent’

This literally removes all the records from

sysmail_mailitems with emails that have not been sent yet. (First parameter is date – delete all records older than that date. Second parameter can take ‘sent’, ‘unsent’, ‘failed’, ‘retrying’) But that procedure does not touch anything in InternalMailQueue. So after executing sysmail_delete_mailitems_sp I was left with no records in the table, but all of them were in the queue. Now, when DatabaseMail.exe runs, it will find a lot of messages in the Queue, but no records about the mail to be sent. So it will create a lot of “FAIL!” records in sysmail_log table. To clean up InternalMailQueue at the moment I’m running this query:

#!sql
exec msdb.dbo.sysmail\_stop\_sp -- stop DatabaseMail.exe, so it does not try hard
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
set nocount on
declare @Conversation_handle uniqueidentifier;
declare @message_type nvarchar(256);
declare @counter bigint;
declare @counter2 bigint;
set @counter = (select count(*) from ExternalMailQueue) -- count millions of queued messages
set @counter2=0
-- now do until nothing is left in the queue
while (@counter2<=@counter)
begin
    receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue    -- pop messages from the queue
set @counter2 = @counter2 + 1
end

exec msdb.dbo.sysmail_start_sp -- start DatabaseMail.exe again

EXEC msdb.dbo.sysmail_help_queue_sp -- verify that there is nothing left in the queue

Hopefully I won’t need this article again, but can be useful for somebody facing the same problem. Most of the details were spread around the internet, but I could not find the big picture.

Reference:

  1. Using and Managing Databse Mail
  2. Forum discussion about message clearing
  3. MSDN: Troubleshooting Database Mail: General steps
  4. MSDN: Database Mail Architecture
  5. Database Mail Setup – SQL Server 2005
  6. Found this after I have wrote this: SQL Server 2008 : Sending and Receiving with Database Mail