SQL Server Blogs

SQL Saturday Edinburgh Agenda

MartinBell — Sat, 11 May 2013 20:13:00 GMT

SQL Saturday Edinburgh Agenda...(read more)

Leeds Usergroup Slides and Scripts

MartinBell — Fri, 10 May 2013 07:53:00 GMT

Presentation and Slides from the Leeds User Group 7th May...(read more)

Resolving data redundancy up front

okeofs — Mon, 22 Apr 2013 17:58:00 GMT

Introduction

As all of us do when confronted with a problem, the resource of choice is to ‘Google it’.

This is where the plot thickens. Recently I was asked to stage data from numerous databases which were to be loaded into a data warehouse. To make a long story short, I was looking for a manner in which to obtain the table names from each database, to ascertain potential overlap.

As the source data comes from a SQL database created from dumps of a third party product, one could say that there were +/- 95 tables for each database.

Yes I know that first instinct is to use the system stored procedure “exec sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'”.

However, if one stops to think about this, it would be nice to have all the results in a temporary or disc based table; which in itself , implies additional labour.

This said, I decided to ‘re-invent’ the wheel. The full code sample may be found at the bottom of this article.

Define a few temporary tables and variables

declare @SQL varchar(max);

declare @databasename varchar(75)

drop table ##rawdata3

drop table #rawdata1

drop table #rawdata11

-- A temp table to hold the names of my databases

CREATE TABLE #rawdata1

(

database_name varchar(50) ,

database_size varchar(50),

remarks Varchar(50)

)

--A temp table with the same database names as above, HOWEVER using an

--Identity number (recNO) as a loop variable.

--You will note below that I loop through until I reach 25 (see below) as at

--that point the system databases, the reporting server database etc begin.

--1- 24 are user databases. These are really what I was looking for.

--Whilst NOT the best solution,it works and the code was meant as a quick

--and dirty.

CREATE TABLE #rawdata11

(

recNo int identity(1,1),

database_name varchar(50) ,

database_size varchar(50),

remarks Varchar(50)

)

--My output table showing the database name and table name

CREATE TABLE ##rawdata3

(

database_name varchar(75) ,

table_name varchar(75),

)

Insert the database names into a temporary table

I pull the database names using the system stored procedure sp_databases

INSERT INTO #rawdata1

EXEC sp_databases

Insert the results from #rawdata1 into a table containing a record number #rawdata11 so that I can LOOP through the extract

INSERT into #rawdata11

select * from #rawdata1

We now declare 3 more variables:

@kounter is used to keep track of our position within the loop.

@databasename is used to keep track of the’ current ‘ database name being used in the current pass of the loop; as inorder to obtain the tables for that database we need to issue a ‘USE’ statement, an insert command and other related code parts. This is the challenging part.

@sql is a varchar(max) variable used to contain the ‘USE’ statement PLUS the’ insert ‘ code statements.

We now initalize @kounter to 1 .

declare @kounter int;

declare @databasename varchar(75);

declare @sql varchar(max);

set @kounter = 1

The Loop

The astute reader will remember that the temporary table #rawdata11 contains our database names and each ‘database row’ has a record number (recNo). I am only interested in record numbers under 25. I now set the value of the temporary variable @DatabaseName (see below) .Note that I used the row number as a part of the predicate.

Now, knowing the database name, I can create dynamic T-SQL to be executed using the sp_sqlexec stored procedure (see the code in red below).

Finally, after all the tables for that given database have been placed in temporary table ##rawdata3, I increment the counter and continue on.

Note that I used a global temporary table to ensure that the result set persists after the termination of the run.

At some stage, I plan to redo this part of the code, as global temporary tables are not really an ideal solution.

WHILE (@kounter < 25)

BEGIN

select @DatabaseName = database_name from #rawdata11 where recNo = @kounter

set @SQL = 'Use ' + @DatabaseName +

' Insert into ##rawdata3 ' +

+ ' SELECT table_catalog,Table_name FROM information_schema.tables'

exec sp_sqlexec @Sql

SET @kounter = @kounter + 1

END

The full code extract

Here is the full code sample.

declare @SQL varchar(max);

declare @databasename varchar(75)

drop table ##rawdata3

drop table #rawdata1

drop table #rawdata11

CREATE TABLE #rawdata1

(

database_name varchar(50) ,

database_size varchar(50),

remarks Varchar(50)

)

CREATE TABLE #rawdata11

(

recNo int identity(1,1),

database_name varchar(50) ,

database_size varchar(50),

remarks Varchar(50)

)

CREATE TABLE ##rawdata3

(

database_name varchar(75) ,

table_name varchar(75),

)

INSERT INTO #rawdata1

EXEC sp_databases

INSERT into #rawdata11

select * from #rawdata1

declare @kounter int;

declare @databasename varchar(75);

declare @sql varchar(max);

set @kounter = 1

WHILE (@kounter < 25)

BEGIN

select @databasename = database_name from #rawdata11 where recNo = @kounter

set @SQL = 'Use ' + @DatabaseName +

' Insert into ##rawdata3 ' +

+ ' SELECT table_catalog,Table_name FROM information_schema.tables'

exec sp_sqlexec @Sql

SET @kounter = @kounter + 1

END

select * from ##rawdata3

where table_name like '%SalesOrderHeader%'

Walking the walk at SQL Saturday Edinburgh

MartinBell — Sun, 21 Apr 2013 19:44:00 GMT

Walking the Walk at SQL Saturday Edinburgh in aid of *** cancer....(read more)

My blog has now moved to http://dataidol.com/tonyrogerson

tonyrogerson — Fri, 19 Apr 2013 08:19:00 GMT

I've decided to implement a new blogging infrastructure based on WordPress; the community server stuff I'm using has got a bit tired and WordPress offers a wealth of plug-ins to do pretty much everything I want.

So, see http://dataidol.com/tonyrogerson for my new blog; its not just a move, I have broadened my coverage to encompass all things data, the first couple of posts talk about Short-Stroking hard disks (something I've presented on a number of times now), I've also got some Erlang content.

Anyway, enjoy!

Monitoring SQL Server Agent job run times

okeofs — Mon, 15 Apr 2013 22:21:00 GMT

Introduction

A few months back, I was asked how long a particular nightly process took to run. It was a super question and the one thing that struck me was that there were a plethora of factors affecting the processing time. This said, I developed a query to ascertain process run times, the average nightly run times and applied some KPI’s to the end query. The end goal being to enable me to quickly detect anomalies and processes that are running beyond their normal times.
As many of you are aware, most of the necessary data for this type of query, lies within the MSDB database.
The core portion of the query is shown below.

select sj.name,sh.run_date, sh.run_duration,
case
when len(sh.run_duration) = 6 then convert(varchar(8),sh.run_duration)
when len(sh.run_duration) = 5 then '0' + convert(varchar(8),sh.run_duration)
when len(sh.run_duration) = 4 then '00' + convert(varchar(8),sh.run_duration)
when len(sh.run_duration) = 3 then '000' + convert(varchar(8),sh.run_duration)
when len(sh.run_duration) = 2 then '0000' + convert(varchar(8),sh.run_duration)
when len(sh.run_duration) = 1 then '00000' + convert(varchar(8),sh.run_duration)
end as tt
from dbo.sysjobs sj with (nolock)
inner join dbo.sysjobHistory sh with (nolock)
on sj.job_id = sh.job_id
where sj.name = 'My Agent Job'
and [sh.Message] like '%The job%')

Run_date and run_duration are obvious fields. The field ‘Name’ is the name of the job that we wish to follow. The only major challenge was that the format of the run duration which was not as ‘user friendly’ as I would have liked. As an example, the run duration 1 hour 10 minutes and 3 seconds would be displayed as 11003; whereas I wanted it to display this in a more user friendly manner as 01:10:03.

In order to achieve this effect, we need to add leading zeros to the run_duration based upon the case logic shown above.

At this point what we need to do add colons between the hours and minutes and one between the minutes and seconds. To achieve this I nested the query shown above (in purple) within a ‘super’ query. Thus the run time ([Run Time]) is constructed concatenating a series of substrings (See below in Blue).

select run_date,substring(convert(varchar(20),tt),1,2) + ':' +

substring(convert(varchar(20),tt),3,2) + ':' +

substring(convert(varchar(20),tt),5,2) as [run_time] from (

select sj.name,sh.run_date, sh.run_duration,

case

when len(sh.run_duration) = 6 then convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 5 then '0' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 4 then '00' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 3 then '000' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 2 then '0000' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 1 then '00000' + convert(varchar(8),sh.run_duration)

end as tt

from dbo.sysjobs sj with (nolock)

inner join dbo.sysjobHistory sh with (nolock)

on sj.job_id = sh.job_id

where sj.name = 'My Agent Job'

and [sh.Message] like '%The job%') a

Now that I had each nightly run time in hours, minutes and seconds (01:10:03), I decided that it would very productive to calculate a rolling run time average. To do this, I decided to do the calculations in base units of seconds.

This said, I encapsulated the query shown above into a further ‘super’ query (see the code in RED below). This encapsulation is shown below. The astute reader will note that I used implied casting from integer to string, which is not the best method to use however it works. This said and if I were constructing the query again I would definitely do an explicit convert. To Recap: I now have a key field of ‘1’, each and every applicable run date and the total number of SECONDS that the process ran for each run date, all of this data within the #rawdata1 temporary table.

Select 1 as keyy,run_date,(substring(b.run_time,1,2)*3600) + (substring(b.run_time,4,2)*60) + (substring(b.run_time,7,2)) as run_time_in_Seconds,run_time into #rawdata1 from (

select run_date,substring(convert(varchar(20),tt),1,2) + ':' + substring(convert(varchar(20),tt),3,2) + ':' +substring(convert(varchar(20),tt),5,2) as [run_time] from (

select sj.name,sh.run_date, sh.run_duration,

case

when len(sh.run_duration) = 6 then convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 5 then '0' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 4 then '00' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 3 then '000' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 2 then '0000' + convert(varchar(8),sh.run_duration)

when len(sh.run_duration) = 1 then '00000' + convert(varchar(8),sh.run_duration)

end as tt

from dbo.sysjobs sj with (nolock)

inner join dbo.sysjobHistory sh with (nolock)

on sj.job_id = sh.job_id

where sj.name = 'My Agent Job'

and [sh.Message] like '%The job%') a )b

Calculating the average run time

We now select each run time in seconds from #rawdata1 and place the values into another temporary table called #rawdata2. Once again we create a ‘key’, a hardwired ‘1’.

select 1 as Keyy, run_time_in_Seconds into #rawdata2 from #rawdata1

The purpose of doing so is to make the average time AVG() available to the query immediately without having to do adverse grouping.

Applying KPI Logic

At this point, we shall apply some logic to determine whether processing times are within the norms. We do this by applying colour names. Obviously, this example is a super one for SSRS and traffic light icons.

select rd1.run_date, rd1.run_time, rd1.run_time_in_Seconds ,Avg(rd2.run_time_in_Seconds) as Average_run_time_in_seconds,

case

whenConvert(decimal(10,1),rd1.run_time_in_Seconds)/Avg(rd2.run_time_in_Seconds)<= 1.2 then 'Green'

when Convert(decimal(10,1),rd1.run_time_in_Seconds)/Avg(rd2.run_time_in_Seconds)< 1.4 then 'Yellow' else 'Red'

end as [color],

Calculating the Average Run Time in Hours Minutes and Seconds and the end of the query.

case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))
end + ':' +
case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)
end + ':' + case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)
end as [Average Run Time HH:MM:SS] from #rawdata2 rd2 innerjoin #rawdata1 rd1
on rd1.keyy = rd2.keyy
group by run_date,rd1.run_time ,rd1.run_time_in_Seconds
order by run_date desc

The complete code example

use msdb
go
/*
drop table #rawdata1
drop table #rawdata2
go
*/
select 1 as keyy,run_date,(substring(b.run_time,1,2)*3600) + (substring(b.run_time,4,2)*60) + (substring(b.run_time,7,2)) as run_time_in_Seconds,run_time into #rawdata1 from (
select run_date,substring(convert(varchar(20),tt),1,2) + ':' +
substring(convert(varchar(20),tt),3,2) + ':' +
substring(convert(varchar(20),tt),5,2) as [run_time] from (
select name,run_date, run_duration,
case
whenlen(run_duration) = 6 then convert(varchar(8),run_duration)
whenlen(run_duration) = 5 then '0' + convert(varchar(8),run_duration)
whenlen(run_duration) = 4 then '00' + convert(varchar(8),run_duration)
whenlen(run_duration) = 3 then '000' + convert(varchar(8),run_duration)
whenlen(run_duration) = 2 then '0000' + convert(varchar(8),run_duration)
whenlen(run_duration) = 1 then '00000' + convert(varchar(8),run_duration)
end as tt
from dbo.sysjobs sj with (nolock)
innerjoin dbo.sysjobHistory sh with (nolock)
on sj.job_id = sh.job_id
where name = 'My Agent Job'
and [Message] like '%The job%') a ) b
select 1 as Keyy, run_time_in_Seconds into #rawdata2 from #rawdata1
select rd1.run_date, rd1.run_time, rd1.run_time_in_Seconds ,Avg(rd2.run_time_in_Seconds) as Average_run_time_in_seconds,
case
whenConvert(decimal(10,1),rd1.run_time_in_Seconds)/Avg(rd2.run_time_in_Seconds)<= 1.2 then 'Green'
when Convert(decimal(10,1),rd1.run_time_in_Seconds)/Avg(rd2.run_time_in_Seconds)< 1.4 then 'Yellow' else 'Red'
end as [color],
Case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)/(3600))
end + ':' +
case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)%(3600)/60)
end + ':' +
case
when len(convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)) = 1 then '0' + convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)
else convert(varchar(2),Avg(rd2.run_time_in_Seconds)%60)
end as [Average Run Time HH:MM:SS]
from #rawdata2 rd2 innerjoin #rawdata1 rd1
on rd1.keyy = rd2.keyy
group by run_date,rd1.run_time ,rd1.run_time_in_Seconds
order by run_date desc

Restoring databases to a set drive and directory

okeofs — Mon, 15 Apr 2013 22:18:00 GMT

Restoring databases to a set drive and directory

Introduction

Often people say that necessity is the mother of invention. In this case I was faced with the dilemma of having to restore several databases, with multiple ‘ndf’ files, and having to restore them with different physical file names, drives and directories on servers other than the servers from which they originated.

As most of us would do, I went to Google to see if I could find some code to achieve this task and found some interesting snippets on Pinal Dave’s website.

Naturally, I had to take it further than the code snippet, HOWEVER it was a great place to start.

Creating a temp table to hold database file details

First off, I created a temp table which would hold the details of the individual data files within the database. Although there are a plethora of fields (within the temp table below), I utilize LogicalName only within this example. The temporary table structure may be seen below:

create table #tmp

(

LogicalName nvarchar(128)

,PhysicalName nvarchar(260)

,Type char(1)

,FileGroupName nvarchar(128)

,Size numeric(20,0)

,MaxSize numeric(20,0),

Fileid tinyint,

CreateLSN numeric(25,0),

DropLSN numeric(25, 0),

UniqueID uniqueidentifier,

ReadOnlyLSN numeric(25,0),

ReadWriteLSN numeric(25,0),

BackupSizeInBytes bigint,

SourceBlocSize int,

FileGroupId int,

LogGroupGUID uniqueidentifier,

DifferentialBaseLSN numeric(25,0),

DifferentialBaseGUID uniqueidentifier,

IsReadOnly bit,

IsPresent bit,

TDEThumbPrint varchar(50)

)

We now declare and populate a variable(@path), setting the variable to the path to our SOURCE database backup.

declare @path varchar(50)

set @path = 'P:\DATA\MYDATABASE.bak'

From this point, we insert the file details of our database into the temp table. Note that we do so by utilizing a restore statement HOWEVER doing so in ‘filelistonly’ mode.

insert #tmp

EXEC ('restore filelistonly from disk = ''' + @path + '''')

At this point, I depart from what I gleaned from Pinal Dave.

I now instantiate a few more local variables. The use of each variable will be evident within the cursor (which follows):

Declare @RestoreString as Varchar(max)

Declare @NRestoreString as NVarchar(max)

Declare @LogicalName as varchar(75)

Declare @counter as int

Declare @rows as int

set @counter = 1

select @rows = COUNT(*) from #tmp -- Count the number of records in the temp

-- table

Declaring and populating the cursor

At this point I do realize that many people are cringing about the use of a cursor. Being an Oracle professional as well, I have learnt that there is a time and place for cursors.

I would remind the reader that the data that will be read into the cursor is from a local temp table and as such, any locking of the records (within the temp table) is not really an issue.

DECLARE MY_CURSOR Cursor

FOR

Select LogicalName

From #tmp

Parsing the logical names from within the cursor.

A small caveat that works in our favour, is that the first logical name (of our database) is the logical name of the primary data file (.mdf). Other files, except for the very last logical name, belong to secondary data files. The last logical name is that of our database log file.

I now open my cursor and populate the variable @RestoreString

Open My_Cursor

set @RestoreString =

'RESTORE DATABASE [MYDATABASE] FROM DISK = N''P:\DATA\ MYDATABASE.bak''' + ' with '

We now fetch the first record from the temp table.

Fetch NEXT FROM MY_Cursor INTO @LogicalName

While there are STILL records left within the cursor, we dynamically build our restore string. Note that we are using concatenation to create ‘one big restore executable string’.

Note also that the target physical file name is hardwired, as is the target directory.

While (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2) -- As long as there are no rows missing

select @RestoreString =

case

when @counter = 1 then -- This is the mdf file

@RestoreString + 'move N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.mdf' + '''' + ', '

-- OK, if it passes through here we are dealing with an .ndf file

-- Note that Counter must be greater than 1 and less than the number of rows.

when @counter > 1 and @counter < @rows then -- These are the ndf file(s)

@RestoreString + 'move N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.ndf' + '''' + ', '

-- OK, if it passes through here we are dealing with the log file

When @LogicalName like '%log%' then

@RestoreString + 'move N''' + @LogicalName + '''' + ' TO N’’X:\DATA1\'+ @LogicalName + '.ldf' +''''

end

--Increment the counter

set @counter = @counter + 1

FETCH NEXT FROM MY_CURSOR INTO @LogicalName

END

At this point we have populated the varchar(max) variable @RestoreString with a concatenation of all the necessary file names. What we now need to do is to run the sp_executesql stored procedure, to effect the restore.

First, we must place our ‘concatenated string’ into an nvarchar based variable. Obviously this will only work as long as the length of @RestoreString is less than varchar(max) / 2.

set @NRestoreString = @RestoreString

EXEC sp_executesql @NRestoreString

Upon completion of this step, the database should be restored to the server.

I now close and deallocate the cursor, and to be clean, I would also drop my temp table.

CLOSE MY_CURSOR

DEALLOCATE MY_CURSOR

Conclusion

Restoration of databases on different servers with different physical names and on different drives are a fact of life. Through the use of a few variables and a simple cursor, we may achieve an efficient and effective way to achieve this task.

On the move

Dave Ballantyne — Sun, 14 Apr 2013 19:01:41 GMT

Its been a fair few years since I started blogging on SQLBlogCasts and the time has now come to find a new home.

From now on (along with a few others of the UK SQL Server community) I shall be blogging on dataidol.com. My page will be at http://dataidol.com/davebally , please update any feeds etc and I hope to see you there.

Sub query pass through

SQL and the like — Fri, 22 Mar 2013 09:10:14 GMT

Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided that it is only necessary to process some data under a certain condition. By way of example, something like this : Create Procedure GetOrder @SalesOrderId integer, @CountDetails tinyint as Select SOH.salesorderid , case when @CountDetails = 1 then (Select count(*) from Sales.SalesOrderDetail SOD where SOH.SalesOrderID = SOD.SalesOrderID) end from sales.SalesOrderHeader...(read more)

Architects and Architectures

GavinPayneUK — Tue, 12 Mar 2013 18:55:45 GMT

In the last few months I’ve been presenting a new session at community events that’s had a positive reception that I’m pleasantly surprised at. It’s had several titles, but currently its “How to be a more successful architect” and I originally created it as an overview of my Microsoft Certified Architect journey, although I’ve taken it on a different course since then. Hopefully of interest to everyone The session is hopefully interesting to everyone in the IT industry, not just a specific type of...(read more)