Tag SQL

Entries 118 Total

Getting the ISO Week and Year

Dec 29, ’09 3:00 PM

Long story short: I need the ISO Week. We need to be able to make sure that when we’re reporting, we’re pulling data for the full week (which happens to coincide with ISO Week at my employer).

The problem with using an existing function is that the existing functions just return the week and not the full year + week combination. I used the function from the Less Than Dot wiki as a starting point and added my own code to create the full YYYYWW string to give me an ISO Week. This isn’t actually in the ISO standard format for reporting the ISO week, but I also don’t care so long as I can use the ISOWeek for effective querying.

Enjoy

ALTER FUNCTION ISOweek (@DATE DATETIME)
RETURNS VARCHAR(6)
AS
  BEGIN
    DECLARE @ISOweek INT,
      @year INT,
      @rVal VARCHAR(6) ;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yyyy, @DATE) AS CHAR(4)) + '0104') ;
    SET @year = DATEPART(yyyy, @DATE) ;

    SET @rVal = CAST(@year AS VARCHAR(4)) + RIGHT('00' + CAST(@ISOWeek AS VARCHAR(2)), 2)

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
      BEGIN
        SET @rVal = dbo.ISOweek(CAST(DATEPART(yyyy, @DATE) - 1 AS CHAR(4))
                                   + '12'
                                   + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2)))
                                   + 1 ;
      END

    --Special case: Dec 29-31 may belong to the next year
    IF (
        (DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
       )
      BEGIN
        SET @ISOweek = 1 ;
        SET @year = DATEPART(yyyy, @DATE) + 1 ;

        SET @rVal = CAST(DATEPART(yyyy, @DATE) + 1 AS VARCHAR(4)) + '01'
      END

    RETURN @rVal ;
  END
GO

This lets me do some snazzy reporting hackery like this:

SELECT  MIN(c2.[Date]) AS StartDate,
        MAX(c2.[Date]) AS EndDate
FROM    dbo.Calendar AS c
        INNER JOIN dbo.Calendar AS c2 ON c.ISOWeek = c2.ISOWeek
WHERE   c.[Date] = '20091229' ;

/*
StartDate               EndDate
----------------------- -----------------------
2009-12-27 00:00:00.000 2010-01-02 00:00:00.000
*/

Which is then used to feed report parameters.

Comments (4)

The PowerShell of the Future

Dec 15, ’09 9:00 AM

PowerShell Today

Allen White recently presented for CBusPASS about PowerShell. First off, I’d like to say thanks to Allen for driving down from Canton, OH to speak in person. He took the time to stick around afterwards and chat about a bunch of things that were on user group members’ minds.

Allen’s presentation covered the basics of PowerShell through concrete examples – automating backups, restores, and database creation. Throughout the entire process, Allen demonstrated many aspects of PowerShell that make the DBA’s life easier. Allen demonstrated how it’s possible to easily work with data from files, the registry, and SQL Server simultaneously through PowerShell. Many of these things would take a considerable amount of work with T-SQL, if they’re even possible at all.

Interestingly, one of the first things I was reminded of was how much PowerShell looks like a variety of programming languages, specifically Perl. One of the nicest aspects of Perl is that it’s a glue language. Glue languages help you piece together various pieces and parts of your servers that have no real way to communicate with each other. PowerShell makes it possible to do the same thing, natively, on Windows.

PowerShell, as part of the Common Engineering Criteria, is enabled in all of the newer Microsoft server products. So, you can be sure that PowerShell is available as part of SQL Server, Exchange Server, Windows Server 2008, SharePoint, and many other products that I’ve forgotten. This will make it easier to take care of a larger number of servers and diverse server products using a single language.

This is the state of PowerShell right now. PowerShell 2.0 is available with Windows 7 and Windows Server 2008 R2.

What’s really exciting is PaSH.

PowerShell Tomorrow

Object reference not set to an instance of a phone

Object reference not set to an instance of a phone

PaSH is an open source reimplementation of PowerShell for other operating systems – Linux, OS X, Windows Mobile.

Think about that for a minute… Did you really wait for 60 seconds to think about that? Good.

With PaSH I could VPN to my network, fire up my PowerShell prompt on my phone, and start diagnosing server problems. Or run a remote backup. Or failover a cluster. Or any number of wonderful and amazing things.

Here’s where it gets cooler: If PaSH catches on, we could soon see similar libraries for MySQL or Apache. Maybe someone will even write a layer that maps common Windows and SQL Server functionality to their open source equivalents. A heterogenous datacenter becomes a lot easier to manage when you can write maintenance scripts using a common language across all platforms.

Looking forward, this isn’t so far fetched. The open source community has embraced mono, the open source version of the .NET Framework – I have mono and MonoDevelop (the mono IDE) installed on my mac. It’s included in several Linux distributions by default. It isn’t that much of a stretch to imagine PaSH becoming an effective and accepted cross-platform administrative scripting language.

Comments (1)

Links for the Week of 2009.12.04

Dec 4, ’09 9:00 AM

SQL Server

  • Kendal Van Dyke: Delegation: What It Is And How To Set It Up – The title says it all, folks.
  • wmarow’s disk & disk array calculator – Storage calculator. This wouldn't be terribly fascinating, except I know the performance characteristics of our I/O subsystem and when I plug in the variables, this is pretty close to real life performance. (Not directly SQL Server related, but it’s my blog so it goes where I wants it.)
  • A Loan At Last! – Brad Schulz thoroughly describes how to create a loan payment schedule stored procedure using nothing but T-SQL. The end result is beautiful to behold.

Development

Stuff & Things

Comments (1)

How Do You Use SQL Server

Dec 2, ’09 9:00 AM

Grant Fritchey took it upon himself to tag me in his most recent blog post about how we use SQL Server. The best part about this is I was just hired Senior DBA, so I can write with the unbridled enthusiasm of someone who is showing off a new car.

Here’s a little bit of background – my employer provides freight, utility, and telecom expense management services. In short, we pay your bills, we hand key your bills, we figure out how to EDI your bills – we do everything we can to process as many bills as possible every second of every day of the year. We were also a bank – expenses can be justified but you better have a damn good reason for wanting to spend 27k per CPU for Enterprise Edition vs 7k per CPU for Standard Edition. My production servers all run Standard Edition.

In a way, though, I’m very lucky. I have a homogenous environment. From development to production, everything is running on SQL Server 2008 sitting on top of Windows Server 2008. Every server gets patched on the same day.

On to the reason why you came here: how we actually use SQL Server.

Most people have an application tier that sits on top of some kind of data access layer that eventually talks to an RDBMS through some kind of query generation engine. The application tier makes decisions and does most of the work and talks to the database to get a result set and then pukes that into a grid on a screen somewhere. We don’t really have that.

Long before I arrived a decision was made to treat SQL Server as an application platform. SQL Server is the core of our business. Before someone gets all up in arms, let me explain.

When I said that we process a large quantity of bills I want you to think massive piles of paper bills being hand keyed by an army of people. Bills are being typed in, scanned in, sent in via EDI, pigeon, and Star Trek teleporter at all hours of the day. We are doing everything we can to process as much data as we can. We do batch processing. We do single line item processing. We do all kinds of crazy transformations on the data.

Guess where that all happens. That’s right: in the database. Sure, we have an application layer that sits on top of the database, but it accesses the database through stored procedures and views. Yes, there is logic in the stored procedures. I am very strongly of the opinion that universal data logic belongs in the database. We make heavy use of stored procedures, constraints, views, indexed views, summary jobs… all the traditional aspects of RDMBS development. We’re leveraging SQLCLR to keep some heavy lifting in SQL Server but move it into the CLR where it belongs and also to give us the ability to create high performance ad hoc reporting capabilities.

Things are very much in their infancy right now, I’m the first full time DBA at this company. Are things smooth right now? Not always. We’ve had runaway processes bring a production machine to its knees for hours. Our SAN isn’t configured optimally and I/O waits account for 1/3 of the time waits on all of our production machines.

Data is still coming into the database from a legacy mainframe application written in COBOL. The monitoring is still largely a collection of hand written scripts that were thrown together by myself and the previous DBA – my manager. On the bright side, both my boss and his boss were DBAs at one point. They understand my pain points and they’re willing to give me the time to get things up and running the way they should be.

In a few years, we’re replacing it with a brand new .NET application. We’ll still be using stored procedures for data access and business logic, but we’ll be expanding to use even more SQLCLR. We’ll also be picking up SQL 2008 R2 or maybe even SQL11 by that point and leveraging new technology like StreamInsight and Master Data Management. Hopefully I’ll be implementing things that were new in 2005/2008 but haven’t had the opportunity to use like Service Broker, Change Data Capture, and Performance Data Collector.

To me, this is heaven. SQL Server is in the place it should be – at the core of the business.

Victims, victims… I need victims… I’m going to tag Tim Benninghoff and Mladen Prajdić.

Comments (2)

SQL Server Presenters on Twitter

Nov 25, ’09 8:00 AM

I put out a call a while back for a list of SQL Server presenters who are on twitter. A fair number of people responded. I kept it quiet because I was busy doing my day job, but I was secretly plotting… Plotting to put this list together and share it worth the world! (evil laughter)

Not only did I put this list on my blog, I also put it on twitter. Behold, my SQL Server presenters twitter list.

I will be trying to maintain this list as more people come get on twitter and become willing to share their twitter info. I wish that I could give this twitter list over to the community to maintain, but I am more than willing to take list maintenance on until there’s a way to trade list ownership on twitter.

The whole point of the list is to make it easy to find presenters and presentations, both for the future needs of a user group as well as for your immediate needs (e.g. your speaker cancels at the last minute).

If you’d like to get on the list, hit me up on twitter or shoot me an email via the contact page.

Josef Richberg @sqlrunner
Brent Ozar @BrentO
Thomas LaRock @sqlrockstar
Kendal Van Dyke @sqldba
Tim Ford @sqlagentman
Lara Rubbelke @sqlgal
TJay Belt @tjaybelt
Todd McDermid @Todd_McDermid
Jack Corbett @unclebiguns
Kevin Kline @kekline
Colin Stasiuk @BenchmarkIT
Trevor Barkhouse @SQLServerSleuth
Ken Simmons @kensimmons
Aaron Nelson @sqlvariant
Jorge Segarra @SQLChicken
Joe Webb @JoeWebb
Glenn Berry @GlennAlanBerry
Pinal Dave @PinalDave
Sean McCown @MidnightDBA
Jonathan Kehayias @SQLSarg
Tim Mitchell @Tim_Mitchell
Eric Humphrey @lotsahelp
Artemakis Artemiou @artemakis
Wes Brown @WesBrownSQL
Aaron Bertrand @AaronBertrand
Adam Machanic @AdamMachanic
Jimmy May @aspiringgeek
Rob Farley @rob_farley
Brad Hurley @bradohurley
Rick Heiges @heigesr2
Chris Randall @cfrandall
Stuart Ainsworth @stuarta
Mladen Prajdic @mladenprajdic
K. Brian Kelley @kbriankelley
Arnie Rowland @Arnie1568
Buck Woody @BuckWoody
Ross Mistry @RossMistry
Grant Fritchey @GFritchey
Rod Colledge @rodcolledge
Denny Cherry @mrdenny
John Sterrett @johnsterrett

Comments (4)

GUIDs: Not Necessarily Unique

Nov 19, ’09 1:00 PM

So, you though that GUIDs were supposed to be unique, eh? You aren’t alone. A lot of people assume that GUIDs are unique. Hell, unique is in the name!

On Friday, I had a bug report filed about a UNIQUE constraint violation. Seeing as how I had bigger fish to fry, and this functionality is only rarely used, I put it aside until this morning when I took a closer look at the bug report and the code in question.

The table in question looks something like this:

CREATE TABLE dbo.Widget
(
  WidgetId BIGINT IDENTITY(1,1) NOT NULL,
  -- other columns
  LegacyIdentifier VARCHAR(50) NOT NULL
);

Before I get yelled at for a VARCHAR(50) in my database let me assure that this is actually a 50 character random garbage string from our legacy COBOl application. Nobody got lazy and just made a VARCHAR(50)… this time.

LegacyIdentifier has a unique constraint on it because all of the Widgets need to be unique between the new system and the old system. In order to keep this uniqueness going I thought to myself, “I know, the U in GUID stands for Unique, I’ll use that since I’m not clustering on it.” I wrote a little piece of code that puts “New_” at the beginning of a GUID and then uses that string as the LegacyIdentifier. That was about 11 months ago. On Friday, we received an exception warning that the application had attempted to insert a duplicate value into the LegacyIdentifier column.

What now? Well, I’m not sure. I’m going to implement functionality to force the database to keep generating GUIDs and attempting inserts until it actually generates a unique GUID.

This solution doesn’t make me happy, though, because GUIDs are not unique. A GUID is, in theory, unique. There are, after all, 2^128 possible permutations, so the probability of the same GUID being generated twice are incredibly slim. However, it can happen. (SQL Server uses a V4 GUID algorithm, which is a pseudo-random number and, as such, is not truly random. Newer GUID algorithms use an SHA hash for randomness, but how random is random?)

What are we to do when we need a truly unique identifier? Frankly, I’m not sure. The thought crossed my mind of generating a random number and casting it to VARCHAR and appending it to the GUID. The statistical likelihood of generating two random values that are the same as a prior value is so astronomically low that I should probably buy lottery tickets if there is ever a collision. As it stands right now, I’ve opted to retry the insert until it actually succeeds (or for 5 times, after which the user is doomed).

So, there you have it, GUIDs are not guaranteed to be unique and you should plan accordingly.

Comments (5)

Dogfood

Oct 30, ’09 9:00 AM

I’m a bit late on this one, but I’m very pleased to announce that I will be speaking at the local Dogfood Conference. I’ll be speaking about SQL Server 2008 R2 and will be conducting an Ask the Experts session in the afternoon, but I’m not the only person who will be at the conference. There are going to be a lot of great sessions at the conference covering the range of topics from Windows 7 to SharePoint to F#.

Check out the Thursday and Friday agendas and sign up.

Comments (1)

Links for the Week of 2009.10.23

Oct 23, ’09 9:00 AM

I came across a lot of great links in the last week. So many that I had to cut about 40% of them to make this list.

As always, you can check out the firehose at my delicious bookmarks page.

SQL Server

Development

Stuff & Things

Comments (3)

SQL University – Snow Day

Oct 22, ’09 9:00 AM

I was supposed to have a second blog post up this week for SQL University, however I haven’t been feeling well and have been focusing my energy on my day job. Unfortunately, I’ve had to drop the ball on a few other things. My second SQL University post for the internals week is what got dropped (as well as a few other things that don’t directly involve anyone else).

However, I did come across this little gem that I’d like to share. Logical Query Processing Poster (PDF alert)

This is the logical query processing diagram that is in Itzik Ben-Gan’s fantastic book Inside Microsoft® SQL Server® 2008: T-SQL Querying. This explains how SQL Server builds a query from the ground up and includes all phases of query processing.

Comments (0)

An Introduction to SQL Server System Databases

Oct 19, ’09 9:30 AM

SQL Server 2008 (and 2005) contain five special databases: master, model, tempdb, msdb, and mssqlsystemresource (aka Resource). These databases are used by SQL Server for its own maintenance and management.

master

The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. If the master database is not present, SQL Server cannot start.

Always take regular backups of the master database.

Since the master database hold all of the information related to logins, endpoints, linked servers, and user databases, it’s important that you take a backup of the master database after configuring any of these server level changes. Otherwise, if your SQL Server suffers a catastrophic failure, those changes will be lost to the sands of time.

Books Online contains a great deal of helpful information about the master database that you can find in the appropriately named master Database article.

Speaking from personal experience, one of the most important points from that article is this: “Do not create user objects in master. Otherwise, master must be backed up more frequently.” I cannot tell you the number of times I have connected to a development server, opened up a file, and run the DDL contents of the file only to realize that I have created new tables on the master database because I did not have a USE AdventureWorks at the top of my DDL script.

model

The model database is used as a template whenever a new user database is created. You can change most database properties, create users, stored procedures, tables, views, etc – whatever you do will be applied to any new databases.

The nice thing is that you can create a guaranteed set of users, stored procedures, and options (including collation options) by making changes to the model database. Once those changes are in place, they are applied to every new database.

Outside of its role as a template, model doesn’t do anything else. You can find more information in the model Database article of Books Online.

msdb

msdb is used by the SQL Server Agent, database mail, Service Broker, and other services. If you aren’t actively working with things like jobs, alerts, log shipping, etc you can pretty safely ignore msdb… sort of.

One important item is that msdb holds backup history. Using the msdb tables (you can start by taking a look at msdb.dbo.backupset), it’s possible to determine when each database and filegroup was last backed up. This is very useful, especially when you’ve just started working at a new company or taken over the maintenance of new servers.

A word of warning: you need to make sure that you are pruning old backup history from msdb. Leaving old backup data can slow down the performance of backup and restore operations. Brent Ozar documented this during his Backup Tricks Week article Checking MSDB Cleanups. In this article, the sp_delete_backuphistory system stored procedure is mentioned. This stored procedure will delete information older than the @oldest_date parameter. It is important that you are incredibly careful when using this store procedure and don’t attempt to delete all of the backup history data in your msdb at once. Attempting to clear out a large number of database backup history records in one fell swoop can have an adverse effect on performance. I suggest removing the data one to two days at a time. It’s tedious, I know, but your users will thank you.

Resource database

The resource database is a hidden system database. This is where system objects are stored. It isn’t possible to see the resource database by normal means. However you can see the data file by navigating to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn. The exact size and modification data of this file will be different from version to version, but the modified date should be the same date that you see when you run SELECT @@version.

It is best to think of the resource database as if it were another system DLL. The resource database is designed to make it easy for quick database upgrades. If new system objects are being put in place, it is only necessary to swap out the resource database MDF file.

Typically, the only way to view the contents of the resource database is using the OBJECT_DEFINITION system function. 

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

-- on my test system this yields the following:
CREATE VIEW sys.objects AS
    SELECT name, object_id, principal_id, schema_id, parent_object_id,
        type, type_desc, create_date, modify_date,
        is_ms_shipped, is_published, is_schema_published
    FROM sys.objects$

tempdb

We come, at last, to tempdb. Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting. You know how you see those spools in your execution plans? When you see one of those, SQL Server is probably spooling the data to a temporary table in the tempdb. Outside of storing temporary results, tempdb is also used during snapshot isolation and for user created temporary tables (this includes table variables).

One thing that is interesting to note about tempdb is that it is re-created every time the SQL Server service is started. Any objects that you have created in tempdb will be gone once the server restarts. If you want specific tables or stored procedures to always be available in tempdb, you will need to add them to the model database or else use a stored procedure to create them in tempdb when the SQL Server is started.

A properly sized and configured tempdb is vital for effective SQL Server performance. By default tempdb starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized too small, system resources will be used growing the tempdb file. This overhead can be avoided by increasing the default size of tempdb.

SQL Server System Databases
System Databases
Working with tempdb in SQL Server 2005
Microsoft SQL Server 2008 Internals pp 126 – 128

Comments (10)

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 401 items have been purified.