SqlDependency Notification database Options troubleshooting

For SQL notifications we followed the steps described in http://dimarzionist.wordpress.com/2009/04/01/how-to-make-sql-server-notifications-work.

However it didn’t work for our database. The instructions missed a step to ensure that database options are set for SqlDependency Notification

I’ve created a function to Follow recommendation to always check the notification source, info and type, it returned

 

SqlNotificationEventArgs, Type : Subscribe, Source : Statement, Info : Options 

    //TODO: pass  SQL string

      /// <summary>

               ///

               /// </summary>

               /// <param name=”logger”></param>

               /// <param name=”e”></param>

               public static void LogSqlNotificationEventArgs( Logger logger, SqlNotificationEventArgs e,DbConnection dbConnection=null)

              {

                      LogLevel level = LogLevel .Warn;

                      //from       http://stackoverflow.com/questions/1764717/windows-application-sqldepedency-calling-onchange-infinitely

                      //You’re supposed to check the values of the SqlNotificationEventArgs argument. Only if Type is Change and Source is Data where you notified for a data change.

                      if ((e.Type == SqlNotificationType .Change) && e.Source == SqlNotificationSource.Data)

                     {

                           level = LogLevel.Info;

                     }

                      if (e.Info == SqlNotificationInfo .Options)

                     {

                            if (dbConnection != null )

                           {

                                   //TODO http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/. Make SP

                                   //check DB set options,

                                   // Execute SqlDependencies_CheckDBSetOptions

                                   // Run Alter DB

                           }

                     }

               //http://msdn.microsoft.com/en-us/library/ms189308%28SQL.105%29.aspx Insert/update/delete/truncate are valid

                     logger.Log(level, “SqlNotificationEventArgs, Type : {0}, Source : {1}, Info : {2} “, e.Type, e.Source, e.Info);

                     

              }

Based on TROUBLESHOOTING QUERY NOTIFICATIONS in http://rusanu.com/2006/06/17/the-mysterious-notification/ 

and Troubleshooting Query Notifications I’ve created Stored Procedure

/*

— RUN manually for every DB that required change

ALTER DATABASE MyDB

set ANSI_NULLS ON

       , ANSI_PADDING ON

, ANSI_WARNINGS ON

, CONCAT_NULL_YIELDS_NULL ON

, QUOTED_IDENTIFIER ON

, NUMERIC_ROUNDABORT OFF

,ARITHABORT ON

*/

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— Drop stored procedure if it already exists

IF EXISTS (  SELECT *     FROM INFORMATION_SCHEMA.ROUTINES     WHERE SPECIFIC_SCHEMA = N’dbo’     AND SPECIFIC_NAME = N’SqlDependencies_CheckDBSetOptions’ )

   DROP PROCEDURE dbo.SqlDependencies_CheckDBSetOptions

GO

— =============================================

— Author:           MNF

— Create date:

— Description:      

— =============================================

CREATE PROCEDURE SqlDependencies_CheckDBSetOptions

       @dbName sysname 

AS

BEGIN

        — SET NOCOUNT ON added to prevent extra result sets from

        — interfering with SELECT statements.

        SET NOCOUNT ON;

select –*

       is_ANSI_NULLS_ON

        ,is_ANSI_PADDING_ON

        ,is_ANSI_WARNINGS_ON

        , is_CONCAT_NULL_YIELDS_NULL_ON

, is_QUOTED_IDENTIFIER_ON

, is_NUMERIC_ROUNDABORT_ON–OFF

,is_ARITHABORT_ON

–select *

From sys .databases where name =@dbName 

and (

       is_ANSI_NULLS_ON =0

        or is_ANSI_PADDING_ON =0

        or is_ANSI_WARNINGS_ON =0

        or is_CONCAT_NULL_YIELDS_NULL_ON =0

or is_QUOTED_IDENTIFIER_ON= 0

or is_NUMERIC_ROUNDABORT_ON = 1–OFF

or is_ARITHABORT_ON= 0)

END

— SqlDependencies_CheckDBSetOptions ‘MyDB’

GO

Eager Loading more than 1 table in LinqtoSql

When I’ve tried in Linq2Sql to load table with 2 child tables, I’ve noticed, that multiple SQLs are generated. I’ve found that  it is

a known issue, if you try to specify more than one to pre-load it just  picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)

There are more explanations in 

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx The reason the relationship in your blog post above is generating multiple queries is that you have two (1:n) relationship (Customers->Orders) and (Orders->OrderDetails). If you just had one (1:n) relationship (Customer->Orders) or (Orders->OrderDetails) LINQ to SQL would optimize and grab it in one query (using a JOIN).  The alternative -to use SQL and POCO classes-see http://stackoverflow.com/questions/238504/linq-to-sql-loading-child-entities-without-using-dataloadoptions?rq=1

Fortunately the problem is not applicable to Entity Framework, that we want to use in future development instead of Linq2Sql

Product firstProduct =  db.Product.Include("OrderDetail").Include("Supplier").First();

м

Monitoring Database disk space

An article Data files: To Autogrow Or Not To Autogrow? recommends NOT to rely on auto-grow, because it causing delays in unplanned times.

We should mtonitor database files(both data and log), and if they close to max capacity, manually increase the size.

 However it doesn’t give references, how to monitor the free space inside databases.

 

I’ve tried to look how to do it. 

It can be done manually using   

execute sp_spaceused for the database in question or 

sp_SOS (can be downloaded from

http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure)

Alternatively you can run SQL commands as suggested in Http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359 by Michael Valentine Jones

select 

[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) 

from dbo.sysfiles a

More useful article Monitor database file sizes with SQL Server Jobs describes how to setup monitoring

 Finally I found the excellent article

Managing Database Data Usage With Custom Space Alerts, that can be followed even support personnel without much DBA experience.

Links about SQL PIVOT

One of my colleagues asked how to create SQL query to show results as columns of a row, that actually stored in multiple rows in the table. Other co-worker suggested to use cursor to manually generate required rows.
I’ve found a few links about SQL PIVOT.  
It is easy to start with
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
or http://programmingsolution.net/post/SQL-Server-Pivot-Converting-Rows-to-Columns-with-Dynamic-Query.aspx

More detailed are articles written by Itzik Ben-Ganhttp://www.sqlmag.com/article/tsql3/pivot-or-unpivot-your-data
and
http://www.sqlmag.com/content1/topic/pivot-or-unpivot-your-data/catpath/tsql3/page/2compaslightly  

How to use Pivoting Without Aggregation is described inhttp://www.sqlmag.com/article/tsql3/pivoting-without-aggregation

Disable messages from SQL Server “Login failed for user” in Event log

I’ve noticed multiple messages from SQL Server in EventLog on my machine
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.222.25.129]
I’ve found that there are machines of my co-workers, but they were not sure, which processes tried to access my SQL server.

I’ve tried a few things and finally in SQL Server Configuration Manager disabled tcp, as it was suggested in
http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx

DDL ‘IF not Exists” conditions to make SQL scripts re-runnable

 

As a part of continuous integration we are using deployment of database scripts, which makes very important to make the scripts re-runnable. Some checks for DDL elements are not obvious, and I decided to put hem in one place.Most answers were found on StackOverflow

 

—Column does not exists
if NOT Exists(select * from sys.columns where Name = N’CreatedDate’ and Object_ID = Object_ID(N’MyTableName‘))

—Check if primary key does not exists
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND TABLE_NAME = ‘MyTableName’ )
or
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME= ‘PK_MyTableName‘)

—Check if foreign key does not exists
if NOT Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =’FK_MyRefTable_MyInstanceTable’)
or (from here)
IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = ‘FK_Name’)
–http://stackoverflow.com/questions/869081/a-way-to-check-if-foreign-key-exists-in-sql-2005

—Check if DEFAULT does not exist
if NOT Exists(select * from sysobjects where xtype=’D’ and NAME=’DF_MyTableName_MyColumnName’)

 

Check if an INDEX doesn’t exist

–from http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table

IF NOT EXISTS(SELECT * FROM sys .indexes WHERE name = ‘_index_MyTableName_MyColumnName_MyColumn2‘ AND object_id = OBJECT_ID(‘MyTableName‘ ))

CREATE NONCLUSTERED INDEX [_index_MyTableName_MyColumnName_MyColumn2] ON [dbo].[MyTableName]

(

        MyColumnName ASC ,

       MyColumn2 DESC

)

go

Check if STATISTICS name doesn’t exist

 –from http://msdn.microsoft.com/en-us/library/ms190397.aspx

IF NOT EXISTS(SELECT name  FROM sys .stats WHERE name = ‘_dta_stat_MyTableName_MyColumn1_MyColumn2‘ AND object_id = OBJECT_ID(‘MyTableName‘ ))

CREATE STATISTICS _dta_stat_MyTableName_PDFAttachmentName_MailID_DateCreated ON [dbo].[MyTableName] (MyColumn1,MyColumn2)

go

 Check if PROCEDURE doesn’t exist

–from http://stackoverflow.com/questions/2072086/t-sql-check-if-a-procedure-exists-before-creating-it

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
   exec('CREATE PROCEDURE [dbo].[MyProc] AS select 1')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
--body of your SP
GO

 Check if VIEW doesn’t exist

From Books Online: “CREATE VIEW must be the first statement in a query batch.”

The solution from http://www.sqlteam.com/forums/topic.asp?topic_id=96633

 

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]’ ))

exec(‘ create view MyView AS select 1′)

GO

 

Alter VIEW [dbo]. [MyView]

AS

….

 

 

 

 

Some compatibility differences between versions of SQL Server

In our environment most of developers use SQL Server 2008,but in production we have SQL Server 2005, and some databases still have compatibility level 80(SQL server 2000)
It sometimes causes problems
E.g. the SQL wtitten on SQL Server 2008

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])

    VALUES  (11,’ChildOfXX’,(select [SiteID] from [SiteIds] where SiteCode=’XX’))

GO

on SQL Server 2005 caused
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Fortunately it’s easy to rewrite in SQL 2005 acceptable form
 

DECLARE @SiteId int

    set @siteID=(select [SiteID] from [SiteIds] where SiteCode=’XX’)

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])  VALUES  (11,’ChildOfXX’,@siteID)

Another statement that generated in SQL Server 2008 should be removed in SQL Server 2005
ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE)

Also we had a stored procedure, that worked in  compatibility level 80(SQL server 2000), but caused an error after changing to compatibility level 90

Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The explanation of the error can be found in SELECT DISTINCT and ORDER BY post.  In my case DISTINCT was completely redundant and I’ve removed it without any side effects.

T-SQL function to Get Maximum of values from the same row

 

 Based on the ScottPletcher   solution from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html
–SELECT dbo.GetMax(23, 45, 64, 22, 18, 224, 74, 138, 1, 98, 11, 86, 198)
–Naturally adjust data type to match what you actually need for your specific values
I’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values.
CREATE FUNCTION GetMaxOfDates13 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL,
@value04 DateTime = NULL,
@value05 DateTime = NULL,
@value06 DateTime = NULL,
@value07 DateTime = NULL,
@value08 DateTime = NULL,
@value09 DateTime = NULL,
@value10 DateTime = NULL,
@value11 DateTime = NULL,
@value12 DateTime = NULL,
@value13 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN (
SELECT TOP 1 value
FROM (
SELECT @value01 AS value UNION ALL
SELECT @value02 UNION ALL
SELECT @value03 UNION ALL
SELECT @value04 UNION ALL
SELECT @value05 UNION ALL
SELECT @value06 UNION ALL
SELECT @value07 UNION ALL
SELECT @value08 UNION ALL
SELECT @value09 UNION ALL
SELECT @value10 UNION ALL
SELECT @value11 UNION ALL
SELECT @value12 UNION ALL
SELECT @value13
) AS [values]
ORDER BY value DESC    
)
END –FUNCTION
GO
CREATE FUNCTION GetMaxOfDates3 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END –FUNCTION

 I haven’t considered UNPIVOT solution at the time of writing these functions, but it probably will be better.

Other solutions can be found at http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

http://www.sqlrecipes.com/sql_questions_answers/find_minimum_maximum_value_across_several_columns-11/

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/007764d0-4a2d-4227-a4db-21fce471fbb3/

Use CompiledQuery.Compile to improve LINQ to SQL performance

 

 

After reading DLinq (Linq to SQL) Performance and in particular
Part 4  I had a few questions.

If CompiledQuery.Compile gives so much benefits, why not to do it for all Linq To Sql queries? Is any essential disadvantages of compiling all select queries? What are conditions, when compiling makes whose performance, for how much percentage? World be good to have default on application config level or on DBML level to specify are all select queries to be compiled? And the same questions about Entity Framework CompiledQuery Class.

However in comments I’ve found answer  of the author ricom

6 Jul 2007 3:08 AM
Compiling the query makes it durable. There is no need for this, nor is there any desire, unless you intend to run that same query many times.
SQL provides regular select statements, prepared select statements, and stored procedures for a reason.  Linq now has analogs.

Also from 10 Tips to Improve your LINQ to SQL Application Performance
 

If you are using CompiledQuery make sure that you are using it more than once as it is more costly than normal querying for the first time. The resulting function coming as a CompiledQuery is an object, having the SQL statement and the delegate to apply it.  And your delegate has the ability to replace the variables (or parameters) in the resulting query.

However I feel that many developers are not informed enough about benefits of Compile.
I think that tools like FxCop and Resharper should check the queries  and suggest if compiling is recommended.

Related Articles for LINQ to SQL:
MSDN How to: Store and Reuse Queries (LINQ to SQL)
10 Tips to Improve your LINQ to SQL Application Performance

Related Articles for Entity Framework:
MSDN: CompiledQuery Class
Exploring the Performance of the ADO.NET Entity Framework – Part 1

Exploring the Performance of the ADO.NET Entity Framework – Part 2
ADO.NET Entity Framework 4.0: Making it fast through Compiled Query
 

Lookup Tables with fallback support

Our database has a few lookup tables that uses similar approach to implemented by .Net Framework for localized resources:

At run time, ASP.NET uses the resource file that is the best match for the setting of the CurrentUICulture property. If there is no match for the current UI culture, ASP.NET uses resource fallback. It starts by searching for resources for a specific culture. If those are not available, it searches for the resources for a neutral culture. If these are not found, ASP.NET loads the default resource file.

Let’s assume that we have a look-up table with Time Zones for different countries, states within the countries and post codes within state.
To reduce maintenance of lookup table, if most of the country has one timezone, but only a few states(or post codes) have different timezones, we are not storing information about all states/postcodes, but storing default timezone for the country and then exceptional  states/postcodes:

Country Code	State Code	Post Code	Time Zone	Comment(not a part of database table)
CC1	Null	Null	1	Simple -the same Time Zone for the whole country
CC2	ST1	Null	2	In the country different states have different zones
CC2	ST2	Null	3	regardless of postcodes
CC3	ST1	1001	2	In the same state different post codes have different time zones
CC3	ST1	1002	3
CC4	null	1001	2	Country doesn’t have states, but different post codes have different time zones
CC4	null	1002	3
CC5	null	null	2	Most of states in the country have one time zone, but some states have different time zones
CC5	ST55	null	3
CC6	ST5	null	2	Most of postcodes in the state have one time zone, but some postcodes have different time zones
CC6	ST5	2007	3

The algorithm to retrieve the the timezone is the following
1. Try to retrieve the row for specified country, state and postcode. If record found, we return the time zone.
2.  If record not found, try to retrieve the row for specified country and state. If record found, we return the time zone.
3.  If record not found, try to retrieve the row for specified country. If record found, we return the time zone.
4.  If record not found, throw exception- we expect that at least 1 record per country should exist.

This approach works quite well, but I have some  concerns about performance. Usually the table is stored as cached singleton with LINQ to SQL generated and loaded table.
I am not sure how good LINQ to SQL generated entities work with queries to retrieve records for specified partial keys.
I world be interesting to see some benchmarks for LINQ to SQL loaded into memory collections similar as it was done for DataTable .
I am appreciate any suggestions regarding improving performance, as well as the regarding whole fallback approach.