Category Archives: Computers

Selective Reindexing of Indexes in SQL Server

I’ve wrote many scripts over the years to selectively reindex indexes and update statistics on Microsoft SQL Server. However more recently I’ve been using Hallengren’s SQL reindex script as it’s very powerful and pretty easy to use and schedule via SQL Agent.

Hallengren’s SQL Reindex Script

I currently use the following script run daily as it reorganises indexes with fragmentation of between 5% and 20%, and performs an online rebuild if over 20%. Fragmentation under 5% is ignored (it’s not worth doing anything with these). It also updates statistics on records which have been modified.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 20,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Monitoring Azure SQL Database CPU Usage

Sometimes there is a need to monitor Microsoft’s Azure SQL database usage, but your after the raw figures rather than an impressive looking graph.

Running the following SQL against the master database will give you the recent information:

DECLARE @DatabaseName nvarchar(50);
SET @DatabaseName = '*databasename*';

SELECT getdate() AS [Current Server Time];

SELECT start_time AS [Start of Period],
end_time AS [End of Period],
active_session_count AS [Sessions At End of Period],
avg_cpu_percent AS [Average CPU Usage %],
avg_physical_data_read_percent AS [Average Log Reads %],
avg_log_write_percent AS [Average Log Writes %]
FROM sys.resource_stats

WHERE start_time > DATEADD(minute, -240, GETDATE()) AND database_name = @DatabaseName
Order By start_time DESC;

Here’s an example of the results:
Azure SQL Database Processor Usage

Remote Vulnerability in BASH

There’s been a report of a new code injection attack in Linux, using environment variables. In order to test to see if your system is vulnerable, run the following code from SSH:

env x='() { :;}; echo vulnerable' bash -c "echo this is a test"

If your OK, you should get something like this:
env x='() { :;}; echo vulnerable' bash -c "echo this is a test"
bash: warning: x: ignoring function definition attempt
bash: error importing function definition for `x'
this is a test

If not, you will get something like this:
env x='() { :;}; echo vulnerable' bash -c "echo this is a test"
vulnerable
this is a test

BASH Screenshot

If vulnerable, it’s recommended to patch the BASH installation.

Further Information

Common SQL Queries

Frequently I use SQL with the work I do with Orbis Taskcentre.

There’s some queries which I use quite a lot and have to always look up on the internet or work out how to do, so to save time, here’s my list:

Date Part of Date Time
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

First Day of This Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

First Day of Last Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

First Day of Next Month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Last Day of Current Month
DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

Last Day of Last Month
DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Last Day of Next Month
DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),DATEADD(MONTH, 2, GETDATE()))

Select Text Before Space
This also checks to see if a space exists, if there’s no space it returns the full text in order to prevent an error
LEFT(@Column, CASE WHEN charindex(' ', @Column) = 0 THEN LEN(@Column) ELSE charindex(' ', @Column) - 1 END)

Count Workdays Between Two Dates
(DATEDIFF(dd, @StartDate, @EndDate) + 1)-(DATEDIFF(wk, @StartDate, @EndDate) * 2)-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)