Mark Mintoff My superpower is common sense


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

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)

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)

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.


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

Adding Regular Expression Support to SQL Server

First and foremost, it is worth noting that SQL Server supports .Net assemblies, allowing us to create methods which can be used from SQL.

.Net assemblies (dll files) can be loaded into the below folder.

With that out of the way, we can get started on creating a .Net assembly. SQL Server does not support regular expressions natively, so I thought it would be useful to add this functionality as my first assembly.

To start, we can create a Class Library Solution (I called mine 'TextFunctions') and add a single class (I called mine 'StringUtility'). The following is the code I wrote for my StringUtility class:

Very simple bit of code, which allows me to check for Regex matches. No big deal. Now we must install this assembly into the database. First we must check to ensure that the database is CLR enabled, so we run:

Next we will create the assembly. Please note that the dll file must be on the same machine as the database is. You might also want to specify the database you will be adding this to. I added a simple "use master;" to my script.

Now we can install the RGX function I created.

Note that the syntax for EXTERNAL NAME is SolutionName.[Namespace.ClassName].MethodName

That is all! You now have a brand new function which executes C# code and returns a boolean value back! Let us try some simple examples:

This function can also be used in WHERE clause conditions as follows:


VN:F [1.9.22_1171]
Rating: 4.3/5 (3 votes cast)

Simple Way To Check For Rows

I would like to share a very simple technique for checking for rows in your table. Before developing this, I used to make the mistake of doing a SELECT COUNT(1) script.

On tables with a large number of rows (millions), this proved to be inefficient (even with indexing). So instead, I developed this efficient way to check for rows, which makes a difference:

It's very simple. All it does is select the top one row from your table, based on your condition in a subquery (X). Then select count on that subquery and convert the result to a bit. The result will always be 1 or 0, which translates to True and False respectively!

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

C# Equivalent for SQL IN

One of the things that irks the most when reading code is cumbersome and long-winded IF statements to the tune of:

Naturally, this could be easily typed out as:

But I am not entirely comfortable with this. Certainly it is infinitely better than trying to cram all those possibilities for a into one line, but there must be a more readable syntax. SQL has the answer with the IN statement. For example:

I wanted to emulate this syntax in C#, so I wrote the following extension method which effectively mimics the SQL IN syntax:

or alternatively as Andreas suggested in the comments below:

What this does conceptually, is incredibly simple. You pass to it a generic List or an array and it simply loops on that array searching for a match returning true or false, depending on whether a match has been found. Conceptually, this is akin to the SQL IN syntax.

What this means, is that we can now type the above cumbersome IF statement as:

Which I feel, is a noticeable improvement in the readability of a long-winded collection of OR conditions in a single IF statement.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tagged as: , , 4 Comments

T-SQL Batch Delete

There are times when you need to delete an arbitrarily large subset of data from a database table. For the sake of example, let us suppose we will be deleting 100,000,000 rows. A simple delete statement:

will take a very long time (hours) to process as 100,000,000 rows will be deleted within a single transaction.

For this purpose, when I need to delete a large subset of data, I prefer to work with a while loop delete and multiple transactions:

This results in a much faster deletion of data as each transaction will only need to delete 1000 rows at a time. An additional benefit is presented if you need to stop the deletion at any point; for example you have locked the table and somebody needs to select some data. There are no long rollbacks and you do not lose progress on what you have deleted.

Hence, to make things easier:



VN:F [1.9.22_1171]
Rating: 4.7/5 (3 votes cast)

SSIS Deployment

Failed to decrypt protected XML node "DTS:Password" with error...This issue occurs mainly due to security related issues. Follow the steps below in order to rectify the issue:

  • In Visual Studio, go to the Control Flow Tab
  • Right-click on an empty area inside the window, select properties.
  • Set Protection Level to EncryptSensitiveWithPassword
  • Set PackagePassword to "myPassword"
  • From the Connection Managers:
    • Double click connection
    • Click "All"
    • Under Security Area:
    • Set Password to your connection's password
    • Set Persist Security Info to True
  • Save the package
  • Connect to SQL Integration Services in SQL Manager
  • Under Stored Packages:
    • Right-Click MSDB
    • Select Import Package
    • Set Package Location to File System
    • Set Package Path to the location of your dtsx file
    • Leave the rest as defaults
    • Click OK
    • Enter "myPassword" into the Package Password dialog
    • You have now successfully imported the package.
  • In order to create an SQL Job:
    • In the job step:
    • Set the Type as SQL Server Integration Services Package
    • Set the Package Source as SSIS Package Store
    • Set the Server as the database server where you stored your package in the steps above
    • Click the button for the package and choose your package.
    • Click OK
    • Enter "myPassword" into the Package Password dialog
Success. You may now schedule and execute the job.
VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)

Monitor Running Processes using T-SQL

At times, monitoring processes which are running on your database is necessary; particularly if you suspect that a particular script is taking too long, or clogging everything up. You might have to kill one or two processes in order to ensure that everything keeps running smoothly. In order to monitor my processes and ensure that everything is well, I put together the following script:

This produces an interesting little 'dashboard', allowing you to identify problematic scripts. Hope this serves you well.

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

Drop Procedures using T-SQL

Quite a specific thing to do, but here's how you can drop all your stored procedures in one go using T-SQL. Simply put, all you need to do is run a cursor on a select from the information schema and create an EXEC script.

For this example, I left everything as a PRINT statement in order to minimize the "Oops I destroyed the database" en-masse factor. Leave the PRINT if you want to see what it puts together. Uncomment the EXEC to run it. If you went ahead and ran the script above with EXEC and feel you ruined your life, you might as well drop all your tables. Here, I'll save you some time:

Happy coding and I hope you don't ruin a database.

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