Just a very quick blog today regarding an issue that has arisen with one of my clients. During Integration it became apparent that one table in particular was failing during the weekly consistency checks, the error being output:
DBCC results for ‘sys.extended_index_1696529623_384000′.
There are 313423 rows in 1627 pages for object “sys.extended_index_1696529623_384000″.
DBCC results for ‘schema.Table’.
There are 312246 rows in 12192 pages for object “schema.Table”.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
A bit of background. The server is running SQL Server 2008R2 SP1 CU2 and the database in question is still in compatibility 90 (SQL Server 2005). The table in question has a spatial index on a Geography column.
So, how do we fix this? Well there’s a couple of options.
- Change the compatibility to 100
- Install SQL Server 2008R2 SP1 CU3…
This is a documented issue (kb 2635827) and the fix can be found on Microsoft’s Support Pages.
FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2
As to which fix we deploy, well that’s for tomorrow’s fun and games
I finally pulled my finger out and took the last exam of the MCSE SQL Server 2012 – Data Platform certification last week and passed with a score of 876 which is pretty respectable in my opinion
The exam consisted of 3 sections:
So those of you who are clever enough to work out that’s a grand total of 45 questions. I’m pleased to say that once again the quality of the questioning was to Microsoft’s usual standard, not in terms of difficulty but more in terms of complete irrelevance!
I’m bound by NDA so can’t go into specifics but I’ll try and provide and example of this complete and utter irrelevance without giving the game away:
Q: What is your favourite holiday destination?
A (Select one of the following):
Hmmmm, bit of a tricky one here. From what I can tell, none of the answers bear any relevance whatsoever to the question…….hmmmmm……..suppose I’ll have to take a random guess and hope that the answer I select is the one they’ve set as being correct!!!
It was just a good job i wasn’t on the threshold of pass/fail as this could potentially have been the difference. From the actual question, it was to be a simple answer but I suppose I’ll never know whether or not my random guess worked out or not . I just hope the comments I left regarding this issue are taken up and fixed so others don’t have the same issue.
All in all I felt comfortable throughout the exam but do know for a fact that my knowledge of Assemblies / CLR’s has slipped significantly and I need a refresher.
So that’s it. SQL Server 2012 MCSE done and dusted and no more exams……well for the next 3 years at least. So whats next? Not sure, there is the MCSM exams but I have to be honest, I’ve got no formal certifications in SQL Server 2005/2008 but I know for a fact that I have far more knowledge and experience of its features than I do for SQL Server 2012 so I suppose I kind of backed up my original issue I have with these exams. Yes I studied and yes I did learn a lot from doing so but in no way shape or form would I class myself as an “Expert” in SQL Server 2012 – I don’t believe anyone could!
As per usual, any thoughts or comments are welcome.
Well I finally got around to completing the MCSA aspect of the SQL Server 2012 Certification and I’m pleased to say i passed with flying colours. As some of you may be aware I managed to nab and pass 3 of the Beta exams (70-461, 70-462 and 70-465) back in April and decided to see the MCSE through.
I really wasn’t sure how this exam was going to go as I’ve been working a lot recently with MDS 2012 and SSIS 2008 and revised the new 2012 features but went in with no real expectations. The exam consisted of 55 questions, again ranging from multiple guess, select the 3 things you’d do in order to a new feature i’ve not seen before and that is a drag n drop facility of a SSIS control flow which I thought was nifty.
The area I thought I’d struggle on was DQS but in fact found that aspect relatively simple, the difficult area for me was the “select the 3 things you’d do in order” relating to the new Project Deployment area of SSIS 2012. I’ve done a fair bit of “tinkering” with this over the last few months but its obvious I’m not as prolific as I thought as I found certain questions difficult to get my head around what it was suggesting in the answers. I obviously did ok in this area (according to the score sheet) but at the time i was sweating a bit.
Anyone wanting hints and tips, I obviously can’t go into detail but I’d definitely brush up on the new features of SSIS 2012!!!
Oh, and anyone wanting to know, the pass mark is 700 – none of the Beta exams told you this and I know some have said it was actually 800……
Now onto 70-464 - Developing Microsoft SQL Server 2012 Databases, to complete the SQL Server 2012 MCSE certification!!!!
I wouldn’t class myself as an expert in SSIS but I certainly know my way around but came across something today which I thought I’d share. As with a lot of things there are “many ways to skin a cat”, none of which is something I’ll go into at the moment but what i will concentrate on is updating columns in a table where the data has changed in the source.
One of the projects I’m currently working on requires this very process and when i set about doing so I created the T-SQL Merge statement to do the business. However, the question was raised as to why I didn’t use SSIS’s built in component Slowly Changing Dimension (SCD)? I didn’t really have an answer other than personal preference but decided to delve into it a bit further and compare the performance of each method.
As a test, I created a source table with a Key and Name column:
USE TempDB; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iSource') AND type in (N'U')) DROP TABLE dbo.iSource; CREATE TABLE dbo.iSource ( ID INT, Name varchar(100) ); IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iTarget') AND type in (N'U')) DROP TABLE dbo.iTarget; CREATE TABLE dbo.iTarget ( ID INT, Name varchar(100) );
and populated it with some dummy data:
INSERT INTO dbo.iSource (ID,Name) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY t.object_id) AS rownumber ,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id)) FROM sys.tables t CROSS JOIN sys.stats s; INSERT INTO dbo.iTarget (ID,Name) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY t.object_id DESC) AS rownumber --Done in descending order ,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id)) FROM sys.tables t CROSS JOIN sys.stats s; SELECT ID, Name FROM iSource; SELECT ID, Name FROM iTarget;
So we now have a source and target table with different Names and we’ll look to update the iTarget table with the information coming from iSource.
Method 1 – MERGE Statement
MERGE dbo.iTarget AS target USING ( SELECT ID, Name FROM dbo.iSource ) AS source (ID, Name) ON (target.ID = source.ID) WHEN MATCHED AND target.Name <> source.Name THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (source.ID, source.Name);
Using this method simply in SSMS for simplicity, profiler output 2 rows for Batch Starting and Batch Completing, CPUTime of 125ms and Duration of 125ms and it updated 6678 records. Top stuff, as expected.
Method 2 – SSIS SCD Component
I rebuilt the tables to put them back to where we started and set about creating the same thing in SCD setting ID as the business key and Name as the changing attribute and not setting inferred members, below is a screen dump of the outcome of this:
I clear down the profiler and run the ssis package and the outcome is quite astounding.
The profiler output 13456 rows including 6678 rows of queries like this:
exec sp_executesql N'SELECT [ID], [Name] FROM [dbo].[iTarget] WHERE ([ID]=@P1)',N'@P1 int',8
as well as 6678 rows of queries similar to this:
exec sp_execute 1,'Name_3304',3304
Total Duration of 37 seconds (yes that’s seconds not ms!!)…….and this is on a table of only ~7k rows!
Well I’ll be damned, the SCD basically runs a cursor looping each record checking for a match on ID and updating that record if so. I can’t actually believe that MS have built a component which performs in this way.
So, to answer the question asked ” why I didn’t use SSIS’s built in component Slowly Changing Dimension (SCD)?”, I now have a definitive answer, it doesn’t perform!
I’m sure SCD has its place but for me, the requirements and the datasets I’m working on I think I’ll stick with MERGE for now…..
NOTE: This was done on SQL Server 2008R2 Developer Edition running on Windows 7 Ultimate, not sure if SQL Server 2012 has improved the SCD performance but I’ll leave that for another day.
Being a 2011 finalist I felt I should try and rally all those who truly are exceptional to get their nominations in and quick sharp as the closing date is getting close.
I was lucky enough to be nominated for this award last year and wasn’t going to follow it through as I felt I didn’t really stand a chance but when I sat and thought about it, if someone is willing to think of you as being exceptional at what you do, enough so to nominate you then why not, what’s the worst that can happen!!??!!
The level of talent out the is phenomenal and the 4 guys I was up against last year are up there with the best in the world. Don’t let that put you off though, I feel that this award is very much focused towards those in the USA and not many actually make it through to the finals from the UK (Kevan Riley Blog / Twitter and myself I think are the only two!) so I think we need to give a bigger push this year and try and get more than one finalist from the UK
If you haven’t been nominated by one of your peers then nominate yourself, there’s no rule saying you can’t and in fact Redgate encourage it.
Get entered, the questions answered and cross your fingers!
Well after being a bit late in trying to book the beta exams I managed to get three of the five I needed for the MCSE data platform booked, taken and I’m pleased to say passed!
Unfortunately due to the fact I did 461, 462 and 465 means I don’t actually come away with any certification as I need 463 for the MCSA and then the 464 to complete the MCSE.
From what others in the field have said about 463, it’s very SSIS orientated which as it happens works out well for me as the project I’m currently working on is primarily SSIS so when things quieter down a bit I’ll look to get it booked. As for 464, I may very well look to batter that one out around the same time so it’s done n dusted.
I’ve never really been a big fan of Microsoft certification and my mindset hasn’t changed much. The questioning is still vague at times and in my opinion done in such a way that it’s a test of whether you can read a question and do what Microsoft believe is the best way to do things but I still don’t feel the questions always give enough information for you to give the best solution. In the real world there are a hell of a lot more questions I’d be asking in some of the scenarios before I could make a correct decision.
Anyway, enough whinging. Time to do some proper work