|
|
I’m writing this blog post from my bed, you may think that’s a little too much information there is a good reason for telling you this as it provided the inspiration for this post. I hadn’t been feeling particularly well at the tail end of last week but pushed on (as you do) as there were things that needed to be done. Come the weekend I had to relent and haul myself off to seek medical advice. In the UK local GP surgeries are not open at weekends so I had to go to something called a “walk-in centre” where I was made to wait outside as I had arrived 10 minutes before it was officially open. That’s right in the UK you are expected to be ill at times convenient to the NHS! I should point out that I think the NHS is a wonderful institution and admire the people who work for it, without them the country would be in a sad state of affairs indeed.
However, after seeing the initial triage nurse I waited to see somebody else for further assessment who I explained my symptoms and concerns to and I was told that I had a viral infection and should go home and rest up and take paracetamol. Come Monday things were much worse and I made an appointment at my local surgery, within 5 minutes I was diagnosed with a chest infection and prescribed anti-biotics for the infection. Not only was this the right diagnosis the whole experience was a lot nicer too. My concerns had been listened too, having had a bad chest infection a few years back I knew what the signs were and what my body was telling me yet the first “professional” chose to ignore me, was quite curt and subsequently made the wrong diagnosis.
This post wasn’t designed just to get something off my chest (nice pun eh) it was designed to make you evaluate the service that you provide as an individual to your clients be they internal or external. Who would you rather be perceived as Professional #1 or Professional #2? To succeed in Information Technology (or Information Services if you prefer) it is no longer just down to your technical skills (although this obviously helps), you must also be able to communicate clearly and effectively with every audience that you interact with. I wrote a post on the same topic last year that you may find useful – Bridging the gap – Good communication is key
I urge you all to take stock of who you are, how you represent yourself and outline the steps you need to take to become who you deserve to be. Take that step now and become the right professional.
Blimey, doesn’t seem like a year since I wrote my last end of year review End of year review 2010 and goals for 2011
Things didn’t quite go to plan, mainly because of an unexpected career change this majorly shifted a number of my priorities which meant that some goals were re-prioritised, others were replaced and some simply dropped. With this in mind let’s see how I faired with my Goals for 2011:
Community
Last year I made a commitment to the SQL community by starting my blog and attending a few events, this year I have built and consolidated on that platform and intend to take things on to the next stage next year.
- Write at least two articles for one of the major SQL community websites.
- Attend at least one SQL User Group per quarter on top of SQL Bits.
- Present some SQL Nuggets at a User Group session.
- Monitor and contribute more to #SQLHelp on Twitter.
- Contribute more to the various SQL forums.
Partial success here.
- No published articles, but I did produce 4 SQL Server webcasts for Quest in November so I like to think that this is a tick.
- Tick, I attended 4 UG’s this year. Southampton, Surrey, two in Maidenhead as well as SQL Relay and SQLBits (twice). I kind of cheated a bit here by starting up the Maidenhead User Group. If you’re interested in attending here’s the link for future events and if you fancy your hand at presenting please get in touch.
- Contributing more to various media channels is a subjective task. Whilst I did contribute you always feel that you can do more and I hope to address this in 2012.
One very nice surprise was that I was awarded the Microsoft Community Contributor Award earlier this year so I must have done something right. It’s a great honour to be recognised in this way and I am very thankful to Microsoft for it.
2012 Community Goals
- Publish more content. Not sure on the media for this yet, I’m in talks with marketing to produce more SQL webcasts so watch this space…
- Work permitting I’m planning on hosting 6 User Group meetings this year and hopefully attending some of the others around the UK. Unfortunately I can’t commit to a number as it really does depend on where I am in the country on that particular day!
- In my new role I have the opportunity to speak to number of users across EMEA, it’s really satisfying to help them with issues and pass on best practices to make their lives easier so this goal is to differentiate between community and professional development better and to try and make a positive difference in and expand the SQL community.
Professional Development
I love SQL Server and try to learn something new each day, to help me achieve this I have set myself the following goals.
- Take and pass 70-433 and 70-451 exams (SQL Server 2008 Development track).
Eventually I want to take the MCM exams and these are the pre-requisites that I have yet to take.
- Take and pass a Windows OS or networking exam.
The idea behind this is that it will provide me with more peripheral knowledge which will enhance my DBA troubleshooting skills.
- Blog more, I have a stack of ideas but have not written them up.
This year I intend to become more organised with my blogging and blog at least once or twice a week. The increase in blogging should help with my goal of publishing a couple of articles.
- Overhaul my website, the look and feel hasn’t quite turned out as I had hoped, so a redesign with a few new features is just what the doctor ordered.
- Read 2 technical and 2 professional development books cover to cover.
At the moment I tend just to read the relevant sections of books and am no doubt missing some real gems.
- Make better goals!
One of the books I am interested in reading is “Getting Things Done” by David Allen which was recommended by Brent Ozar Blog|Twitter in his blog article Goals? Where we’re going, we don’t need goals. The idea of having goals at different levels which keep you on track does sound appealing and is something I will be looking into.
Changing jobs really hammered the plans I had for personal development in a number of areas as I found myself studying other areas that I had not expected.
- SQL Exams, I took and passed 70-433 you can read up on the resources I used to pass this exam here. Study time for the 70-451 exam has been limited so this will be a goal for 2012.
- Periphery exam goals were dropped.
- Blog more, I seem to have blogged less than last year. Definitely an area for improvement.
- As you can see my blog hasn’t been overhauled yet, but I have found someone who’s quite good at that kind of thing and is willing to lend a hand.
- Book reading, tick.
2012 Professional Development Goals
- Make time to study for and take the 70-451 exam.
- Make a big dent in the MCM reading list. This means reading it not printing it out, sticking it up and throwing things at it (that wouldn’t be professional at all).
- Learn more about the Windows OS, especially Windows Core and Virtualisation.
- Blog more to reinforce study material as well as overhauling it.
I still have personal goals, but this probably isn’t the right place for them so I shall refrain from posting about that area of my life. This is a SQL blog after all.
Happy New Year,
Rich
It’s that time of the month again, no not time, T-SQL time! If you have not heard of T-SQL Tuesday before it was set up by Adam Machanic (Blog|Twitter) and you can read all about it here: T-SQL Tuesday. This months host is Allen White who asked the question “What T-SQL tricks do you use today to make your job easier?” I’m guessing that people will be going mad with DMV’s so I’m going to go down a slightly different route. I must admit I haven’t used it recently due to changing jobs which has meant a change of duties, but it’s just too good not to share.
At some point in your career you’ll be asked by somebody “so just how big is the database?” At this point you have a couple of choices, you can look at the properties of the database or you could even use the system stored procedure sp_spaceused. The problem I found with this sproc was it returned data in two result sets making storing the data more complicated than it actually needs to be, as you can see from the image below.
I decided to find out if there was an easier way to collate the data for all databases on an instance and indeed there was. The following script uses the internals of sp_spaceused which I have tweaked to return a single result set and is called from within the most documented of undocumented commands sp_msforeachdb the results are written to a temporary table. A good way to use this script is to schedule it within a job to write to a permanent table that can then be used to monitor database growth.
/*
Script written by Richard Douglas
HTTP://SQL.RichardDouglas.co.uk
Script will insert data about the sizes of all databases on the instance into a predetermined table.
This can be used as a snapshot or made into a job to chart the growth of databases over time.
Please note that the DATA_COMPRESSION functionality was introduced in SQL 2008.
If you're running on SQL Server 2005 comment out the use of DATA_COMPRESSION WHEN creating storage table.
*/
USE TempDB
GO
/*
Create a table to store results.
*/
IF NOT EXISTS (SELECT 1
FROM sys.Objects
WHERE Name like '#DatabaseGrowth%'
AND OBJECT_SCHEMA_NAME(object_id) = 'dbo'
)
BEGIN
CREATE TABLE [dbo].[#DatabaseGrowth]
(
[Database_Name] [char](128) NOT NULL
,[Database_Size_MB] DECIMAL(15,2) NOT NULL
,[Unallocated_Space_MB] DECIMAL(15,2) NOT NULL
,[Reserved_MB] DECIMAL(15,2) NOT NULL
,[Data_MB] BIGINT NOT NULL
,[Index_Size_MB] BIGINT NULL
,[Unused_MB] BIGINT NOT NULL
,[DateTimeStamp] DATETIME NOT NULL DEFAULT GETDATE()
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE)
END
TRUNCATE TABLE [dbo].[#DatabaseGrowth]
/*
Insert the data into storage table
*/
INSERT INTO [dbo].[#DatabaseGrowth]
([Database_Name]
,[Database_Size_MB]
,[Unallocated_Space_MB]
,[Reserved_MB]
,[Data_MB]
,[Index_Size_MB]
,[Unused_MB])
EXEC sp_MSforeachdb
'DECLARE
@pages BIGINT -- Working variable for size calc.
,@dbname SYSNAME
,@dbsize BIGINT
,@logsize BIGINT
,@reservedpages BIGINT
,@usedpages BIGINT
,@rowCount BIGINT
SET NOCOUNT ON
SELECT
@dbsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 = 0 THEN Size ELSE 0 END))
,@logsize = SUM(CONVERT(BIGINT,CASE WHEN STATUS & 64 <> 0 THEN Size ELSE 0 END))
FROM [?].dbo.sysfiles
SELECT
@reservedpages = SUM(a.total_pages)
,@usedpages = SUM(a.used_pages)
,@pages = SUM(
CASE
/* XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" */
WHEN it.internal_type IN (202,204,211,212,213,214,215,216) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END
)
FROM [?].sys.partitions p
JOIN [?].sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN [?].sys.internal_tables it on p.object_id = it.object_id
/* unallocated space could not be negative */
SELECT
database_name = ''?''
,database_size = (@dbsize + @logsize) * 8192 / 1048576
,''unallocated space'' = LTRIM(STR((CASE WHEN @dbsize >= @reservedpages THEN (CONVERT (dec (15,2),@dbsize) - CONVERT (DEC (15,2),@reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2))
,reserved = LTRIM(STR((@reservedpages * 8192 / 1024.)/1024,15,0))
,data = LTRIM(STR((@pages * 8192 / 1024.)/1024,15,0))
,index_size = LTRIM(STR(((@usedpages - @pages) * 8192 / 1024.)/1024,15,0))
,unused = LTRIM(STR(((@reservedpages - @usedpages) * 8192 / 1024.)/1024,15,0))'
/*Show data*/
SELECT
Database_Name
,Database_Size_MB
,Unallocated_Space_MB
,Reserved_MB
,Data_MB
,Index_Size_MB
,Unused_MB
,DateTimeStamp
FROM [dbo].[#DatabaseGrowth]
Thanks for hosting Allen.
Sometimes it’s not possible to have the data you need in order to create the most efficient queries possible. This may be because you don’t have a machine with enough storage capacity, it might even be on a laptop so there is also the security aspect. So how do you address the issue of writing queries with no data to work with?
Luckily there are a couple of ways that this problem can be overcome.
- Use a data generator.
This sounds great in theory, however the data you will then be querying against will bare no resemblance to the data you will be querying against in production (hopefully a dev/uat/pre-production server first!). The reason for this is that the statistics will be completely different, the same problem occurs when you use obfuscated data. If only there were a way you could query against the statistics themselves, oh wait, there is!
- Export the production database schema and it’s statistics.
You may not have access to do this in your live environment, so ask your friendly DBA to do it for you. If you don’t have any third party analysis tools it might be a good idea to do this once a month and keep copies so you can see how queries change over time.
In this example I will export the schema for all database objects of the AdventureWorks database with the exception of extended properties and foreign keys. Foreign keys may cause you an issue here so it might be best to create them as a separate step at the end. I do recommend that you do add the foreign keys in if you are going to be using this methodology as constraints will be checked during insert and update statements as well as giving the optimizer extra options in certain select scenarios.
Open up SSMS and right click on the database you wish to export the statistics for, select “Tasks” and then “Generate Scripts…”:
Depending on your setting this may launch a wizard with the following welcome page. If this is displayed simply click Next:
In this example I am choosing to export data from the AdventureWorks database and have checked the option to script all objects:
The key part of the whole script is changing the “Script statistics” option to be “Script Statistics and histograms” as you can see this produces a warning, but in a database the size of AdventureWorks it really took no extra time at all.
The next two screens are pretty self explanatory, select your destination be it a new query or a file. Then review the selections you have made thus far.
In my example I chose to create a new database called AdventureWorks_Nodata and ran the script in the context of this new database to create a clone on AdventureWorks with no data but up to date statistics.
Once this is done your server will think that each table actually contains data, if you run the Disk Usage Top Tables Report on your new database you will see that SQL Server believes there are records and these records take up physical space:
Brilliant, we’re now in a position to start writing and running some queries! Well not quite, remember we didn’t actually export any data other than the metadata. This means that to do any testing using this database you will need to be looking at the estimated execution plans. Estimated execution plans do have their own issues of course, but that’s a blog post for another time.
Let’s now write a simple query and view the estimated plan it creates form both our AdventureWorks and new database, as you can see I have taken the liberty of pasting the properties together into one screenshot for you so you can see in one image that the statistics are indeed the same:
As you can see the estimated query plan is identical, and so it should because the statistics are identical!
If we look at the actual query plan for the same query we can see that the estimated number of records is the same, but unsurprisingly the number of rows returned is not. Now we know what the statistics are we could use various performance tuning tricks to tune this query – all without the need for data.
Unless you’ve worked in a vacuum the likelyhood is that you either are a third company or you have many third companies with you, possibly both. Some are really really good and you can learn a lot from talking to them and working things out together, others have staff who should never have been allowed to go near a keyboard period or speak to a customer. I.T. can be extremely stressful and bizarrely it’s these people that can make your week. With this in mind I’d like to nominate my favourite 5 third party quotes of my career so far in reverse order to brighten up your day:
- Email from third party: We’re delighted to inform you that we received your server yesterday, our software has been installed and configured on the two drives which have been configured as RAID 10 which means mirrored and striped.
My reply: That’s really impressive, would you mind telling me how you managed to mirror AND stripe on only two physical disks?!?!
I never received a response.
- Third Party: We’re concerned that you are not backing up your databases.
Me: I am backing them up.
Third Party: You’re not.
Me: I’ll double check 99.9% sure they are though, not had any failure notification come through. Yup just checked MSDB everything looks fine.
Third party: I tell you you’re not! I dialled in yesterday upon X’s request on ticket Y and saw you had no maintenance plans!!!
Me: Can I just put you on hold for a few seconds please…. Guy’s you’ll never believe this, this guy thinks you can only back up databases via maintenance plans….
- Me: I’m not convinced this design is scalable, you have lots of redundant data here.
Third Party: Normalization went out with the dinosaurs.
- Me: I’m just reading this maintenance schedule, you say we have to abide by to keep your application optimal. I’m all for proactive maintenance but this doesn’t seem right can I skip some of it.
Third Party: No, that would invalidate your support agreement with us.
Me: But you want me to autoshrink all your databases EVERY night.
Third Party: Yup, keeping them small makes them more optimal.
Me: That’s not true, all you’ll do is fragment all the data. Have you not read Paul Randal’s blog?
Third Party: Never heard of him, does he work for your company?
Me: I wish.
- Me: X, I’ve looked at this database and you have 250 tables all with triggers on and they all have the same logical flaw.
Third Party: It’s not our fault, SQL Server comes with triggers built in!
I have paraphrased in places to protect the guilty, but there are many many more where these came from which I might store up for another post at another time if it proves popular. Hope you enjoyed reading the conversations as much as I did having them!
|
|
