Mark Mintoff My superpower is common sense

20Oct/140

Checking and Fixing Index Fragmentation and Statistics in SQL Server

In recent experience, a query which took an hour to execute, executed for three consecutive days with no end in sight. The query had to be terminated early.

What caused this was a high index fragmentation and incorrect table statistics, which lead to an invalid execution plan to be created.

The following script checks and reports on the index fragmentation of all tables within a database:

 Microsoft recommends the following corrective statements:

  • For index fragmentation 5-30%; ALTER INDEX REORGANIZE
  • For index fragmentation greater than 30%; ALTER INDEX REBUILD

http://msdn.microsoft.com/en-us/library/ms189858.aspx

To that end, I created the following script to automatically Reorganize or Rebuild fragmented indices and update the statistics on the table:

 After running the above, the same query that was stuck running for three days, completed in just sixteen minutes; an improvement even on the original execution time!

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
5Nov/130

Microsoft Excel Mod Problem

There exists a problem in Microsoft Excel's Mod function, whereby performing modulo on decimal numbers results in incorrect remainders.

For instance, MOD(0.17, 0.17) results in 0 (as expected), whereas MOD(0.51, 0.17) results in 0.17 (incorrect).

In fact, a modulo on the following multiples of 0.17 has resulted in many erroneous reports of remainders:

modulo1

The reasoning behind this is that internally a float data type is used which results in an incorrect modulo to be calculated. In fact, if you try this out in C# using float or double, you will get the exact same results.

The solution, I have found is to use the formula for modulo:
    x - (y * INT(x/y))

To this end, I have created a simple function through VBA which achieves a correct result by enforcing the usage of the decimal data type.

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
9Jul/130

How long will my database be IN RECOVERY? (SQL 2008)

I write this as I stare at a screen waiting for a critical database to return out of being In Recovery. Have you ever been there? Have you ever had an unexpected shutdown or error on the production server, while you were running a large transaction? If your answer is "Yes", then inevitably you are wondering how long it is going to take.

To this end, I have devised a simple query which will loop through all databases on the current server and give a report on the database recovery process for any that are in recovery. This will give you an estimate of how long you have left twiddling your thumbs and hoping you did not lose any data.

Enjoy and I hope you did not lose any data!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
11Jun/134

ASP.Net Update Panel Flickering Scroll Issue

There appears to be an issue with the ASP.Net Update Panel which causes scroll position to be lost in between page reloads, making the page appear jumpy.

This is a very simple, yet effective fix:

The secret is this little nugget of javascript written exactly after the Script Manager:

This disallows the resetting of the scroll position, meaning that the original scroll position is maintained.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)
12Mar/130

T-SQL IsNullOrEmpty

Lately (because of my line of work) I am using SQL Server more than I am using Visual Studio. I miss some of the functions provided in C# however.

One of them is String.IsNullOrEmpty and SQL Server does not natively have it.

Dead simple, but a comfort in long CASE statements:

Usage is simple and according to expectations:

Unfortunately I do not know of a way to avoid the "= 1" as I do not think boolean/bit values are handled the same in SQL.

Enjoy!

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
7Mar/131

Regex Split by Comma not Surrounded by Quotes

Recently I had a requirement where I had to programmatically extract data from a very messy CSV file and populate a table. The data contained addresses, which included commas in them. In order to distinguish between a comma which separates columns and a comma in the text, the text was surrounded by double quotes.

The solution is obvious; use Regex. The regular expression however is not so straight-forward.

In C#, with escape characters:

Works like a charm.

VN:F [1.9.22_1171]
Rating: 4.9/5 (19 votes cast)
10Jan/130

Developer Friendly Windows Search

As a developer, I find Windows' search incredibly lacking. I can never find the files I want, especially if I'm trying to find a particular pattern of text within the files. To this end, I resorted to making my own simple search algorithm:

I hope you find this to be as useful as I do.

VN:F [1.9.22_1171]
Rating: 5.0/5 (3 votes cast)
16Nov/120

C# ASP.Net Custom HTML Button

I have opted to create an ASP.Net button (postback and event handle) from scratch.

Why? Because I can. Also because it allows me more control over the visual presentation of a button.

This little endeavor is still a work in progress however it supports a server-side click event, a client-side click event, button text, an icon image and works within ASP Update Panels as well.

The markup is relatively straight-forward:

I am using an

  • as the root element, because most of the time I make use of a
      -based layout

      The back-end for the button is as follows:

      As you can see, I am making use of the IPostBackEventHandler and registering the control with the ScriptManager as a PostBackControl or an AsyncPostBackControl, depending on whether or not the control is contained within an Update Panel.

      Currently, I am hoping there is a more efficient way of detecting whether the button is within an Update Panel, but the while loop will do for now.

      I have written a small amount of CSS for the button, in order to make it look good:

      In a perfect world, I would not need to write CSS to support anything less than IE8, but this CSS breaks on IE7. I would imagine it would do very much the same thing on IE6. However, I actually dedicated (wasted) an hour towards trying to get this to work on IE7:

      Usage is simple and can be figured out from the download:

      What I have provided is a veritable playground of design customization in lieu of the standard button whilst more or less retaining the same functionality of the standard button.

      Any customization or features are completely up to you. Happy coding/designing!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  • 24Sep/126

    C# Console Progress Bar

    At work, I sometimes find myself needing to make Console applications which take some time. For example, I have a few console applications which parse dump files into objects and then insert the data into a database. Usually, I represent the progress of these applications with a simple incremental percentage display, however I thought I'd create a generic method which would display an ASCII progress bar.

    The code itself is pretty self explanatory and probably more verbose than it really needs to be, but it gets the job done and looks good. It is a nice throwback to Pascal and QBasic for me.

    EDIT: Amended code to append characters to a string before writing on screen. This fixes the flickering problem.

    VN:F [1.9.22_1171]
    Rating: 5.0/5 (13 votes cast)
    27May/120

    The Towers of Hanoi Problem

    About a year ago, in the summer of 2011 I had found myself unemployed for 2 months through no fault of my own. I had accepted an ASP.Net development position with a gaming company called GamingVC and had thus resigned from my job at WagerLogic.

    Unfortunately, on the day I was to begin my new job with GamingVC, as soon as I entered the building; I was taken into a boardroom and fired without reason. Thus, I spent two months searching for gainful employment and had many a strange interview.

    The worst interview I had that summer, and consequently the worst interview I had in my entire life is what I shall be talking to you about today.

    By nature, I am an introverted person. I do make an effort to be sociable, however it takes me some time to warm up to a person and be comfortable. The interview, unbeknownst to me consisted of an interview with a woman in human resources and then a surprise interview consisting of the IT manager and a senior programmer. The IT manager was boisterous and rude, bouncing a stress ball against the wall, seemingly paying no regard to a word I was saying. I felt stressed, sweating inside a thick suit in the middle of June, when suddenly the CIO walks in. After a few minutes he launches into a tirade about games.

    He mentions to me Towers of Hanoi as if it were the greatest game ever to have been created (he's wrong, the greatest game is chess), framing it with arrogant disdain aimed at me with the term "before your World of Warcraft nonsense we had a game called Towers of Hanoi". It is funny, come to think of it, because I never even played World of Warcraft. Anyway, he wanted me to programmatically solve Towers of Hanoi on a whiteboard whilst they all watched. Given my character and nervousness I failed miserably and here's why:

    Towers of Hanoi is a game I played when I was seven years old. I played it on a cheap hand-held device along with Tetris, Snake and some other monochrome pixel games. At that age, I had not a care in the world and I had not yet started to analyze everything in terms of functional programming. Nor had I any idea what recursion was when I was that young. Suffice it to say, I knew how to solve it myself, which I saw mentally as a while loop, without having figured out that it was a recursive loop. What's more I am not accustomed to programming without a computer and with three people burning a hole in the back of my head.

    So, for the sake of no longer feeling stupid (because they made me feel pretty stupid) at having mucked it up so badly during my interview, I present to you the solution:

    I believe that redeems me from the absolute mess of a while loop I had tried to do on a whiteboard and I can (after a year) finally move on.

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)