07 April 2016

Error when creating a Database diagram: Database owner is invalid

After restoring a database, I got an error when creating a Database diagram. Like the following image:



The error informs that the database owner is invalid.

The solution is to change to database owner using the sp_changedbowner.

To change the database owner for the sa user:
EXEC sp_changedbowner 'sa'

04 April 2016

The mistery of the slow stored procedure in the web App and fast in SSMS

Sql Server has a lot of mysteries and hidden "features".

In the troubleshooting of a web application page response time, I got a timeout executing a Stored Procedure. The query took more than 30 seconds executing on the SQL Server profiler.

I captured the query and executed it in SQL Server Management Studio (SSMS), to start analyzing the query execution plan.
For my surprise it was super-fast when executed in SSMS.

After some research I found that the applications the use ADO.Net set the ARITHABORT to OFF and SSMS set it to ON.
The setting can be disable as shown the in following image.

This setting difference makes that the SSMS will not reuse the same cache entry that the ADO.Net application uses. SQL Server will compile the stored procedure and create a new execution plan.
There are also other possibilities like parameter sniffing to cause this difference on execution times.
In my case the parameters were the same, but what only changed was the ARITHABORT setting.

Having identified that the problem was with the Stored Procedure corrupted execution plan and it's cache, I simply forced a recompile of the stored procedure using the WITH RECOMPILE.
Example usage:

ALTER PROCEDURE myStoredProcedure
(
@id INT,
@date DATETIME
)
WITH RECOMPILE
AS


SQL Server recompiled the stored procedure, generated a new plan and the timeout disappeared.