SQL Ninja

Generate a GUID/newid() in SSIS 2008

2011-06-01T09:41:00.009+10:00

Inexplicably, SSIS doesn't natively include a method for generating new GUIDs in the Derived Column Data Flow Transformation.

The get-around is to create a custom Script Component. In SSIS 2008, we have a choice of using Visual Basic or C# to write the script code; I'm going to demonstrate a C# script.

Let's say we have a Data Flow Source and a Data Flow Destination.
(you can click on these pictures to enlarge):

For the sake of this demo, the only difference between the source and the destination is that the destination has a GUID column, with no default constraint which adds its own NEWID() on insert. So we need to generate the GUID in SSIS. The usualy was we'd add a new column worth of data is by using the Derived Column transformation, but as I've mentioned (and you've probably found if you're reading this) there isn't a new GUID option.

So we add a script component.

Choose the "Transformation" option, hit OK, and drag the Source's green arrow onto the Script Component.

Double click the script component, then "Inputs and Outputs", then expand Output 0 and Add Column. Call the column whatever you like, but I'm going to call it "SQLNinjaGUID" so you can see how it's referenced in the C# code. Change the data type of the column to unique identifier [DT_GUID].

Jump back to the script tab, make sure we're using Microsoft Visual C# 2008 as the ScriptLanguage, Edit Script, and replace ALL of the code with the following.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.SQLNinjaGUID = System.Guid.NewGuid();
    }

}

Now when you drag the Script Component's green arrow onto your Data Flow Destination, you should see SQLNinjaGUID (or whatever you called your new column) pop up as an available Input Column in the destination's Mapping tab.

Project Lucy

2011-05-26T10:02:00.003+10:00

Quest Software, the kind people behind SQLServerPedia are running an experiment in cloud based performance analysis called Project Lucy.

The idea is that you can upload a SQL .trc (Profiler trace) file, and Project Lucy will do some analysis for you - analysing CPU time, durations, IO etc. There's histograms of statement durations, and the ability to filter the trace easily by picking from drop downs.

All this is of course possible by uploading your trace file to a database table (or saving the trace output directly to a table) and writing your own SQL queries. However, Project Lucy's aim is to start mining the crowd sourced data to provide comparison between your trace and similar workloads uploaded by the rest of the community.

It's an interesting project, and like SQLServerPedia it needs community support to succeed. It's useful now, but to reach its potential it needs feeding with data. I've started uploading traces over the last couple of days, and I'm finding that the analyses available thus far are useful enough to keep you interested while Quest work on extending the functionalities. Each trace file you upload is its own "analysis", and all your analyses are saved for later appraisal.

Also, they're giving away a $50 Amazon voucher each day (to US residents only unfortunately), so that's a pretty good incentive to give it a try.

Get a list of all the Tables in a Database

2011-04-27T14:02:00.003+10:00

Script follows, with little to no fanfare!

SELECT 
        schemas.name + '.' + tables.name SchemaTableName
FROM 
        sys.tables 
  JOIN
        sys.schemas
     ON tables.schema_id = schemas.schema_id

Report Permissions

2011-02-25T11:35:00.002+11:00

A reader of this blog asked me this morning: "If you wanted to design a query to make a report for management that would show which AD Groups or users have access to which reports, how would you go about it."

This is what I quickly whipped up:

SELECT
         Catalog.Name ReportName
         ,Users.UserName
         ,Roles.RoleName
   FROM [dbo].[Catalog]
         JOIN
         dbo.PolicyUserRole
         ON   [Catalog].PolicyID = PolicyUserRole.PolicyID
         JOIN
         dbo.Users
         ON   PolicyUserRole.UserID = Users.UserID
         JOIN
         dbo.Roles
         ON   PolicyUserRole.RoleID = Roles.RoleID

Can anyone see any problems with that?

Microsoft Certified Master - Database Structures

2011-02-09T19:54:00.003+11:00

The MCM program is aimed at the uber-uber-uber SQL gurus, and from what I'm reading on various blogs it's no walk in the park for them.

The awesome thing that's popped up out of the newly revised program is that there are a bunch of free training material videos available.

While I'm not personally anywhere near thinking about attempting the MCM certification, I'm interested in hearing about what lies under the covers of SQL server from people who know it inside out. The absolute nitty gritty: like learning about DNA and mitochondria as opposed to the more systemic/anatomical view that the MCITP type courses cover.

The first video in the series covers database structures: the ways that data is stored and managed on the disk. It's presented by Paul Randal, who spent 9 years working on the storage engine for SQL server, so the info is really from the horse's mouth.

To paraphrase the summary of the video:

Records - the rows, or "slots" which make up our tables,
Pages - the 8kB chunks where the records live,
Extents - collections of 8 contiguous pages,
Allocation bitmaps - keep an eye on the extents, and;
IAM chains and allocation units - keep track of what's living where.

If you've a spare 42 minutes, check it out here.

Formatting SQL Code for Blogs

2011-02-08T09:13:00.006+11:00

Up until recently, all the SQL code on this blog looked awful: completely lacking in formatting and unreadable (though still useful!). It looked like this:

SELECT
*
FROM
Ow.My.Eyes.Hurt

Then I discovered The Simple-Talk Code Prettifier.

You pop your formatted SQL code in (copied out of SSMS or Visual Studio), choose the style of HTML (forums in my case), whether to correct indenting and lenth of tabs etc., and voila: html code is produced which results in something real pretty like the below:

SELECT
         *
   FROM
         Oh.That.Be.Nice

Thanks to the HOBT (Aaron Alton) for blogging about this tool better and sooner than I.

Importing an Excel Sheet to a Database Table.

2011-02-07T13:35:00.010+11:00

I've always found the SQL Server Import and Export Wizard a great way to pull data out of Excel into SQL Server. Except for when it doesn't work. It can be a frustrating beast, with all kinds of little idiosyncratic things to consider.

However, it's easily avoided for basic imports from Excel. If you just want a table in a SQL database which looks and feels exactly like the source data, this command is your new friend:

SELECT
         *
         INTO
         dbName.schemaName.tableName
   FROM
         OPENROWSET('Microsoft.ACE.OLEDB.12.0'
            , 'Excel 12.0;Database=excelFilePathName.xlsx'
            , [worksheetName$])

Works a charm, and avoids clicking through a GUI which tends to crash when there's any slight problems.

If you have any "missing provider" problems, which may happen on new 64 bit SQL servers, download the provider from Microsoft Access Database Engine 2010 Redistributable.

Which Reports Aren't Inherting Permissions?

2009-12-11T10:53:00.001+11:00

Managing security settings in Reporting Services through the Report Manager website is usually pretty easy. However I've found that sometimes things go astray. Today I found a few reports which weren't inheriting permissions from their parent directory. Easy enough to fix, you jump into the security settings for that report and set "Revert to Parent Security". However, I have dozens of reports in each of a dozen directories; if I suspect this problem is going on in other reports, I'm not keen on a "needle in a haystack" search.

Here's the code for identifying which reports are no longer inheriting permissions from their parent:

USE ReportServer
SELECT
         [Path],
         [Name]
   FROM
         [dbo].[Catalog]
   WHERE
         [PolicyRoot] = 1

Another Standards Discussion: Views and Business Rules

2009-07-13T12:08:00.002+10:00

Let’s say that the business you work for has also been around for many years, and in that time loads of little rules about the data have appeared. Market A works differently to market B. This car shouldn’t be included in this list of products. When pulling together a list of shoppers, we only care about those that have credit cards.

Every time a new project comes up, a new part of a public facing website or a new module in an in-house application, new stored procs are developed. Often, these procs are written by people who weren’t around when the DB schema involved was designed, or they haven’t got all of those little rules I discussed above memorized. Who does?

This is where Views can come in handy. Instead of building each of those annoying little rules into each new Stored Procedure that’s developed, build them into Views, and point all Stored Procs towards Views instead of Tables.

And I mean ALL Stored Procs. As a standard. If you follow this standard, you can be sure that when it’s time to implement a new business rule, you can find the appropriate View, alter some code, and all the dependant Stored Procedures will now follow that new business rule.

For example, you have a table called Lotto.Entry. It records who has entered a lottery, and how old they are. Supplying the website, there are three hundred stored procedures which access data from this table. The CEO at your company decides that it’s no longer ethical to be reporting on people under 17 who have entered the lottery. If all those 300 Stored Procedures are pointing towards the table, you’re faced with moving data around, or changing all of those procs. If the procs are instead pointing towards Lotto.vEntry, that view can have a “WHERE Entry.Age > 17” clause added. Other Views should point towards this View instead of the underlying Table, so that the business rules are “inherited” across the system.

Another advantage is that the business rules are centralized. You know to look towards the basic view of a table if you want to know how its data is handled.

Pretending to delete data is another thing that can be done in views. Let’s say you want the users of the website/UI to feel like they’re deleting data, but you want an audit trail on that data, and you simply want to hide it from the users. Add an “IsDeleted” field to your table, and a “WHERE IsDeleted = 0” clause to the base view of that table. The data is then easily “undeleted” in cases of errors.

Views are a centralized, transparent and predictable way of implementing rules in which your data is accessed.

Object Naming Standards

2009-07-09T12:35:00.001+10:00

Having a look through the new features of SQL2008, I was intrigued to find the new Declarative Management Framework. In short, it's a way to enforce various policies on your environments.

Without going too far into it (in this post), it made me think about what kinds of policies or standards I like to enforce as a Team Leader.

One particular area is in object naming conventions. This is always going to be an idiosyncratic/personal aspect of programming, but I’ve found that if one person takes the initiative, other people will follow. An agreed standard makes it easy to predict what an object will be called, cutting down on searching and guessing when you’re not familiar with a schema that someone else has designed.

Tables:
- No tables have a plural name, i.e. “Lottery.Entry”, not “Lottery.Entries”

Stored Procedures
- have the prefix “s”
- wherever possible include the main object they reference, what they are doing to the object, and by what parameters (if any, and only when there’s only one or two, otherwise use “filters” or “various” etc.) in the format “s[OBJECTNAME][ACTION]by[PARAMETERS]”. The main point is the object name and the action. This is open to a fair bit of interpretation, but here’s some examples
o Lottery.sEntrySelectByID
o Lottery.sEntryInsert
o Lottery.sResultSelectByFilters
o Lottery.sPredictionUpdate

- no underscores. One of my bugbears is guessing whether or not an underscore might separate one part of an object name from another. The simplest answer is to not use them

Views
- have the prefix “v”
- if they return fields from one table only, then they are named “v[TableName]”
- it they return fields mostly from one table, and reference other tables/views in order to bring back names etc,. for IDs in the original table, then they are named “v[TableName]Overview”
- no underscores

Triggers
- prefixed with “trig”
- suffixed with “AfterInsert”, “InsteadOfInsert” etc
- example: Lottery.trigEntryAfterInsert

Functions
- same rules as for stored procedures, but prefixed with “f”

Keys
- prefixed with “pk” for primary, “fk” for foreign, “uq” for unique
- perhaps “uk” would be more consistent for uniques? Hmmm I may have stuffed that one up over the last few years….
- reference the object name and the fields: e.g. fkEntry_LotteryID
- hey what’s that underscore doing there? I’m fine with underscores in objects that are rarely referenced which writing code off the top of your head. I think they’re fine in keys, indices etc., anything that’s not a view, proc or table

Indices
- ixTABLENAME_FieldName1_FieldName2 etc
- no need to reference INCLUDEd columns

Constraints
- “df” prefix for default constraints, e.g. dfEntryName
- “ck” prefix for Check Constraints

I’m sure there are plenty of people who would vehemently disagree with some of the choices I’ve made above. That’s great, and I’d love to hear why! My main point though is that having standards helps save time by making it easy for fellow developers/DBAs to predict what your objects are called when they’re searching for them. In a database like the main one I manage at the moment, where there are now over 10,000 objects, this can save some of the precious sanity we have left.

It's been a while!

2009-07-06T22:35:00.002+10:00

Excuses? Work, Vietnam, Cambodia, Malaysia, music composition duties. It's been a while since I've blogged. Well I haven't forgotten about SQL, in fact it's high on the priority list.

I've just received some new SQL books in the mail. It's time to have a go at some certifications again. 70-432 and 70-433, Database Implementation and Maintenance, and Database Design, respectively. Flicking through the books, there's so much I already know, but I've never actually been near SQL2008, so there's also some huge chunks that are absolute news to me.

I'm also starting a Masters of Information Technology at the University of New England, part-time by "distance education". My aim there is to broaden my knowledge of IT, being a person who "fell into" IT at work rather than doing an IT based degree first. The degree I completed in 1995 was a Bachelor of Science, majoring in microbiology and genetics. Now I'm a DBA. Go figure! Apparently it's not such a rare thing for your degree to not have a huge bearing on your career path anymore.

Well, anyhow, just wanted to let y'all know I'm still here. Catch you soon.

Database Performance Monitoring on the Cheap Part 2

2009-02-23T12:49:00.002+11:00

I was pretty happy to see that my last post "Database Performance Monitoring on the Cheap" got a bunch of hits on the intertubes. This somewhat balanced out my shame that the procedure I provided for querying the recorded data needed to be a bit smarter to be truly helpful.
Turns out that the stats provided by the sys.dm_os_performance_counters DMV are not all straightforward to interpret.
Some of them are either ratios (where one counter needs to be divided by another), or they are cumulative (where the counter needs to be compared to a previous value, and divided by the number of seconds elapsed).
Thankfully, there's an easy way to tell which counters are which, by checking the cntr_type field. When it comes to ratios, the numerator type is 537003264, and the denominator (or base) is 1073939712 (and has the word "base" at the end of the counter_name). The cumulative fellows are of type 272696576.

So without further ado, here's a smarter way to query that data which we've had collecting:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
   @FormerDate DATETIME,
   @LatterDate DATETIME
         AS
   BEGIN
   SELECT
            DateTimeID
            ,DATETIME
            ,OBJECT_NAME
            ,counter_name
            ,instance_name
            ,cntr_value
            ,cntr_type
      FROM
      (
      SELECT
               DateTimeID
               ,DATETIME
               ,OBJECT_NAME
               ,counter_name
               ,instance_name
               ,cntr_value
               ,cntr_type
         FROM
               Monitoring.PerformanceCounters
         WHERE
               NOT PerformanceCounters.cntr_type IN (272696576,537003264,1073939712) --Cumulative and Ratio counters
         UNION
      SELECT --Cumulative Counters
               CurrentPerformanceCounters.DateTimeID
               ,CurrentPerformanceCounters.DATETIME
               ,CurrentPerformanceCounters.OBJECT_NAME
               ,CurrentPerformanceCounters.counter_name
               ,CurrentPerformanceCounters.instance_name
               ,(CurrentPerformanceCounters.cntr_value-PreviousPerformanceCounters.cntr_value)/
               DATEDIFF(ss,PreviousPerformanceCounters.DATETIME,CurrentPerformanceCounters.DATETIME) cntr_value
               ,CurrentPerformanceCounters.cntr_type
         FROM
               Monitoring.PerformanceCounters CurrentPerformanceCounters
               JOIN
               Monitoring.PerformanceCounters PreviousPerformanceCounters
               ON CurrentPerformanceCounters.counter_name = PreviousPerformanceCounters.counter_name
            AND CurrentPerformanceCounters.instance_name = PreviousPerformanceCounters.instance_name
            AND CurrentPerformanceCounters.DateTimeID
         = PreviousPerformanceCounters.DateTimeID
               + CASE RIGHT(CurrentPerformanceCounters.DateTimeID,2)
                  WHEN 00 THEN 45
         ELSE 5
         END
         WHERE
               CurrentPerformanceCounters.cntr_type = 272696576
         UNION
      SELECT --Ratio Counters
               NumeratorPerformanceCounters.DateTimeID
               ,NumeratorPerformanceCounters.DATETIME
               ,NumeratorPerformanceCounters.OBJECT_NAME
               ,NumeratorPerformanceCounters.counter_name
               ,NumeratorPerformanceCounters.instance_name
               ,NumeratorPerformanceCounters.cntr_value/CAST(DenominatorPerformanceCounters.cntr_value AS FLOAT) cntr_value
               ,NumeratorPerformanceCounters.cntr_type
         FROM
               Monitoring.PerformanceCounters NumeratorPerformanceCounters
               JOIN
               Monitoring.PerformanceCounters DenominatorPerformanceCounters
               ON DenominatorPerformanceCounters.cntr_type = 1073939712
            AND NumeratorPerformanceCounters.DateTimeID = DenominatorPerformanceCounters.DateTimeID
            AND NumeratorPerformanceCounters.instance_name = DenominatorPerformanceCounters.instance_name
         WHERE
               NumeratorPerformanceCounters.DATETIME BETWEEN @FormerDate 
            AND @LatterDate
            AND NumeratorPerformanceCounters.cntr_type = 537003264
      ) AllResults
      WHERE
            AllResults.DATETIME BETWEEN @FormerDate 
         AND @LatterDate
   END

Database Performance Monitoring on the Cheap

2009-02-18T18:31:00.011+11:00

The system views are (to many) hidden gems in SQL. One in particular I'd like to address today is [sys].[dm_os_performance_counters], available in SQL2005 (and I assume 2008).

A quick SELECT * FROM [sys].[dm_os_performance_counters] will show you all that's available; a whole range of performance counters that you'd often go to Perfmon to check out.

The advantage of having this all so simply queryable from SQL is that we can start recording this every x minutes/hours, and start charting it out, and get some ideas about some of the weaknesses of our DBs, when they're under load etc.

First we need somewhere to store the data:


   ----------------------------------------------------------------------------------------
   --
   -- Create a schema
   --
   ----------------------------------------------------------------------------------------
CREATE SCHEMA [Monitoring];
   
   ----------------------------------------------------------------------------------------
   --
   -- Create a table for the stats to live in
   --
   ----------------------------------------------------------------------------------------
CREATE TABLE [Monitoring].[PerformanceCounters](
            [DateTimeID] [bigint] NOT NULL,
            [DateTime] [datetime] NOT NULL,
            [object_name] [nvarchar](128) NOT NULL,
            [counter_name] [nvarchar](128) NOT NULL,
            [instance_name] [nvarchar](128) NULL,
            [cntr_value] [bigint] NULL,
            [cntr_type] [int] NULL,
            CONSTRAINT [pkPerformanceCounters] PRIMARY KEY CLUSTERED
      (
               [DateTimeID] ASC,
               [counter_name] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
   ) ON [PRIMARY]

I've used a DateTimeID field as I don't like using Datetimes as part of primary keys, and also they can be useful in any Analysis Services you might want to do down the road.

Now for a proc to record some counters (feel free to pick and choose which counters you think are appropriate, and please let me know if you've any suggestions):


   ----------------------------------------------------------------------------------------
   --
   -- Create a procedure which will query the DMVs and store the results for reporting
   --
   ----------------------------------------------------------------------------------------
CREATE PROCEDURE [Monitoring].[sPerformanceCountersInsert]
         AS
   BEGIN
   INSERT INTO
               [Monitoring].[PerformanceCounters]
      SELECT
            CAST(DATEPART(YEAR, GETDATE()) AS bigint)*100000000+
            DATEPART(MONTH, GETDATE())*1000000+
            DATEPART(DAY, GETDATE())*10000+
            DATEPART(hour, GETDATE())*100+
            DATEPART(minute, GETDATE()),
            GETDATE(),
            [dm_os_performance_counters].[object_name],
            [dm_os_performance_counters].[counter_name],
            [dm_os_performance_counters].[instance_name],
            [dm_os_performance_counters].[cntr_value],
            [dm_os_performance_counters].[cntr_type]
      FROM
            [TheDBYouWantToMonitor].[sys].[dm_os_performance_counters]
      WHERE
      (
               OBJECT_NAME = 'SQLServer:Buffer Manager'
            AND (
                  counter_name = 'Page life expectancy'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:General Statistics'
            AND (
                  counter_name = 'User Connections'
               OR counter_name = 'Processes blocked'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Databases'
            AND instance_name = 'TheDBYouWantToMonitor'
            AND (
                  counter_name = 'Transactions/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:Access Methods'
            AND (
                  counter_name = 'Full Scans/sec'
               OR counter_name = 'Range Scans/sec'
               OR counter_name = 'Index Searches/sec'
               OR counter_name = 'Page Splits/sec'
               OR counter_name = 'Table Lock Escalations/sec'
         )
      )
         OR (
               OBJECT_NAME = 'SQLServer:SQL Statistics'
            AND counter_name = 'SQL Re-Compilations/sec'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Memory Manager'
            AND counter_name = 'Memory Grants Outstanding'
      )
         OR (
               OBJECT_NAME = 'SQLServer:Transactions'
            AND counter_name = 'Transactions'
      )
   END

This is all pretty DB centric stuff, it's unfortunate that more server level stats aren't available with this method, such as memory and disk usage.

Now we just need to set up a SQL Server Agent job to fire off that job every x minutes/hours. That'll depend on how busy your DB is, on my main prod DB I've got this guy running every 5 minutes, and a similar proc recording Data and Log file sizes on a daily basis.

Once you've got enough historical data, it's a simple matter to query:

CREATE PROCEDURE [Monitoring].[sPerformanceCountersSelectByDateRange]
   @FormerDate DATETIME,
   @LatterDate DATETIME
         AS
   BEGIN
   SELECT
            [DateTimeID]
            ,[DateTime]
            ,[object_name]
            ,[counter_name]
            ,[instance_name]
            ,[cntr_value]
            ,[cntr_type]
      FROM
            [Monitoring].[PerformanceCounters]
      WHERE
            [DateTime] BETWEEN @FormerDate 
         AND @LatterDate
   END

I'm hooking this proc up into a Reporting Services report at the moment, charting each counter seperately. It's quite interesting to see the shapes of the charts, and spurring me into understanding more about what each one really means. It's great stuff to show to mgt as well, giving them a bit of a look into the black box that is their SQL Server.

Warning: Don’t play with Reporting Services’ SQL Agent Jobs! Or How to Tell Which Job is Doing What

2009-01-16T08:46:00.006+11:00

Having a look through SQL Server Agent’s list of jobs yesterday, I got a bit upset. About half the jobs in there are named things like “0343229B-0642-4E38-B7A5-C603C1F45976”. They’re Reporting Services Subscription jobs. Once again RS looks like a half-arsed product.

So I decide to go about renaming them, figuring that RS’ subscriptions will still be able to recognize the jobs as I’m only changing their names, not their IDs. Bad move. RS uses the names, can’t find the jobs when you restart the service, and recreates all those jobs with new GUIDs. Worse yet, I started getting “Only members of sysadmin role are allowed to update or delete jobs owned by a different login” errors whenever I tried to update subscriptions through the Report Manager, forcing me to have to play around with login permissions and job owners. A nightmare!

The script I wrote to help me recognize which job fires which subscription is below. It’s now more useful than ever:

NB: The CASE statement which transforms the “DaysOfWeek” int figure into actual days of the week doesn’t cover ever possible case, but it covered my needs. For a rundown of how this int works, see “Toolman’s” post at http://www.sqlservercentral.com/Forums/Topic501408-150-1.aspx

Also thanks to “stevefromOZ” from whose post at http://www.sqlservercentral.com/Forums/Topic254010-150-1.aspx I nabbed the email address part of the code below.

USE ReportServer
SELECT
         sysjobs.name,
   'RS - '
         + Catalog.Name
         + ' ['
         + CASE
            WHEN DaysOfMonth IS NOT NULL
            THEN CAST(DaysOfMonth AS VARCHAR(10)) + ' Day of Month'
            WHEN DaysOfWeek = 1 THEN 'Monday'
            WHEN DaysOfWeek = 2 THEN 'Tuesday'
            WHEN DaysOfWeek = 4 THEN 'Wednesday'
            WHEN DaysOfWeek = 8 THEN 'Thursday'
            WHEN DaysOfWeek = 16 THEN 'Friday'
            WHEN DaysOfWeek = 32 THEN 'Saturday'
            WHEN DaysOfWeek = 64 THEN 'Sunday'
            WHEN DaysOfWeek = 62 THEN 'Monday - Friday'
            WHEN DaysOfWeek = 120 THEN 'Wednesday - Saturday'
            WHEN DaysOfWeek = 126 THEN 'Monday - Saturday'
            WHEN DaysOfWeek = 127 THEN 'Daily'
   END
         + ' '
         + CAST(DATEPART(hh,Schedule.StartDate)AS VARCHAR(2))
         + CASE
            WHEN LEN(CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))) = 1
            THEN ':0' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   ELSE ':' + CAST(DATEPART(n,Schedule.StartDate)AS VARCHAR(2))
   END
         + ']' [NewName]
   FROM
         msdb.dbo.sysjobs
         JOIN
         dbo.ReportSchedule
         ON sysjobs.name = CAST(ReportSchedule.ScheduleID AS VARCHAR(255))
         JOIN
         dbo.Schedule
         ON ReportSchedule.ScheduleID = Schedule.ScheduleID
         JOIN
         dbo.Catalog
         ON ReportSchedule.ReportID = Catalog.ItemID
   ORDER BY
         Catalog.name

Querying ReportServer Database

2009-01-15T11:48:00.009+11:00

Here's a script I knocked up which gives you some insights into your Reports Catalog in Reporting Services.

I got frustrated with the Reports Manager site and its inability to give you a wholistic view of subscriptions, and the script blew out a little from there as I had a scrounge around the ResportServer database structure.

I might hook this up to an RS report at some stage, if that's not akin to "crossing the streams".

Note that info about the number of executions and last execution time are based on the execution log, which only keeps records for the last 60 days by default.

USE ReportServer
SELECT
         CatalogParent.Name ParentName,
         Catalog.Name ReportName,
         ReportCreatedByUsers.UserName ReportCreatedByUserName,
         Catalog.CreationDate ReportCreationDate,
         ReportModifiedByUsers.UserName ReportModifiedByUserName,
         Catalog.ModifiedDate ReportModifiedDate,
         CountExecution.CountStart TotalExecutions,
         ExecutionLog.InstanceName LastExecutedInstanceName,
         ExecutionLog.UserName LastExecutedUserName,
         ExecutionLog.Format LastExecutedFormat,
         ExecutionLog.TimeStart LastExecutedTimeStart,
         ExecutionLog.TimeEnd LastExecutedTimeEnd,
         ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
         ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
         ExecutionLog.TimeRendering LastExecutedTimeRendering,
         ExecutionLog.Status LastExecutedStatus,
         ExecutionLog.ByteCount LastExecutedByteCount,
         ExecutionLog.[RowCount] LastExecutedRowCount,
         SubscriptionOwner.UserName SubscriptionOwnerUserName,
         SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,
         Subscriptions.ModifiedDate SubscriptionModifiedDate,
         Subscriptions.Description SubscriptionDescription,
         Subscriptions.LastStatus SubscriptionLastStatus,
         Subscriptions.LastRunTime SubscriptionLastRunTime
   FROM
         dbo.Catalog
         JOIN
         dbo.Catalog CatalogParent
         ON Catalog.ParentID = CatalogParent.ItemID
         JOIN
         dbo.Users ReportCreatedByUsers
         ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
         JOIN
         dbo.Users ReportModifiedByUsers
         ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
         LEFT JOIN
   (
   SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) LatestExecution
         ON Catalog.ItemID = LatestExecution.ReportID
         LEFT JOIN
   (
   SELECT
            ReportID,
            COUNT(TimeStart) CountStart
      FROM
            dbo.ExecutionLog
      GROUP BY
            ReportID
   ) CountExecution
         ON Catalog.ItemID = CountExecution.ReportID
         LEFT JOIN
         dbo.ExecutionLog
         ON LatestExecution.ReportID = ExecutionLog.ReportID
      AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
         LEFT JOIN
         dbo.Subscriptions
         ON Catalog.ItemID = Subscriptions.Report_OID
         LEFT JOIN
         dbo.Users SubscriptionOwner
         ON Subscriptions.OwnerID = SubscriptionOwner.UserID
         LEFT JOIN
         dbo.Users SubscriptionModifiedByUsers
         ON Subscriptions.ModifiedByID = SubscriptionModifiedByUsers.UserID
   ORDER BY
         CatalogParent.Name,
         Catalog.Name

The Command Line

2009-01-06T11:44:00.004+11:00

I didn't go to school to learn about computers, LIFE taught me about computers. I learned SQL on the job (well, maybe there's lots of study that's gone on since).

As a consequence, while I can write T-SQL better than I can write complete sentences in english, there's at least a few little basics that have escaped me along the line. So yesterday when I was trying to work out how to bring up a remote failed cluster node onto which I could no longer remote desktop, it was a revelation to me when someone told me how to remotely reboot a computer from the command line.

I thought "how do people know this black magic"?

And then I found the goods: An A-Z Index of the Windows XP command line

which looks quite similar to the TechNet article, but I'm not complaining.

Backups

2008-12-18T09:00:00.003+11:00

There's quite a jump between being proficient in T-SQL/SSMS and understanding the guts of SQL Server. For example, being able to backup and restore databases is a handy skill, but it's only the tip of the iceberg. Under the water is the differences between the recovery models, the different types of backups, and the effects that doing backups have on recoverability and growth/maintenance of logs.

As mentioned in my post on "ongoing education", it's helpful to have reference resources which talk to you like a human, and pictures are pretty helpful too. So I'd like to pass on this amazing site: http://www.sqlbackuprestore.com/

It'll get you up to speed on the nitty-gritty of backups and restores a lot faster than any book or course I've attempted slogging through.

Just check it out if you're needing help, especially if you use Red Gate's backup gear, as he knows the innards of that lot intimately and provides a great lot of code samples which have saved me quite a few hours over the last few days.

Modifying your TempDB setup

2008-12-15T13:38:00.003+11:00

Today I was moving the tempDB on a new server from the drive where every other DB data file sits to its own dedicated drive, with the intention of preventing drive contention.

To cut a long story short (and anyway I'm not even sure how I got myself into such a tangle) here's a couple of annoyances and their fixes:

Annoyance #1 - Reducing the Initial File Size of TempDB: If you right click a database, go into Properties, and Files, you can see the Initial file size of the database. Let's say that someone else set this DB up, and you'd like the initial size to be smaller. This is only really a problem you're going to get with TempDB, which re-creates each time you restart the SQL Service btw. So you type in a new value, and hit OK. Well, not OK, because if you re-open that same GUI, you'll see that your changes have not been kept! ARGH!

Solution #1 - Issue a DBCC SHRINKFILE (N'file' , size in MB) where the size in MB is an amount LESS than the initial size you wish to set. Then, ALTER DATABASE tempdb MODIFY FILE (NAME = 'file', SIZE = new initial size)

Annoyance #2 - Reducing the Number of Database Files: So you've got TempDB split up into too many files. So you go into the Properties, Files etc and remove the file(s), hit OK and they file's disappeared from the hard drive. But upon restarting the SQL Service for whatever reason, the files get recreated on the hard drive!

Solution #2 - They've got to be explicity removed from the system catalogue it seems. ALTER DATABASE tempdb
REMOVE FILE (NAME = tempdev2, FILENAME = 'c:\tempdev2.ndf')

Little secrets.

Ongoing Education for the Terminally Lazy/Busy

2008-12-05T13:56:00.000+11:00

Reading msdn articles and whitepapers and textbooks blows goats as far as I'm concerned. I'm interested for about 5 minutes, max.

However I’ve been learning a lot about SQL (as well as plenty of other things) since I got myself set up in the RSS world, subscribing to blog and site feeds in Google Reader.

Maybe I’m preaching to the choir on this blog entry about RSS, but amongst the DB people I know very few seem that savvy about efficient net usage.

These sites below publish regularly, and when you subscribe to them in Google Reader, you can get a heap of relevant information into your unread “inbox” instead of regularly checking sites to see if anything’s updated.

If you’ve got a gmail account, you’re crazy not to start using Reader, and it works great on mobile phones cutting down bandwidth hungry sites into nice little readable chunks.

So, below, are my favourite sites/blogs which update regularly with useful SQL goodies. At least check them out, and if you’re not already in the RSS feed world, get yourself subscribed to these guys in Google Reader and you’ll be picking up more info than ever in a digestible fashion.

Blogs:
http://www.brentozar.com/
http://statisticsio.com/
http://itknowledgeexchange.techtarget.com/sql-server/
http://blog.sqlauthority.com/

For sites, check out:
http://sqlserverpedia.com/wiki and http://www.sqlservercentral.com/

SQL Ninja, Hardware Concerns

2008-09-23T22:07:00.000+10:00

It turns out that the title of my blog is also the name of a SQL injection attack generator program! I knew it was too good to be true, but did I bother Googling it first? No, that'd be like reading the manual..

Ah well, moving on with life.

Today's problem: you've got to provide a report on the state of health of your server hardware, and you've got to provide it quickly.

Quickest way? The Real Time server stats in SQL Nexus will give you a look at the server performance as a current snapshot.

Time to hit PerfMon direct instead if you need to see more in-depth and chart it out over time, like say over the next hour. But what to monitor?

Measuring a database server's performance (as opposed to the performance of the database code and objects), is really down to looking at a few parts: the CPU, the disk system, & the RAM.

For the CPU: enable Processor - % Processor Time and System - Processor Queue Length. The lower for both of these the better, hopefully less that 50% for the former and less than 2 per CPU for the latter.

For the disk system: % Disk Time (aim for average less than 50%), Average disk queue (like the CPU, less than 2 per disk is good), average writes per second and average reads per second (both of these depend on the disk capacity, against which you'll need to compare and hopefully you'll be using less than 85%).

For RAM: Memory - pages per sec (lower the better, less than 20 preferably) and Available Bytes (at least 10% of the total RAM). With a grain of salt taken, SQL Server Buffer Mgr - Buffer Cache Hit Ratio should be at least 99%, and the Page Life Expectancy should be above 300 seconds. I mention the grain of salt, as coding quality can affect these counters, an apparent lack of RAM may really be a case for proper indexing or another DB optimisation problem in disguise. On the other hand, RAM may be cheap relative to development costs..

Set these counters up, and save a log, polling every 15 seconds or less if there are performance problems (and be aware that running PerfMon will cause its own slight performance hit, as will saving the logs on the local disk if you have to). The log can be reloaded into PerfMon to see averages, maxes and mins and charting of the performance over the monitoring time.

These counters aren't the be all and end all, but should be enough to spot if there's a problem in one or more of the main systems, and suggest how the server can be beefed up if necessary.

Hello World

2008-09-11T12:39:00.001+10:00

I just found the first cute little bit of SQL I ever wrote at work, on the 6th of October 2004;

USE Scott_Test
DECLARE @TestNum INT
   SET @TestNum = 1047
SELECT SHID_ID, SHID_Complete, SHID_SMPNo, SHID_Date, SHID_TotalShares, SHID_PortionAnalyzed,
         SHID_complete, SHID_NoExtracts, SHID_EnteredBy, SHID_ReportSentDate,
         SHID_ReportSentBy, SHID_Stage 
   FROM Shid 
   WHERE SHID_COID = @TestNum 
      AND SHID_Stage <> 0
   ORDER BY SHID_ID DESC
SELECT SHID_ID, SHID_Date, SHID_InternalDeadLine, SHID_CompletionDate, SHID_Benchmark,
         SHID_NoExtracts, Shid_Comment 
   FROM Shid 
   WHERE SHID_COID = @TestNum 
      AND SHID_Stage = 0
   ORDER BY SHID_ID ASC

SQL Nexus Tool

2008-09-10T18:01:00.001+10:00

Amazingly, even in SQL 2008 there's no performance testing/tuning GUI. But of course, you just need to know where to look to find free tools. Welcome to Codeplex.

SQL Nexus takes advantage of the SQLDiag command line tool that ships with SQL Server, sucking in DMV and Trace data to present in a great drill-through Reporting Services based interface.

Within an hour, I'd identified 2 hardware based issues with solid evidence to present to management.

Tip: Read the installation steps carefully; it's not an all-in-one install. Also, when directed towards a download of PerfStatsScript.zip, look here for the download.

Measuring Performance of Stored Procedures

2008-09-08T11:19:00.000+10:00

Lately I've been faced with dual problems: how to spot which stored procs are performing the worst within an application which has been released, and secondly, how to help developers write code that runs quickly before deploying it to production.

Of course you can run Traces, or have a look into System Views, but knowing the how is a lot different to knowing the whiches, whens and whys. For example, which trace events do you enable?

This article by Preethiviraj Kulasingham at SQL Server Universe set off a few light bulbs for me.