SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
I got a nice little surprise in my inbox today: The first reader submitted Captain Varchar comic from Rod Colledge of SQLCrunch.
I have written about this twice. I pushed it here and I toned it down after the facts came in from PDC. TJay Belt also had some commentary on it here today.
The comic does raise another off topic but interesting point. Apple’s desktop market share has been gaining ground. What happens when SaaS makes the browser the OS? Yes, another stretch but that is where some “experts” think we are heading.
Check all the other geek graffiti @ oddee
Guess who the victim of this deadlock is?
On the technical tip, the "end all be all" of deadlock troubleshooting can be found here.
This post was inspired by the SQL Quiz going around that that Chris started, while humorous, can help us learn from each others mistakes.
Over the years, I have gone from a mental project plan to notepad to excel to MS project. My success % has increased and fire fights have decreased for major changes. I recommend it.
A Mental(ly Disturbed) Note
The comic template was adapted from OfficeOFFline.
So Chris Shaw called me on a SQL Quiz. I like it. It is so much less painful to learn from someone else's mistakes than your own. Here are the details.
<snip>I started to think about this and I am going to try to start a game of tag. So here is the way it works, for many new DBA’s they may not realize that all of us have made mistakes and that our mistakes can be… rather stupid. I challenge each of the people I tag in my blog to post as least one mistake that they recently made I will start by describing 2 mistakes that I made. One of my mistakes was as a junior DBA and one about 6 months ago. The point of this game is to in no means embarrass ourselves or discredit ourselves. But more of a learning experience from our mistakes type of a deal.</snip>
<snip>
I started to think about this and I am going to try to start a game of tag. So here is the way it works, for many new DBA’s they may not realize that all of us have made mistakes and that our mistakes can be… rather stupid. I challenge each of the people I tag in my blog to post as least one mistake that they recently made I will start by describing 2 mistakes that I made. One of my mistakes was as a junior DBA and one about 6 months ago. The point of this game is to in no means embarrass ourselves or discredit ourselves. But more of a learning experience from our mistakes type of a deal.
</snip>
Before I start, let take this opportunity to look at what could result from a DBA’s mistake.
No pressure, right?
1.
The first big mistake I made was pretty bad. I almost gave up SQL Server. It was right after SQL Server 7.0 came out. A new customer was trying to move there data to their hosted application. It was a few tables. <Lightbulb> Hey, that sounds like a good use for that new DTS thingymabobber <Lightbulb /> So I go through the DTS wizard and it fails after creating the schema. I run the wizard again and it works EXCEPT I got the source and destination backwards. Now, they have empty schemas on both sides. Since the source was their development environment, they didn’t have backups. Of course.
What I learned: Always check for backups. No matter what you do, have a backup you can trust. That goes beyond disk backups sometimes. Copy them to somewhere on the network if you are working on the storage. Do a SELECT INTO before you do that adhoc DELETE. Srsly, don’t be that guy.
2.
We were having ongoing disk IO issues. Later on that night, I am adding a disk array. However, while poking around I discover that the stripe size of the existing data drive is 8k. It looks like I can easily “migrate” it to 64k. I quote migrate because that is what it says in the HP management software. That doesn’t sound destructive does it? Psst, it is. A few hours later, the database had been restored but we were sector aligned and had a had a 64k stripe size. That doesn’t sound so bad except the application cannot afford a couple hours of down time even for a free 10% of disk performance.
What I learned: Don’t be a cowboy. Up until then, it was on the job training. I learned it as soon as it broke and I had to fix it. It is a good way to learn but a bad way to DBA. All changes should be planned, tested, executed or rolled back. A cowboy DBA may shine 99% of the time but that one time can blow the SLA for the year.
Runner ups:
Who am I calling out? You. If you are reading this, I am throwing down the gauntlet. I shall name names too by picking on the recent commentators.
K. Brian Kelly
Marlon Ribunal
Mladen
Ted Malone
Rhys
Tom
Jimmy May, Aspiring Geek
Michael O'Neill
Ludovico Caldara
Adam Machanic
Aaron Lowe
I was also asked to plug, http://sqlfool.com , by someone other than Michelle so I will call her out too.
Once again, I was struggling for a Varchar(MAX) topic and twitter came through. This comic is based on these two tweets.
Update: Anarchy has erupted on twitter. Brent blogged it here.
BrentO If yo momma was a table, she'd be a heap. #SQLputdownsFri, Oct 31 12:31:24 from mobile web
jeffrush @BrentO If she was a datatype, she'd be a BLOBFri, Oct 31 12:37:41 from OutTwit
Yo Mamma is a SQL Server
This template is based on Office OFFline.
Disclaimer:
Just because you can does not mean you should and even if you should, use with care. For example, you will have no friends after a day or so of updating your facebook status every time a tlog backup runs on your farm. There are also many security concerns to consider. That said, I can think of a few uses for SQL Server Web 2.0 mashups and I am not all that creative.
How it works
Sign up at http://ping.fm and then sign up at whatever services you want to update from SQL. Here is a list of what they currently support. From SQL, we could send a message to twitter or even better, yammer via database mail. Here is the obligatory, “Don’t send anything confidential over email”
Possible Uses
Augmenting your monitoring – The keyword is word is “augmenting” except for the smallest installations. For the crown jewel of the company, you might get a call from the 24/7 staff and an email. Why not a tweet too especially if it can add SMS functionality that you may not currently have?
Turn X into an RSS feed – Where X could be, all job executions, any SEV error messages, any trace event or DDL operations, or [your idea here]. You can funnel the RSS into the intranet, your reader or [insert creative idea here].
An insert trigger on your press release table – Blast it to the world. Some companies are already doing similar things http://dell.com/twitter.
Personal updates from the SQL Editor – I don’t know about you but I have spent weeks on end in tsql. Some deadlines don’t allow for time to come up for air. It would pretty cool in a geeky way to tell the world that I just replaced 500 lines of cursor code with a 10 liner or whatever without leaving SSMS.
Example Code:
sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Database Mail XPs', 1;GORECONFIGUREGOEXECUTE msdb.dbo.sysmail_add_account_sp@account_name = 'Ping.fm demo', @description = 'Mail account', @email_address = 'jason@statisticsio.com', @replyto_address = 'jason@statisticsio.com', @display_name = 'Jason Massie', @mailserver_name = 'mail.statisticsio.com' ;-- Create a Database Mail profileEXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = 'MailAccount', @description = 'Profile used for administrative mail.' ;-- Add the account to the profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp@profile_name = 'MailAccount', @account_name = 'Ping.fm demo', @sequence_number =1 ;-- Grant access to the profile to all users in the msdb databaseEXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'MailAccount', @principal_name = 'public', @is_default = 1 ;
-- here is the magic blasphemy. EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'I just updated twitter, linkedin, facebook, plurk, rejaw from SQL!
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'
In closing
One more time, pay attention to security. There are a few commercial products on the horizon. http://ping.fm and most of these service also have API’s that may be a better alternative than database mail. Finally, database mail was meant for administrative alerts and I suggest you stick with that mindset..