Longest Running Procedures in Sql Server

Its useful to determine which SProc/Code have the greatest impact on the Server. Sometimes, that is determined by examining the I/O cost,sometimes by the Exectution Duration. In this eg, Total Impact is determined by examining the length of execution and the frequency of execution.

SELECT TOP 10 
 temp.text as ProcedureName, 
 s.execution_count as ExecutionCount, 
 isnull( s.total_elapsed_time / s.execution_count, 0 ) as AvgExecutionTime, 
 s.total_worker_time / s.execution_count as AvgWorkerTime, 
 s.total_worker_time as TotalWorkerTime, 
 s.max_logical_reads as MaxLogicalReads, 
 s.max_logical_writes as MaxLogicalWrites, 
 s.creation_time as CreationDateTime, 
 s.total_physical_reads as PhysicalReads,
 isnull( s.execution_count / datediff( second, s.creation_time,getdate()), 0 )as CallsPerSecond
FROM sys.dm_exec_query_stats s 
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) temp 
ORDER BY 
-- s.total_elapsed_time DESC 
s.execution_count desc

Transaction Log growing in Sql Server

Transaction log in SQL Server is one of the most important parts of a SQL Server , as well as one of the most common generators of problems I see online.The following are the causes for transaction log growth,

 

1.Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
2.Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
3.Running extremely large transactions like Bulk Insert, Select Into commands.
http://support.microsoft.com/kb/317375/

 

The following are the proactive measures in order to minimize the unexpected log file growth,

 

1.If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
2.Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
3.Configure the automatic expansion of transaction log in terms of MB instead of %.
4.Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
5.You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

 

Solutions

1. Take full backup of your database
2. Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
3. Shrink if you do not have any free space. You can perform this operation manually if required.
4. Generally avoid shrinking the database and keep it as the last option.

If you are in full recovery, then no it won't be truncated.run the following command:

 

SELECT DATABASEPROPERTYEX('Lorenzo347', 'RECOVERY')

If it returns FULL or BULK_LOGGED, then you will have to backup the log, either to disk or specifying the truncate only to get it truncate the space off.  If you do that, then you might as well change your recovery model to SIMPLE and be done with this problem.  If it says you are already in SIMPLE, then it should shrink without problem.

 

backup log <your database name> with truncate_only

 

Shrink Log files

 

DECLARE @LogFileName varchar(100)

SELECT @LogFileName = rtrim(name)

FROM dbo.sysfiles

WHERE Name like '%_log%'

dbcc SHRINKFILE(@LogFileName, 2)

Shrinks the log file  to 2 MB

http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx

Note:Detaching and deleting the log is definitely not advisable   At best it forces you to take your database offline. Worst case is that you invalidate your entire database and have to  restore from backup.

 

Index to be recreated in Sql Server

When your database grows, the index fragmentation becomes too high,that will scale down the performance of the sql server. To overcome that we need to re-build the index, that should be done at the down time of the server.Here there is a query that will list all the index to be recreated/rebuild.It uses the following tables(sys.indexes,sys.tables,sys.schemas,sys.dm_db_index_physical_stats,sys.partitions). For detailed checkup change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED'

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name +'] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD'
ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' +
cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id,avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT
partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

VS 2010 Tranining kit

MSFT released the training kit(october preview) of VS 2010 framework.Can download it from Microsoft website.The Beta 2 release of the Training Kit contains 15 presentations, 19 hands-on labs, and 13 demos. Many technologies are covered in this release, including: C# 4, VB 10, F#, Parallel Extensions, Windows Communication Foundation, Windows Workflow, Windows Presentation Foundation, ASP.NET 4, Entity Framework, ADO.NET Data Services, Managed Extensibility Framework, and Visual Studio Ultim

Click here to dowload from Microsoft

Getting Country List from CultureInfo

We can use CultureInfo to get the list of countries/languages.Today i got a method to list all the countries, even it has some duplicate values that are elimated by a distinct linq query.

public void CountryList()
{
ArrayList cList=new ArrayList();
foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures & ~CultureTypes.NeutralCultures))
{
RegionInfo ri = new RegionInfo(ci.LCID);
cList.Add(ri.EnglishName);
}
var countries = cList.ToArray();
var i = (from temp in countries select temp).Distinct().OrderBy(s=>s);
foreach (var item in i)
{
listBox1.Items.Add(item.ToString()); 
}
}

Also an interesting thing with Textinfo to make the first letter of a string/sentence to capital.(Little bit tricky :-))

string temp = "MULTIDIMENSIONAL NEWTON RAPHSON";
string firstSmall = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(temp.ToLower()); //I made a trick to make the string to lower case
MessageBox.Show(firstSmall.ToString());

Output :Multidimensional Newton Raphson

Workstation & Server Garbage Collector

There are two types of garbage collector,the workstation GC and Server GC.Workstation is the default on client versions of windows,but server is much faster on multicore machines.The Server GC utilize more memory

  
<configuration>  <runtime>    
 <gcServer enabled="true"/> 
 </runtime>
</configuration>

Note:All sample code is provided is for illustrative purposes only.These examples have not been thoroughly tested under all conditions.Myself, therefore, cannot guarantee or imply reliability,serviceability, or function of these programs.

Bugs in Sql Server

Today i got noticed a bug in sql server 2005(also in SSMS 08) while executing a simple select statement

select 3 --(*)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '--(*'.

If we put a space or any other characters before the "(" there is no error. Also
select 3 --)* not returning error.Any way this is a bug in sql server as it does not parse the comment statements



Also i got an error message while Parsing an sql script as

."Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."

But the script is executing successfully and giving the accurate results!!!!

Last modified Db User Objects & Tables without Trigger in Sql server

If you need to check the details of all DB User Objects by Last Modified Date try the following query


select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc  
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.
 
SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name

ELMAH (Error Logging Modules and Handlers)

HTTP modules and handlers can be used in ASP.NET to provide a high degree of componentization for code that is orthogonal to a web application, enabling entire sets of functionalities to be developed,packaged and deployed as a single unit and independent of an application. ELMAH (Error Logging Modules and Handlers) illustrates this approach by demonstration of an application-wide error logging that is completely pluggable. It can be dynamically added to a running ASP.NET web application, or even all ASP.NET web applications on a machine,without any need for re-compilation or re-deployment.The end-user(if allows) can view the exception/inner exception logged in the server,Its description and even the screen-shot of the
error page


It can be downloaded from
http://code.google.com/p/elmah/
http://msdn2.microsoft.com/en-us/library/aa479332.aspx
http://download.microsoft.com/download/E/9/7/E97DB6A9-3418-46DF-99CA-840
357FE4D72/MSDNElmah.msi

Using spt_values to generate junk datas

master..spt_values can be used if you need to populate a table with 100 numbers from 1,or if you need to generate some Junk/Test data.Refer the query below that will generate data of various datatypes

select
abs(checksum(newid()))%10000 as n_id,
abs(checksum(newid()))*rand()/10000 as f_float,
substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as s_string,
dateadd(day,0,abs(checksum(newid()))%100000) as d_date,
abs(checksum(newid()))%2 as b_byte
from master..spt_values
where type='p' and number between 1 and 200