Whoop!! Whoop!! Site Down!!!

Just checked tsqltidy.com and it's down!
Grrrrr....

*Amendment:

DOH! It was my fault, some dodgy coding around a twitter feed.

Oh well. Wont do that again (for a few months!).

Finding Non-SARGable queries

I was in Rob Farley's sessions at SQLBits when he was talking about: Understanding SARGability (to make your queries run faster)

It got me thinking....

Is there a way I can find out which SQL queries are doing table or index scans?

So I put some proof of concept code (POC) together.

SELECT top 10 text ,query_plan ,*

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_query_stats.plan_handle)

CROSS APPLY sys.dm_exec_sql_text (sql_handle)

WHERE

CONVERT(varchar(max), query_plan) like '<tablescan%'

OR

CONVERT(varchar(max), query_plan) like '<IndexScan%'

ORDER BY total_elapsed_time desc;

Its not a very sophisticated query (it only took a few minutes to write and yes, I agree, I should shred the xml instead of using a convert and like) but it shows the concept of querying the execution plans to get details of queries that has run (rather than the best way of getting the information).

It takes a while to run so don't go running it in LIVE, and might bring back some false positives - so please be careful with the results.

References:

http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

http://sqlbits.com/Agenda/event7/Understanding_SARGability__to_make_your_queries_run_faster_/default.aspx

Gadget Show Top 5 Budget Mobile Phones

I've been trying to find this list all day and its nowhere on the Gadget Show website.

It's...

1) Samsung Genio Qwerty

2) LG KS365

3) Huawei U7510

4) INQ Mini 3g

5) Alcatel OT808

How to setup a MSSQL Server Management Studio (SSMS) 2008 New Query Template

It took me a small amount of Googling with Bing to find this post on Stack Overflow.

But it works for SQL 2008 r2 - apart from changing the paths slightly.

%Install Dir%:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql

I cant work out why is there nothing on MSDN about this cool feature?
Or why is not part of the SQL templates.


References:
http://sqlsoundings.blogspot.com/2010/03/using-ssms-templates-for-fun-and-profit.html
http://msdn.microsoft.com/en-us/library/ms174169.aspx
http://stackoverflow.com/questions/593047/mssql-server-management-studio-ssms-2005-new-query-template

Microsoft Certified IT Professional: Database Administrator 2008

I’ve now passed both Microsoft certification exams for the MCITP: Database Administrator 2008 :

• Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
• Exam 70-432: TS: Microsoft SQL Server 2008, Installation and Maintenance

As preparation for the 70-432 exam I got the following books:

• MCTS Self-Paced Training Kit (Exam 70-432): Microsoft SQL Server 2008 Implementation And Maintenance Book/CD Package (PRO-Certification)
• The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance
• SQL Server 2008 Administration: Real-World Skills for MCITP Certification and Beyond (Exams 70-432 and 70-450)

I read all the MCTS Self-Paced Training Kit and actually enjoyed it (as much as you can enjoy reading a technical book – but it wasn’t as painful as Gallbladder surgery!) – I have to agree with some of the comments on Amazon that there are a few mistakes, but who hasn’t released code/documentation that didn’t need a few tweaks after it was released?

I have to make a special mention of SQL Server 2008 Administration: Real-World Skills for MCITP Certification and Beyond (Exams 70-432 and 70-450)- it’s the only book I found that covers the 70-450 exam!!!  In the appendix, there is a mapping of chapters to exam objectives so you only need to read the appropriate chapters.

After leaving the test centre having passing 70-432 with 960, I thought this exam felt much easier than the 70-433 exam.

I was (quickly) convinced (by my wife – Hello!) that I should just try and take the next exam straight away.  The odd thing about the Microsoft exams is you only need to get 700 – it doesn’t matter how well you do, you still get the qualification in this case  ‘just enough is good enough’ .

So, just a few days after passing 70-432, I took  70-451 and passed with 929. 

I was surprised by how easy the MCITP: Database Administrator 2008 was compared with the MCITP: Database Developer 2008.  There were (in total) 84 questions for the Database Administrator and 128 for the Database Developer – a big difference.  

It’s not the number of questions, although the more questions, the more sweating you do.  It’s the breadth of the content.  There was so much to learn and remember.  I think Brent Ozar conveyed it really well in his blog post: MCM Prep Week: Microsoft Exams 70-433 and 70-451

There is only the MCITP: Business Intelligence Developer 2008 to go, then I’ll have the whole set! :-)

MCITP: Database Developer 2008

In May I passed 70-433 and in June I passed 70-451 . Which means I can call myself a Microsoft Certified IT Professional (MCITP)!!!



I started in Feb/March working through two books;


MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008 - Database Development Book/DVD Package and The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design


The books cover a huge range of subjects; the MS press book covered more areas and was in some ways better but was also tedious at times - often sending me off for a good nights' kip! The Prep Kit was written in (my opinion) a nicer style for the user, but didn't cover nearly enough of the exam.

I read the Prep-Kit book first, took a practice exam and scored very low! I bought the MS Press book and found this filled-in the gaps and reinforced what I learnt from the first book. I found the different writing styles very useful as it didnt feel like I was reading the same book twice.



I took 70-433 and scored 1000.



There is no official exam prep for the 70-451 exam, so I took it 2 weeks after passing the first exam. The exam was tough, very tough! I thought I'd failed. When I clicked 'finish' I closed my eyes as I really thought I would have to tell everyone at work I'd failed.



But to my amazement, I passed with 865! I spent the next 4 hours in shock. I had a hospital appointment later that day and my resting heart rate was over 100 bpm!



Now for a little rest before I start on the MCITP: Database Administrator 2008!

Website updates...

I'm now working on a few updates for the T-SQL Tidy website while I take a break between Microsoft Certification exams.

There aren't any (huge) increases in functionality. I'll just fixed problems that annoy me. :-)

Fixes;
New menu - I did the old one in a rush so it didn't really work very well.
Updated blog feed - I sorted out the problems with the formatting.
Twitter account - I created a twitter account (Apparently social networking is the next big thing!)

SQLBits (episode) VI - Westminster Strikes Back...

After the hugely successful SQLBits V (in Newport), SQLBits VI has been announced today. It's going to be held at the Church House Conference Centre, London on the 16th April 2010.

Check out the SQLBits.com website for more details.

Format T-SQL as HTML and more...

I've just added some new features to the T-SQL Tidy website.

Format the T-SQL into HTML (for blogs and web sites).

More formatting options than you can shake a stick at.

New Web Service call "ParseSQLwithOptions". http://www.tsqltidy.com/SQLTidy.asmx?op=ParseSQLwithOptions

Special thanks to Jean-Claude Manoli for the HTML formatting code website:http://www.manoli.net/csharpformat/

SQL Source Control

Just released a beta of SQL Version Tracker.

It's a version control utility that is integrated into SQL Server Management Studio 2008 and SSMS 2008 Express.

Simply install the Add-in to Management Studio and enable it on your database.

It will track all the changes on the schema of the SQL Server databases.

Feel free to download and try it!! But most importantly give me feedback!!!

SQL Server Version List update (part 2)

I've now added the option to download the SQL Server Version List.

You can download it in XML or CSV formats.

SQL Server Version List update.

As well as adding a couple of new versions to SQL Server version list to the list, I've added the the ability for people to added any missing versions.

• Simply go to the SQL Server version list, scroll to the bottom of the list and enter the missing version information