Thursday, January 19, 2012

SQL Server Resources


Improving SQL Server Performance 

This is just one chapter an online book called Improving .NET Application Performance and Scalability.


Locking and Row Versioning

Pay special attention to the links on the left navigation for information on these topics


The Curse and Blessing of Dynamic SQL

Very in-depth article on issues associated with Dynamic SQL including escaping quotes.


Arrays and Lists in SQL Server 2008

Very in-depth article on how to work with Arrays and Lists (or the lack thereof). It gives solutions for the different versions of SQL Server.


Using a Table Valued Function in a JOIN

Another solution for parsing delimited string. See the above link Arrays and Lists in SQL Server 2008 for more solutions.


Reorganizing and Rebuilding Indexes


SQL Server script to rebuild all indexes for all tables and all databases

This is a very handy script to rebuild all your indexes for a list of databases. I call this after a I import a bunch of data into tables. I also call sp_updatestats AFTER to update stats on the non-indexed columns. Or force all stats on individual tables (including indexes, columns, etc) to be updated by using UPDATE STATISTICS dbo.MyTableHere WITH FULLSCAN. Since the only time data changes when I import it, I have turned off automatic stats update at the database level so that the query optimizer doesn’t have to make sure the stats are up to date. BTW, rebuilding indexes and then calling sp_updatestats didn’t always update all the stats if it thought they didn’t need to be updated. I prefer to force the update after the import.


SQL Server 2008 R2 Books Online (from Microsoft)

This is an excellent resource for lots of topics on SQL Server.


Optimizing Bulk Import Performance

Tuesday, January 10, 2012

Check if application is running via command line (in Windows)

You are probably familiar with Task Manager in Windows. It is very useful for seeing what processes are running. Wouldn’t it be great if there was a command line tool that did the same thing. As it turns out, there is one that is built into Windows. It is called tasklist. It is very easy to use.

In its simplest usage just type tasklist at the command prompt. You will get a list very much like the Processes tab in Task Manager.

The cool thing is it has filtering built in. For example, if you just wanted to see the notepad processes you could do something like: tasklist /FI "IMAGENAME eq notepad.exe"

Type tasklist /? to get a details on the other options you have. There are lots of them. You can specify a remote system, list all tasks currently using a exe or dll, display services hosted in each process, format output as a table, list, or csv, use other filters, turn off headers, etc.

Very nice little tool to have.

Zabbix Server Tips

Basic installation information including default passwords are located here.

To connect to the Zabbix server I use putty.exe. This works well. The default username and password are root/zabbix.



Zabbix Server is down

The Zabbix Dashboard web page shows the status of things. In particular sometimes I see (under the section called Status of Zabbix) that the Parameter Zabbix server is running is No instead of Yes. This means that Zabbix is essentially down. This can be confusing since the Zabbix web page is showing the Dashboard. The important thing to understand is Zabbix has many components. For instance, the Dashboard is actually just a web application that reads data from a database. Since we are able to see the Dashboard that means that the database (MySQL) and the web server (Apache) are both still up and working. However, since the Zabbix server itself is not running according to the Dashboard you should also be able to see under Monitoring tab | Latest data that data has not been collected for a while (since Zabbix server went down). You can also confirm that the Zabbix server is not running by using putty.exe to go to the console and verify that the Zabbix server is not running using something similar to ps -e | grep "zabbix_server". You can always start it manually by executing the Zabbix server /usr/sbin/zabbix_server, but a better plan would be to use service zabbix_server start. Alternatively, you could bounce the box as a last resort using something like shutdown –r now.


Checking Log Files

The first step is to locate the log files. The best way to do that is probably go to the /etc/zabbix/zabbix_server.conf configuration file to see where it is. You can do that using less /etc/zabbix/zabbix_server.conf and then type / and then LogFile and then enter to highlight the file. You will likely see that the LogFile variable is set to /var/log/zabbix/zabbix_server.log. Type q to exit less. Now go to the /var/log/zabbix directory. Here you will see a server_problems.log and zabbix_server.log. Open these log files and see what you can see using less or something similar.


What ports is zabbix listening on

netstat -ntpl | grep 'zabbix'


Check built in items

From the machine you are monitoring, you can use the Zabbix agent to test a item by using the syntax zabbix_agentd.exe -t proc.num[notepad.exe]. In this example I am using the proc.num to determine how processes of notepad.exe are running, but you could replace proc.num with any of the built in items. You will get the result immediately on the command line. In this case I got [u|1] which means 1 instance of notepad.exe is running. I then add a new item for that server using the Zabbix web configuration screens. No client (the machine you are monitoring) side configuration needed.


Checking how many instances of an application are running

This can all be done using the Zabbix configuration screens. All you have to do is navigate to the host (or template if you want to perform this check on multiple machines) you want to check and add an item. Click the Items link and then the Create Item button on the top right. Now fill in the form for the new item. In particular, the key you select is the important piece. You need to select proc.num and then edit the key value so that it is proc.num[notepad.exe] if you are monitoring the number of instances of notepad.exe. You will need to change it as desired to you process name (use Task Manager’s process tab to view these).


Documentation of all the built in items

Thursday, January 5, 2012

Adding Time Zone Support to Dynamic Data

I love the ASP.NET Dynamic Data architecture. It really makes developing applications so much easier. It is quite easy to extend once you get used to how it all works. I always store my dates/times as UTC in the database. This means by default that is what users will see when the Dynamic Data application displays the date/time. Most people don’t think about UTC, so it is of little meaning to most people. In an ideal world all date/time fields would be displayed in the current users time zone of choice.

This can be done quite easily actually when you are using Dynamic Data. The reason is that all the date/time fields are displayed and edited using a common user controls that you have in your project. In particular if you open your ASP.NET Dynamic Data web site / project you will see there is a DynamicData directory and then a FieldTemplates directory. Here you will find two user controls: DateTime.ascx and DateTime_Edit. These are the controls that are used to display  and edit respectively all data/time data in your DynamicData application. So, all we have to do is convert UTC to desired timezone before it is displayed, and on save we need to convert desired timezone to UTC. That way the user will see the date/time in a time zone they understand, but the date/time is stored in UTC in the database.

Enhancing DateTime.ascx

The key to extending the DateTime.ascx control is knowing where to hook into the architecture of Dynamic Data Field Templates. The answer for this control is to override the FieldValue property.

 public override object FieldValue
                var dateUI =  Convert.ToDateTime(base.FieldValue).GetDateTimeForUI();
                return dateUI;
                base.FieldValue = value;

That is all that is required. I’ll show the meat of the GetDateTimeForUI() extension below. Alternatively, you could do your own time zone conversion here. The key is that this is where you convert the base.FieldValue (which is UTC) to the time zone you desire.

Enhancing DateTime_Edit.ascx

To extending the DateTime_Edit.ascx control the key is again knowing where to hook into the architecture of Dynamic Data Field Templates. The answer for this control is to override the DataBind() method and the ConvertEditedValue() method.

public override void DataBind()
    if (Mode == DataBoundControlMode.Edit && FieldValue != null)
        TextBox1.Text = DateTime.Parse(FieldValueEditString).GetDateTimeForUI().ToString();
protected override object ConvertEditedValue(string value)
    string valueToSave = DateTime.Parse(value).GetUtcDateTime().ToString();
    return base.ConvertEditedValue(valueToSave);

That is all that is required. I’ll show the meat of the GetUtcDateTime() extension below. Alternatively, you could do your own time zone conversion here. The key is that in the DataBind() method you convert the FieldValueEditString (which is in UTC) to the time zone you desire, and that in the ConvertEditedValue() method you convert value (which is in the desired time zone) to UTC.

Doing the actual Timezone Conversion

I implemented the conversion methods as an extensions to the DateTime class. This is just for ease of use, but you can do this however you see best. In .NET 3.5 Microsoft added a very nice class called TimeZoneInfo. It makes converting between time zones trivial. The best thing is that it takes Day light savings into consideration when it does the conversion. Below is my implementation for displaying date/times in Pacific Standard Time (PST). For simplicity and limiting scope,  in this case I have hard coded the desired timezone, but you could pull it from the Profile of the current user if you collect that info from the user in some UI.

namespace MyApp.Helpers
    public static class DateTimeExtensions
        // call when displaying value from db to the ui
        public static DateTime GetDateTimeForUI(this DateTime dateAsUtc)
            DateTime dateForUI = TimeZoneInfo.ConvertTime(dateAsUtc, TimeZoneInfo.Utc, TimeZoneInfo.FindSystemTimeZoneById(Config.DefaultTimeZone));
            return dateForUI;

        // call when saving value to db from the ui
        public static DateTime GetUtcDateTime(this DateTime dateFromUI)
            DateTime dateAsUtc = TimeZoneInfo.ConvertTime(dateFromUI, TimeZoneInfo.FindSystemTimeZoneById(Config.DefaultTimeZone), TimeZoneInfo.Utc);
            return dateAsUtc;