SpurData Blog

Attached are the PowerPoint and scripts from my "T-Log First: How SQL Server storage (should) affect your Disk Architecture & T-SQL Design" presentation. This was first shown at the February 21st, 2012 Tulsa SQL meeting. This walks through the very basics of Write-Ahead Logging in SQL Server, how the Transaction Log file is written to differently from the Data file, and how all that naturally leads to the hardware best practices that you always hear and wonder about as a SQL newbie. There's also a demo script that further proves how you can manipulate transaction log writes for improved performance. After I gave my presentation I discovered that Tim Chapman described this process much more succinctly a few years ago in the "explicit transactions" section of his optimization article: http://www.techrepublic.com/blog/datacenter/optimize-sql-server-queries-with-these-advanced-tuning-techniques/179 Consider this further validation :^) Thanks to all the folks ...

If you're trying to apply SQL 2005 SP4 (or any SQL service pack), and it's failing, and you're seeing error "MSP Error:  29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Invalid object name 'MSreplication_subscriptions'", then you've hit this bug which Microsoft is currently saying they "Won't Fix": https://connect.microsoft.com/SQLServer/feedback/details/521231/failure-during-server-script-upgrade-process-on-database-in-sp-vupgrade-replication-procedure#tabs Probably you've done one of 2 things: You're using synonyms that match the system replication table names You've got some vestige replication tables laying around in one of your old databases My case was the latter. The fix was to find the vestige tables and get rid of them. I found them using the following script: [sourcecode language="sql"] /* This will detect db's containing vestiges of Replication...

Little disciplines to remember when programming T-SQL. Structured programming practices: Find what varies and encapsulate it Keep your procedures small; let each do one thing and do it well Favor Clarity over Cleverness Clever solutions are being scrapped in IT shops all over the world every day and replaced with less "clever" solutions that are understandable and maintainable. Usually Consistent is better than Better. (Sad but true.) Simply formatted header comments are maintained; complex formats are not. Example of a simple header: /* Note on what the proc does. usage: exec usp_MyProc created YYYYMMDD by username updated YYYYMMDD by username - chg# xxxx: description */ In Reporting queries, prefer #Temp Tables over @Table Variables. Table variables are easier to pass around, but the optimizer always assumes there is only one row. For larger temp storage (>100 rows), use #temp tables which produce real statistics. Set vs. Select - prefer Set Assume @x = ...

Some time ago I read a blog that I've lost track of, but it suggested that in the same way that practicing piano scales builds muscle memory and proficiency that then allows you to more easily attain higher level skills, so in other disciplines we should practice our scales in order that our daily work could achieve a higher level with less effort. It's similar to how having memorized your times tables makes algebra more accessible. So for myself I want to put together some SQL Scales, little mantras that help me stay on track as I'm writing SQL or administrating SQL Server.

During the Internet blackout today against SOPA, I was complaining on VoiceOfTheDBA that I wish we could get a blackout on the recent "indefinite detention of US citizens" fiasco. I said: Unfortunately (as you know) we have bigger issues. I’d love to see a blackout for basic liberty:   http://www.washingtonpost.com/opinions/is-the-united-states-still-the-land-of-the-free/2012/01/04/gIQAvcD1wP_story.html   http://www.huffingtonpost.com/2011/11/29/senate-votes-to-let-military-detain-americans-indefinitely_n_1119473.html   http://abcnews.go.com/blogs/politics/2011/12/with-reservations-obama-signs-act-to-allow-detention-of-citizens/ But now I've read the actual final section of the law under question, and couldn't figure out what the big deal is: http://thomas.loc.gov/cgi-bin/cpquery/?&dbname=cp112&sid=cp112MdQpb&refer=&r_n=hr329p1.112&item=&&&sel=TOC_1073854& Upon further research, I see that mostly the NDAA refused to clarify our sta...

I remember one long day Not all that long ago    When versioning schema    Seemed like a dream-a And proved an elusive goal I was only able to bear Without pulling out my hair    Using video tips    On genning out scripts With the incomparable SQL Compare And yet I knew in my soul That my process still had a hole    When to my delight    On RedGate's new site They announced SQL Source Control This was a real game changer I could now be a TFS Ranger    With a folder called "\Code"    And another "\DB" All living in the same, uh, Manger Now I version my T-SQL gaily I commit all my changes daily    What once was all fragile    Is now beyond Agile And life is all smooth saily :^)