Wednesday, May 25, 2011

SQL Scripts to Check for Keys & Contraints


When we are depoying application in We need to write the SQL Scripts in such way that the script can be executed without any issues how many times we run it.
For Example if we want to add a constraint to a column for an existing table we should write simple adding the constraint logic. Because if write like that when we run the same script for the 2nd time we’ll be getting error message saying that “Constarint with the specified name already exists.” To get rid of this kind of issues first we need to check whether the Constraint or Keys already exists in Database.  If not exists then only we have to create the required one. Below are the scripts generally used to check for the same.

Script to Check for Non-Clustered & Primary Keys:

    select object_name(i.object_id) as objectName, i.name as indexName, sum(p.rows) as rowCnt
    from sys.indexes i
    join sys.partitions p
    on  i.object_id = p.object_id
    and  i.index_id = p.index_id
    where i.object_id = object_id('tblTest')
    and  i.index_id <= 1
    group by i.object_id, i.index_id, i.name 

To know the number of Rows in table Join with sys.partitions otherwise leave it. 

Script to Check Default Constraints:

select  t.name, d.name,c.name
       from sys.tables t   
         join    sys.default_constraints d on d.parent_object_id = t.[object_id] 
         join    sys.columns  on c.[object_id] = t.[object_id] and c.column_id = d.parent_column_id
where d.name = 'pob_default_current' and t.name = 'em_aduit_place_of_business'

While creating the Keys or Indexes or Constraints it is best practice to give a proper name to it.  If you didn’t specify the name SQL will give it a random number. If you want to revert back or drop the changes at that time it will be very difficult as the name is system generated. Here is the sample How to handle the Syster Generated Names.

If  Default Constraint Name is System Generated:

IF exists(SELECT 1 FROM sysobjects,syscolumns WHERE sysobjects.id = syscolumns.id AND sysobjects.name = 'am_exam_entities' ANDsyscolumns.name = 'created_dt')
BEGIN
declare @default sysname, @sql nvarchar(max)

       SELECT @default = d.name
       from sys.tables t   
         join    sys.default_constraints d on d.parent_object_id = t.[object_id] 
         join    sys.columns c  on c.[object_id] = t.[object_id] and c.column_id = d.parent_column_id
        where t.name = 'am_exam_entities'
         and c.name = 'created_dt' 

       IF @default IS NOT NULL
       BEGIN
              set @sql = N'alter table am_exam_entities drop constraint ' + @default
              exec sp_executesql @sql
       END

       ALTER TABLE am_exam_entities DROP COLUMN created_dt
END
Go

Hope you enjoyed this article.  Will catch you next time with some other interesting article.

Friday, March 4, 2011

Tuning of a Function


Last week I was asked to work on function to improve its performance.  The function implements the security in the system. It deals with 5 tables where each table having more than 10,000 records & it returns a table (i.e. table – valued function).  The Store procedures which are using this function is executing very slow.
After looking couple of times I did some observations.
  •  They are using “Distinct” statements in every Select Query.
  • There is table variable which deals more than 20,000 inserts/updates.
  •  Couple of delete statements over table variable.
Use “Distinct” when there is no alternative:
Distinct is a costly operation. It performs two operations. One it sorts all the records & removes the duplicate ones based on the query. While insert into the table variable “Distinct” is being used.  Instead of that they can apply the distinct while returning the table i.e. at the end of the function.

Use “Table Variable” Unless you don’t have any other option when dealing with large number of records:
Instead of the table variable we can use “Left outer join” or “right outer join” in most of the cases. Its all depends on how you implement the logic.  Remember one thing you can’t use Temporary Table in functions.

Delete Statement on Table Variable:
While dealing with huge data table variable is not best option.  But some times its difficult to find alternative for it. Try to avoid Delete Statement on the table variable at that time.  Because it will affect the performance as it involves so many in-memory operations.  You can use Outer Joins as alternative to Delete Statement.
Hope this information might helps you. Will catch you with some other interesting stuff in the next blog.

Thursday, December 30, 2010

4 Most Important EXE files used in DotNet FrameWork

CLRVer.exe

.Net Framework SDK includes it. This utility can show which CLR versions installed on a machine & which version of clr is being used by processes currently running on the manchine by using -all switch. anothe switch is process id.

AL.exe

AssemblyLinker.exe. Used to link the assemblies(multiple assemblies into single assemble). grouping the modules in assembly.

NGen.exe

Its the tool that ships with the .NET Framework SDK. This tool compiles all of an assembly’s IL code into native code and saves the resulting native code to a file on disk. At runtime, when an assembly is loaded, the CLR automatically checks to see whether a precompiled version of the assembly also exists, and if it does, the CLR loads the precompiled code so that no compilation is required at runtime. But the navtive code produced by NGen.exe is not higly optimized as JIT Compiler produced code. The NGen'd files can get out of sysnc because the characteristics of the Ngen'd files depend on
CLR Version, CPU type, Windows OS Version, Assembly's identity module version ID (MVID - This changes when recompiling), Referenced Assemblys versionIDs, Security (SkipVerification , declarative link-time declarative inheritance.)

PEVerify.exe

It examines all of an assembly’s methods and notifies you of any methods that contain unsafe code. You may want to consider running PEVerify.exe on assemblies that you are referencing; this will let you know if there may be  roblems running your application via the intranet or Internet.

Whenever I get chance I’ll try to write a separate blog for each tool with examples listed above.

Monday, December 27, 2010

Session Timeout Reasons: (InProc Mode)

Sometimes we’ll experience session time out problems. The reasons for the session timeout are brief explained here. There are just based on my experience. There might be more issues/reasons than what I have mentioned below. All the reasons are for In-Proc mode.

1)      Application Pool is recycled.
Altering the web.config, machine.confi, global.asax and bin related directories will cause an immediate recycle of Application Pool. Sometimes updating the .aspx files also cause Application Recycle. This is because in System.Web in web.config we’ll have a configuration entry “numRecompilesBeforeAppRestart”, which indicates no of recompilations to recycle the application pool. If any deadlock situation happens in your application code base on the “responseDeadlockInterval ” setting, Application Pool will get restart. If application pool is restarted you can find the entry for the same in System Logs.
2)      Worker Process is restarted.
Its role is to process requests. The recycling of Worker Process is based on Application Pool setting. In IIS6 you can find options to Recycle Worker Process in minutes, in requests, at particular scheduled time, after reaching the maximum virtual memory (Configurable option) and after reaching the maximum used memory (Configurable option).
In health monitoring tab of the worker process we’ll have option “Enable rapid-fail protection”, which recycles the application pool after certain number of worker process failures (Configurable Option) within specified time period (Configurable Option).
3)      Anti-Virus is running.
If Anti-Virus is running is running on Server, whenever it scans the application folder it will update the datetime of the file, which will intern caused to recompile of that file. So try to exclude the extensions used in your application from scanning or try to place the files in network location where you have very restricted privileges & don’t have the anti-virus.
4)      Buggy Code.
No need to explain I suppose :).