|
|
|
|
-
You might encounter a situation, under a very heavy workload, where processing for certain transactions starts out very efficiently, but over time becomes much less efficient.
A good place to start troubleshooting is, of course, PerfMon. But PerfMon can fail to reveal the culprit, since the CPU can remain stable and moderate while still experiencing this problem. It’s time to pull out a more granular troubleshooting tool, the SQL Server 2005 DMV wait states. Of course, you won’t encounter this exact problem every time, but sometimes you might find that you have ever increasing SOS_SCHEDULER_YIELD waits occurring. BOL provides this illuminating description of the wait:
"Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed."
Now, I can count the number of times after a hard day’s work that I want to get my quantum renewed. You? Anyway, the description doesn’t explain much.
That’s why you need to learn more about %signal waits in this blog post:
http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx
Thanks to the SQL CAT team for figuring out this problem and thanks to Bill Graziano and Linchi Shea for pointing out the issue.
|
-
For some reason, the title of this blog post makes me grin because it evokes a mental image of one guy trying to comically strangle another guy. But I digress…
Speaking at a user group meeting recently, an attendee asked whether the mirroring process had any capability to speed up or slow down its workload according to the amount of work waiting on it. I cast around for an answer to this and my friend, Kalen Delaney, already had the answer. She already has an answer for most any question on SQL Server, but again I digress. She got her answer from Peter Byrne, a Microsoft program manager and member of the SQL Server Storage Engine team.
Peter says…”There is a throttling heuristic used by mirroring to try to keep the backlog from getting too large on the mirror. Essentially, at commit time SQL Server may pause briefly if it determines the REDO queue on the mirror is getting large enough that failover time would be significantly affected.”
You can find more details about this process in the white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
|
-
If you’ve ever taken the time to read http://www.tpc.org/results/FDR/TPCC/hp_orca1tb_win64_fdr.pdf, you might find a bit in there that’s confusing.
"Microsoft SQL Server 2005 Enterprise Edition 64-bit Service Pack 1 was configured to utilize "soft NUMA", a feature that allows network connections to be affined to specific groups of CPUs (this is independent of the hardware NUMA features of the HP Integrity Superdome). SQL Server was configured with 33 SoftNuma nodes. 31 of these nodes were configured with 2 CPUs each. The 32nd SoftNuma node as configured with one CPU and used for the checkpoint process. The 33rd SoftNuma node, also with one processor, was reserved for the Log Writer thread. A script doing continuous checkpoints of 1700 seconds (28 minutes, 20 seconds) was started on one of the server after steady state was reached with a connection port that connected to the 33rd Numa Node. This allowed the main checkpoint process to run on that single processor, which directed the processing of the checkpoint tasks that SQL assigned to each of the other 31 SoftNuma Nodes."
What’s confusing about this is it’s reference to the checkpoint process, since the BOL also says that you can configure I/O affinity and CPU but not specific threads.
As it turns out, this issue is explained in the BOL article “How to” Map TCP/IP Ports to NUMA Nodes”. The “connection process” mentioned above is not the more commonly know background checkpoint that flushes dirty pages to disk, but is a separate connection affinitized (is that even a word?) to the 32node which issues a CHECKPOINT command.
Thanks to Eladio Rincon (Solid Quality Mentors) and Peter Byrne (Microsoft) for working this one out.
|
-
One of the best things about becoming a Microsoft MVP is meeting other MVPs. I bring this up because last week was the annual MVP Summit in Seattle, WA. I was really looking forward to meeting Steve Kass. Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions. A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting. A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.
Steve notes that you could use the following for hashing something small in size:
CAST(
SUBSTRING(
HASHBYTES('SHA1',
CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint)
This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence. However, it’s a good start.
Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.
|
-
You'd think an article called "NASTY RUMORS ABOUT MAXDOP" would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it'd be total fiction (at least, it would be coming from my pen).
So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading. You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number. I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself. But I’m curious if any readers out there have definitive information one way or the other.
Evidently, there are two roles involved in parallel processing, a writer and a consumer. Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.
Hope this helps,
-Kevin
|
-
There's a lot of talk at the moment that Amazon is forcing self-publishers to use its own publishing centers in order to stay listed with the powerhouse Internet sales site. For example, this blog post Has Amazon Gone Mad by Rick Jelliffe rather aptly describes the situation. The Writer's Weekly first broke the story here, but it's now been taken up by major media such as the Wall Street Journal. Blogger M. David Peterson points out that there are alternatives - simply print enough copies ahead of time and ship them to the Amazon printing centers.
This certainly isn't the death knell of publishing. But it is a telling sign. When I first started writing books, an author could reasonably expect to sell the first printing of their book, probably about 5000 books. This was usually enough sales to cover the advance that the publisher paid them and maybe a bit extra to cover a celebratory round of beers with his/her buddies. If the book was good and the the stars were in proper alignment, the author would be lucky to get addition printings of their book out the door and actually make a little money on the project. Nowadays, it seems like the first printing of a new title is only around 3000 and fewer books seem to be ascending to the level of "strong seller".
Of course, there is always a place for titles like Word 2007 for Dummies. Those sort of everygreen titles will always sell. But it appears that the more niche your content is, the worse it will do as a book. The obvious reason for this is the Internet. Why would someone spend $40 for a book on SQL Server query tuning (as an example, I'm not knocking any specific book), when you could simply subscribe to the RSS feeds at sites like SQLblog, SQLMag, or SQL-Server-Performance and get nearly as much content? For that matter, the power of a good Google search (and not Windows Live Search, imo) enables you to pull valuable content from all three of these sites in short order. Not only is your search more taylored to your specific needs, it's also more likely to be up-to-date with the latest versions, service packs, and nuances.
Authors like me are also concerned because the immediate alternative that comes to mind is writing and publishing an e-book. However, e-books seem to hold even less promise no than in years past. At least when you buy a book, you have a physical object that you own. You can take it on a plane and read it during that "turn off all electronic devices" stage of the flight. You can take it to the bathroom. You can share it with your team mates at work (hopefully, not right after taking it to the bathroom). E-books, though, have all the limitations of a book with none of the advantages of the internet.
I believe that as the hard-print media (book publishers, magazine publishers, newsletters, journals, and newspapers) continue to see shrinking audiences, the key to survival is tapping in to blogging and expert opinion. For example, the New York Times is doing extremely well with their Freakonomics blog, based upon the eponymous book (and a personal favorite of mine).
|
-
If you're well-versed in using PerfMon, I'd like to hear about the counters that you use regularly and what constitutes a good or bad value.
However, it's a rare individual who knows their way around PerfMon and its multitude of performance objects and counters. Which ones should you track? And even if you know the right ones to track, what values indicate good or bad performance?
If you've ever struggled with knowing which PerfMon counters to track for SQL Server performance, take a look at my TechTarget series of screencasts at SearchSQLServer.com. They'll give you most everything you need to know about using PerfMon and assessing PerfMon counters to learn about the behavior of SQL Server. It's a four-part series with the first two sessions, PerfMon Counters for Tracking Memory and PerfMon Counters for the Windows OS, already posted. These screencasts not only tell you which counters to track and why, but also what are the general rules of thumb for good performance when using these counters.
Enjoy!
-Kevin
P.S. I cover a variety of PerfMon counters in these screencasts. Many of them are discussed in an excellent document put together by Microsoft PSS. Read their blog at http://blogs.msdn.com/psssql/default.aspx.
|
-
A while back, Quest Software started a series of webcasts called "The Pain of the Week". These webcasts are about a specific area of difficulty for SQL Server DBAs and developers, and how specific Quest tools can help.
In the next webcast, I and my colleague from Quest, Bryan Oliver, will present a methodology and show you how to code faster for faster code using Toad for SQL Server.
As a SQL Server DBA, you’re the go-to for writing SQL and T-SQL and to do this effectively requires a special set of skills. Some of the challenges with this is that not only do you have to use multiple native tools, you have to search all over for scripts and stored proc templates, and you have to be an expert in SQL tuning – just to name a few.
Toad for SQL Server is uniquely designed to make work easier and more efficient for anyone coding Transact-SQL scripts and routines. From its top-rated debugger and built-in Intellisense, to its powerful tools for comparing and synchronizing multiple instances of SQL Server, Toad delivers improved performance that will save database developers hundreds of hours per year.
All of the details for the event, including registration, are here:
If you're interested in coding faster in SQL and Transact-SQL and are open to trying new tools, then please join me for this presentation on April 3rd, Thursday, at 10:00 am central time.
Thanks,
-Kevin
|
-
If you've read SQL Server Magazine, then you know that I write a monthly column about free SQL Server tools called Tool Time. I recently wrote about a set of automation scripts from Microsoft's internal MIS group that automates all sorts of regular tasks they perform on their SQL Server's.
Today, I'd like to share some more scripts brought to my attention by Ola Hallengren. Ola wrote some great scripts for backup, integrity check and index optimization that add to what Microsoft had written. I think that it in some areas has some advantages over Microsoft's solution.
Ola's index optimization stored procedure rebuilds indexes online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and lob existence. This solution was made about 6 months ago and it has been running in a large production environment since then. You can see them at:
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola has put a lot of time in this and would really appreciate your feedback. You can see more of his work at http://ola.hallengren.com.
I'd love to hear about any other database automation scripts you may have written. Be sure to let me know so I can share them with the community.
Enjoy!
-Kevin
|
-
Spend a Day with the Experts... Unplugged
Featuring SQL Server MVPs Kevin Kline and Hilary Cotter, and SQL Server Expert Jason Hall (Free Event)
Location: Tampa, FL - Westshore Marriott
Date: Wednesday, March 19, 2008
Time: 9 a.m. - 5 p.m.
*Complimentary lunch and refreshments included.
Join SQL Server MVPs Kevin Kline and Hilary Cotter, and SQL Server expert Jason Hall, for a complimentary full-day seminar. They will discuss methodologies to maximize performance and productivity in your current environment and give their perspectives on what to expect in SQL Server 2008.
Session Topics:
-
Got SQL Server Performance Headaches?
Get Permanent Relief: Detect, Diagnose and Resolve the Pain
Kevin Kline will provide insight, techniques and tools for detecting, diagnosing and resolving performance issues in SQL Server.
-
Coaxing Maximum Performance from SQL Server 2005
Hilary Cotter’s Tips and Best Practices for being the most productive you can be as a SQL Server professional and getting the most out of your SQL Server investment.
-
Roundtable: SQL Server 2008 – What’s New, What’s Great, Why it’s Worth the Wait
Interactive panel discussion with Kevin Kline, Hilary Cotter and Jason Hall as they explore SQL Server 2008, some of the particulars that will make your life as a DBA easier and solve your biggest pain points, and what features they find to be the most exciting and valuable.
Space is limited. Registration will close at 200. All others will be placed on a waiting list.
Register at http://www.quest.com/events/listdetails.aspx?contentid=5962&technology=&prod=&prodfamily=&loc=4.
|
-
I read a lot of blog posts - a LOT. Sometimes it's interesting to sit back and evaluate all of the reading that I'm doing to try to glean ways of improving my efficiency. One thing that has enormously improved my efficiency is installing a Google plug-in RSS reader. Now, all the blogs that I read come into one sidebar where I can easily keep track of what's new.
So here are some of the posts I've been reading in the last couple days primarily covering SQL Server and also my other interests to a lesser degree:
http://sqlcat.com/whitepapers/default.aspx - The SQL CAT (Customer Advisory Team) are simply awesome.
http://www.sqlskills.com/blogs/bobb/2008/03/04/FeatureSynergyInSQLServer2008.aspx - Bob Beauchemin's blog is always a wonderful site for great new information. This particular article talks about the interplay of PowerShell and SSMS, which I'm trying to get up to speed on.
http://blogs.msdn.com/dtjones/archive/2008/03/07/getting-started-with-powershell.aspx - Dan Jone's nice entry on getting started with PowerShell.
http://sqlblogcasts.com/blogs/ssqanet/archive/2008/03/04/pre-order-the-kalen-delaney-sql-server-internals-course-on-dvd.aspx - Kalen has been an expert in SQL Server since before it was a Microsoft product. This DVD is very much worth it. It's actually really cheap considering how much information it contains.
http://www.sqlskills.com/blogs/paul/2008/03/05/TechNetRadioInterviewWithPaulAndKimberlyOnSQLServer2008Part1.aspx - Kimberly Tripp was the first person I ever took a SQL Server class from. Her content has always been top-tier and now that she and Paul are working together, it's only gotten better.
http://blogs.msdn.com/psssql/archive/2008/03/05/how-it-works-sqliosim-checksums.aspx - SQLIOSim is very useful for stress testing, but notoriously hard to interpret the results. (I wrote about this free tool some months ago in my SQL Server Magazine column, Tool Time.) This blog post from the PSS team helps you decifer checksum error messages that might appear in the SQLIOSim log.
http://blogs.msdn.com/benjones/archive/2008/03/05/store-your-data-in-the-cloud.aspx - Okay, so Microsoft has plans to allow you to host your SQL Server data in the cloud. That means that small businesses (or small teams inside of big businesses) can easily pop up SQL Server without the infrastructure or management headaches. I'm not sure how much traction this will get, but it shows that Microsoft now considers Google to be its biggest competitor IMO.
http://blogs.msdn.com/sqlperf/ - I was intrigued by a posting from the SQL Server Performance Engineering team that not only did SQL Server 2008 launch at February party for Windows 2008 and Visual Studio 2008, it launched with exceptionally good TPC-E numbers. This surprised me because, afaik, there's still a lot of debug code in the CTPs. I know this because my own TPC-C test were disappointing. So I was wondering what the secret sauce was so that they could post strong numbers with a straight face. I haven't yet read the TPC disclaimers, but rest assured that I will soon.
http://blogs.technet.com/wardpond/archive/2008/03/06/are-you-paying-to-read-this-post-you-don-t-have-to.aspx - I like to keep up with what Ward Pond is up to and, at the moment, he's ticked off at a variety of websites that are aggregators for other people's content, including his own. These websites charge monthly fees, but often create very little of their own content, instead repackaging what other people have written. This is what my Google RSS reader does for free.
https://blogs.msdn.com/sqlserverstorageengine/archive/2007/02/14/wow-lots-of-blogs-from-the-sql-product-team.aspx - I always go back to this web page from time to time to make sure that I'm checking in on all of the SQL Server team blogs. Right now, I'm making sure that all of the blogs with RSS feeds are in my reader so that I don't have to go out and pull down their latest postings manually.
http://statisticsio.com/ - This is a new (to me at least) blog from Jason Massie that I found to be fun and interesting. I think the SQL All Stars competition is what caught my eye.
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1296396,00.html?track=sy200&asrc=RSS_RSS-3_200 - I feel like I never seem to know enough about how the SQL language and XML interact on SQL Server. This is a good article by Michelle Gutzait examining XML on SQL Server, compared to simply using VARCHAR(MAX).
http://benchmarkfactory.inside.quest.com/index.jspa - I use Benchmark Factory (from my employer, Quest Software) to do my large-scale SQL Server testing. I was on the discussion forum reading some tips for doing a 3000 concurrent user TPC-C test.
And now for something completely different, I'm a big fan of the book Freakonomics and the accompanying blog hosted by the New York Times. In this book (and on their blog), the authors look to interprete the data as it really is, rather than the data as we wish it meant. The results are sometimes shocking and always interesting (social conservatives beware). In this particular blog post (http://freakonomics.blogs.nytimes.com/2008/03/04/when-journalists-gripe/), they point to a website where journalists gripe about being a journalist. I was interested in this because I think old world journalists today are what icemen were in the 1920's, on the verge of extinction. So I'm curious to see what's happening in their world.
I'm also very concerned about the current state of the economy, which has been brewing for quite a long time now, and the dire straights we're now in:
http://dailykos.com/storyonly/2008/3/7/63218/05713/729/471169
http://ap.google.com/article/ALeqM5iwL8pyStPKCF3LSCswXmM4jrhyygD8V8724G0
http://www.reuters.com/article/domesticNews/idUSN2862827920080229
http://www.fool.com/investing/value/2008/03/03/its-so-much-worse-than-you-think.aspx
I hope you find one or two of these posts of value, as I did.
Enjoy,
-Kev
|
-
Hi everyone,
If you're reading in the UK and especially Londontown, you might want to see if you have time to attend the inaugural meeting of the London Financial District SQL Server User Group. Get all details are here for the kick-off event on March 6th. And if you're willing, I'm looking for some tourism advice for Saturday. I know there's a lot to do in London, but I don't know the weather patterns or tourism tips for this time of year.
In addition, I'm happy to report that the Nashville SQL Server user group, which had run out of steam for a while, is back up and running. My friend and co-author, Louis Davidson, wrote about it here. We had a great crowd of over 100 people for our kick-off. I hope to see you at our next meeting at lunch time on April 4th!
Hope to see you there!
-Kevin
|
-
-
Hi everyone,
This is from my friend, Johnny Ortega, Lead Developer of Toad for SQL Server:
Quest has started the Toad for SQL Server v4.0 beta program. Please visit Toadsoft.com (http://www.toadsoft.com/toadsqlserver/toad_sqlserver_beta.htm) to try out beta build 4.0.0.211. Naturally, it is free and a great way to sample the feature set of this product.
For the Toad for SQL Server v4.0 beta, Quest is giving away a Nintendo Wii! (How they got one is a question I want answered!) At the end of the beta cycle, we will announce the top ten beta testers based on number of issues submitted and feedback given through the Yahoo! beta group. From the top ten beta testers, we will draw the winner.
We are in the middle of new functionality for certain features, including better Toad Insight and Version Control support for Team Foundation Server, so some functionality is not ready yet. Because of this, please focus your beta testing to the new functionality in these areas in this build:
- Export Wizard
- Automation
- Query Builder
- Database Browser's Data tab
- Data Diff Viewer
Please check the beta release notes (http://www.toadsoft.com/toadsqlserver/ReleaseNotes/Beta/ToadSqlServBetaRN.htm) for more details on the new functionality in these areas.
As more features are completed in subsequent beta releases, we will highlight them for beta testing.
Happy beta testing!
Johnny Ortega - Toad for SQL Server Development Team Lead
|
-
This isn't a technical topic at all. It's just sort of a quick and funny topic that hit me on a whim.
I'd hit one of those moments today when I couldn't get any more work done because I was way to fed up with the messiness of my office. It was time for me to take stock of all the stacks of paper, notes, and other junk that'd built up since the last time I'd done a major house cleaning. As I was triaging the various accretions of the last few months, I started coming across some unusual and interesting tchotchkies and doodads all related to SQL Server that I'd picked up at various events and conferences. As often happens when I'm cleaning things up, I got onto a tangent of cataloging all the SQL Server stuff I've accumulated over the years.
I'm certain that this is a CHALLENGE that I'm gonna lose, but I'm THROWIN' DOWN THE GAUNTLET to see what other MVP, writer, or SQL Server enthusiast out there can OUT DO MY LOAD O'SWAG?!?
Here are the rules:
a. It has to be official SQL Server stuff from the US or an international subsidiary.
b. It has to be free SWAG - none of the custom-ordered stuff from the Microsoft store or a conference store.
c. It can't be from a special, non-specific program like the MCP, MVP, or other "not Microsoft" organizations like PASS, Ineta, Culminis, SQLMag, SSWUG, SQL Server Central, SQLTeam, etc. (This kills me because I've got dozens of shirts & swag between all of these sources, my favorite of which is the "99.999% Uptime Boxer Shorts" from SQL Server Magazine.)
d. And just 'cuz I'm making up the rules, you cannot work for Microsoft now or in the past, since you'd have access to all kinds of goodies that us mere mortals cannot touch. I'm also inclined to exclude consultants who work directly for Microsoft on the SQL Server team (sorry, Kimberly, Kalen, Ron, et al...), but you're ok if you work for them through a partnership deal at a customer site.
So here's my inventory - and the first volley in this smackdown:
- 1 SQL Server wristwatch (Thank you Microsoft Singapore!)
- 1 pair of SQL Server flipflops (TechEd '05)
- 1 SQL Server denim long-sleeve shirt (can't remember but prolly TechEd '02-'04)
- 1 SQL Server God-awful Hawaian shirt (not the PASS shirt either, for those of you who've seen that one)
- 6 SQL Server polo shirts (my favorites were given to me by Pete Petisch at the release of SQL Server 6.0 and by Marc Souza prior to the SQL Server 7.0 airlift). I probably have owned more, but I recently purge all of my XL's because they're way too big for me these days.
- 1 SQL Server BI polo shirt with the snazzy chrome bar on the sleeve (big thanks to Angie O'Hara at the TechEd '06)
- 1 SQL Server girl action figure (acquired for me by Rick Heiges at TechEd '06, thanks dude!)
- 10 buttons from various TechEd's saying something like "Ask Me About SQL Server" or "Ask Me About Transact-SQL"
OK, so it's not quite as impressive a haul as I initially thought. I'm sure that there are at least a few other packrats out there who've outdone me by a long shot. Let's hear your inventory! And even if you've only gotten one or two goodies, I want to hear about them - especially if they're weird... like the rubber-chicken someone gave me at the PASS 2005 Summit. Top that!
Cheers!
-Kev
|
|
|
|
|
|