|
|
If you have been to any of the SQLBits events many of you will have no doubt made a conscious effort to go to the sessions by Ramesh Meyyappan. Ramesh is absolutely awesome at performance tuning, you can see some of his previous presentations at the SQLBits site here, but did you know that he has also posted some fantastic free webcasts at http://www.sqlworkshops.com/webcast?
I watched most of these a few months ago and wow, this is some serious stuff. I really fancied attending one of the courses as it truly is a level 400 course, take a look at the recommended reading list:
My Book recommendation for developers:
I recommend reading Microsoft SQL Server 2008 series from Itzik Ben-Gan. He has contributed greatly to the SQL Server
community by writing these technical books.
· Microsoft SQL Server 2008 T-SQL Fundamentals from Microsoft Press
· Inside Microsoft SQL Server 2008: T-SQL Querying from Microsoft Press
· Inside Microsoft SQL Server 2008: T-SQL Programming from Microsoft Press
Try to read the SQL Server 2008 books, even if you are not using SQL Server 2008 yet for 2 reasons, SQL Server 2005
features are a subset SQL Server 2008, eventually you will use SQL Server 2008.
· Inside Microsoft SQL Server 2005: T-SQL Querying from Microsoft Press
· Inside Microsoft SQL Server 2005: T-SQL Programming from Microsoft Press
My book recommendation for administrators and developers:
I recommend developers to read above T-SQL books before they venture in to the below books. As a developer you have
some responsibility to understand SQL Server architecture.
· Microsoft SQL Server 2008 Internals from Microsoft Press
· SQL Server 2005 Practical Troubleshooting: The Database Engine by Ken Henderson
· Inside Microsoft SQL Server 2005: Query Tuning and Optimization from Microsoft Press
· Inside Microsoft SQL Server(TM) 2005: The Storage Engine from Microsoft Press
· The Guru’s Guide to SQL Server Architecture and Internals by Ken Henderson
I have not read recent books, so I have no suggestion on them, may be you can share some of your opinions. I remember
reading the ‘The Guru’s Guide to SQL Server Architecture and Internals by Ken Henderson’ long long time ago, read
chapter 10 again, I don’t remember the rest of the book much anymore, but chapter 10 is a must read.
Like I mentioned all books, like software having bugs, have mistakes, larger, smaller, cosmetic, technical, try to practice
what you learn so you don’t learn something wrong. Keep away from undocumented stuff.
Read this blog from top to bottom, read twice if necessary: http://blogs.msdn.com/craigfr, Craig Freedman blog is a
chapter in ‘Inside Microsoft SQL Server 2005: Query Tuning and Optimization’, but I like the blog better, not just because
it is free, it is alive, well was not for nearly 6 months, but is back. You know what I think, you need to keep sending Craig
questions and comments, If not the number of blog entries will decrease over time.
The following KB articles/patches for SQL Server have been released over the last few days:
SQL Sever 2005
SQL Server 2008
SQL Server 2008 R2
In a previous post “Moving TempDB” I provided step by step instructions on how you can move your TempDB database with an emphasis on reducing disk IO contention. If you have read this post you will notice that I did not change the path for the TempDB database files from the USB stick back to their default location, the reason for this is that I want to show you how you can recover SQL Server when TempDB is not available.
After starting up my machine this morning the instance tried to start up and not surprisingly failed, I have added a subsection of the SQL Log which provides the clues as to why the instance did not start:
2010-08-27 08:45:08.89 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2010-08-27 08:45:08.97 spid10s Error: 5123, Severity: 16, State: 1.
2010-08-27 08:45:08.97 spid10s CREATE FILE encountered operating system error 3(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘J:\SQLTest\tempdb.mdf’.
2010-08-27 08:45:09.15 spid10s Error: 17204, Severity: 16, State: 1.
2010-08-27 08:45:09.15 spid10s FCB::Open failed: Could not open file J:\SQLTest\tempdb.mdf for file number 1. OS error: 3(failed to retrieve text for this error. Reason: 15105).
2010-08-27 08:45:09.17 spid10s Error: 5120, Severity: 16, State: 101.
2010-08-27 08:45:09.17 spid10s Unable to open the physical file “J:\SQLTest\tempdb.mdf”. Operating system error 3: “3(failed to retrieve text for this error. Reason: 15105)”.
After looking at the data in the log file you can clearly see the error is that it can not find the TempDB data file. In my case this was because I did not have the USB stick plugged in, but let’s use our imagination and pretend that this drive is actually a single physical disk that has failed to come back online after some kind of maintenance.
The first step is pretty obvious we need to start the instance up, however in reality this could be quite tricky as one of the things a SQL instance does when it starts up is perform a recovery of each database – including TempDB. Luckily the trace flag 3608 allows you to bypass this for every database baring the master database. It is not recommended to use this as a standard startup parameter, every time you start your instance you will want to ensure that each database is transactionally consistent. You will also need to add the /f parameter this will allow you to start up the instance in minimal configuration mode.
To start the instance again you should open up the command prompt (running as an administrator) and run the NET START command. In my environment the service I wanted to start was MSSQL$MAPS. The full command needed to run from the command prompt is shown below:
NET START MSSQL$MAPS /f /T3608
You should then see that the instance has been started successfully. Similar to the image below:
At this point we break out the old trusty SQLCMD utility which you can call from the same command prompt session. The command you will need is:
SQLCMD –S Machine\Instance
At this point you are connected to the instance and can run the ALTER DATABASE statement as we did in “Moving TempDB”, I’ve included below the code I used to bring my TempDB database back to it’s default settings.
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAPS\MSSQL\DATA\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MAPS\MSSQL\DATA\tempdb.ldf');
To execute a batch of commands in sqlcmd you must use the GO command. At this point you will receive confirmation that the command has completed successfully and you will be informed that the new path will be used the next time the database is started. Exit out of the SQLCMD session and restart the SQL instance.
Once you have restarted the service you will be able to log in to the instance using Management Studio, users will fall at your feet, managers will give you promotions, pay rises and anything that your heart desires. Either that or someone will walk up to you and say “why did that take so long?”
It’s been a busy week again this week in both my professional and personal life, so not much time for reading community content. However I have noticed an outstandingly funny hash tag on Twitter #inappropriatePASSSessions, possibly my favourite so far is from Bill Fellows (Blog|Twitter)
I would like to point out that no developers or managers were harmed during the composition of this blog post.
If you fancy starting your own blog Simon Sabin (Blog|Twitter) has written an entry on the tools he uses when blogging:
Blogging – how do you do it?
Thanks to Simon I now know about the Windows 7 Snipping tool (which I used to take the tweet from TweetDeck above).
Once you have been blogging for a while, you might fancy trying your hand at presenting, Microsoft’s Andrew Fryer (Blog|Twitter) has written a blog entry on how Windows 7 can help make things easier:
Windows 7 the Presenter’s friend
Aaron Bertrand (Blog|Twitter) has written a great blog entry on his findings of Red Gate’s new Storage Compress 5.0 product:
First Look : Red Gate SQL Storage Compress 5.0
On SQLServerCentral.com you can find an interview with this years winner of Red Gate’s Exceptional DBA winner Tracy Hamlin (Twitter):
Tracy Hamlin, Exceptional DBA of 2010, on what it means to go the extra mile
SQLServerPedia syndicated blogger Michael J Swart (Blog|Twitter) has written about his SQL peeves in the post: Ten Things I Hate to See in T-SQL
One of the planned projects in my schedule for the coming financial year is to add some new disks in to one of our SQL servers. As part of this project I will be moving the TempDB database onto a different disk thereby helping to reduce the IO contention on the server. If you want to read more about the benefits of moving TempDB and other best practices then I suggest that you read chapter 7 (Knowing TempDB) in Professional SQL Server 2008 Internals and Troubleshooting, you can read my review about it here.
You may think that you need a server in order to be able to test this, but you can do this on a budget with a bog standard desktop or laptop (as long as it meets SQL Server Express’s minimum requirements) with one physical drive and a USB stick.
First of all insert the USB stick, ensure it’s mapped as a drive and connect to the instance on which you wish to move the TempDB database then run the following code:
USE master;
GO
SELECT Name, Type_Desc, File_ID, Physical_Name, Size, Growth, Is_Percent_Growth
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
This code provides the current location of the files (make sure you note this down if you are running this as a test as you will want to change it back to the original configuration) as well as the size and growth settings. The reason I am adding the extra size information in the resultset is that we are going to need to restart the instance and when the instance is restarted TempDB will be pre sized to the values shown above. You really need to check to see if these values reflect the current size of the database. You can do this one of two ways.
- Run the stored procedure sp_SpaceUsed whilst connected to TempDB.
- Run the Disk Usage report for TempDB in SSMS. To do this you need to right click on TempDB, choose “Reports” then “Standard Reports” and then “Disk Usage”.
I recommend using option 2 if you do not regularly check the size of your databases as the report has a feature allowing you to see recent file growth.
If the pre determined size is smaller than the current size then you really do need to pre size it accordingly. Again this can be done two ways, via the GUI or by the “ALTER TABLE” T-SQL command. Of course you may have the other issue where TempDB is pre sized and it will not fit onto the USB Stick, in this instance you will need to shrink the database files (never thought I would be saying that on a blog post!).
Now we have checked the size of TempDB it is time to move the TempDB database files onto the USB stick. In my case the USB stick has been mapped to drive “J” and I have created a directory SQLTest to hold the files. To move the files you need to use the “ALTER DATABASE” command with the “MODIFY FILE” clause:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'J:\SQLTest\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'J:\SQLTest\templog.ldf');
GO
Depending on the number of files you have in TempDB you will receive a message similar to the following:
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.
I found that when I ran the first query again on my local MAPS instance (SQL Server 2008 R2 Express), it showed the TempDB database files in the new location with the correct sized files. However Books Online says that this will not happen until the service is restarted. I would still recommend restarting the service just to make sure that everything is OK.
After the service restart you should run the first query to make sure that everything is OK, you will then be safe to delete the old database files to free up space.
On Sunday night I was implementing a significant change release on our main production environment and thought after after implementing numerous implementation and migration projects over the years that a blog on the mechanics of change control could be a great help to people in their jobs, be it Developer, DBA or Project Manager. I hope there’s something in here for everyone.
First off I should apologise as this really should have been posted a few weeks ago when it was a little more topical, but as I mentioned in my last post Weekly SQL Blog round up WC 20100815 unfortunately I’ve been off ill for a little while and am still catching up with a backlog of things in both my professional and family life.
I keep banging on about how great the SQL community is and I consider myself very lucky to work with a product where people are so keen to share their knowledge and experience with others. In fact this is the main reason that I blog – to help give something back.
Most if not all SQL professionals will have heard of SQLServerCentral.com, if you haven’t it’s a great community site with it’s own SQL Server forum where you can ask questions and receive answers from your peers. The site has a contribution center feature where registered members can submit articles, scripts and even a “Question of the day”. In true community spirit I shared one of the scripts that I had been working on. It was a great feeling when I received an email from SSC saying that my script would be published on their site. It was even listed in the daily digest email:
The decision to publish a script on SSC rather than my own site was based on the premise that it would gain more visibility and be able to help more people than if I were to list it here on my own blog. At the time of writing this it has received over 1,200 hits in under 3 weeks which has proved that I made the right decision.
The script itself can be found here. What the script does is to expand the functionality of sp_spaceused when you are trying to find the size of a database. If you have used sp_spaceused before you will know that it returns two result sets. My script combines these two resultsets into a single resultset but also returns the information for each database in the instance rather than just the database specified. This can really help you to monitor the size of your database over time.
I hope that you find the script useful, and just maybe I have even convinced you to share a script of your own with the community.
In my last post I promised that I would write a weekly roundup of SQL blog entries that I have found interesting, due to illness I’ve not been able to keep that promise. I’ve been back in the office this week with lots to catch up on, as you can imagine checking out new blog entries was not my top priority.
Of the few posts I have been able to check out in the evenings there are a couple of posts that I just have to share:
Paul White has continued his rich vein of form with another great blog entry:
Viewing Another Session’s Temporary Table
Christian Bolton announced on his blog that Coeo will be giving away a Fusion IO SSD drive at SQLBits:
Coeo are giving away a Fusion-IO card at SQLBits
Talking about SQLBits and more importantly discount codes for attending, founding member Simon Sabin has said on his blog that if you contact him he will provide you with a discount code:
Taking control of your career with SQL Server
Last but not least Buck Woody has shared a great quote which ties in nicely with the anniversary celebrations going on at the moment: Quote of the Day: The Role of Persistence
There are lots and lots of people blogging quality SQL content these days, rather than clogging up my blog and Twitter account with pingbacks and retweets I thought I would start up a weekly post on the blog entries and possibly tweets that I have found interesting over the week. Due to the sheer volume of SQL content on the web it may be that some of the blog postings are quite old.
Brad McGehee has had a couple of good blog entries this week:
Instant File Initialization Speeds SQL Server - This post is all about the benefits of having Instant File initialization set on your servers. If you’re not aware what it is then it allocates the space to the file without zeroing out the space on the disk (which may cause a security risk on shared servers). Quite a few people know about this, but it’s worth reading the comment by Jose A. Hernandez who says you can allocate the policy permission to a windows group to avoid having to add the permission to a user should the service account change – great tip!
Windows OS Power Saving Feature Directly Affects CPU Performance – As you may have gathered by the title this post is about Windows power setting and what direct affect it has on the CPU. After reading this I went straight to my inherited servers to check their settings. You may want to consider adding this as a step in your server setup if you do not already have it.
Paul White – A SQL Server technical blog from the K?piti Coast, New Zealand:
The “Segment Top” Query Optimisation – Paul’s maiden post at his new blog home on SQLBlog.com
Simon Sabin – SimonS SQL Blog
Want to support SQL Server ? - SQL MVP Simon Sabin provides a link to career opportunities at Microsoft to work with SQL Server.
If you’re in the U.K. and work with SQL Server chances are you have heard for this and may have even registered, for those of you who aren’t subscribed to the #SQLBits twitter tag, here’s a brief overview of what SQLBits is and why you should attend.
SQLBits is a not for profit organisation created by members of the SQL community for the SQL community. The first ever SQLBits was held back in October 2007, since then a further 5 have been held in various venues around the country. The idea behind this is to try and involve as many people as geographically possible by simple moving it to people who would not otherwise attend events in other UK towns and cities.
To give an idea of the frequency and locations of the events I’ve listed below all the previous incarnations of SQLBits:
- October 2007 – Reading.
- The SQL – March 2008 – Birmingham.
- Cubed – September 2008 – Hatfield.
- Goes Fourth – March 2009 – Manchester – This was my first SQLBits experience.
- Goes West – November 2009 – Newport – You can read my review of Goes West here.
- The 6th Sets – April 2010 – London – I had breakfast with Christian Bolton and Connor Cunningham at this one!
The current incumbent promises to be the biggest event ever and is considered by many to be the premier SQL Server event in Europe with many Europeans and even Americans flying in to attend.
Why would so many people want to attend from so far away? The reason is this, the sessions that are available have actually been voted for by the people who want to attend from an open session submission process. This means that you could submit a session to SQLBits and actually end up presenting and almost certainly guarantees a diverse and high quality programme.
But I’m not a DBA, would I really benefit from going to this?
Yes, there are normally three or four different tracks available. At the last SQLBits the tracks were:
- Business Intelligence
- DBA
- Dev
- SQL2008
So there’s enough diversity to cater for everyone!
OK, I’m sold. How do I go about attending? Is it going to cost much?
The event itself is going to be held over 3 days from September 30th to October 2nd. If you wish to attend the first two days then there will be a cost, but the Saturday event is totally free – they’ll even provide refreshments:
| All prices are exclusive of VAT |
| |
Cut-Off
Date
|
Pre-Conference
Thursday
|
7 Wonders of SQL
Friday |
Full Conference
Thursday & Friday |
Community Day
Saturday |
| Early Bird |
3 Sep 2010 |
£250.00 |
£125.00 |
£350.00 |
FREE |
| Standard |
24 Sep 2010 |
£300.00 |
£175.00 |
£450.00 |
| Last Minute |
|
£350.00 |
£225.00 |
£550.00 |
Discounts available from local usergroups. Contact your usergroup organiser for
details.
Extra Discount – 20% off when 6 or more people book for the whole conference.
For more information on travel and accommodation for the conference click here. One quick tip if you’re going by train and have a change to make look at buying a ticket for each change rather than one to the final destination, sometimes it works out cheaper and you’ll end up on the same train anyway! My wife has saved up to £30 by using this method in the past.
All this is only possible sue to the dedication of the founders of SQLBits and the continued support of the sponsors, I urge you to take a look at the sponsor stands if you go as not only do they sell some cool stuff that will make your jobs easier, but there are also competitions to win things like Xbox’s!
Finally, to register for SQLBits 7 click here
P.S. If anyone can tell me what the “7 wonders of SQL” are I’d love to know!
|
|
