Looking forward to heading to Southern California this weekend for SQL Saturday #120 in Huntington Beach. They always put on a great event and you can’t beat the location. Not to mention that it is very close to where I grew up so I get to see some old friends while I’m there.
I will be doing two sessions at this event:
Of course there are many other fantastic sessions as well as a pre-conference session all day on Friday.
Hope to see you there!
This last Saturday was the most recent iteration of Utah Code Camp and I have to say it was a resounding success. In addition to speaking, I was invited to participate in an “Ask a DBA” panel discussion and also had the opportunity to have SQL Solutions Group sponsor the event. So it was a busy day.
My first session was the last one before lunch and is one of my favorites to give. But it worked in Dev! SQL Server Performance for Developers is always a lively session with good discussion. Given that this was a code camp, the audience was almost all developers so it worked out great. Based on the discussions both during and after the session, I am confident more than a few developers will be looking at their SQL more closely than they did before. There was one participant who was actually pretty shaken up when learning what “NOLOCK” really does and does not do. I think I may have ruined his weekend but as they say, forewarned is forearmed.
Thanks to Platinum Sponsor Domo, we had a great lunch catered by Famous Dave’s. Then it was on to afternoon sessions. I finished the day participating in a panel discussion of DBA’s called “Ask a DBA”. This was an informal session with DBA’s representing SQL Server, Oracle, and mySQL. Most of the attendees were SQL Server users so that is where the discussion seemed to focus but it was great hearing the contrast between the systems anyway. There were some great questions regarding scalability (up vs. out), NoSQL, and many others. Kudos to Pat Wright for putting this together.
The most impressive thing about this Code Camp was the attendance. I believe 380 was the offical count and that is almost double what they had last year. As a sponsor, this was fantastic as we had a ton of people come by the booth and made some good contacts, both as prospective clients and as prospective consultants. Since the gating factor for the growth of my business has been more in relation to having the consultants to do the work than in having enough work to do, this part is important.
Thanks to the folks at Utah Geek Events who as always, did a fantastic job putting on the event.
This is a bit late but wanted to post about my experience at the recent SQL Saturday held in Omaha, Nebraska. This was a fantastic event, particularly given that it was the first SQL Saturday held in Omaha. It was well-organized, had great sponsor support, and the facility at the University of Nebraska at Omaha was top notch. Kudos to John Morehouse and team for putting on a fantastic SQL Saturday.
I had the privilege to present on two topics at the event. I try to come up with catchy titles for my presentations but attendance was a bit down for these (both new sessions) so I wonder if the issue was people had no idea what they were really about. In any case I am happy to report we had quality if not quantity in attendance. Great feedback / input from the attendees. And I tried hard not to offend the developers too badly.
As always, the best part about SQL Saturday is the interaction and networking with other speakers and attendees. I didn’t get to attend many other sessions but I did sneak into Kathi Kellenburger’s session on new T-SQL features in Denali. Good stuff.
Links to session downloads:
Next up: SQL Saturday #94 in Salt Lake City. In addition to speaking, my company is a sponsor so it will be a busy day.
One of the most important tasks for the DBA is managing backup files. Often the SQL Server backups files themselves are written to a drive local to the database server, but we then need to copy them off to a remote share. RoboCopy is a fantastic utility included in Windows that is great at doing this. If you’re not familiar with this tool, you can find detailed information on it here.
However, when using SQL Server Agent to schedule a RoboCopy job, you run into an issue with Process Exit Codes. This is because RoboCopy does not always return a 0 for success, non-zero for error. So when you tell SQL Server Agent that 0 = Success, you get false job failures. In fact, what RoboCopy returns is a bitmask as follows:
| Bit | Description |
| 16 | Serious error. Robocopy did not copy any files. This is either a usage error or an error due to insufficient access privileges on the source or destination directories. |
| 8 | Some files or directories could not be copied (copy errors occurred and the retry limit was exceeded). Check these errors further. |
| 4 | Some Mismatched files or directories were detected. Examine the output log. |
| 2 | Some Extra files or directories were detected. Examine the output log. |
| 1 | Some Extra files or directories were detected. Examine the output log. |
| 0 | No errors occurred, and no copying was done. The source and destination directory trees are completely synchronized. |
As you can see from the above, there are several exit codes that are successful. 1,2,3,7,8,9, etc. In most cases, any combination of the 0,1,2,4 bits would be fine. So what we need is a way to interpret the bitmap that is returned and send a 0 or 1 to SQL Server indicating success or failure.
Enter PowerShell
I wrote a short PowerShell script to deal with this as follows
1: param
2: (
3: [Parameter(Position=0, Mandatory=$true)] [string]$SrcFolder,
4: [Parameter(Position=1, Mandatory=$true)] [string]$TgtFolder
5: )
6:
7: ##copy
8: robocopy $SrcFolder $TgtFolder /S /XO /XC /XN /NP /MIN:1024 /R:2 /W:2
9:
10: ##set exit code
11: $exit = $lastexitcode -band 24
12:
13: exit $exit
The script used the powershell bitwise operation –band to check to see if bits 8 or 16 are set, as these are the two actual errors we are concerned about. It then exits with a 0 or 1, which is what SQL Server Agent expects from an OperatingSystemCmdExec job step. Note that this applies mainly to SQL Server 2005. In 2008 and 2008 R2 you can create a PowerShell job step so you might handle this a bit differently.
One of the issues I often run into is the need to truncate very large tables. But if there is a foreign key constraint on the table, SQL Server will not allow a truncate, only a delete. This is fine for smaller tables but if the table has millions of rows, we want to do a truncate vs. a delete, as a truncate is minimally logged and much faster. Unfortunately, disabling the constraints is not enough. It has to be dropped. So I put together a script to do this for me.
The first thing we need to do is document all the foreign keys so we can re-create them later. I do this by getting the constraint definitions from a couple of DMV’s and storing the pertinent data in a temporary table. Note that if for some reason you lost your connection, you would lose this table so you could give some consideration to using a permanent table.
1: SELECT
2: fk.name AS FKName
3: ,OBJECT_NAME(fk.parent_object_id) AS TableName
4: ,OBJECT_NAME(fk.referenced_object_id) AS ReferencesTable
5: ,COL_NAME(fk.parent_object_id,fkc.parent_column_id) AS ConstraintColumn
6: ,COL_NAME(fk.referenced_object_id,referenced_column_id) AS ReferenceColumn
7: INTO #fk
8: FROM sys.foreign_keys fk
9: INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
10: ORDER BY 1
Now we can use dynamic SQL to drop the constraints
1: DECLARE
2: @FKName sysname
3: ,@TableName sysname
4: ,@ReferencesTableName sysname
5: ,@ConstraintColumn sysname
6: ,@ReferenceColumn sysname
7: ,@SQL NVARCHAR(4000)
8:
9: DECLARE cur CURSOR FOR
10: SELECT FKName, TableName FROM #fk
11: OPEN cur
12: WHILE 1=1
13: BEGIN
14: FETCH NEXT FROM cur INTO @FKName, @TableName
15: IF @@fetch_status !=0
16: BREAK
17:
18: SET @SQL = 'alter table ' + @TableName + ' DROP CONSTRAINT ' + @FKName
19: EXEC dbo.sp_executesql @SQL
20: END
21: CLOSE cur
22: DEALLOCATE cur
23: GO
Using the undocumented stored procedure sp_msforeachtabled we can easily truncate all the tables now.
1: exec sp_MSforeachtable 'truncate table ?'
Finally, we use dynamic SQL to re-create the constraints
1: DECLARE
2: @FKName sysname
3: ,@TableName sysname
4: ,@ReferencesTableName sysname
5: ,@ConstraintColumn sysname
6: ,@ReferenceColumn sysname
7: ,@SQL NVARCHAR(4000)
8:
9: DECLARE cur CURSOR FOR
10: SELECT * FROM #fk
11: OPEN cur
12: WHILE 1=1
13: BEGIN
14: FETCH NEXT FROM cur INTO @FKName, @TableName, @ReferencesTableName,
15: @ConstraintColumn, @ReferenceColumn
16: IF @@fetch_status !=0
17: BREAK
18:
19: SET @SQL = 'alter table ' + @TableName + ' ADD CONSTRAINT ' + @FKName +
20: ' FOREIGN KEY(' + @ConstraintColumn + ') REFERENCES '
21: + @ReferencesTableName + '(' + @ReferenceColumn + ')'
22: EXEC dbo.sp_executesql @SQL
23: END
24: CLOSE cur
25: DEALLOCATE cur
26: GO
1: create table #rowcount (tablename varchar(128), rowcnt int)
2:
3: exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
4:
5: select * from #rowcount order by tablename
6:
7: DROP TABLE #rowcount
8:
Just a quick shout out to a great article on how to Debug CLR Projects.
Headed to Chicago tomorrow for SQL Saturday #67. Heading out a day early to attend a pre-conference seminar on Friday on troubleshooting and performance tuning. While I feel pretty strong in these areas, one thing I haven’t done as good of a job of as I should is keeeping up on new features to assist with this in SQL Server 2005 and 2008. Dynamic Management Views, Extended Events, and so forth. So this should be a good class to help teach an old dog new tricks. Then on Saturday I get to present the XML session again which I created for Utah Code Camp. Looking forward to it.
I’ve been doing a lot of traveling lately speakng at SQL Saturday events. This last weekend it was my great pleasure to speak at Utah Code Camp, a local event. Not only was it nice to be local, but I really enjoyed the code camp. Unfortunately I didn’t get to stay all day as I had some other things going on as I have been gone a lot of Saturdays lately but I rally enjoyed the time I was there. I prepared a new session for the event, which I am also doing at SQL Saturday in Chicago this weekend (3/26). The session is titled “Hierarchical data in my database? Understanding SQL Server XML Features”. It is a quick overview of the various ways to get XML in and out of SQL Server, the T-SQL XQuery implementation, etc. A new session is always a bit worrisome as no matter how much you prepare and practice, you’re not real sure how the timing will go. But it seemed to go well, I had several positive comments from attendees afterwards, and I finished with about 5 minutes to spare. So not a bad first outing at all.
This discussion came up on Twitter so I thought I’d blog about it. UPDATE FROM (sometimes called an UPDATE JOIN) is an language construct that is an extension to the ANSI-92 standard supported by several DBMS’s, but not by Oracle. I first discovered this when doing some ETL work in SSIS with an Oracle destination. The Oracle DBA’s answer was to use a cursor, which admittedly has much less of a performance impact in Oracle than it does in SQL Server. But my set-based brain just couldn’t deal with that. So I went digging and discovered a set-based method to use in Oracle to accomplish the same thing.
The following two code snippets show how I would accomplish the same thing in both T-SQL and in Oracle.
T-SQL
update t1 set
t1.col2 = t2.col2
from t1
inner join t2 on t1.col1 = t2.col1;
Oracle
update
(select
t2.col2 as src_2,
t2.col2 as tgt_2
from t1
inner join t2 on t1.col1 = t2.col1
)
set tgt_2 =src_2;
As you can see, the set-based Oracle version would become quite verbose if you’re dealing with a lot of columns. Also, Oracle requires the subquery to return unique rows, which makes me wonder if it isn’t using a cursor under the hood anyway.
I was asked by a client today to provide 4 bullet points to include in a job description for a developer position that will be primarily responsible for SQL Development. I explained that my preference is to focus on database engine first, then worry about T-SQL. Too many times I see customers who focus on “code skills” for database developers. And end up with coders who don’t understand the engine, physical storage, indexing, data modeling, etc.
Anyway, here is what I came up with.
Justin Dearing 1:35 pm on February 5, 2011 Permalink |
I guess my only comment is the forth point should be tuned based on needs of the hiring shop. I worked for a place that was deeply invested in XML on all tiers, but had no need to use SQL’s internal reporting services. A consulting house would need a jack of all trades.
As far as knowing the internals first, and T-SQL second I agree. As a programmer who has to wear the DBA hat, I never say “I wish a real DBA could show me how to use @TSQL_KEYWORD” and I can read a query plan and know if it looks ugly. However, beyond “it normalized”, columns are as small as possible and “CI SEEK > NCI SEEK > IX SCAN > TABLE SCAN” I don’t know what to do.
Randy Knight 10:21 pm on February 5, 2011 Permalink |
Good points. My thought on bullet 4 is that anybody who claims to know SQL Server should at least know what each of those things are. And then you could delve into expertise on a given feature depending on needs.