Enterprise Software Development

SQLTRACE_BUFFER_FLUSH, Performance Metrics on SQL Server 2005

SQL Server 2005 gathers metrics data for you automatically out of the box! I discovered this bit of information today, as I was looking into why my DotNetNuke database was starting to drag down. (remember my previous post about How to reset a DNN password at the Database)

I started by looking at some of the hidden metrics data that SQL Server 2005 collects out of the box for you. The first thing to look at was to run a query about the causes of the wait times, to do this execute the following query.

select top 10
wait_type "Wait Type",
wait_time_ms / 1000 "Wait time (s)",
Convert(Decimal(12,2), wait_time_ms * 100.0
/ Sum(wait_time_ms) over ()) "% Waiting"
from sys.dm_os_wait_stats
where wait_type not like ‘%SLEEP%’
order by wait_time_ms desc

This query did not tell me a very much, except that SQLTRACE_BUFFER_FLUSH was at the top of the scale with 99.92% of the wait time.

So I moved on to a more complex query to show a list of missing indexes. This is an interesting query, and it proved to be the one that fixed the slowness problems on my DotNetNuke database.

Here is the query I used to show the cost of missing Indexes

select top 10
round(avg_total_user_cost * avg_user_impact
* (user_seeks + user_scans), 0) "Total Cost",
avg_user_impact, statement "Table Name",
equality_columns "Equality Columns",
inequality_columns "Inequality Columns",
included_columns "Include Columns"
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats s
on s.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d
on d.index_handle = g.index_handle
order by "Total Cost" desc;

Another great article on the impact of SQLTRACE_BUFFER_FLUSH is found Here

.NET 2.0 Generic Collections to 1.0 and 1.1 Collections

I am always looking around for the Generic equivalents for the non-generic collections. Especially when converting 1.1 code over to 2.0

Performance

List<T> : a List<T> uses an underlying array that maintains order, thus inserting and removing from this object can take a variable amount of time.

Dictionary<TKey,TValue> : a Dictionary<TKey,TValue> is generally average with add, remove, and contains, even when it contains a large set of data.

SortedDictionary<Tkey,TValue> : SortedDictionary<Tkey,TValue> uses a balanced tree as the underlying implementation. This can provide you with fast lookups, but inserting will more than likely be slower.

SortedList<TKey,TValue> : SortedList<TKey,TValue> uses two separate arrays to store the data, both in order.

Streaming Video From Home to Work

Have you ever wanted to stream some video from your house to a remote location. Like to your cell phone, or to your computer at work? Maybe you are scheduled to work late during the World Cup, or NBA Finals. Well here is a simple way to stream that video/audio using VLC (VideoLAN media player).

Requirements:

• A knowledge of how to open ports on your home router / home computer firewall
• VLC (at the time of this article we used 0.8.6c for windows)

The source PC:

VLC Source Settings

• Start VLC
• Click on File … Open
• Click on the tab that will be your source
  • File - You can even open DVD ISO’s with this one
  • Disc - You can use a DVD disc as the source (this is a little weird though since you will not be able to control the DVD menu remotely)
  • DirectShow - This is if you would want to stream from a hardware video source, like a webcam, or even a TV Tuner (like my DViCO HDTV 5 Lite clear QAM tuner)
• After you pick your source, we need to do some work on the advanced panel beneath your source.
• Click on the Stream/Save checkbox
• Click on the Settings… button
• Check the HTTP option in the Outputs frame, note the TCP port 1234 (you can change this if you want to)
• Select ASF in the Encapsulation Method frame
• Check the Video codec, select mp4v, and Bitrate (i used 512 because my DSL upstream is 768) in the Transcoding options frame
• Check the Audio codec, select mpga, and Bitrate (i used 96 because my DSL upstream is 768) in the Transcoding options frame

You will want to monkey with the Transcoding options if your client is not VLC, like if its Media Player, or QuickTime (if its QuickTime you may need to change the Encapsulation method to something else, like MP4 (maybe) )

Firewall Settings

• Modify your firewall to allow the TCP port that was selected in VLC (the default is 1234)

The client PC

• Start VLC
• Click File … Open Network Stream
• Select HTTP/HTTPS/FTP/MMS
• Type in your source PCs IP address and TCP port in the URL textbox (ex: http://mycomputer.dyndns.org:1234)
• You may want to add some Caching under Advanced Options, especially if your Bitrates selected in the Transcoding Options add up to close to your available upstream bandwidth.

Tweaking the Settings

If your video is getting jumpy

Its likely that your stream is too large for your upstream to handle.

It can be useful to watch the Stream and Media Info window (on the client VLC) and the Statistics tab, to monitor the Input bitrate … if you see that bitrate stay above your available upstream too often, then adjusting the source bitrate should fix this problem.

Extra Information

• VideoLAN website - all the documentation for VLC
• Doom9.org - excelent source of transcoding knowledge
• VideoHelp.com - many many many HOW TO articles on audio/video

Easy way to kill MS SQL server transaction logs

I manage many SQL 2000 databases. And many times while performing maintenance I have run into the ‘transaction log full’ error.

like this:
Error: 9002, Severity: 17, State: 2
The log file for database ‘%.*ls’ is full.

So like many people I resort to using the commands
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE

The hassle with this, is that you need to first find out the logical name from sysfiles, or from enterprise manager to be able to use the DBCC SHRINKFILE command

So I wrote a stored procedure that you can install into each of your databases, it will enumerate through the sysfiles table. And perform the above steps against all of the Log files found for the current database. The nice thing is that this works with databases with more than one log file defined.

create procedure proc_killLog

(

 @size int = 2

)

as

begin

 declare @logFile sysname, @db sysname

 select @db = db_name()

 declare curFiles cursor for

 select rtrim([name]) from sysfiles where groupid = 0

 open curFiles

 fetch next from curFiles

 into @logFile

 while @@FETCH_STATUS = 0

 begin

 BACKUP LOG @db WITH TRUNCATE_ONLY

 DBCC SHRINKFILE(@logFile, @size)

 fetch next from curFiles

 into @logFile

 end

 close curFiles

 deallocate curFiles

end

This procedure should work with SQL 7, as well as SQL 2005 databases

ASP.NET What Server Side controls require a Server Form

This is a list that I have compiled of ASP server side controls that require or do not require to be nested inside of a server form (form runat=server)

HOWTO obtain the ClickOnce Version at runtime

This article will show you how to get the ClickOnce Version at runtime of you .NET application. My examples are in VB.NET and C# but the same technique would work for any other .NET Assembly.

First you need to add a project reference to System.Deployment

Next import the namespace into your class

VB.NET

Imports System.Deployment

C#

using System.Deployment;

You can obtain the Current Version from the ApplicationDeployment.CurrentDeployment.CurrentVersion property. This returns a System.Version object.

Note (from MSDN): CurrentVersion will differ from UpdatedVersion if a new update has been installed but you have not yet called Restart. If the deployment manifest is configured to perform automatic updates, you can compare these two values to determine if you should restart the application.

Retrieve the ClickOnce Version from the CurrentVersion property

NOTE: The CurrentDeployment static property is only valid when the application has been deployed with ClickOnce. Therefore before you access this property, you should check the ApplicationDeployment.IsNetworkDeployed property first, it will always return a false in the debug environment.

VB.NET

Dim myVersion as Version
if (ApplicationDeployment.IsNetworkDeployed) then
   myVersion = ApplicationDeployment.CurrentDeployment.CurrentVersion
end if

C#

Version myVersion;
if (ApplicationDeployment.IsNetworkDeployed)
   myVersion = ApplicationDeployment.CurrentDeployment.CurrentVersion;

Using the Version object

From here you can use the version information in a label, say on an about form in this way.

VB.NET

label1.Text = string.Format("ClickOnce published Version: v{0}.{1}.{2}.{3}", myVersion.Major, myVersion.Minor, myVersion.Build, myVersion.Revision)

C#

label1.Text = string.Format("ClickOnce published Version: v{0}.{1}.{2}.{3}", myVersion.Major, myVersion.Minor, myVersion.Build, myVersion.Revision);

Webservice fix for error message - Maximum request length exceeded

So I ran into a weird error this morning on one of my webservices. It was a SOAP exception which yielded a 500 HTTP Status error (as viewable from the trace.axd)

“There was an exception running the extensions specified in the config file. –> Maximum request length exceeded.”

The WebService call was posting a DataSet to a WebMethod. I figured out that the DataSet that I was posting was larger than 4MB (4096 KB) which happens to be the default maximum request length for posting to ASP.NET web applications.

You can change this to fix the problem. It is important to note the reason there is a limit in the first place … this setting is there to help protect against DOS attacks. So take precaution when changing this on internet web applications

To increase the default posting limit for your web application. Just add / change the httpRuntime section inside of the system.web element.

<system .web>
<httpruntime maxRequestLength="8192" />
   . . . </system>

This changes it from 4 MB to 8 MB (8192 KB)

Utilizing SQL 2005 Linked Server to connect to external systems via OLE DB

You can utilize MS SQL Server 2005’s Linked Servers ability to connect to any OLE DB provider. Here we are connecting to an iSeries (AS/400) DB2 using the IBMDA400 ole db provider

Here is the command to add a linked server:

EXEC master.dbo.sp_addlinkedserver
  @server = N'MYSERVER',
  @srvproduct=N'IBMDA400',
  @provider=N'IBMDA400',
  @datasrc=N'MYSERVER',
  @provstr=N'Transport Product=Client Access;
    Force Translate=0;
    DATA SOURCE=MYSERVER;
    USER ID=MYUSERID;
    PASSWORD=MYPASSWORD;
    Connect Timeout=30;'

You’ll notice the Force Translate is set to 0, this is to translate the CSSID 65535 characters. For some reason on my system a 0 makes this work… I have seen other people use a value of 1 or 37 for this.

Querying data on a Linked Server

I had to use the OpenQuery statement to access the data on my linked server. Although I have seen other syntax that would suggest that you can access the linked server with out this. I was unable to make that syntax work with my instance.

Here is an example query I was able to execute against my JD Edwards library:

SELECT * FROM OpenQuery(JDEPROD, 'select * from F0911 where gldgj > 107001')

Why is this SO COOL!?

Not only can you select data from a linked system, but you can use most of the extra select parameters with this, like the INTO clause, or the TOP clause.

I was able to use this to transfer data from my JD Edwards EnterpriseOne production library with a command like this:

SELECT *
 INTO #glData
 FROM OpenQuery('select * from f0911 where glfy = 7')

This is just another option you have for integrating, reporting, or data warehousing. Think of composite reporting with your financial system, your project management system, your time keeper system, your HR system, your <insert anything here> system!

Drop me a note if any of you have used this, your likes / dislikes about this method.

Publishing a .NET ClickOnce Application to a linux Apache Server

You might think that you can only publish a ClickOnce application to a IIS server. This is in fact not the case, you CAN publish a ClickOnce application to a apache web server either running on Linux or Windows. In fact its pretty simple.

Configure your apache publish directory for ClickOnce applications

At the root directory of where you are publishing your ClickOnce application, create / edit a .htaccess file. (click here for the apache documentation on AddType) (if you still need help with apache I suggest the following book Apache: The Definitive Guide (3rd Edition) )
Add the following lines to it:

AddType application/x-ms-application application
AddType application/x-ms-manifest manifest
AddType application/octet-stream deploy

For some reason I also needed the following line in my .htaccess file

AddType application/x-msdownload dll

For more reading on ClickOnce check out Smart Client Deployment with ClickOnce(TM): Deploying Windows Forms Applications with ClickOnce(TM) (Microsoft .NET Development Series).

Use FTP when publishing from Visual Studio, Unless you want to install FrontPage Server Extensions on your Apache, Yuk!

Using a ClickOnce application with FireFox

Currently ClickOnce applications will only work with Internet Explorer, you CAN however use FireFox, but you will need to install the FFClickOnce extension.

Creating a Period Date Range Table, with SQL!

You need a way to lump dated records into periodic chunks, like say for a timesheet system. Say your company’s payroll periods are bi-weekly (that’s every other week). So you need a way to distinguish the dated payroll data into two week timesheets.

Sure you could spend three days figuring out a fancy algorithm, only to find out 6 months into your project that there is just this one teeny-tiny instance where they manually change the dates around, and your algorithm doesn’t work.

Well my friend what you need is a table that you can look up against. How are you going to create the data for that table? Very simple, especially with my example of a bi-weekly pay period schedule. We’ll use a SQL Query to perform the insert for us.

Here is what the table will look like for us when we are done:

Here is the SQL Script that I used to generate the data for the period table (bi-weekly):

declare @i int, @ws datetime, @we datetime
 
select @ws = '2006-09-25', @i = -2
 
while @i < 2000
begin
  select @i = @i + 2
  select @we = dateadd(day, -1, dateadd(week, @i+2, @ws))
  insert into PayPeriod (PeriodStart, PeriodEnd) values (dateadd(week, @i, @ws), @we)
  print cast(dateadd(week, @i, @ws) as varchar) + ' | ' + cast(@we as varchar)
end

Methodology used here

• We use a sentinel start date that we know 9-25-2006
• Then in a loop:
  • we increment a counter by 2
  • the start date is sentinel date + counter (in weeks with the date add function)
  • the finish date is sentinel date + counter + 2 weeks , then subtract 1 day to get the date just prior to the start of the next period

There you go, now you have a table with a unique id given to a date range of start date and end date. So now if you want to know what period a particular record is in, you can just join to that table in this way:

select employee, dateWorked, PayPeriod.id as payPeriod
 from Timesheet, PayPeriod
where 
 Timesheet.dateWorked between PayPeriod.PeriodStart and PayPeriod.PeriodEnd

Notice there is no JOIN statement used, that means we need to specify the join on criteria in the WHERE clause. We do it this way in order to utilize the BETWEEN function (inclusive of the start date and end date).

There is always a simple solution; some times it takes a little longer than other times to work it out. Coffee always seems to help me, Oh! and google.

For more SQL recipies and help you should check out the SQL Pocket Guide it covers Oracle, Microsoft SQL Server, MySql, IBM DB2, and PostreSQL.