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

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.

 

Topics

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

http://www.zabbix.com/documentation/1.8/manual/config/items

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
        {
            get
            {
                var dateUI =  Convert.ToDateTime(base.FieldValue).GetDateTimeForUI();
                return dateUI;
            }
            set
            {
                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()
{
    base.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;
        }
    }
}

Converting CLng in MS Access SQL to T-SQL (SQL Server)

The problem I need to solve is how do I reproduce the exact functionality of the CLng (function built-in to MS Access SQL) in MS SQL Sever. The answer is not so difficult, but knowing what the question to ask was not so obvious.

After much frustration I finally realized that how CLng in MS Access SQL works is actually quite unintuitive. My situation can be explained quite simply. I have a query in MS Access that I need to convert to run completely on SQL Server. In parts of the MS Access queries the CLng function is used. CLng is similar to Round() in SQL Server, but is not the same particularly when it comes to values that end in .5. Initially I thought it would just truncate the decimals of a double. For example, I thought CLng(.5) should be 1, but MS Access says it is 0. Interestingly, CLng(3.5) is 4, not 3 and CLng(2.5) is 2, not 3. This behavior is very inconsistent to me, but I need to reproduce it on SQL Server using T-SQL so that I can compare the queries.

After some reading about issues related to rounding in general on Wikipedia I found out that there are many ways to round. After reading a technical article from Microsoft I found out that indeed VBA (and appears to be MS Access SQL) do not round the same as MS SQL Server. From this I learned the key to the solution; the CLng function in MS Access implements rounding using the Banker’s Rounding method described in both of the above sources. I did find an implementation of Banker’s Rounding once I knew what to search for. I found a good solution here. I have copy and pasted the solution below.

Here is that function you can execute on SQL Server that will do the same thing as Clng on MS Access SQL because it is an implementation of Banker’s Rounding.

create function MSAccessClng(@x as float)
returns int
as
BEGIN
Declare @DecimalPlaces as tinyint
Set @DecimalPlaces = 0

set @x = @x * power(10, @DecimalPlaces)

return
  case when @x = floor(@x) then @x
  else
    case sign(ceiling(@x) - 2*@x + floor(@x))
    when 1 then floor(@x)
    when -1 then ceiling(@x)
    else 2*round(@x/2,0) end
  end / power(10, @DecimalPlaces)
END

Now you have a function you can use on SQL Server just like you have on MS Access. Keep in mind this is a Scalar-valued function and this means there is a performance penalty for using it, but if you need this functionality you need the functionality.

Below are different examples of what I used to test that the functionality is the same on MS Access and MS SQL Server (T-SQL). Notice, the ones that say SAME means that CLng() has the same functionality as cast(). The ones that say DIFFERENCE means that the special logic was needed to implement differences. You can also see the usage of the function.

-- SAME
select clng(-3.3) -- -3
select cast(-3.3 as int) -- -3
select Round(-3.3, 0) -- 3
select dbo.MSAccessClng(-3.3) -- -3

-- SAME
select clng(-0.3) -- 0
select cast(-0.3 as int) -- 0
select round(-.3,0) -- 0
select dbo.MSAccessClng(-0.3) -- 0

-- DIFFERENCE
select clng(-3.5) -- -4
select cast(-3.5 as int) -- -3
select Round(-3.5,0) -- -4
select dbo.MSAccessClng(-3.5) -- -4

-- SAME
select clng(-0.5) -- 0
select cast(-0.5 as int) -- 0
select round(-0.5, 0) -- error
select dbo.MSAccessClng(-0.5) -- -0

-- DIFFERENCE
select clng(-0.51) -- -1
select cast(-0.51 as int) -- 0
select round(-0.51, 0) -- error
select dbo.MSAccessClng(-0.51) -- -1

-- DIFFERENCE
select clng(-0.8) -- -1
select cast(-0.8 as int) -- 0
select round(-0.8, 0) -- error
select dbo.MSAccessClng(-0.8) -- -1

-- SAME
select clng(-1) -- -1
select cast(-1 as int) -- -1
select round(-1, 0) -- -1
select dbo.MSAccessClng(-1) -- -1

-- SAME
select clng(-100000) -- -10000
select cast(-100000 as int) -- -10000
select round(-100000, 0) -- -10000
select dbo.MSAccessClng(-100000) -- -10000

-- SAME
select clng(3.3) -- 3
select cast(3.3 as int) -- 3
select Round(3.3, 0) -- 3
select dbo.MSAccessClng(3.3) -- 3

-- SAME
select clng(0.3) -- 0
select cast(0.3 as int) -- 0
select round(0.3,0) -- 0
select dbo.MSAccessClng(0.3) -- 0

-- DIFFERENCE
select clng(3.5) -- 4
select cast(3.5 as int) -- 3
select Round(3.5,0) -- 4
select dbo.MSAccessClng(3.5) -- 4

-- SAME
select clng(0.5) -- 0
select cast(0.5 as int) -- 0
select round(0.5, 0) -- error
select dbo.MSAccessClng(0.5) -- 0

-- DIFFERENCE
select clng(0.51) -- 1
select cast(0.51 as int) -- 0
select round(0.51, 0) -- error
select dbo.MSAccessClng(0.51) -- 1

-- DIFFERENCE
select clng(0.8) -- 1
select cast(0.8 as int) -- 0
select round(0.8, 0) -- error
select dbo.MSAccessClng(0.8) -- 1

-- SAME
select clng(1) -- 1
select cast(1 as int) -- 1
select round(1, 0) -- 1
select dbo.MSAccessClng(1) – 1

-- DIFFERENCE
select clng(34.5) -- 34
select cast(34.5 as int) -- 34
select Round(34.5,0) -- 35
select dbo.MSAccessClng(34.5) -- 4

-- DIFFERENCE
select clng(0) -- 0
select cast(0 as int) -- 0
select round(0, 0) -- 0
select dbo.MSAccessClng(0) – 0

-- SAME
select clng(100000) -- 10000
select cast(100000 as int) -- 10000
select round(100000, 0) -- 10000
select dbo.MSAccessClng(100000) -- 10000

Get First or Second Word using T-SQL

When working with SQL (T-SQL) I often need to work with words with-in a string (nvarchar or varchar). For instance I want to display only the first word (first name in this case) of a field (FullName in this case) or maybe the second word in other cases. Words are funny things, they can be separated by commas, spaces, hyphens, and other characters such as semi-colons, exclamation point, parenthesis, brackets perhaps.

Below is the code to create a Scalar-valued Functions in SQL Server (T-SQL) that get the first word or second word.

 

Get First Word

create function GetFirstWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @FirstWord as nvarchar(100)
Declare @SpaceIdx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx > 0)
    SET @FirstWord = SUBSTRING(@Name, 0, @SpaceIdx)
else -- all one word
    SET @FirstWord = @Name

return @FirstWord;
END;

 

Get Second Word

create function GetSecondWord(@Name as nvarchar(100))
returns nvarchar(100)
as
BEGIN

Declare @SecondWord as nvarchar(100)
Declare @AfterWord1 as nvarchar(100)
Declare @SpaceIdx as int
Declare @Space2Idx as int

-- convert word separators to space for simplicity
Set @Name = Replace(@Name, ',', ' ') -- comma to space
Set @Name = Replace(@Name, '.', ' ') -- period to space
Set @Name = Replace(@Name, '-', ' ') -- hyphen to space

Set @SpaceIdx = CHARINDEX(' ', @Name)

if (@SpaceIdx = 0) return ''

Set @AfterWord1 = SUBSTRING(@Name, @SpaceIdx+1, len(@Name) - @SpaceIdx)

Set @Space2Idx = CHARINDEX(' ', @AfterWord1)

if (@Space2Idx > 0)
    SET @SecondWord = SUBSTRING(@AfterWord1, 0, @Space2Idx)
else -- remainder of word
    SET @SecondWord = @AfterWord1

-- if second word is the same as the original, then we only have one word
if @SecondWord = @Name return ''

return @SecondWord;
END;

Usage

select GetFirstWord(‘Joe Smith’)

returns: Joe

select GetSecondWord(‘Joe Smith’)

returns: Smith

 

Extending the Code

In the example above was just concerned with with commas, spaces, and hyphens, but you can extend the code quite easily by adding additional replace statements.

 

Note on Performance

I have implemented these functions as Scalar-valued functions which are known to incur a noticeable performance hit when you use them in a select statement. 

Efficiently Counting Spaces using T-SQL

Let’s assume you have a table called Person and it has a column called FullName. As you might expect, the FullName is really just a concatenation of the persons first and last name separated by a space. If I want to get some stats on how many people have only one space (implying two words only) it is actually quite easy to figure this out. Consider the following SQL statement

 

select FullName from Person

Let pretend it returns something like

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

Exactly 1 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 1

Jeff Jones
Lance Strong
Ben Wheel

Exactly 2 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) = 2

James Johnson Jr.
Jane Dela Cruz

Less than or equal to 3 Space

To get all rows that have exactly one space in them you could do the following

select FullName from Person
where len(FullName) - len(Replace(FullName, ' ', '')) <= 3

Ben Wheel
James Johnson Jr.
Jane Dela Cruz
Jeff Jones
Lance Strong

As you can see it is relatively easy to count occurrences of a character using T-SQL. You can expand on these queries just by changing the stuff in red.