Category: Tools


image Over the past couple of years I’ve been deliberately avoiding using any form of third-party add in for SQL Server Management Studio. I was doing this since I was pursuing the SQL Server Master Certification, and wouldn’t have had access to these tools during the exams. Since I’m no longer actively doing this, I’ve started looking around.

For several years, I’ve been using the DevExpress Coderush product for development work in C# and Visual Studio, having made the decision between it and ReSharper. That was a difficult choice to make.

Fortunately, the decision for SQL Server is easier; I’m only aware of one, which is Redgate’s SQL Prompt. I had a look at this a year or so back, but decided to uninstall it due to the reasons given above.

Having started looking at it again, and particularly with the new version I’m loving the experience.

What I like:

  • Tab History –  When you close Management studio (SSMS), SQL Prompt saves the contents of all your open tabs, and reopens them when you open SSMS again. I do this at least once a day, and it’s stopped me kicking myself. You can also see the history of tabs that have been open, and this also has a preview screen (shown below) which is really helpful for all those SQLQuery1.sql files.

image

  • Shortcuts – They are saving me time. You can type a shortcut, and it’ll replace it with the SQL. My favourite so far is st100, which gets replaced with SELECT TOP 100 * FROM
  • Improved Intellisense – It gives a more intelligent set of results to help you type your queries. This even goes down to the level of suggesting join criteria.
  • Performance – One of the best features is that it really doesn’t seem to impact performance of Management studio.

What I don’t like:

This was a hard one. The only thing that really stopped me using it (aside from the exam thing) is the cost.

Final Thoughts

Would I recommend it ? Yes, definitely, it’s improved my SQL, reduced code errors and allows me to concentrate on what the T-SQL is supposed to do, rather than focussing on what the syntax is.

There are a couple of features I’d like it to have: I’d quite like it to also work with MDX. I’d like it to also recommend code changes, to help with implementing best practices.

Disclaimer:

I was offered a free licence of SQL Prompt, by Redgate, for this review. However, the content of the review has been written by myself, and the benefit of the licence is that it gave me time to write the review.

Advertisements

So, about 10 months ago, I did a blog article about my experiences moving from a Blackberry to an iPhone 4s (Read it here).

Well, I’ve moved off it now. It was a combination of reasons, including:

  1. I can’t write stuff for it. I’m starting to do more programming at the moment, and it’s a nice idea to be able to write stuff for the phone I’m using. I can’t do this on an iPhone.
  2. The screen is small. I’ve also got an iPad2 and still happily use that. However, the screen on the iPhone is small compared with alot of newer smart phones.
  3. Magpie syndrome. I like shiny things, while my Blackberry 9700 kept me happy for 2+years, the Blackberry 9900 didn’t, and it turns out that the iPhone hasn’t either.
  4. Using the iPhone feels like I’m in a glass box, with all the fun things being outside. I know this sounds ridiculous, but I’m not sure how else to explain it.

So, what have I moved to ? A very kind person has let me have their previous phone, a Galaxy Nexus. This is lovely to use, and easily cover the items above. In addition to this, it lets me:

  1. Use almost all the apps I was using on the iPhone (Evernote, RunKeeper, Pluralsight, Netflix, Flipboard and iPlayer).
  2. I discovered some new apps I could use now, including Falcon Pro (a really nice Twitter client) and Ingress. Ingress is really interesting, and doesn’t appear to have anything like it on the iPhone.

I’m not saying that I’m not using the iPhone anymore. I am, I’m using it as a GPS device for running. It’s great that the RunKeeper app is available on both platforms, and also, the iPhone is smaller so fits nicely in my pocket when out and about.

I’ve also started a separate blog (separation of anxieties, or something), RunningNick.com, which will be covering my training for the Great South Run, which I’m doing in October (10 MILES!!!). If you’d like to sponsor me, there is a link on the right, funds go to Diabetes UK.

TestDay

Unit Testing is a methodology that we should all embrace and understand.

It’s not just for Programmers

Unit Testing Frameworks are available for almost every Platform, from ABAP to XSLT. So if you are a hard-core coder, a SQL DBA, a Web Developer, or a Sys Admin, you can join in!

While the use of Unit Testing is getting more common, it’s not as common as it could be.

So I’d like to propose TEST DAY 2012!!!

How do you do Testing ?

Why not share how you do Unit Testing, why you started it, what your experiences have been, or something related to Unit Testing ?

We can all learn from each others experiences.

What do you need to do ?

  • Write a blog Post and Share it with the internet, so everyone can learn from your experiences.
  • Your blog post must be published between Wednesday, 12th December 2012 00:00:00 GMT and Thursday, 13th December 2012 00:00:00 GMT.
  • If you are on Twitter please tweet your blog using the #TestDay2012 hashtag. I can be contacted there as @nhaslam, in case you have questions or problems with comments/trackback.
  • Either, include the TestDay2012 Picture (above) and hyperlink it back to this post, or have a link back to this post.
  • If you don’t see your post in trackbacks, add the link to the comments below.

What will I do ?

A week or so later (depending on the number of posts), I’ll do a summary post and cover all the submissions.

I look forward to reading your posts!

Travelling with Gadgets

Following on from a previous post on my journey to Seattle (Sleeplessness in Seattle) for the SQL Skills Immersion Event on Performance Tuning (IE2), last week, I thought, I’d share my experiences of travelling with Gadgets.

To allow me to have access to everything I needed while I’d be away, I took the following with me:

  1. Apple iPhone 4s – My personal mobile
  2. Blackberry Bold 9700 – Work mobile
  3. Apple iPad 2
  4. Amazon Kindle (currently reading SnowCrash by Neil Stephenson)
  5. Acer Aspire 3810TZ laptop
  6. North Face Borealis rucksack
  7. Logitech M510 Wireless mouse
  8. Noice Cancelling earphones and iPod Nano
  9. Chargers, US Adapters…

photoOut of all these items, I’d have to give a special shout out to the iPhone and iPad. They surpassed themselves, by giving me perfect access to the internet through numerous WiFi access points, and also by allowing me to speak to my family through Skype, over these devices.

Also, and this is a surprise to me, I have to mention the Acer Laptop. For a very long time, I’ve always found Acer laptops to be somewhat shoddy. However, this one has carried out a sterling job, with 8+ hours of battery life, and no issues with responsiveness. Having said that, I did improve it’s performance with a Crucial M4 SSD and a memory upgrade (to 8Gb, from 4Gb), just to ensure that it would be bearable running SQL Server on it.

I’ve been impressed with the quality of the WiFi access in the US (I was in Seattle). All the Starbucks I’ve been to had free WiFi, as did the hotel I stayed in (Courtyard Marriott in Downtown Bellevue).

While I could have taken notes on the course on the iPad, or typed them into the Laptop, I prefer to use a Moleskine to take notes. Yes, it may be a little old-school, but if it was good enough for Picasso, Van Gogh and Hemingway, then it’s good enough for me. Smile

So, I’ve been a bit slack on the technical articles front recently, and for that I apologise, work has been a bit hectic with a big project going live (read the case study here). However, now that’s done and things should return to a normal 8-6 day (yes 9-5 is normal, but really? Does anyone actually work that? – interesting article here)

At the moment, I’m continuing to do work on SQL Server and my MCM, since I have only the lab exam to do. I have started doing some work on 2012, on the grounds that I can’t keep pretending it doesn’t exist and given my role, I need to understand current products to give our customers the best advice.

I’m delighted to be off to Seattle in August to attend the second week of the SQLSkills Immersion training, and following that, I’m hoping to get the Lab exam booked for September. It gives me time to prepare myself (primarily to get into the Study frame of mind again). Excited to be going to Seattle too, though it’s a shame I couldn’t tag a week on to the end to go up to Vancouver for some time too, since I loved Vancouver when I was there 10 years(or so) ago, and would’ve loved to see how it’d changed.

Anyway, all being well, there’ll be articles on SQL coming up, as well as possible ones on Hadoop (and integration with SQL), and maybe Unit Testing with SQL Server (tSQLt) which I’ve been investigating at work.

Enjoy and look out for some interesting stuff ahead.

Hello, my name’s Nick, and I’m now an iPhone user.

I didn’t think I’d be saying that at the beginning of the year.

It’s not really a long story, but I though it might be worth telling it anyway (particularly since I’ve not blogged for a while).

I use my phone a lot, though mainly for accessing social media (the Third Place reference…), and taking photos of my family. It’s rarely used for actually making phone calls, like a lot of phones at the moment, I imagine.

Many moons ago, I had a Nokia N95, and that was the phone I had when I started using Twitter. It was a great phone for its time and I loved using it, primarily since it had a really good Twitter app.

Then, at some point later I decided to move to a Blackberry Bold 9700. This was a good phone, and I used it for a good 2 years, and am still using it as a work phone now. During the time I had this Blackberry, I also won a Windows Phone 7 phone in an MSDN competition, and while I liked the phone and the look of the OS, it turns out that I couldn’t actually type on the keyboard. It made me feel like I have fat fingers, so I stayed using the Blackberry.

Then the new Blackberry Bold 9900 came out, which it’s bigger touchscreen, and faster processor, so I upgraded. We never really bonded in the same way as I had with the 9700 and the N95. I think the main reason was that there was no real buzz from using it, I was expecting more. I guess it was actually disappointing, even though spec-wise it was supposed to be better than the 9700.

The final straw came when my parents both moved to Android phones. They were good, better than I was expecting, but that gave me a dilemma. Should I change to an Android, or look at the iPhone?

Having looked at the iPhone, I went for that (iPhone 4S), and I have to say, I’m very, very happy.

Why?

Well, the reasons are pretty straightforward. It’s all down to usability. It doesn’t make me feel like I have fat fingers, it has a good keyboard to type on, though I do need to learn to type better on it ( I keep sending texts saying hometown instead of hometime to my wife, but shes very understanding). Also, I love the integration between the iOS devices. I have an iPad 2 which I’ve had had for a while and love that (and am typing this article on that, rather than the PC).

I am sorry to have left the Blackberry behind, but I feel I am in a better place now.

If you are debating whether to change from a Blackberry to an iPhone, I’m sure you know someone with an iPhone (or a friendly Phone Shop). Go and try it out and I’m reasonably sure you won’t be disappointed.

While working with one of our customers over the past couple of days, and troubleshooting the performance issues on their Data Warehouse I found an interesting set of issue with Page Corruption.

Following on from the outstanding SQL Skills course I attended a few weeks ago (and as part of my ongoing MCM preparation), I thought it’d be an interesting exercise to run through my findings.

Step 1 – Why is everything so Slow?

Initially, I wanted to find out why the performance was so bad. I’ve been using Glenn Alan Barry’s Diagnostic scripts since I’ve found them to be really useful in the past. This one, to ‘Isolate top waits for Server instance’, gave me some really interesting figures. I ran this:

WITH Waits AS
 (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
 ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
 'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
 'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
 'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
 'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH'))
 SELECT W1.wait_type,
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
 FROM Waits AS W1
 INNER JOIN Waits AS W2
 ON W2.rn <= W1.rn
 GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
 HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold

And this was the result:

image

Step 2 – What is BAD_PAGE_PROCESS ?

Having Google’d this, I found a Technet article saying that it was related to Suspect Pages.

BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more
than every five seconds. Excessive suspect pages cause the logger to run frequently.

Step 3 – What are the suspect pages ?

You can see what suspect pages you have (and ideally, there should be none), with this T-SQL:

select * from msdb.dbo.suspect_pages

Sadly, we had more than none.

image

Event type1 is an 823 error caused by an Operating system CRC error.

Event type 2 is a bad checksum.

Step 4 – Ok, Can DBCC CheckDB help ?

Next up, I used DBCC CheckDB to see what the issue was, and whether it could fix them. I used this command:

DBCC CHECKDB (<dbname>) WITH ALL_ERRORMSGS, NO_INFOMSGS

Having waited for quite a while for this to run, I got the following result.

Msg 8946, Level 16, State 12, Line 1
 Table error: Allocation page (1:1002912) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.
 Msg 8921, Level 16, State 1, Line 1
 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
 Msg 8909, Level 16, State 1, Line 1
 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1002912) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
 Msg 8909, Level 16, State 1, Line 1
 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1002912) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
 Msg 8998, Level 16, State 2, Line 1
 Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:1002912) to (1:1010999). See other errors for cause.
 CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
 CHECKDB found 2 allocation errors and 1 consistency errors in database <dbname>.

That’s not so good, since, as Paul Randal says here:

The only repair for a PFS page is to reconstruct it – they can’t simply be deleted as they’re a fixed part of the fabric of the database. PFS pages cannot be rebuilt because there is no infallible way to determine which pages are allocated or not.

Ok, not a good thing, but the customer has a backup, so they are restoring that from tape at the moment. Once that’s accessible, I’ll restore the database, and reprocess the Data warehouse.

Step 5 – What are the errors on the page?

Since this is the first time I’ve seen Page corruption in the wild, and had a vague idea where to look, I thought I’d have a look at the corrupt pages.

To read the contents of a page, you can use DBCC PAGE.

First, here is the last successfully read page:

dbcc traceon(3604)
 go
 dbcc page(<dbname>,1,1002854,0)

This gave me this information (note the highlighted nextPage)

image

So lets look at the next page, using this :

dbcc traceon(3604)
 go
 dbcc page('DataWarehouse',1,1002855,0)
 go

image

This started giving interesting results, since it is now pointing to page 0 as the next page.

Next, I looked at the page referenced in the DBCC error message above.

dbcc traceon(3604)
 go
 dbcc page('DataWarehouse',1,1002912,0)
 go

image

Summary

In summary, we resolved the issues by restoring the database from a backup. It got the database up and running, and resolved the issue.

It was interesting looking through the issue, and seeing where the issue was caused.

Tech that just works: #Devolo

Today, one of the Devolo Powerline adapters that we have at home died (flashing orange power light = dead).

Amazingly, they have a 3 year warranty on them, and they have been running since i got them back in November 2009, so I’ll be claiming on that, though for once, I bear no ill will to a failed gadget.

It made me think. I’ve never (till now) had any problems with them, despite them running 24hrs a day, 365 days a year, and we’ve still got 2 others running.

That is what technology should be like: reliable, and not cause stress.

Thank you Devolo!

This post is something that has been bubbling away for quite a while now, and hopefully it doesn’t come across as a bit of a rant. However, I’ve been doing this for 14 years, so have some knowledge of what I’m talking about, but if you have comments, please share in the Comments.

I work as a Consultant, Consultant Developer, Developer or Systems Analyst depending on who you talk to. It’s all just a title, though Consultant Developer is my preference as it is easier for anyone to understand (and it contains the D word..  ).

One of the things that has become more apparent to me recently is that there is a wide range of skills that Consultants have. Ranging from having minimal experience in anything other than their specific area (which they know well), to having in depth experience in everything related to their area. This wide variance seems to be related to either age (so time in the industry), or passion (so the desire to learn more). Sadly, it seems to be comparatively rare to have someone who has a wealth of Experience, and still retains Passion for it.

So given that you can’t gain experience in the industry without time, and Passion isn’t there for everyone, what skills should all consultants have?

In my opinion, they are the following:

1. Ability to install and Configure Windows

If you can’t install Windows, then you don’t have a full understanding of your primary platform, and here, I’m not just talking about the current desktop version. I think you should be able to install the most recent couple of versions of the desktop (XP and Win7) and server(2003, 2008 and R2) OS’s.

This will then give you a level of understanding with Domain security, Networking and how to set up Services (IIS, Active Directory, etc.). While you may not need to do this on a Production server, you may well need to do it for a development (or Test) environment.

2. Understanding of Databases

It’s rare that an application doesn’t require some form of database (from a full blown enterprise system, down to something with a small local store in SQL CE, for example), so not even having a basic grounding in some form of database is a substantial flaw in my mind.

3. Be aware of PowerShell

PowerShell functionality is available for almost all Microsoft products and is part of the Common Engineering Criteria for all Microsoft Server products, so if the MS products you are using don’t have it now, they will have with the next version.

Newer products allow you to view the PowerShell script generated through the Admin GUI, so you can improve your skills easily.

There is a very useful handbook, called the Windows PowerShell 2.0 Administrators Pocket Consultant (also available in Kindle form), which I’ve found to be really helpful in answering questions.

4. Know how to phrase a query in Google

There are alot of people that I meet, and work with who are unable to find things. When asked, they say they’ve checked on Google, but when I look, it’s there at the top of the first page. So, the ability to phrase a query is key. It makes you self sufficient.

5. Use StackOverflow, SuperUser, ServerFault, etc.

These sites are used by a wide range of Developers and IT Professionals, and if you are able to either search for an answer, or phrase a coherent question, you’ll get a prompt response from these highly useful sites.

Stack Overflow profile for Nick Haslam at Stack Overflow, Q&A for professional and enthusiast programmers Server Fault profile for Nick Haslam at Server Fault, Q&A for system administrators and desktop support professionals
Super User profile for Nick Haslam at Super User, Q&A for computer enthusiasts and power users

Yes, it’s not the most artistic drawing in the world, but I’m pretty impressed with this as a drawing tool!

DoodlePad_2011-01-29_09-37-20-PM