Sunday, November 13, 2011

Justice League of #SQLFamily

Wow, that's a pretty dumb title, and one that I humbly apologize for.  I find myself with a dilemma - I really want to contribute something serious to Thomas LaRock's #SQLFamily meme, but this blog thing I've got going here is, as evidenced by the superhero theme, meant to be silly and humorous.  I decided to put some serious thought into what I wanted to say, and see if a title revealed itself.

Flexing my thinking muscle, I started looking back over my years as a SQL guy.  Twenty-plus years is a long time, time that has seen me working with Clipper, Foxpro, Windows NT, networking, IIS, ASP, PHP, XYZ and, among others, SQL.  I've learned a lot along the way, made a lot of mistakes along the way, made and lost friends along the way, had some miserable jobs along the way, and had a lot of fun along the way.  I think the place where I had the most fun was the same place where I picked up the Real SQL Guy moniker.  The group of guys that I worked with, including my boss, were all made from the same mold.  We all worked extremely hard, had the same warped sense of humor, made fun of the same people for the same reasons, and just really had a lot of fun together.  I think the biggest reason for that was that we all felt the same pains from our jobs - the same sorts of problems from the same people.  One of us could complain to the rest of the group, and we all "got it", we understood and could react accordingly, which usually meant making fun of somebody for asking for or doing something "stupid".

That was six years ago.  Today, I'm with a different company, with a different group of people, with drastically different personalities and backgrounds.  Some of them share my sense of humor, some of them don't, and because of that, I've had to mature and be more "selective" with my reactions to day-to-day challenges.  This is positively a good thing, and part of becoming a better professional, but there are times when I simply feel the need to yell "WTF?" and share with the world that "dumb thing" that I just had to fix.  That's partly why I revived this blog, to serve as an outlet for that sort of stuff.  Something else happened though - in order to get my hilarious blog content to be read by as many people as possible, I decided to get more aggressive in the social media arena.  I joined Twitter.  I cleaned up my LinkedIn profile.  I created a Google+ profile.  Across all of these platforms, I started following and interacting with some of the SQL celebrities, the guys and gals who write the books.  In doing so, I made a pleasant discovery.  Some of these people are a lot like me!

There's always somebody making fun of their pet stupid developer, or sharing a bad joke, or an inappropriate link.  It's like being back with that original group of guys again.  I can complain that "idiot developer asked me to expand VARCHAR(50) to VARCHAR(255) on 5M row table, then got upset when I questioned why", and these people GET IT, they understand my frustration and the desire to smack that developer between the eyes.  In superhero terms, they're my Justice League.

I don't truly consider myself part of the #SQLFamily yet, but I intend to get there.  I've been to my first SQL Saturday event, I'm going to start attending some of the local PASS meetings, and I'm going to try to make it to the PASS Summit next year.  Save me a seat at the dinner table...

Wednesday, November 9, 2011

Lock 'Em Up And Throw Away The Key/Value Pairs

I have discovered a dastardly plot to wreck my database.  This thing runs deep, even the Russians are involved.  Like a ticking time bomb, this plot has been stewing within the bowels of my biggest OLTP instance, just waiting to explode.  Luckily, I discovered it before it was too late.

It all started with a seemingly innocent request from a developer.  Oh, I've been seeing clues along the way that something wasn't right, but the alarms hadn't yet gone off.  That is, until this request came long.

"Can you expand this VARCHAR column named Value from 50 characters to 255 please?", the developer asked.  Hmmm, well, let's see, there are 5.5 million rows in this table, you're asking me to allow another 200 characters per row, potentially a gig of data.  This particular table was already on my watch list as being overly busy, I just hadn't had time to fully dissect it to understand why.  "Can you tell me a little more about why this change is needed?", I asked.  The response was "I want to store URL's in there, and it's not long enough."

Storing a URL in a column named Value?  Something smells funny here.  I took a closer look at the table, and exclaimed "Egads!  It's a key/value table!"  Each row in the table is an attribute for an entity!  The reason the table is so busy is because the application is making multiple trips to fetch all of the attributes as each entity is referenced.



This developer was wanting to add an additional Key value to the table for storing a URL of some type.  Acting quickly, I began interrogating developers about why this table exists, who approved this, are you insane, etc.  Almost as quickly, I whipped up a proposal for a proper design, one row per entity, normalized data storage, proper data types, all of the things a good DBA would do.

CREATE TABLE dbo.CorrespondenceEventType
 (
  CorrespondenceEventTypeID   INT    NOT NULL  IDENTITY(1,1),
  CorrespondenceType     VARCHAR(50)  NOT NULL,
  CONSTRAINT PK_CorrespondenceEventType PRIMARY KEY CLUSTERED (CorrespondenceEventTypeID)
 ) WITH (DATA_COMPRESSION=PAGE)
GO

INSERT INTO dbo.CorrespondenceEventType
SELECT DISTINCT Value
FROM dbo.CorrespondenceEventParameters
WHERE [Key] = 'CorrespondenceType'
GO

CREATE TABLE dbo.CorrespondenceEventParameter
 (
  CorrespondenceEventParameterID INT    NOT NULL  IDENTITY(1,1),
  CorrespondenceEventID   INT    NOT NULL,
  Client_ID      INT    NOT NULL,
  CorrespondenceEventTypeID  INT    NOT NULL,
  OrderItemID      INT,
  Order_ID      INT,
  EventID       INT,
  Payment_ID      INT,
  Student_ID      INT,
  AuthorizationID     INT,
  EmailAddress     VARCHAR(255),
  AppName       VARCHAR(20),
  LastUpdate      DATETIME,
  LastUpdateAppUserID    INT,
  CONSTRAINT PK_CorrespondenceEventParameter PRIMARY KEY CLUSTERED (CorrespondenceEventParameterID),
  CONSTRAINT FK_CorrespondenceEventParameter_Clients FOREIGN KEY (Client_ID) REFERENCES dbo.Clients(Client_ID),
  CONSTRAINT FK_CorrespondenceEventParameter_CorrespondenceEvent FOREIGN KEY (CorrespondenceEventID) REFERENCES dbo.CorrespondenceEvent(CorrespondenceEventID),
  CONSTRAINT FK_CorrespondenceEventParameter_CorrespondenceEventType FOREIGN KEY (CorrespondenceEventTypeID) REFERENCES dbo.CorrespondenceEventType(CorrespondenceEventTypeID),
  CONSTRAINT FK_CorrespondenceEventParameter_ExamRegistration FOREIGN KEY (OrderItemID) REFERENCES dbo.ExamRegistration(OrderItemID),
  CONSTRAINT FK_CorrespondenceEventParameter_Orders FOREIGN KEY (Order_ID) REFERENCES dbo.Orders(Order_ID),
  CONSTRAINT FK_CorrespondenceEventParameter_Payments FOREIGN KEY (Payment_ID) REFERENCES dbo.Payments(Payment_ID),
  CONSTRAINT FK_CorrespondenceEventParameter_Students FOREIGN KEY (Student_ID) REFERENCES dbo.Students(Student_ID),
  CONSTRAINT FK_CorrespondenceEventParameter_Authorizations FOREIGN KEY (AuthorizationID) REFERENCES dbo.Authorizations(AuthorizationID)
 ) WITH (DATA_COMPRESSION=PAGE)
GO

CREATE INDEX IX_CorrespondenceEventParameter_CorrespondenceEventID ON dbo.CorrespondenceEventParameter (CorrespondenceEventID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_ClientID ON dbo.CorrespondenceEventParameter (Client_ID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_CorrespondenceEventTypeID ON dbo.CorrespondenceEventParameter (CorrespondenceEventTypeID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_OrderItemID ON dbo.CorrespondenceEventParameter (OrderItemID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_Order_ID ON dbo.CorrespondenceEventParameter (Order_ID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_EventID ON dbo.CorrespondenceEventParameter (EventID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_PaymentID ON dbo.CorrespondenceEventParameter (Payment_ID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_StudentID ON dbo.CorrespondenceEventParameter (Student_ID) WITH (DATA_COMPRESSION=PAGE)
CREATE INDEX IX_CorrespondenceEventParameter_AuthorizationID ON dbo.CorrespondenceEventParameter (AuthorizationID) WITH (DATA_COMPRESSION=PAGE)
GO

I even wrote the conversion script to take the data from their existing key/value table and store it properly within my new design:

INSERT INTO dbo.CorrespondenceEventParameter
 (CorrespondenceEventID, Client_ID, CorrespondenceEventTypeID, OrderItemID, Order_ID, EventID, Payment_ID, Student_ID, AuthorizationID, EmailAddress, AppName, LastUpdate, LastUpdateAppUserID)
SELECT
 CorrespondenceEventID    = Pivoted.CorrespondenceEventID,
 Client_ID       = Pivoted.ClientId,
 CorrespondenceEventTypeID   = CorrespondenceEventType.CorrespondenceEventTypeID,
 OrderItemID       = ExamRegistration.OrderItemID,
 Order_ID       = Orders.Order_ID,
 EventID        = Pivoted.EventId,
 Payment_ID       = Payments.Payment_ID,
 Student_ID       = Students.Student_ID,
 AuthorizationID      = Authorizations.AuthorizationID,
 EmailAddress      = Pivoted.EmailAddress,
 AppName        = (SELECT MAX(AppName) FROM dbo.CorrespondenceEventParameters WHERE CorrespondenceEventParameters.[Key] = 'CorrespondenceType' AND CorrespondenceEventParameters.Value = CorrespondenceEventType.CorrespondenceType AND CorrespondenceEventParameters.CorrespondenceEventID = Pivoted.CorrespondenceEventID),
 LastUpdate       = (SELECT MAX(LastUpdate) FROM dbo.CorrespondenceEventParameters WHERE CorrespondenceEventParameters.[Key] = 'CorrespondenceType' AND CorrespondenceEventParameters.Value = CorrespondenceEventType.CorrespondenceType AND CorrespondenceEventParameters.CorrespondenceEventID = Pivoted.CorrespondenceEventID),
 LastUpdateAppUserID     = (SELECT MAX(LastUpdateAppUserID) FROM dbo.CorrespondenceEventParameters WHERE CorrespondenceEventParameters.[Key] = 'CorrespondenceType' AND CorrespondenceEventParameters.Value = CorrespondenceEventType.CorrespondenceType AND CorrespondenceEventParameters.CorrespondenceEventID = Pivoted.CorrespondenceEventID)
FROM
 (
  SELECT *
  FROM
   (
    SELECT 
     CorrespondenceEventID,
     [Key],
     Value = MAX(Value)
    FROM dbo.CorrespondenceEventParameters
    GROUP BY CorrespondenceEventID, [Key]
   ) AS EventParams
  PIVOT
   (
    MAX(Value)
    FOR [Key] IN (AuthorizationId, ClientId, CorrespondenceType, EmailAddress, EventId, ExamRegistrationId, OrderId, PaymentId, StudentId)
   ) AS PivotTable
 ) AS Pivoted
INNER JOIN dbo.CorrespondenceEventType
 ON Pivoted.CorrespondenceType = CorrespondenceEventType.CorrespondenceType
LEFT JOIN dbo.ExamRegistration
 ON ExamRegistration.OrderItemID = Pivoted.ExamRegistrationId
LEFT JOIN dbo.Orders
 ON Orders.Order_ID = Pivoted.OrderId
LEFT JOIN dbo.Payments
 ON Payments.Payment_ID = Pivoted.PaymentId
LEFT JOIN dbo.Students
 ON Students.Student_ID = Pivoted.StudentId
LEFT JOIN dbo.Authorizations
 ON Authorizations.AuthorizationID = Pivoted.AuthorizationId
WHERE Pivoted.ClientId IS NOT NULL

I met resistance right away.  "This will make our queries more complicated", they cried.  "How so?", I asked.  They muttered something about having to lookup ID values, and then run a second query to get the data they wanted.  "Ever heard of an INNER JOIN?", I asked?  How is this complicated?

SELECT CorrespondenceEventParameter.LastUpdate
FROM dbo.CorrespondenceEventParameter
INNER JOIN dbo.CorrespondenceEventType
 ON CorrespondenceEventParameter.CorrespondenceEventTypeID = CorrespondenceEventType.CorrespondenceEventTypeID
WHERE CorrespondenceEventParameter.OrderItemID = 244389219
 AND CorrespondenceEventType.CorrespondenceType = 'ExamReminder'

Still they resisted, so I proceeded with implementing my solution in a copy of the production database, and demonstrated for them that my way would result in a query plan that costs a fraction of the plan produced by their current design.  In both cases, the intent is to return the last date/time that a reminder was sent for a specific appointment.  The top query plan is from the current key/value table design.  The bottom plan is from my new redesigned structure.


The real kicker came when I translated that into a metric that they could understand - time.  Using collected performance stats, I summed up the processing time that their current query had accumulated in production over the previous hour.  Using the cost difference between the query plans as a guide, I estimated that my new design would have cut nearly 30 minutes of processing time out of the previous hour for their application.  This got their attention, and redesigning this process has magically found its way onto the priority list.  It's hard to argue against solid evidence.

Wednesday, October 19, 2011

On The Right Track, or WAITing At The Station?

For the past several months, I have been aggressively tuning my production OLTP server.  We've also upgraded to a new "smart" SAN.  This is a big beefy box, 48 cores, 512GB of RAM, nearly enough to hold all of the production databases in memory.






The machine is NOT taxed, and I rarely hear complaints about performance.


My tuning efforts have been based strictly on metrics that I can collect from SQL Server itself.  The reason for writing this post is that I think I've hit the bottom of the barrel, and I'm soliciting opinions from the audience as to where to go next.

I've been collecting info on the wait stats for the past six months, as shown below.  Notice that for the majority of that time, SQL spent a lot of time waiting on I/O.  When the new SAN was introduced in late September, the I/O waits disappeared completely, replaced with LCK_M_IX.  At that point, I began focusing on missing indexes and high-read queries.  Over the past couple of week, I've eliminated several million reads PER HOUR from the machine's workload, and LCK_M_IX is no longer the top wait type.

I'm now looking at PREEMPTIVE_OS_PIPEOPS, which I believe is stemming from the use of xp_cmdshell for log-shipping and other file-manipulation tasks.  I know, I know, please don't lambaste me with the evils of xp_cmdshell, it is what it is for now.  The real question is, what can I do about this wait, and with an average wait of under 1 second, CAN I do anything about it?


StatsDateWaitTypeAverageWaitSeconds
2011-10-19 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7953
2011-10-18 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7464
2011-10-17 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6384
2011-10-16 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6897
2011-10-15 00:00:00.000LCK_M_IX2.4724
2011-10-14 00:00:00.000LCK_M_IX2.5408
2011-10-13 00:00:00.000LCK_M_IX2.6177
2011-10-12 00:00:00.000LCK_M_IX2.6951
2011-10-11 00:00:00.000LCK_M_IX2.7822
2011-10-10 00:00:00.000LCK_M_IX2.8687
2011-10-09 00:00:00.000LCK_M_IX2.9573
2011-10-08 00:00:00.000LCK_M_IX3.0560
2011-10-07 00:00:00.000LCK_M_IX3.1617
2011-10-06 00:00:00.000LCK_M_IX3.2784
2011-10-05 00:00:00.000LCK_M_IX3.3528
2011-10-04 00:00:00.000LCK_M_IX3.4955
2011-10-03 00:00:00.000LCK_M_IX3.6444
2011-10-02 00:00:00.000LCK_M_IX3.7869
2011-10-01 00:00:00.000LCK_M_IX3.9452
2011-09-30 00:00:00.000LCK_M_IX4.1280
2011-09-29 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9454
2011-09-28 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9050
2011-09-27 00:00:00.000LCK_M_IX5.2260
2011-09-26 00:00:00.000LCK_M_IX4.4864
2011-09-25 00:00:00.000LCK_M_IX2.7938
2011-09-24 00:00:00.000ASYNC_IO_COMPLETION162.4325
2011-09-23 00:00:00.000ASYNC_IO_COMPLETION175.7272
2011-09-22 00:00:00.000ASYNC_IO_COMPLETION194.2177
2011-09-21 00:00:00.000ASYNC_IO_COMPLETION219.2856
2011-09-20 00:00:00.000ASYNC_IO_COMPLETION257.5027
2011-09-19 00:00:00.000ASYNC_IO_COMPLETION319.3036
2011-09-18 00:00:00.000ASYNC_NETWORK_IO0.0486
2011-09-17 00:00:00.000LCK_M_IX1.2955
2011-09-16 00:00:00.000LCK_M_IX1.3107
2011-09-15 00:00:00.000LCK_M_IX1.3246
2011-09-14 00:00:00.000LCK_M_IX1.3365
2011-09-13 00:00:00.000LCK_M_X1.3894
2011-09-12 00:00:00.000LCK_M_X1.4316
2011-09-11 00:00:00.000LCK_M_X1.4661
2011-09-10 00:00:00.000LCK_M_X1.5037
2011-09-09 00:00:00.000LCK_M_X1.5487
2011-09-08 00:00:00.000LCK_M_X1.5983
2011-09-07 00:00:00.000LCK_M_X1.6526
2011-09-06 00:00:00.000LCK_M_X1.7105
2011-09-05 00:00:00.000LCK_M_X1.7661
2011-09-04 00:00:00.000LCK_M_X1.8230
2011-09-03 00:00:00.000LCK_M_X1.8831
2011-09-02 00:00:00.000LCK_M_X1.9520
2011-09-01 00:00:00.000LCK_M_X2.0417
2011-08-31 00:00:00.000LCK_M_X2.1422
2011-08-30 00:00:00.000ASYNC_IO_COMPLETION141.5480
2011-08-29 00:00:00.000ASYNC_IO_COMPLETION139.6930
2011-08-28 00:00:00.000ASYNC_IO_COMPLETION138.0323
2011-08-27 00:00:00.000ASYNC_IO_COMPLETION144.3802
2011-08-26 00:00:00.000ASYNC_IO_COMPLETION143.2951
2011-08-25 00:00:00.000ASYNC_IO_COMPLETION140.3102
2011-08-24 00:00:00.000ASYNC_IO_COMPLETION136.7581
2011-08-23 00:00:00.000ASYNC_IO_COMPLETION132.0145
2011-08-22 00:00:00.000ASYNC_IO_COMPLETION128.7737
2011-08-21 00:00:00.000ASYNC_IO_COMPLETION122.5329
2011-08-20 00:00:00.000ASYNC_IO_COMPLETION133.8007
2011-08-19 00:00:00.000LCK_M_IX1.1325
2011-08-18 00:00:00.000LCK_M_IX0.9612
2011-08-17 00:00:00.000LCK_M_IX0.9091
2011-08-16 00:00:00.000ASYNC_IO_COMPLETION87.3158
2011-08-15 00:00:00.000ASYNC_IO_COMPLETION93.7826
2011-08-14 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7019
2011-08-13 00:00:00.000ASYNC_IO_COMPLETION147.0161
2011-08-12 00:00:00.000ASYNC_IO_COMPLETION146.5126
2011-08-11 00:00:00.000ASYNC_IO_COMPLETION145.8625
2011-08-10 00:00:00.000ASYNC_IO_COMPLETION145.1408
2011-08-09 00:00:00.000ASYNC_IO_COMPLETION144.3159
2011-08-08 00:00:00.000ASYNC_IO_COMPLETION143.3599
2011-08-07 00:00:00.000LCK_M_IX1.6269
2011-08-06 00:00:00.000LCK_M_IX1.6530
2011-08-05 00:00:00.000LCK_M_IX1.6721
2011-08-04 00:00:00.000ASYNC_IO_COMPLETION145.4297
2011-08-03 00:00:00.000ASYNC_IO_COMPLETION144.5607
2011-08-02 00:00:00.000ASYNC_IO_COMPLETION143.3563
2011-08-01 00:00:00.000ASYNC_IO_COMPLETION141.6229
2011-07-31 00:00:00.000ASYNC_IO_COMPLETION140.4128
2011-07-30 00:00:00.000ASYNC_IO_COMPLETION146.8009
2011-07-29 00:00:00.000ASYNC_IO_COMPLETION145.1902
2011-07-28 00:00:00.000ASYNC_IO_COMPLETION143.2194
2011-07-27 00:00:00.000ASYNC_IO_COMPLETION141.1237
2011-07-26 00:00:00.000ASYNC_IO_COMPLETION138.9945
2011-07-25 00:00:00.000ASYNC_IO_COMPLETION136.0320
2011-07-24 00:00:00.000ASYNC_IO_COMPLETION133.2664
2011-07-23 00:00:00.000ASYNC_IO_COMPLETION145.1821
2011-07-22 00:00:00.000ASYNC_IO_COMPLETION142.6912
2011-07-21 00:00:00.000ASYNC_IO_COMPLETION139.1670
2011-07-20 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9265
2011-07-19 00:00:00.000ASYNC_IO_COMPLETION125.2301
2011-07-18 00:00:00.000ASYNC_IO_COMPLETION104.5218
2011-07-17 00:00:00.000LCK_M_S2.4249
2011-07-16 00:00:00.000ASYNC_IO_COMPLETION142.5515
2011-07-15 00:00:00.000ASYNC_IO_COMPLETION141.9216
2011-07-14 00:00:00.000ASYNC_IO_COMPLETION141.1482
2011-07-13 00:00:00.000ASYNC_IO_COMPLETION140.3958
2011-07-12 00:00:00.000ASYNC_IO_COMPLETION139.6792
2011-07-11 00:00:00.000ASYNC_IO_COMPLETION137.5899
2011-07-10 00:00:00.000ASYNC_IO_COMPLETION136.7927
2011-07-09 00:00:00.000ASYNC_IO_COMPLETION140.4587
2011-07-08 00:00:00.000ASYNC_IO_COMPLETION139.4960
2011-07-07 00:00:00.000ASYNC_IO_COMPLETION138.4204
2011-07-06 00:00:00.000ASYNC_IO_COMPLETION137.2699
2011-07-05 00:00:00.000LCK_M_SCH_S27.7115
2011-07-04 00:00:00.000LCK_M_SCH_S28.2566
2011-07-03 00:00:00.000LCK_M_SCH_S29.0291
2011-07-02 00:00:00.000LCK_M_SCH_S29.5383
2011-07-01 00:00:00.000LCK_M_SCH_S30.1864
2011-06-30 00:00:00.000LCK_M_SCH_S33.7304
2011-06-29 00:00:00.000LCK_M_SCH_S34.4017
2011-06-28 00:00:00.000LCK_M_SCH_S35.2142
2011-06-27 00:00:00.000LCK_M_IX5.4730
2011-06-26 00:00:00.000LCK_M_IX5.7981
2011-06-25 00:00:00.000LCK_M_IX6.2078
2011-06-24 00:00:00.000LCK_M_IX6.7215
2011-06-23 00:00:00.000LCK_M_IX7.5673
2011-06-22 00:00:00.000LCK_M_IX8.7047
2011-06-21 00:00:00.000LCK_M_IX10.0213
2011-06-20 00:00:00.000LCK_M_IX2.6511
2011-06-19 00:00:00.000LCK_M_SCH_S1664.2028
2011-06-18 00:00:00.000ASYNC_IO_COMPLETION144.0897
2011-06-17 00:00:00.000ASYNC_IO_COMPLETION143.3284
2011-06-16 00:00:00.000ASYNC_IO_COMPLETION142.8788
2011-06-15 00:00:00.000ASYNC_IO_COMPLETION142.4000
2011-06-14 00:00:00.000ASYNC_IO_COMPLETION141.5003
2011-06-13 00:00:00.000ASYNC_IO_COMPLETION140.9441
2011-06-12 00:00:00.000ASYNC_IO_COMPLETION140.5529
2011-06-11 00:00:00.000ASYNC_IO_COMPLETION143.8691
2011-06-10 00:00:00.000ASYNC_IO_COMPLETION143.4363
2011-06-09 00:00:00.000ASYNC_IO_COMPLETION142.9559
2011-06-08 00:00:00.000ASYNC_IO_COMPLETION142.1377
2011-06-07 00:00:00.000ASYNC_IO_COMPLETION141.1368
2011-06-06 00:00:00.000LCK_M_IX1.5562
2011-06-05 00:00:00.000LCK_M_IX1.6175
2011-06-04 00:00:00.000LCK_M_IX1.6823
2011-06-03 00:00:00.000LCK_M_IX1.7465
2011-06-02 00:00:00.000LCK_M_IX1.8304
2011-06-01 00:00:00.000LCK_M_IX1.8280
2011-05-31 00:00:00.000LCK_M_IX1.8692
2011-05-30 00:00:00.000LCK_M_IX1.9600
2011-05-29 00:00:00.000LCK_M_IX2.0590
2011-05-28 00:00:00.000LCK_M_IX2.1797
2011-05-27 00:00:00.000LCK_M_IX2.2281
2011-05-26 00:00:00.000ASYNC_IO_COMPLETION143.3396
2011-05-25 00:00:00.000ASYNC_IO_COMPLETION142.4637
2011-05-24 00:00:00.000ASYNC_IO_COMPLETION141.2364
2011-05-23 00:00:00.000ASYNC_IO_COMPLETION139.2355
2011-05-22 00:00:00.000ASYNC_IO_COMPLETION136.9468
2011-05-21 00:00:00.000ASYNC_IO_COMPLETION150.4211
2011-05-20 00:00:00.000ASYNC_IO_COMPLETION148.4708
2011-05-19 00:00:00.000ASYNC_IO_COMPLETION145.4053
2011-05-18 00:00:00.000ASYNC_IO_COMPLETION141.5062
2011-05-17 00:00:00.000ASYNC_IO_COMPLETION134.0149
2011-05-16 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6527
2011-05-15 00:00:00.000PREEMPTIVE_OS_WRITEFILE0.0222
2011-05-14 00:00:00.000ASYNC_IO_COMPLETION158.1498
2011-05-13 00:00:00.000ASYNC_IO_COMPLETION157.9670
2011-05-12 00:00:00.000ASYNC_IO_COMPLETION157.6677
2011-05-11 00:00:00.000LCK_M_IS8.3349
2011-05-10 00:00:00.000LCK_M_IS8.5990
2011-05-09 00:00:00.000LCK_M_IS9.0284
2011-05-08 00:00:00.000LCK_M_IS9.3260
2011-05-07 00:00:00.000LCK_M_IS9.5627
2011-05-06 00:00:00.000LCK_M_IS9.8709
2011-05-05 00:00:00.000ASYNC_IO_COMPLETION161.0149
2011-05-04 00:00:00.000ASYNC_IO_COMPLETION161.0506
2011-05-03 00:00:00.000ASYNC_IO_COMPLETION161.0995
2011-05-02 00:00:00.000ASYNC_IO_COMPLETION160.7030
2011-05-01 00:00:00.000ASYNC_IO_COMPLETION160.0951
2011-04-30 00:00:00.000ASYNC_IO_COMPLETION167.4388
2011-04-29 00:00:00.000ASYNC_IO_COMPLETION167.1990
2011-04-28 00:00:00.000ASYNC_IO_COMPLETION166.7961
2011-04-27 00:00:00.000ASYNC_IO_COMPLETION166.4862
2011-04-26 00:00:00.000ASYNC_IO_COMPLETION164.8733
2011-04-25 00:00:00.000ASYNC_IO_COMPLETION163.0835
2011-04-24 00:00:00.000ASYNC_IO_COMPLETION161.0020
2011-04-23 00:00:00.000ASYNC_IO_COMPLETION175.6034
2011-04-22 00:00:00.000ASYNC_IO_COMPLETION173.2586
2011-04-21 00:00:00.000ASYNC_IO_COMPLETION169.3020
2011-04-20 00:00:00.000ASYNC_IO_COMPLETION163.5116
2011-04-19 00:00:00.000ASYNC_IO_COMPLETION152.1601
2011-04-18 00:00:00.000ASYNC_IO_COMPLETION125.0617
2011-04-17 00:00:00.000LCK_M_S28.2886
2011-04-16 00:00:00.000ASYNC_IO_COMPLETION193.3536
2011-04-15 00:00:00.000ASYNC_IO_COMPLETION193.2422
2011-04-14 00:00:00.000ASYNC_IO_COMPLETION192.9083
2011-04-13 00:00:00.000ASYNC_IO_COMPLETION192.3132
2011-04-12 00:00:00.000ASYNC_IO_COMPLETION191.5557
2011-04-11 00:00:00.000ASYNC_IO_COMPLETION190.9004
2011-04-10 00:00:00.000ASYNC_IO_COMPLETION190.2397
2011-04-09 00:00:00.000ASYNC_IO_COMPLETION195.3725
2011-04-08 00:00:00.000ASYNC_IO_COMPLETION194.3511
2011-04-07 00:00:00.000ASYNC_IO_COMPLETION192.7628
2011-04-06 00:00:00.000ASYNC_IO_COMPLETION191.9506
2011-04-05 00:00:00.000ASYNC_IO_COMPLETION190.2253
2011-04-04 00:00:00.000ASYNC_IO_COMPLETION189.0877
2011-04-03 00:00:00.000ASYNC_IO_COMPLETION187.8966
2011-04-02 00:00:00.000ASYNC_IO_COMPLETION194.9759
2011-04-01 00:00:00.000ASYNC_IO_COMPLETION194.0448
2011-03-31 00:00:00.000ASYNC_IO_COMPLETION192.6620
2011-03-30 00:00:00.000ASYNC_IO_COMPLETION191.2033
2011-03-29 00:00:00.000ASYNC_IO_COMPLETION189.8906
2011-03-28 00:00:00.000ASYNC_IO_COMPLETION188.2785
2011-03-27 00:00:00.000ASYNC_IO_COMPLETION186.0158
2011-03-26 00:00:00.000ASYNC_IO_COMPLETION197.8071
2011-03-25 00:00:00.000ASYNC_IO_COMPLETION196.3743

Monday, October 17, 2011

Automated Source Control: A Librarian For Your Database Objects, Part 3

Now that the PASS Summit is over, and you're all sitting down and paying attention again, let's wrap up the series on source control.  SSHHHH!!

As I mentioned earlier, we use Perforce as our source control package, so all of the scripts presented here are written for Perforce.  I'll try to clearly explain, in terms of source control, the functional parts of the script, so that you can easily transfer this to your own source control package.  They should all offer the same basic features and some sort of command-line interface.  If you are a Perforce user, you may find it helpful to create a "non-expiring" login for this script to use.

Ok, sit up straight, eyes forward, let's get started...

The first thing that Perforce requires us to do is to create a "change list", where all changed, added, or deleted scripts will be recorded.  If, at the end of the process, nothing has been changed, added, or deleted, the change list will be discarded.  All it takes is a couple of lines of Powershell script to create the change list and save the number that Perforce assigns us:

#Create new Perforce changelist
$NewChangeListCommand = p4 -u RealP4Login change -o | select-string -pattern change, client, status
$NewChangeListCommand += "Description: SQL Server automated object script"
[int]$changeListNumber = $NewChangeListCommand | p4 -u RealP4Login change -i | %{ $_.split()[1] }

As part of the process, as database objects are being scripted out, we're going to do one of two things - add the script to Perforce if it's not in there already, or change the script in Perforce if it is.  We need to check to see if the script already exists, so that we can make that decision:

#Check file status against Perforce
$P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL
if ($P4FileStatus)
 {
  #Open Perforce files for edit
  p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL
  p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
 }

Now that we know if the script is already in Perforce, we can act accordingly.  If it's not there, we add it as a new script.  If it IS there, we need to see if the script generated from the database is different than the script currently in Perforce.  There is a powerful "diff" function built in to Perforce, so we'll use that to compare the "new" script with the current script.  If they match, we'll simply revert our check-out and move on.  If they don't match, we'll check-in the new script.

#Add new files to Perforce
if (!($P4FileStatus))
 {
  p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
  $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
" } else { #"diff" file, revert if unchanged $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL if (!($P4FileDiff)) { echo "Reverting" p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL } else { $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
" } }

Finally, to button things back up again, we need to know if there's actually anything to submit to Perforce.  This is easily done by checking the contents of our change list.  If there's nothing in the change list, then we know that we didn't add or change any database scripts.  If that's the case, we simply discard the change list and we're done.  Otherwise, we "submit" the change list, which commits the changes to source control.  As an added bonus, we're going to send an email to the DBA letting him know that some things have changed in his database.

#Check if changelist includes any new/modified files
$P4FilesChanged = p4 -u RealP4Login opened -c $changeListNumber
if ($P4FilesChanged)
 {
  #Submit changelist to Perforce
  p4 -u RealP4Login submit -c $changeListNumber
  
  #Send summary email
  $Now = Get-Date
  $SMTPserver = "RealSMTPServer"
  $from = "real@real-sql-guy.com"
  $to = "real@real-sql-guy.com"
  $subject = "$dbname database changes submitted to Perforce"
  $emailbody = "

Changelist #$changeListNumber


Submitted $Now

$ChangeListMessage " $mailer = new-object Net.Mail.SMTPclient($SMTPserver) $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody) $msg.IsBodyHTML = $true $mailer.send($msg) } else { #Delete changelist p4 -u RealP4Login change -d $changeListNumber }

I hope this all makes sense, and that you can adapt it to fit your own source control package.  Here's the final script, with all of the Perforce integration included:

#Set server and database names here
$server = "REALServer"
$dbname = "REALDB"

#Create SMO connection to DB
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
    $db = $SMOserver.databases[$dbname]
 
#Build collection of objects to script
$Objects = $db.Tables
$Objects += $db.Views
$Objects += $db.StoredProcedures
$Objects += $db.UserDefinedFunctions

#Set destination for generated scripts
$SavePath = "C:\temp\db\Source\" + $($dbname)

#Create new Perforce changelist
$NewChangeListCommand = p4 -u RealP4Login change -o | select-string -pattern change, client, status
$NewChangeListCommand += "Description: SQL Server automated object script"
[int]$changeListNumber = $NewChangeListCommand | p4 -u RealP4Login change -i | %{ $_.split()[1] }
if ($changeListNumber -gt 0)
    {
    echo "P4 changelist number $changeListNumber"
    $ChangeListMessage = ""

 #Loop through the object collection
 foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
  #Create Scripter object
  $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
  
  #Set options for generated scripts
  $scriptr.Options.AppendToFile = $True
  $scriptr.Options.AllowSystemObjects = $False
  $scriptr.Options.ClusteredIndexes = $True
  $scriptr.Options.DriAll = $True
  $scriptr.Options.ScriptDrops = $False
  $scriptr.Options.IncludeHeaders = $False
  $scriptr.Options.ToFileOnly = $True
  $scriptr.Options.Indexes = $True
  $scriptr.Options.Permissions = $True
  $scriptr.Options.WithDependencies = $False

  #Create destination folder and subfolder, if necessary
  $TypeFolder=$ScriptThis.GetType().Name
  if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")
    {"Scripting Out $TypeFolder $ScriptThis"}
   else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}
  $ScriptFile = $ScriptThis -replace "\[|\]"

  #Check file status against Perforce
  $P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL
  if ($P4FileStatus)
   {
    #Open Perforce files for edit
    p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL
    p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
   }

  #Set output filename for script
  $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"

  #Add commented USE statement for deployment validation tool
  "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"

  #Add existing object handler (drop procedure, table exists, etc..)
  if ($TypeFolder -eq "StoredProcedure") {
  "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
   BEGIN
   
   DROP PROCEDURE $ScriptFile
   
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "View") {
  "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
   BEGIN
   
   DROP VIEW $ScriptFile
   
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "UserDefinedFunction") {
  "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
   BEGIN
   
   DROP FUNCTION $ScriptFile
   
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "Trigger") {
  "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
   BEGIN
   
   DROP TRIGGER $ScriptFile
   
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  } elseif ($TypeFolder -eq "Table") {
  "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
   BEGIN
   
   IF EXISTS(SELECT * FROM $ScriptFile)
    BEGIN
    
    USE tempdb
    RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
    RETURN
    
    END
   ELSE
    BEGIN
    
    DROP TABLE $ScriptFile
    
    END
   END
  GO
  " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
  }

  #Generate script for this object
  $scriptr.Script($ScriptThis)

  #Add new files to Perforce
  if (!($P4FileStatus))
   {
    p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL
    $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
" } else { #"diff" file, revert if unchanged $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL if (!($P4FileDiff)) { echo "Reverting" p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL } else { $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
" } } } #Second loop through tables to script triggers foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) { #Loop through triggers on this table foreach ($ScriptThis in $Table.Triggers) { #Create Scripter object $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver) #Set options for generated scripts $scriptr.Options.AppendToFile = $True $scriptr.Options.AllowSystemObjects = $False $scriptr.Options.ClusteredIndexes = $True $scriptr.Options.DriAll = $True $scriptr.Options.ScriptDrops = $False $scriptr.Options.IncludeHeaders = $False $scriptr.Options.ToFileOnly = $True $scriptr.Options.Indexes = $True $scriptr.Options.Permissions = $True $scriptr.Options.WithDependencies = $False #Create destination folder and subfolder, if necessary $TypeFolder=$ScriptThis.GetType().Name if ((Test-Path -Path "$SavePath\Trigger") -eq "true") {"Scripting Out Trigger $ScriptThis"} else {new-item -type directory -name "Trigger"-path "$SavePath"} $ScriptFile = $ScriptThis -replace "\[|\]" #Check file status against Perforce $P4FileStatus = p4 -u RealP4Login fstat $SavePath\$TypeFolder\$ScriptFile.SQL if ($P4FileStatus) { #Open Perforce files for edit p4 -u RealP4Login sync $SavePath\$TypeFolder\$ScriptFile.SQL p4 -u RealP4Login edit -c $changeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL } #Set output filename for script $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL" #Add commented USE statement for deployment validation tool "USE $dbname" > "$SavePath\$TypeFolder\$ScriptFile.SQL" #Add existing object handler (drop trigger, etc..) "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL BEGIN DROP TRIGGER $ScriptFile END GO " >> "$SavePath\Trigger\$ScriptFile.SQL" #Generate script for this trigger $scriptr.Script($ScriptThis) #Add new files to Perforce if (!($P4FileStatus)) { p4 -u RealP4Login add -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL $ChangeListMessage = $ChangeListMessage + "Added " + $TypeFolder + " " + $ScriptFile + "
" } else { #"diff" file, revert if unchanged $P4FileDiff = p4 -u RealP4Login diff -sa $SavePath\$TypeFolder\$ScriptFile.SQL if (!($P4FileDiff)) { echo "Reverting" p4 -u RealP4Login revert -c $ChangeListNumber $SavePath\$TypeFolder\$ScriptFile.SQL } else { $ChangeListMessage = $ChangeListMessage + "Changed " + $TypeFolder + " " + $ScriptFile + "
" } } } } #Check if changelist includes any new/modified files $P4FilesChanged = p4 -u RealP4Login opened -c $changeListNumber if ($P4FilesChanged) { #Submit changelist to Perforce p4 -u RealP4Login submit -c $changeListNumber #Send summary email $Now = Get-Date $SMTPserver = "RealSMTPServer" $from = "real@real-sql-guy.com" $to = "real@real-sql-guy.com" $subject = "$dbname database changes submitted to Perforce" $emailbody = "

Changelist #$changeListNumber


Submitted $Now

$ChangeListMessage " $mailer = new-object Net.Mail.SMTPclient($SMTPserver) $msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody) $msg.IsBodyHTML = $true $mailer.send($msg) } else { #Delete changelist p4 -u RealP4Login change -d $changeListNumber } } else { echo "P4 changelist could not be created" }

Thursday, October 13, 2011

Don't Count On Existence


I like jellybeans.  I like jellybeans a lot.  If you don't like jellybeans, you must be from some crazy bizarro world world where yummy things taste bad and bad things taste good.  Seek help.

Besides being sweet and gooey and delicious, jellybeans also have another purpose.  They're very useful for "Guess The Number Of Jellybeans In The Jar" contests.  Without jellybeans, those contests just wouldn't be the same.  The jar in my photo, for instance, if not jellybeans, what else would you fill it with?


Suppose I asked you to tell me if there are any red jellybeans in that jar?  Not a hard question to answer, is it?  A quick glance, and you can say "Yes, there are red jellybeans in that jar".  What if I asked you HOW MANY red jellybeans are in the jar?  That's a completely different question, requiring a lot more effort to answer.  You're going to have to look at every jellybean, counting up those that are red, before you can answer the question.  I imagine you'd tell me to take a hike instead of counting jellybeans.  Fortunately, I'm a fairly good person, and would never ask someone to do that.  I'd never ask SQL Server to do it either, but some people do, not because they're twisted and demented, but because they simply don't know any better.

At this point, you're asking "What in the world is he talking about?".  I'm simply offering up my version of the COUNT versus EXISTS debate.  There are countless articles and blog posts out there already on this topic, but I had a discussion with someone about it yesterday, and thought it would be a good topic to write about today.

Let's start by creating a table and some test data:

CREATE TABLE JellyBeanJar
 (
  BeanID   INT   NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  BeanColor  CHAR(6)  NOT NULL
 );

WHILE (SELECT COUNT(*) FROM JellyBeanJar) < 10000
 INSERT INTO JellyBeanJar (BeanColor) VALUES (CASE ((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))) % 6) WHEN 0 THEN 'Black' WHEN 1 THEN 'Red' WHEN 2 THEN 'Green' WHEN 3 THEN 'Yellow' WHEN 4 THEN 'White' WHEN 5 THEN 'Purple' ELSE 'Pink' END);

CREATE INDEX IX_JellyBeanJar_BeanColor ON JellyBeanJar (BeanColor);

We now have a virtual jar of jellybeans, in various colors.  Let's ask SQL Server how many red jellybeans are in the jar.  We'll actually ask twice, once using COUNT, once using EXISTS:

-- Check for red jellybeans using COUNT(*)
IF (SELECT COUNT(*) FROM JellyBeanJar WHERE BeanColor = 'Red') > 0 PRINT 'There are red jellybeans in the jar';

-- Check for red jellybeans using EXISTS
IF EXISTS(SELECT * FROM JellyBeanJar WHERE BeanColor = 'Red') PRINT 'There are red jellybeans in the jar';

Compare the query plans of these two SELECT statements.  The COUNT query is twice the expense of the EXISTS query.  The reason is simple - like you in the physical world, to fulfill the first request, SQL Server has to find every red jellybean in the jar, count them, and report back the results.  The second query, using EXISTS, is equivalent to you doing a visual glance at the jar - once you've seen red in the jar, you can answer the question.  SQL Server too, once it finds one red jellybean, can answer the question.



Let's ask a slightly different question - "Prove to me that there are NO blue jellybeans in the jar".  You can glance at the jar and get a pretty good idea that there are no blue jellybeans in there, but the only way to prove it is to check each and every jellybean, looking for blue ones.

Can SQL Server do it better?

-- Check for lack of red jellybeans using COUNT(*)
IF (SELECT COUNT(*) FROM JellyBeanJar WHERE BeanColor = 'Blue') = 0 PRINT 'There are NO red jellybeans in the jar';

-- Check for lack of red jellybeans using NOT EXISTS
IF NOT EXISTS(SELECT * FROM JellyBeanJar WHERE BeanColor = 'Blue') PRINT 'There are NO red jellybeans in the jar';

Comparing the plans for these two statements, we see that they're equally expensive.  Like you, SQL Server can't just glance at the jar, it has to examine all of the jellybeans to make sure none of them are blue.



Spread the word, let's stop the SQL Server abuse, and make sure to get your recommended daily allowance of vitamin J(ellybean).

Friday, October 7, 2011

Loopty-Loop

A recent encounter with my arch-enemy, The Tangled Loop, left me weary and scarred, I haven't yet fully recovered.  During the battle, I witnessed unspeakable things, lines of code that mangled T-SQL into something unrecognizable.  I feel guilty exposing you all to this, but it must be done, for you must know what to be on the lookout for.  The Tangled Loop is still out there, somewhere, he may surface in your workplace next.  What you're about to see is real, actual code that a developer wanted to deploy to our production server.

The code below, and I use the term "code" loosely, is intended to loop (yes, that's right, loop) through a table, and for certain rows, replace certain string combinations with something else.  Go ahead, look at it.  I'll give you moment to stop vomiting and catch your breath.

-------------------------------------
-- declare variables
-------------------------------------
DECLARE @conversion TABLE (
  fromvalue VARCHAR(50),
  tovalue   VARCHAR(50))
DECLARE @rowsToUpdate TABLE (
  idtoupdate    INT,
  paramtoupdate VARCHAR(255))
DECLARE @paramName VARCHAR(20)
DECLARE @ruleID INT
DECLARE @ruleParam VARCHAR(255)
DECLARE @delim CHAR
DECLARE @delimIndex INT
DECLARE @delimWork VARCHAR(255)
DECLARE @oldValue VARCHAR(50)
DECLARE @newValue VARCHAR(50)
DECLARE @newValues VARCHAR(255)

---------------------------------------
---- Initialize variables
---------------------------------------
INSERT INTO @conversion
VALUES      ('E',
             'EMAIL')

INSERT INTO @conversion
VALUES      ('S',
             'SMS')

INSERT INTO @conversion
VALUES      ('X',
             'DO_NOT_Send')

INSERT INTO @conversion
VALUES      ('EMAIL',
             'EMAIL')

INSERT INTO @conversion
VALUES      ('SMS',
             'SMS')

INSERT INTO @conversion
VALUES      ('DO_NOT_Send',
             'DO_NOT_Send')

SET @delim = ','
SET @paramName = 'CP='

-------------------------------------
-- Procedure body
-------------------------------------
-- find all rows to process
INSERT INTO @rowsToUpdate
SELECT correspondencetemplateconfigurationselectionruleid,
       ruleparam
FROM   dbo.correspondencetemplateconfigurationselectionrule
WHERE  ruleparam LIKE @paramName + '%'

-- loop through the rows to process
SET rowcount 1

SELECT @ruleID = idtoupdate,
       @ruleParam = paramtoupdate
FROM   @rowsToUpdate

WHILE @@ROWCOUNT <> 0
  BEGIN
      PRINT N'Change: ' + CAST(@ruleParam AS VARCHAR(255))

      -- The rule parameter is like: CP=a,b,c  First we pull out the value portion (a,b,c)
      SET @delimWork = Substring(@ruleParam, Len(@paramName) + 1,
                       Len(@ruleParam))
      SET @newValues = @paramName

      -- loop through value list by delimiter (ex:  if values are a,b,c then we process a then b then c)
      SELECT @delimIndex = Charindex(@delim, @delimWork, 0)

      WHILE @delimIndex > 0
        BEGIN
            SELECT @oldValue = Ltrim(Rtrim(Substring(@delimWork, 0, @delimIndex)
                                     ))

            -- convert the old value to new new value
            SET @newValue = NULL

            SELECT @newValue = tovalue
            FROM   @conversion
            WHERE  fromvalue = @oldValue

            IF ( @newValue IS NOT NULL )
              BEGIN
                  -- save new value to new list of values
                  SET @newValues = @newValues + Ltrim(Rtrim(@newValue)) + @delim
              END

            -- setup for the next loop of delimited values
            SELECT @delimWork = Substring(@delimWork, @delimIndex + 1, Len(
                                @delimWork)
                                )

            SELECT @delimIndex = Charindex(@delim, @delimWork, 0)
        END

      -- need to pull the last item out of the value list
      IF Len(@delimWork) > 0
        BEGIN
            SET @oldValue = @delimWork
            -- convert the old value to new new value
            SET @newValue = NULL

            SELECT @newValue = tovalue
            FROM   @conversion
            WHERE  fromvalue = @oldValue

            IF ( @newValue IS NOT NULL )
              BEGIN
                  -- save new value to new list of values
                  SET @newValues = @newValues + Ltrim(Rtrim(@newValue))
              END
        END

      PRINT 'To: ' + @newValues

      PRINT '------------------------------------------'

      -- Update the table
      UPDATE dbo.correspondencetemplateconfigurationselectionrule
      SET    ruleparam = @newValues
      WHERE  correspondencetemplateconfigurationselectionruleid = @ruleID

      -- setup next loop of rows to update
      DELETE FROM @rowsToUpdate
      WHERE  idtoupdate = @ruleID

      SELECT @ruleID = idtoupdate,
             @ruleParam = paramtoupdate
      FROM   @rowsToUpdate
  END

SET rowcount 0 


We all know looping is not preferred within T-SQL, but this example takes it to the extreme.  Not only does it loop through the rows of a table, but when doing the string replacements, it loops through the strings, ONE CHARACTER AT A TIME!

When The Tangled Loop launched this attack, I grabbed the nearest T-SQL reference book to use as a shield.  This brief respite allowed me the time to muster up a counter-attack:

UPDATE dbo.correspondenceconfiguration
SET    param = 'CP=' + REPLACE((SELECT map.newvalue AS [data()]
                                FROM   (SELECT '|E|'   AS oldvalue,
                                               'EMAIL' AS newvalue
                                        UNION ALL
                                        SELECT '|S|' AS oldvalue,
                                               'SMS' AS newvalue
                                        UNION ALL
                                        SELECT '|X|'         AS oldvalue,
                                               'DO_NOT_Send' AS newvalue) AS map
                                WHERE  REPLACE(REPLACE(
                                       correspondenceconfiguration.param + '|',
                                               '=',
                                               '=|'), ','
                                       , '|,|')
                                       LIKE '%' + map.oldvalue + '%'
                                FOR XML PATH('')), ' ', ',')
FROM   dbo.correspondenceconfiguration
WHERE  param LIKE 'CP=%'


The server was saved from a terrible fate, but unfortunately, The Tangled Loop escaped, and is still out there, somewhere.  I'm sure we haven't seen the last of him.

Automated Source Control: A Librarian For Your Database Objects, Part 2

I assume that by now you've tried the script from Part 1, and have seen how easy it is to script out your database objects using Powershell.  The resulting scripts are the standard DDL scripts that you can generate from Management Studio, and for most of us, are just fine right out of the box.

What if, however, we need to customize those scripts?  In my environment, our software deployments (including the database scripts) are automated, and we need certain elements to be included in the scripts for the automation to function properly.  I also like to include a certain degree of fail-safe in the scripts, to prevent accidentally deploying an object to the wrong database, or possibly wiping out a table full of data.

Turns out that it's a snap to customize the scripts that are generated, using simple text piping, just like you've been doing in batch files for a decade or two.  Simply insert the following into the script from Part 1.  For your convenience, the entire script is repeated at the end of this post.

I'll resume this series after SQL Pass.  I'm not attending, but I will be taking some time off next week, and with all of the buzz around the conference, nobody will be paying attention to little ol' me anyway!

    #Add commented USE statement for deployment validation tool
    "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"

    #Add existing object handler (drop procedure, table exists, etc..)
    if ($TypeFolder -eq "StoredProcedure") {
    "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
        BEGIN
        
        DROP PROCEDURE $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "View") {
    "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
        BEGIN
        
        DROP VIEW $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "UserDefinedFunction") {
    "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
        BEGIN
        
        DROP FUNCTION $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Trigger") {
    "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
        BEGIN
        
        DROP TRIGGER $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Table") {
    "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
        BEGIN
        
        IF EXISTS(SELECT * FROM $ScriptFile)
            BEGIN
            
            USE tempdb
            RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
            RETURN
            
            END
        ELSE
            BEGIN
            
            DROP TABLE $ScriptFile
            
            END
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    }

The complete script now looks like this:

#Set server and database names here
$server = "REALServer"
$dbname = "REALDB"

#Create SMO connection to DB
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $server
    $db = $SMOserver.databases[$dbname]
 
#Build collection of objects to script
$Objects = $db.Tables
$Objects += $db.Views
$Objects += $db.StoredProcedures
$Objects += $db.UserDefinedFunctions

#Set destination for generated scripts
$SavePath = "C:\temp\db\Source\" + $($dbname)

#Loop through the object collection
foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
	#Create Scripter object
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
	
	#Set options for generated scripts
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False

	#Create destination folder and subfolder, if necessary
    $TypeFolder=$ScriptThis.GetType().Name
    if ((Test-Path -Path "$SavePath\$TypeFolder") -eq "true")
            {"Scripting Out $TypeFolder $ScriptThis"}
        else {new-item -type directory -name "$TypeFolder"-path "$SavePath"}
    $ScriptFile = $ScriptThis -replace "\[|\]"

	#Set output filename for script
    $scriptr.Options.FileName = "$SavePath\$TypeFolder\$ScriptFile.SQL"

    #Add commented USE statement for deployment validation tool
    "USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"

    #Add existing object handler (drop procedure, table exists, etc..)
    if ($TypeFolder -eq "StoredProcedure") {
    "IF OBJECT_ID('$ScriptFile', 'P') IS NOT NULL
        BEGIN
        
        DROP PROCEDURE $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "View") {
    "IF OBJECT_ID('$ScriptFile', 'V') IS NOT NULL
        BEGIN
        
        DROP VIEW $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "UserDefinedFunction") {
    "IF OBJECT_ID('$ScriptFile', 'FN') IS NOT NULL OR OBJECT_ID('$ScriptFile', 'TF') IS NOT NULL
        BEGIN
        
        DROP FUNCTION $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Trigger") {
    "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
        BEGIN
        
        DROP TRIGGER $ScriptFile
        
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    } elseif ($TypeFolder -eq "Table") {
    "IF OBJECT_ID('$ScriptFile', 'U') IS NOT NULL
        BEGIN
        
        IF EXISTS(SELECT * FROM $ScriptFile)
            BEGIN
            
            USE tempdb
            RAISERROR('Table exists and contains data, cannot proceed.', 16, 1)
            RETURN
            
            END
        ELSE
            BEGIN
            
            DROP TABLE $ScriptFile
            
            END
        END
    GO
    " >> "$SavePath\$TypeFolder\$ScriptFile.SQL"
    }

	#Generate script for this object
    $scriptr.Script($ScriptThis)
}

#Second loop through tables to script triggers
foreach ($Table in $db.Tables | where {!($_.IsSystemObject)}) {
	#Loop through triggers on this table
    foreach ($ScriptThis in $Table.Triggers) {
		#Create Scripter object
        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
		
		#Set options for generated scripts
        $scriptr.Options.AppendToFile = $True
        $scriptr.Options.AllowSystemObjects = $False
        $scriptr.Options.ClusteredIndexes = $True
        $scriptr.Options.DriAll = $True
        $scriptr.Options.ScriptDrops = $False
        $scriptr.Options.IncludeHeaders = $False
        $scriptr.Options.ToFileOnly = $True
        $scriptr.Options.Indexes = $True
        $scriptr.Options.Permissions = $True
        $scriptr.Options.WithDependencies = $False
         
		#Create destination folder and subfolder, if necessary
		$TypeFolder=$ScriptThis.GetType().Name
        if ((Test-Path -Path "$SavePath\Trigger") -eq "true")
                {"Scripting Out Trigger $ScriptThis"}
            else {new-item -type directory -name "Trigger"-path "$SavePath"}
        $ScriptFile = $ScriptThis -replace "\[|\]"

		#Set output filename for script
        $scriptr.Options.FileName = "$SavePath\Trigger\$ScriptFile.SQL"

		#Add commented USE statement for deployment validation tool
		"USE $dbname" >  "$SavePath\$TypeFolder\$ScriptFile.SQL"

        #Add existing object handler (drop trigger, etc..)
        "IF OBJECT_ID('$ScriptFile', 'TR') IS NOT NULL
            BEGIN
            
            DROP TRIGGER $ScriptFile
            
            END
        GO
        " >> "$SavePath\Trigger\$ScriptFile.SQL"

		#Generate script for this trigger
        $scriptr.Script($ScriptThis)
	}
}