tag:blogger.com,1999:blog-109530162024-03-13T17:24:36.573-05:00DatabaseMiscellaneous SQL ServerKenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.comBlogger54125tag:blogger.com,1999:blog-10953016.post-90275293144706077412017-10-30T14:50:00.000-05:002017-10-30T16:08:14.847-05:00The Owl Has Been Fed<div style="font-family: "helvetica neue"; font-stretch: normal; line-height: normal;">
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal;">
<span style="font-kerning: none;">After two and one half years of on-again off-again effort - mostly a couple months up front and a few weeks at the end, I finally completed the final section in the Duolingo Spanish curriculum. They optimistically rate me at 57% fluency, but I take that with quite a few grains of salt.</span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal; min-height: 18px;">
<br />
<span style="font-kerning: none;"></span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal;">
<a href="https://1.bp.blogspot.com/-ctTqhlyQ7jE/WfeABwYkgRI/AAAAAAAAA6k/lpwuQLC43M8WRpm8E0uGttJWDVVM4Bf1gCEwYBhgL/s1600/Screen%2BShot%2B2017-10-22%2Bat%2B7.51.27%2BPM.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="1067" data-original-width="1600" height="213" src="https://1.bp.blogspot.com/-ctTqhlyQ7jE/WfeABwYkgRI/AAAAAAAAA6k/lpwuQLC43M8WRpm8E0uGttJWDVVM4Bf1gCEwYBhgL/s320/Screen%2BShot%2B2017-10-22%2Bat%2B7.51.27%2BPM.png" width="320" /></a><span style="font-kerning: none;">What it seem to mean is that, given time to think about it, I can recognize many of the major constructs of the language and piece together the meaning of various words and phrases. </span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal; min-height: 18px;">
<span style="font-kerning: none;"></span><br /></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal;">
<span style="font-kerning: none;">However, it doesn’t involve a lot of vocabulary and I still display an almost complete lack of verbal fluency. I do notice I can pick out a little more of the commentator dialog when watching soccer, but cannot understand snippets of conversation in real-life.</span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal; min-height: 18px;">
<span style="font-kerning: none;"></span><br /></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal;">
<span style="font-kerning: none;">All-in-all it was an enjoyable refresher for my 20-year old Spanish minor and, if I spend some time each day keeping the 62 sections colored gold, I would expect the average time to recognize and/or generate a word or phrase will continue to drop, as well as improved competence with the various verb tenses which came at a rapid pace near the end.</span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal; min-height: 18px;">
<br />
<span style="font-kerning: none;"></span></div>
<div style="-webkit-text-stroke-color: rgb(0, 0, 0); -webkit-text-stroke-width: initial; font-family: "Helvetica Neue"; font-size: 16px; font-stretch: normal; line-height: normal;">
<span style="font-kerning: none;">Next up will be "Duolingo Stories" which is a beta product that consists of four sets of ten "mini stories" that focus more on listening and reading comprehension for intermediate to advanced learners. Should be a nice supplement to the basics supplied by the original courses and a good way to build out some more vocabulary and develop an "ear" for the language.</span></div>
</div>
KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-25531603554369565722011-10-04T23:11:00.002-05:002011-10-05T15:36:23.994-05:00JOIN Me<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">This month's T-SQL Tuesday (#23) is hosted by <span class="Apple-style-span" style="line-height: 18px;">Stuart Ainsworth (<a href="http://codegumbo.com/">Blog</a> | <a href="http://twitter.com/#!/stuarta">Twitter</a>) and he has selected JOINs as this month's topic.</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">I thought the topic seemed mildly interesting from a deep technical perspective. If you agree, you might find some good reading <a href="http://msdn.microsoft.com/en-us/library/ms191517.aspx">here</a> or <a href="http://www.sqlskills.com/BLOGS/CONOR/post/Outer-joins-and-ON-clauses-vs-WHERE-clauses.aspx">here</a>, and I've even touched on the topic of <a href="http://kenj.blogspot.com/2006/12/legacy-outer-joins.html">Legacy JOINs</a>. However, I do deep technical writing much of each day, and I sometimes like to relax and ramble a bit in the blog. If it's SQL Server related rambling, so much the better.</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://codegumbo.com/index.php/2011/09/27/tsql2sday-t-sql-tuesday-23early-edition/" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><img border="0" src="http://3.bp.blogspot.com/-KpShsH9xZRE/TSviTFwT5QI/AAAAAAAAAlc/L7Q-vqpACic/s1600/tsql2sday.jpg" /></span></a></div><span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Besides, I don't know anything particularly deep or technical about JOINs in SQL Server. Come to think of it, there are a lot of deep technical things I don't know about SQL Server in general that can be solved by a few JOINs.</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">Here some JOINs that have helped me to solve deep technical problems in SQL Server. Maybe they've helped you, too.</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... the <a href="http://www.sqlpass.org/">Professional Association for SQL Server</a></span></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... your local <a href="http://www.funwith.net/">SQL Server User Group</a></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... your SQL Server Peers for a <a href="http://www.sqllunch.com/">SQL Lunch</a></span><br />
<span class="Apple-style-span" style="line-height: 18px;"><span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... an online <a href="http://ask.sqlservercentral.com/">SQL Server Forum</a></span></span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... an online SQL Server <a href="http://www.sqlservercentral.com/">Community</a> for the <a href="http://sqlskills.com/JoinCommunity.asp">Inside</a> scoop</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... <a href="http://twitter.com/">twitter</a> for the <a href="http://www.brentozar.com/archive/2009/12/i-need-sqlhelp/">#SQLHelp</a> hash tag</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">JOIN... your workmates for lunch</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">All those things you don't know about SQL Server? The places above are absolutely loaded with folks who DO know them... oftentimes even the coworker you didn't realize had seen it before. And, do you know what else? To somebody less experienced than you with SQL Server, you will be one of the ones with the answer.</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Verdana, sans-serif;">So JOIN up with somebody else and promote some SQL Server excellence.</span>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com1tag:blogger.com,1999:blog-10953016.post-82725322112224521052011-05-05T10:00:00.001-05:002011-05-05T10:00:07.977-05:00Sometimes It's Not The DatabaseThe other day for <a href="http://thomaslarock.com/category/meme-monday/">Meme Monday</a> I put together a list of <a href="http://kenj.blogspot.com/2011/05/meme-monday-its-not-disk.html">9 Things That Go Wrong With SQL Server That Aren't The Disk</a>. That got me to thinking about the old "It's the database" stand-by for when The Application isn't working right. Many of us have been there... The Application is acting up, and the knee-jerk response by the development or support team is, "it's the database"<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.blogger.com/goog_581423523"><img border="0" src="http://3.bp.blogspot.com/-Eu92gbKZRwo/TcIn3pU9p-I/AAAAAAAAAmU/M24CpNBYSeQ/s1600/2901537304_1287ae2788_m.jpg" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><a href="http://www.flickr.com/photos/sdierdorf/2901537304/">The Database... Seriously?</a></td></tr>
</tbody></table><br />
The frustrating thing with this is when the problem is found to lie in The Application or in the computing infrastructure, as it often is, but all the business stakeholders remember hearing was "it's the database."<br />
<br />
Then there are the few cases when it's true... a broken clock is right twice a day, after all. This happens just often enough that the other support and infrastructure teams sometimes actively refuse to do any troubleshooting unless, and until, the DBA can prove that the problem lies with a non-database component. Cross-discipline troubleshooting/resolution team? Not when it's obviously the database<br />
<br />
I was at a shop that recently had some recurring database connectivity issues with helpful error messages like "SQL Server Does Not Exist Or Access Denied", "Timeout Expired" and "General Network Error." Now, you know where the finger points when the words "SQL Server" appear in an error message.<br />
<br />
So the database team dutifully combed event logs, performance monitor and server side trace data, and was able to demonstrate pretty conclusively that nothing was wrong with the database or the server it was on. Till the next time The Application got busy and started throwing errors, when they had to do it all over again.<br />
<br />
What it ultimately came down to was the owners of The Application server had turned off connection pooling while troubleshooting an installation issue weeks earlier. It turns out that, by default, Windows only allows 4000 sockets (ports) per destination IP address. With the amount of database server traffic generated by the application server -- it had recently been "consolidated" -- they were able to hit that threshold pretty reliably.<br />
<br />
Microsoft has a <a href="http://support.microsoft.com/kb/328476">KB article</a> on considerations for servers with connection pooling disabled that describes this nicely:<br />
<blockquote>If you rapidly open and close 4000 sockets in less than 4 minutes, you will reach the default maximum setting for client anonymous ports, and new socket connection attempts fail until the existing set of TIME_WAIT sockets times out.</blockquote>After weeks of recurring connection errors with the word "SQL Server" in them, the connection pooling issue was quietly fixed by upping the socket limit to some random number like 65535 :) and The Application performance returned to normal. I'm sure you can guess the words that were never spoken to the business stakeholders... "It's Not The Database."KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-20244835914624766262011-05-02T22:28:00.000-05:002011-05-02T22:28:46.375-05:00Meme Monday: It's not the disk!<a href="http://thomaslarock.com/">Thomas LaRock</a>, aka <a href="http://twitter.com/#!/SQLRockstar">@SQLRockstar</a> (not THAT <a href="http://kenj.blogspot.com/2010/11/restore-me-please.html">rock-star</a>), has a fun way to get the blog month started: <a href="http://thomaslarock.com/category/meme-monday/">Meme Monday</a>. This month, it's just a list of 9 things we frequently see go wrong with a database server that are NOT related to disk issues.<div><br />
</div><div>In no particular order...</div><div><br />
</div><div>9. Good old boys</div><div>8. "It's just like a spreadsheet" database design</div><div>7. Out of date statistics </div><div>6. Improperly partitioned tables (see 7 above)</div><div>5. Good old boys</div><div>4. Microsoft patches</div><div>3. Vendor software</div><div>2. Internally developed software</div><div>1. Good old boys</div><div><br />
</div><div>I couldn't believe the number of disk related problems that kept coming to mind while putting this nod-disk related list together - especially when considering the SAN as a black-box hard disk.</div><div><br />
</div><div>You may have noticed that Good old boys made the list slightly more than once. Unfortunately, that is based on years of direct observation. I'm even going to manufacture a statistic to support its frequency in the list:</div><div><br />
</div><div>Nearly 60% of production database outages are, directly or indirectly, caused by the incompetence fostered by Good old boy cronyism... and that's NOT the disk.</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-61152199918443777992011-04-22T09:28:00.000-05:002011-04-22T09:28:51.921-05:00Nice Teeth<div class="MsoNormal"><span style="color: #1f497d; font-family: Calibri, sans-serif; font-size: 11pt;">Some of the commentary about the recent Amazon "Cloud" has focused on the reliability of cloud offerings, and their aggressively high availability Service Level Agreements (SLA). Just for reference, the Microsoft Azure SLA is 99.9%, and Amazon’s availability SLA happens to be 99.95%<o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://farm4.static.flickr.com/3109/2918567169_86112c79b2_o.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="220" src="http://farm4.static.flickr.com/3109/2918567169_86112c79b2_o.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Didn't see that one coming</td></tr>
</tbody></table><span style="color: #1f497d; font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: 11pt;">The interesting thing about an SLA is that it </span><span class="Apple-style-span" style="font-size: 15px;">doesn't</span><span class="Apple-style-span" style="font-size: 11pt;"> make the service, or its servers, more reliable, it just specifies penalties and/or contract options for when the services become </span><s style="font-size: 11pt;">unreliable</s><span class="Apple-style-span" style="font-size: 11pt;"> unavailable. To go with their aggressive promises, the SLA usually provides creative definitions for “available” to help ensure the SLA is met, e.g., if the service drops half your connections, it’s still available, and you’re <b>S</b>imply <b>O</b>ut of <b>L</b>uck<o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="color: #1f497d; font-family: Calibri, sans-serif;"><span class="Apple-style-span" style="font-size: 11pt;">If a company actually uses its data to make money, the SLA </span><span class="Apple-style-span" style="font-size: 15px;">doesn't</span><span class="Apple-style-span" style="font-size: 11pt;"> fund the enterprise while the service is unavailable. And the SLA typically doesn’t cover service degradation, just the creatively defined “availability.” It also doesn’t manage or repair your tarnished reputation with your customers or your business.<o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="color: #1f497d; font-family: Calibri, sans-serif; font-size: 11pt;">Want to bet your reputation, and that of your organization on the cloud? It’s probably a good bet, since you’re likely not getting 99.9% uptime from your own servers. But be prepared when that .1% kicks your reputation in the teeth.<o:p></o:p></span></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com1tag:blogger.com,1999:blog-10953016.post-67393690008957015832011-04-13T09:00:00.002-05:002011-04-13T09:00:08.408-05:00Passing the WordSo, I had this long running trace going through profiler last night (don't worry, it was just a DEV thing) and when I came in this morning, profiler had ended the trace with the following Trace Error:<br />
<br />
<br />
<div style="color: #00930e; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">-- 'Ӈ耇' was found in the text of this event.</div><div style="color: #00930e; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">-- The text has been replaced with this comment for security reasons.</div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-RBJ9PLK_VnU/TaUH8NWAXJI/AAAAAAAAAmQ/pMJU-kE1_SI/s1600/sql_profiler_error_unexpected_shutdown.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="116" src="http://1.bp.blogspot.com/-RBJ9PLK_VnU/TaUH8NWAXJI/AAAAAAAAAmQ/pMJU-kE1_SI/s320/sql_profiler_error_unexpected_shutdown.png" width="320" /></a></div>At first, I feared a Chinese Injection Attack, but after a little digging around, I was able to reproduce it by running a trace, then stopping the traced SQL Service while profiler was still running the trace. It's apparently a built in error code.<br />
<br />
I thought I had seen similar behavior in the past around queries that include the word "PASSWORD" in them, so I started playing around to see if I remembered correctly. It turns out my recollection wasn't perfect.<br />
<br />
When you run a security statement that might contain a login's password, SQL Profiler will remove the TextData from the event.<br />
<br />
Here are a few such statements where TextData is stripped:<br />
<br />
<div style="color: #900009; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">sp_password</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">CREATE<span style="color: black;"> </span>LOGIN</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">ALTER<span style="color: black;"> </span>LOGIN</div><br />
<br />
Of course, before rediscovering this, I had already run a statement using the word "PASSWORD" and profiler happily displayed my query text. I thought I'd share, not because of the query is so profound, but because I wrote it simply for the joy of writing it. <br />
<br />
<br />
<div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">CREATE<span style="color: black;"> </span>DATABASE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">USE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">CREATE<span style="color: black;"> </span>SCHEMA<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">CREATE<span style="color: black;"> </span>TABLE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD <span style="color: #929292;">(</span>PASSWORD<span style="color: black;"> </span>SYSNAME<span style="color: #929292;">);</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">INSERT<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD <span style="color: #929292;">(</span>PASSWORD<span style="color: #929292;">)</span><span style="color: black;"> </span>VALUES <span style="color: #929292;">(</span><span style="color: #fb0018;">'PASSWORD'</span><span style="color: #929292;">);</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">CREATE<span style="color: black;"> </span>INDEX<span style="color: black;"> </span>PASSWORD<span style="color: black;"> </span>ON<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD <span style="color: #929292;">(</span>PASSWORD<span style="color: #929292;">);</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px; min-height: 12.0px;"><br />
</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">SELECT<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">FROM<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: black;"> </span>WITH<span style="color: #929292;">(</span>INDEX<span style="color: #929292;">(</span>PASSWORD<span style="color: #929292;">))</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">WHERE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: black;"> </span><span style="color: #929292;">=</span><span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px; min-height: 12.0px;"><br />
</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">DROP<span style="color: black;"> </span>TABLE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">.</span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">DROP<span style="color: black;"> </span>SCHEMA<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">GO</div><div style="font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;"><span style="color: #3900fc;">USE</span> TEMPDB<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">go</div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">DROP<span style="color: black;"> </span>DATABASE<span style="color: black;"> </span>PASSWORD<span style="color: #929292;">;</span></div><div style="color: #3900fc; font: 10.0px Helvetica; margin: 0.0px 0.0px 0.0px 0.0px;">GO</div><br />
<br />
It's also amazingly blue because nearly every word is reserved. If you'd like to play with it, it should run for you on any system that doesn't already have a database named "PASSWORD"<br />
<br />
I hope you also find such light-hearted simple ways to enjoy the tools you work with.KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-66695230958499989432011-03-08T17:50:00.000-06:002011-03-08T17:50:07.623-06:00The Absolute Best<span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px;">This month's T-SQL Tuesday is hosted by Jes Schultz Borland ( <a href="http://blogs.lessthandot.com/index.php?disp=authdir&author=420">Blog</a> | <a href="http://twitter.com/grrl_geek">Twitter</a> ) and she has selected Aggregate Functions as this month's topic.</span><br />
<span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px;">Besides helping data make the transformation to information and to tell their story better, I would suggest that Aggregate Functions are the basis for all meaningful communication. </span><br />
<div class="im" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px;"><div class="MsoNormal" style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div></div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/come-one-come-all-to" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://1.bp.blogspot.com/__bT4E1xRvVw/TSviTFwT5QI/AAAAAAAAAlc/ka5qJX5vgZs/s1600/tsql2sday.jpg" /></a>Big claim, right? Just remember, it can't be the worst claim without an aggregate function. To illustrate, I'm going to tell a short story two times. One time without aggregate fuctions, and one time with aggregate functions.</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="text-align: right;"></div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The story starts with Bobby. Yes, that's <a href="http://xkcd.com/327/">Bobby Tables</a>, but he's all grown up now, and sells leather-free cowboy boots.</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Bobby went to the computer store and purchased a post-PC device. He had just received his annual sales bonus, and wanted to splurge on something new. After making it through the checkout process and returning home, Bobby was pleased to discover that he could access his home network through the device. Bobby was content with his purchase.</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Here it is again. See if you can spot the aggregate functions...</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Bobby went to his favorite computer store and purchased the market leading post-PC device. He had just received his largest annual sales bonus, and wanted to splurge on something new. After making in through the most customer friendly checkout process and returning home in record time, Bobby was pleased to discover that this device had the easiest setup for accessing his home network. Bobby thought this was the best purchase he had ever made.</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Did you spot the aggregate functions?</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
<ul><li>favorite computer store: having preference = max(preference)</li>
<li>market leading post-PC device: having market_share = max(market_share)</li>
<li>largest annual sales bonus: having bonus = max(bonus)</li>
<li>most customer friendly: having customer_experience = max(customer_experience)</li>
<li>returning home in record time: having transit_time = min(transit_time)</li>
<li>easiest setup: having setup_difficulty = min(setup_difficulty)</li>
<li>best purchase ever: having buyers_remorse = min(buyers_remorse)</li>
</ul></div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Whether it's to define <a href="http://kenj.blogspot.com/2010/09/whats-in-name.html">What's in a Name</a>, or to describe an evening out on the town, we can't communicate effectively without aggregate functions, and we can't learn anything from our data without them. Without aggregate functions, there is no superlative. Nothing is better than average, nothing comes first, nothing comes last, there is no typical.</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">So, next time somebody tells you the bathroom is the first door on the left, or you do something the fastest, or you are rated the highest, take a moment to thank your aggregate functions. </div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">They are the bes<span class="Apple-style-span" style="color: #1f497d;">t.</span></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com2tag:blogger.com,1999:blog-10953016.post-74619002189923362512011-02-28T22:02:00.000-06:002011-02-28T22:02:50.375-06:00Are you my type?<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.blogger.com/goog_1569833718"><img border="0" height="320" src="https://lh3.googleusercontent.com/-CSnXLX6Dtbc/TWxnkShtolI/AAAAAAAAAmM/GRtUgbZqG58/s320/421005108_b3925df0d3_z.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><a href="http://www.flickr.com/photos/grrrl/421005108/"><br />
</a></td></tr>
</tbody></table>Sometimes it's fun to take a look at how people tick, even when those people are, well, myself.<br />
<br />
I've taken the online <a href="http://www.myersbriggs.org/my-mbti-personality-type/mbti-basics/">Myers Briggs Type Indicator</a> a few times over the years, and typically seem to wind up with INTP.<br />
<br />
Beyond telling me that I'm slightly out-of-touch with the reality of day-to-day living, I don't know how much life-altering significance there is in this type of thing beyond explaining why I'm always first in the meeting to shout "Bingo!"<br />
<br />
There are a few good sites explaining what the various personality types are. PersonalityPage has a pretty good <a href="http://www.personalitypage.com/INTP.html">INTP overview</a>.<br />
<br />
Here is the description that Myers Briggs provided, which, aside from saying I should be a master designer of all kinds of theoretical systems, also gives me credit for being pragmatic - about ideas, so it's not <i>quite</i> completely contradictory.<br />
<div><br />
</div><div><div><br />
</div><div><div align="center" class="MsoNormal" style="background: white; text-align: center;"><b><span style="color: #0f5fa4; font-family: Tahoma, sans-serif; font-size: 10pt;">Rational Portrait of the Architect (INTP)</span></b><span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"><o:p></o:p></span></div><div class="MsoNormal" style="background: white; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><b><span style="color: #0f5fa4; font-family: Tahoma, sans-serif; font-size: 10pt;">Architects</span></b><span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"> need not be thought of as only interested in drawing blueprints for buildings or roads or bridges. They are the master designers of all kinds of theoretical systems, including school curricula, corporate strategies, and new technologies. For Architects, the world exists primarily to be analyzed, understood, explained - and re-designed. External reality in itself is unimportant, little more than raw material to be organized into structural models. What is important for Architects is that they grasp fundamental principles and natural laws, and that their designs are elegant, that is, efficient and coherent. <o:p></o:p></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span></div><div class="MsoNormal" style="background: white; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;">Architects are rare - maybe one percent of the population - and show the greatest precision in thought and speech of all the types. They tend to see distinctions and inconsistencies instantaneously, and can detect contradictions no matter when or where they were made. It is difficult for an Architect to listen to nonsense, even in a casual conversation, without pointing out the speaker's error. And in any serious discussion or debate Architects are devastating, their skill in framing arguments giving them an enormous advantage. Architects regard all discussions as a search for understanding, and believe their function is to eliminate inconsistencies, which can make communication with them an uncomfortable experience for many. <o:p></o:p></span><br />
<span class="Apple-style-span" style="color: #555555; font-family: Tahoma, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span><br />
<span class="Apple-style-span" style="color: #555555; font-family: Tahoma, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span></div><div class="MsoNormal" style="background: white; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;">Ruthless pragmatists about ideas, and insatiably curious, Architects are driven to find the most efficient means to their ends, and they will learn in any manner and degree they can. They will listen to amateurs if their ideas are useful, and will ignore the experts if theirs are not. Authority derived from office, credential, or celebrity does not impress them. Architects are interested only in what make sense, and thus only statements that are consistent and coherent carry any weight with them. <o:p></o:p></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span></div><div class="MsoNormal" style="background: white; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto;"><span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;">Architects often seem difficult to know. They are inclined to be shy except with close friends, and their reserve is difficult to penetrate. Able to concentrate better than any other type, they prefer to work quietly at their computers or drafting tables, and often alone. Architects also become obsessed with analysis, and this can seem to shut others out. Once caught up in a thought process, Architects close off and persevere until they comprehend the issue in all its complexity. Architects prize intelligence, and with their grand desire to grasp the structure of the universe, they can seem arrogant and may show impatience with others who have less ability, or who are less driven.<o:p></o:p></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span><br />
<span style="color: #555555; font-family: Tahoma, sans-serif; font-size: 10pt;"></span></div></div><br />
<span class="Apple-style-span" style="color: #555555; font-family: Tahoma, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><span class="Apple-style-span" style="color: black; font-family: Arial; font-size: small;"></span></span></span>Have you found these types of evaluations to be useful? Do certain types work better together on different projects, or does it all depend on how each type is manifested?<br />
<span class="Apple-style-span" style="color: #555555; font-family: Tahoma, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><span class="Apple-style-span" style="color: black; font-family: Arial; font-size: small;"></span></span></span><br />
<span class="Apple-style-span" style="color: #555555; font-family: Tahoma, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><span class="Apple-style-span" style="color: black; font-family: Arial; font-size: small;"></span></span></span></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com1tag:blogger.com,1999:blog-10953016.post-67945438625902728232011-01-18T09:00:00.008-06:002011-01-18T09:00:01.085-06:00The AbaculatorI enjoy using Red-Gate products. Really. I even suspect that the SQL Prompt code-completion product reduces the time it takes me to write SQL.<br />
<br />
I enjoyed their recent add likening Hand Coding your SQL to an Abacus, while <a href="http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/effortlessly">SQL Prompt</a> was likened to a Calculator:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/effortlessly"><img border="0" height="57" src="http://4.bp.blogspot.com/__bT4E1xRvVw/TTULMwnPloI/AAAAAAAAAmA/BW3d2odp_YE/s320/abaculator.png" width="320" /></a> </div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;">Think how much more efficient you could be with this product! We can even get supporting evidence from the inter-webs showing just how slow the Abacus really is when compared to our electron chomping marvel:</div><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/__bT4E1xRvVw/TTUM3aE-CcI/AAAAAAAAAmE/zj3c-pM4rhI/s1600/abacus_eats_calculator.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="http://4.bp.blogspot.com/__bT4E1xRvVw/TTUM3aE-CcI/AAAAAAAAAmE/zj3c-pM4rhI/s320/abacus_eats_calculator.jpg" width="320" /></a></div><br />
Oops! I had a feeling that the abacus would turn out to be pretty fast, but <a href="http://vimeo.com/6795526">three times faster</a>? And that's just a virtual abacus - think of the power and air conditioning costs this 9 year old mathematician will save.<br />
<br />
Now I'm slightly embarrassed to be a SQL Prompt user.<br />
<br />
Red-Gate might want to take a lesson from <a href="http://www.mathsecret.com/">MathSecret</a> on this one.KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-67729304004902886552011-01-13T09:00:00.000-06:002011-01-13T09:00:00.147-06:00Hacking The Doctor's Credentials<a href="http://www.brentozar.com/">Brent Ozar</a> just published his <a href="http://www.brentozar.com/archive/2011/01/mcm-certificate-photoshop-contest">Microsoft Certified Master certificate</a> along with a call for enhancements.<br />
<br />
I never excelled at Photoshop Tennis, but thought I'd take a stab at it using Microsoft's <a href="http://blogs.technet.com/b/itinsights/archive/2011/01/07/virtualization-alone-does-not-a-cloud-solution-make.aspx">recent jab</a> at VMWare as a starting point.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/__bT4E1xRvVw/TS0umVEUSaI/AAAAAAAAAlk/pn26PLymG6w/s1600/Brent+Ozar+MCM+Enhanced.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="248" src="http://2.bp.blogspot.com/__bT4E1xRvVw/TS0umVEUSaI/AAAAAAAAAlk/pn26PLymG6w/s320/Brent+Ozar+MCM+Enhanced.jpg" width="320" /></a></div><br />
Of course, Brent's breadth and depth of SQL and IT experience, along with his presentation and writing skills, do a SQL master make.<br />
<br />
Congratulations on the MCM!<br />
<div class="separator" style="clear: both; text-align: center;"></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-11198359002526215092011-01-10T23:09:00.003-06:002011-01-11T22:41:45.707-06:00Lies, Damned Lies, and ResolutionsThank you to <a href="https://mvp.support.microsoft.com/profile=5A3C842D-4104-405C-946B-9A849F04C9C1">Microsoft MVP</a> and rampant <a href="http://www.midnightdba.com/Jen">blogger</a>, <a href="http://twitter.com/#%21/MidnightDBA">tweeter</a>, and <a href="http://www.ustream.tv/channel/dbas-midnight">silver-screen maven</a> <a href="http://www.midnightdba.com/Jen/about/">Jen McCown</a> for hosting this month's <a href="http://www.midnightdba.com/Jen/2011/01/tsql-tuesday-014/">T-SQL Tuesday (#14)</a>. Congratulations, Jen! (Yes, <a href="http://kenj.blogspot.com/2010/09/feeling-love.html">I'm a fan</a> of the Midnight DBAs)<br />
<div style="text-align: right;"><a href="http://www.midnightdba.com/Jen/2011/01/tsql-tuesday-014/" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://1.bp.blogspot.com/__bT4E1xRvVw/TSviTFwT5QI/AAAAAAAAAlc/ka5qJX5vgZs/s1600/tsql2sday.jpg" /></a></div><br />
Many people claim they don't make new year resolutions. A few of them are telling the truth. The rest have secret resolutions they won't disclose for fear of failing publicly. I fall in the latter category.<br />
<br />
With this in mind, I have not resolved to get a SQL Server Certification: I have pre-purchased an <a href="http://www.microsoft.com/learning/career/en/us/career-offer.aspx">Exam 4-Pack</a> to cover a double MCITP and am sitting the <a href="http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-433&locale=en-us">MCTS: SQL Server 2008, Database Developer</a> today.<br />
<br />
I have also not resolved to participate in a T-SQL Tuesday: I am writing and submitting my first post right now. Insecure? Yep. Letting it trip me up? Not this time.<br />
<br />
These things started off as secret professional resolutions, but I've decided to take action on them.<br />
<br />
I still have other secret resolutions, mainly recycled secret failures from years past. Will I be acting on them? Some yes, and some no. Will I put them out there as "resolutions" and risk the public failure? I guess so.<br />
<ul><li>That 100 extra pounds (45 Kg / 7 stone) I've acquired since settling down? Nope. Not going to resolve it. I am, however, "resolving" to spend some quality time with the exercise bike so I can manage the stairs better and let the weight take care of itself. You have my permission to call me on it in a couple months.</li>
</ul><ul><li>Replace the broken dishwasher? Nope. I'm going to hire somebody who's good at it to do it for me. I make my living specializing in SQL Server and don't expect my clients to be especially brilliant with database systems. Why should I expect to hack together a home repair that's anything but mediocre? A quick note here: my house of full of mediocre but largely functional do-it-yourself jobs, so letting this go is a bit of a challenge for me.</li>
</ul><ul><li>Speak at the local SQL Server <a href="http://omahamtg.com/">User Group</a>? If they'll have me again.</li>
</ul>So, nothing spectacular, just a few public steps out of the comfort zone. Won't you join me?KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com2tag:blogger.com,1999:blog-10953016.post-70207786669265144762011-01-10T09:00:00.001-06:002011-01-10T09:00:01.837-06:00You can't afford what?<a href="http://www.mssqltips.com/author.asp?authorid=53">Tibor Nagy</a> recently did a nice intro to <a href="http://www.mssqltips.com/tip.asp?tip=2181">Populating a SQL Server Test Database with Random Data</a>.<div><br />
</div><div>It was a good primer to generating gobs of data, but part of the problem the solution was addressing wasn't technical: "<span class="Apple-style-span" style="font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">I heard about some excellent commercial tools but they are expensive and my company cannot afford them.</span>" The first commercial tool that comes to mind is <a href="http://www.red-gate.com/products/sql-development/sql-data-generator/">Red Gate's SQL Data Generator</a>, which isn't expensive by any measure, least of all a DBAs time.</div><div><br />
</div><div>It sickens me when a company can afford to squander days of a $50/hour DBA's time hand rolling something that could be had commercially for only a few hundred dollars. How can a company that can afford a DBA not afford a simple tool that will save days of effort and free up that expensive human time so it can be spent solving business problems?</div><div><br />
</div><div>To illustrate, a company where I used to work had an extravagantly expensive suite of database design, monitoring, and migration tools. That was well and good, but the migration tool took hours to compare a simple database, and often didn't succeed (though you still had to wait hours to find out). The result was a lot of squandered time and production releases that were often missing database objects or permissions. Ouch!</div><div><br />
</div><div>This company frowned on any suggestion to evaluate a different tool-set, so one of the DBAs, who believed more in getting things done than toeing the line (this gave him a reputation as a something of a cowboy), downloaded a trial copy of <a href="http://www.red-gate.com/products/sql-development/sql-compare/">Red Gate SQL-Compare</a> and started playing with it. This DBA's compare times went from hours to minutes (or less) and he no longer had last minute manual compare frenzies prior to production releases.</div><div><br />
</div><div>After a few more DBAs started using the evaluation - the tool completely paid for its purchase price in the first week of evaluation - the company finally realized that all this "free" salary time was being squandered, and decided to make the purchase. The enterprise-class database tool was put to rest, along with its rather generous annual maintenance fees, and they never looked back.</div><div><br />
</div><div>Though I have yet to work there, someday I will work at or run a shop where each DBA has his or her own annual tools budget equal to roughly a single day's pay that can be used to purchase simple tools about which they are passionate. They gain experience managing a corporate budgetary item and get the power and confidence to use that money to save even more money without any corporate red-tape, thank you very much.</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-36902541504488721222010-11-15T10:00:00.001-06:002011-01-10T21:25:17.774-06:00Nearly Competent<div style="line-height: 16.75pt;">I thought this was a pretty good insight from Jeff Atwood over at <a href="http://www.codinghorror.com/blog/archives/001289.html" target="_blank">CodingHorror</a>…<o:p></o:p></div><div style="line-height: 16.75pt; margin-left: 0.5in;"><span style="color: #333333;">You can tell a competent software developer from an incompetent one with a single interview question:</span><o:p></o:p></div><div style="line-height: 16.75pt; margin-left: 0.5in; text-indent: 0.5in;"><b><span style="color: #333333;">What's the worst code you've seen recently?</span><o:p></o:p></b></div><div style="line-height: 16.75pt; margin-left: 0.5in;"><span style="color: #333333;">If their answer isn't immediately and without any hesitation these two words:</span><o:p></o:p></div><div style="line-height: 16.75pt; margin-left: 0.5in; text-indent: 0.5in;"><b><span style="color: #333333;">My own.</span><o:p></o:p></b></div><div style="line-height: 16.75pt; margin-left: 0.5in;"><span style="color: #333333;">Then you should end the interview immediately. Sorry, pal.</span><o:p></o:p><br />
<span style="color: #333333;"><br />
</span></div>I love the brutal efficiency of the question, and find it to be revealing in the same way as the “on a scale of 1 – 10, how would you rate yourself” question. This question isn't quite as efficient -- there are more than two answers -- but any answer that isn't actually between 1 and 10 should end the interview. If the applicant doesn’t realize that 15 is not less than 10, what can you possibly hope to salvage by continuing?<o:p></o:p><br />
<br />
Yes. I’ve heard, with my own ears, somebody seriously rate themselves higher than 10 out of 10. I'll never forget the claim: "On a scale of 1 to 10, I'm a 13. My SQL Server skills are <i>that</i> good." Of course the candidate was hired (it wouldn't be much of a story otherwise) and the consequences weren't pretty. Years later, the team that made the hire is still trying to overcome the reputation of minimal competence, unresponsiveness, and outright contempt that this individual bequeathed them.<o:p></o:p><br />
<br />
On a lighter note, a little tidbit about the most overlooked risk in software engineering (again, from<span style="color: #1f497d;"> </span><a href="http://www.codinghorror.com/blog/archives/001289.html" target="_blank">CodingHorror</a>):<span style="color: #1f497d;"><o:p></o:p></span><br />
<div style="line-height: 15pt; margin-left: 0.5in;"><span style="color: #333333;">Q: What is the most often-overlooked risk in software </span><span style="color: #1f497d;">“</span><span style="color: #333333;">engineering</span><span style="color: #1f497d;">”</span><span style="color: #333333;">?</span><o:p></o:p></div><div style="line-height: 15pt; margin-left: 0.5in;"><span style="color: #333333;">A: Incompetent programmers.</span><br />
<span style="color: #333333;"><br />
</span></div>My name is “Bob” and I’m a developer.<span style="color: #1f497d;"> </span>Now let’s begin planning around my incompetence.<o:p></o:p>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-21452331504680501892010-11-11T10:00:00.006-06:002010-11-11T10:00:10.233-06:00Restore Me, Please<span class="Apple-style-span" style="font-family: Calibri, sans-serif; font-size: 15px;">Our shop has a stored procedure to restore databases that takes care of all the ugly details of file moves and the like. You supply a target_database_name and a backup_file and it does the rest. No worrying about which paths to move your data and log files to on each server. It's even supposed to distinguish between native and Red-Gate backups and do the appropriate magic.</span><br />
<span class="Apple-style-span" style="font-family: Calibri, sans-serif; font-size: 15px;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Calibri, sans-serif; font-size: 15px;">A couple years ago, we had a rock-star DBA "optimize" the restore procedure, and it has only worked intermittently ever since. We've all taken to using the native backup syntax so we don't depend on "the procedure." However, we occasionally get a compressed database we need to restore (.sqb), and I recently ran through a painful restore that I thought I'd share to give a quick overview of restoring backups that have been compressed with Red-Gate SQL Backup.</span><br />
<div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">After making a good-faith effort to restore with "the procedure," I grabbed version 6.4 of the Red-Gate <a href="http://labs.red-gate.com/">SQB Converter</a> so I could extract the .SQB file into its constituent .BAK files. Not entirely related, but we tend to have 15 .BAK files wrapped up in each .SQB file. It helps backup performance, but can be a killer on <a href="http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase\SQL_Backup\KB200708000117.htm">MemToLeave</a> Virtual Address Space (VAS) on 32-bit servers.</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">In this particular case, it wouldn’t convert the file because it wanted a password. This is often an indication that the converter version is older than the SQL backup version that created the .sqb file, but I'm sure it could have other meanings, too (like needing a password?).</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">Since I couldn't get my .bak files out of the .sqb file, I had to crack open "the procedure" and scarf its Red-Gate syntax to do the restore. Other than the additional Red-Gate syntax, the process is the same we would use with native tools…</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.75in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>Restore filelistonly to get your logical filenames</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.75in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>Determine the location to place your .mdf and .ldf files on the server</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.75in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>Restore your database with move</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">Here is the relevant syntax…</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>Get your logical filenames with this:</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">EXEC</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">master</span><span style="color: grey;">..</span>sqlbackup<span style="color: blue;"> </span><span style="color: red;">N'-SQL "restore filelistonly from disk = ''path_to_your_sqb_file.sqb''"'</span></span></div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span>Get the default file locations for your server (if you can create a database, you should have permissions to write restored database files there, too):</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">CREATE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DATABASE</span> bob</span></div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">EXEC</span><span style="font-family: 'Courier New'; font-size: 10pt;"> bob<span style="color: grey;">..</span><span style="color: maroon;">sp_helpfile</span></span></div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">DROP</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DATABASE</span> bob</span></div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in; text-indent: -0.25in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span>Once you have your logical filenames, and the paths to where you want to move them, you can run the restore…<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"></span></div><div class="x_MsoListParagraph" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.5in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0.25in; margin-right: 0in; margin-top: 0in;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">EXEC</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">master</span><span style="color: grey;">..</span>sqlbackup<span style="color: blue;"> </span><span style="color: red;">N'-SQL "restore database your_database from disk = ''path_to_your_sqb_file.sqb'' with move ''data_file'' to ''path_to_mdf_file.mdf'', move ''log_file'' to ''path_to_your_log_file.ldf''"'</span></span></div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;"><br />
</div><div class="x_MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin-bottom: 0.0001pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">I'm still a big fan of Red-Gate <a href="http://www.red-gate.com/products/SQL_Backup/">SQL Backup</a>, I just don't listen to rock any more.</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-91822851912717867622010-11-08T10:00:00.013-06:002010-11-08T10:00:08.289-06:00The Patching Treadmill<div>Well, it's almost <a href="http://en.wikipedia.org/wiki/Patch_Tuesday">Patch Tuesday</a> again. Are you ready to apply and test them? It's one of the most important things you can do to maintain the security and availability of your SQL Servers. Remember <a href="http://en.wikipedia.org/wiki/SQL_Slammer">Slammer</a>? The patch for that was months old, but hadn't been widely applied.<br />
<br />
Keeping up with patches used to be a time consuming, hit-and-miss sort of affair. By scheduling a regular monthly patch release, Microsoft has provided the predictability that many organizations need to manage and keep up with the patching process. There isn't really an excuse any more for not having some sort of regularly scheduled patch deployment and testing process.<br />
<br />
We happen to use <a href="http://technet.microsoft.com/en-us/wsus/default.aspx">Windows Server Update Services (WSUS)</a> to authorize and deploy patches in our environment. We push the patches through QA and test environments before pushing them to pilot groups then the rest of the enterprise. It's a pretty decent system and has served us well.<br />
<br />
Testing the patches is critical, because they do occasionally cause production outages of their own. We had to back one out of production a couple of months ago because it affected the communication between one of our Windows applications and its WCF service. Of course, since we're not perfect, the same patch made it back into test last month, but we were able to get it backed out before it made it through to production again (it had been mistakenly re-authorized in WSUS). We'll have to really watch out this month: the only thing more embarrassing than getting caught by the same mistake twice is getting caught by it three times. Three strikes, and you're out, right?<br />
<br />
Just to help get things rolling, this is the schedule we use for applying Microsoft's monthly patches. Just place the Microsoft release on the second Tuesday then testing and production rollouts fall right in place.</div><a href="http://3.bp.blogspot.com/__bT4E1xRvVw/SlZsFhIQ-6I/AAAAAAAAATw/WhKzjI3SlmM/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" style="text-decoration: none;"></a><br />
<a href="http://3.bp.blogspot.com/__bT4E1xRvVw/SlZsFhIQ-6I/AAAAAAAAATw/WhKzjI3SlmM/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" style="text-decoration: none;"></a><br />
<a href="http://3.bp.blogspot.com/__bT4E1xRvVw/SlZsFhIQ-6I/AAAAAAAAATw/WhKzjI3SlmM/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" style="text-decoration: none;"></a><br />
<a href="http://3.bp.blogspot.com/__bT4E1xRvVw/SlZsFhIQ-6I/AAAAAAAAATw/WhKzjI3SlmM/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" style="text-decoration: none;"></a><br />
<a href="http://3.bp.blogspot.com/__bT4E1xRvVw/SlZsFhIQ-6I/AAAAAAAAATw/WhKzjI3SlmM/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" style="text-decoration: none;"><div style="text-align: center; text-decoration: underline;"><span class="Apple-style-span" style="color: black;"><span class="Apple-style-span" style="text-decoration: none;"><br />
</span></span></div></a><a href="http://1.bp.blogspot.com/__bT4E1xRvVw/SlZsRg4KkrI/AAAAAAAAAT4/k06cJ5aVpS4/s1600-h/Monthly+MS+Patch+Cycle.PNG" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5356587854942868146" src="http://1.bp.blogspot.com/__bT4E1xRvVw/SlZsRg4KkrI/AAAAAAAAAT4/k06cJ5aVpS4/s400/Monthly+MS+Patch+Cycle.PNG" style="cursor: hand; cursor: pointer; display: block; height: 251px; margin: 0px auto 10px; text-align: center; width: 400px;" /></a><br />
<div>This is based on an Excel Template by <a href="http://www.vertex42.com/ExcelTemplates/">Vertex42</a></div><div><br />
</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-66943267013103091722010-11-03T09:00:00.000-05:002010-11-03T09:00:11.472-05:00My Descendents Thank You<span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: 16px;">I have 3,932,160 grandchildren.</span><br />
<div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">At least I will in 600 years, if my children and theirs each produce two reproducing children per generation. And that’s exactly when today’s performance optimization will pay for its own development time in CPU cycles.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">I have written </span></span><a href="http://kenj.blogspot.com/2010/10/dont-waste-my-users-time.html"><span style="font-family: Arial, sans-serif; font-size: 12pt;">previously</span></a><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"> about intentionally leaving query results unordered to save some academic CPU cycles while burning money in end-user time – a poor “passive” optimization. Now we’ll discuss actively making inane optimizations that will never pay for themselves – a poor “aggressive” optimization.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">A developer modifies a query with the academic goal of saving several milliseconds per execution (perhaps as few as 6 ms). If this was a query that ran thousands of times per hour during user time, we might have been able to quantify some savings to the business. Unfortunately, the optimized queries were in some procedures that run once per day during a nightly batch cycle that had no time pressure.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">I ran the numbers on the CPU savings and it will take more than 600 years for the saved CPU cycles to accumulate the same amount of time spent to “optimize” the query. Because the CPU time for a batch process does not correspond to the real money being spent in user-time, this optimization will never be paid for… even in the 600 year break-even period for the CPU time.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">These two types of performance optimization illustrate the danger of optimizing for purity of execution without giving any consideration to the practical implications of where our technical decisions force the business to spend its money.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><span class="apple-style-span" style="font-family: Arial;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">If we keep wasting it like this, they certainly won’t be spending it on us.</span></span>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-46940274154020779092010-10-29T09:00:00.042-05:002010-10-30T00:50:38.837-05:00Don’t waste my (users’) time<div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Tradeoffs are everywhere. This is particularly true in the realm of software development. As the saying goes, “speed costs. How fast do you want to go?” We should always keep these tradeoffs in mind as we balance our technology decisions with the needs of the business.</span></span><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
<br />
<span class="apple-style-span">Here is a quick tale of a not-so-great performance optimization that really emphasizes the importance of making optimization decisions that actually have business value…</span><br />
<br />
<span class="apple-style-span"><b style="mso-bidi-font-weight: normal;">The case of a missed sort optimization</b></span><br />
<span class="apple-style-span">A business user and her colleagues currently sort and search a grid manually because "without some kind of 'sort' you have no idea where in the list your application will be found". These results apparently get scrambled each time the grid refreshes. The company is spending wage dollars (read “real money”) to do the work that the CPU could be doing for “free”.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><span class="apple-style-span"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">In this case, the ten seconds worth of real money spent by the developer to type “ORDER BY <column>” would have been a huge win. One ten-second real money line of code would have saved thousands of several-second real money live employee sorts. I don’t know about you, but I get excited about single order-of-magnitude performance increases. This was a three-orders-of-magnitude oversight!<o:p></o:p></column></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal"><span class="apple-style-span"><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 12pt;">Because the developer </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">didn't</span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 12pt;"> make this 10 second investment up front, the company now has two options (there may be others... conversations are good at flushing out options!):<o:p></o:p></span></span></span></div><div class="MsoListParagraphCxSpFirst" style="font-family: Arial; font-size: 13px; line-height: 115%; text-indent: -0.25in;"><span class="apple-style-span"><span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span></span><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;">do nothing and continue to spend real money to do the sorting that the CPU could be doing for free</span></span><span class="apple-style-span"><span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;"><o:p></o:p></span></span></div><div class="MsoListParagraphCxSpLast" style="font-family: Arial; font-size: 13px; line-height: 115%; text-indent: -0.25in;"><span class="apple-style-span"><span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span></span><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;">spend thousands of dollars of real money to add an ORDER BY <column> (or an appropriate index to force a sort).</column></span></span><span class="apple-style-span"><span style="font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;"><o:p></o:p></span></span></div><div class="MsoListParagraphCxSpLast" style="font-family: Arial; font-size: 13px; line-height: 115%; text-indent: -0.25in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt; line-height: 115%;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">I can hear you scoffing now, “Sure, thousands of dollars!” Picture it with this nearly exaggerated process – <o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">We start with our line-of-business employees spending their time (remember, that’s real money) adding the suggestion to the feature request system and voting it up (picture Microsoft Connect for your business applications). We just spent more real money getting the bug typed in and voted up than we would have spent with the initial sort, but I digress.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Next, we have the bug validation team spending real money making the appropriate contacts and follow ups with a decision maker who determines (spending more real money) if this is a legitimate feature request.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Once we have determined that this is a real bug, worthy of company resources (more real money), we proceed to the <i style="mso-bidi-font-style: normal;">team</i> (think real money multiplied by <a href="http://dilbert.com/strips/comic/2007-11-08/">Dilbert</a>) that determines which features and applications get worked by which development teams and when.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Now that we’ve gotten all the way through the verification, validation, prioritization, and schedulization phase, it’s time for a software development <i style="mso-bidi-font-style: normal;">team</i> (there’s that real money multiplier again) to actually begin working the bug – remember that ORDER BY <column>thing we were talking about? Our software development <i style="mso-bidi-font-style: normal;">team</i> is going to go through its own verification, validation, prioritization and schedulization process.<o:p></o:p></column></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px; margin-left: 0.5in;"><span class="apple-style-span"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">They have to reproduce the bug, find the source code module where they can actually make the fix, fix it (our initial 10 second line of code), then run it though the test deployment process, user acceptance testing, the pre-prod deployment process, the pre-prod testing process, the change request for production process (think about another team here), the production deployment process and the post-production testing process.<o:p></o:p></span></span></div><div class="MsoNormal" style="margin-left: 0.5in;"><span class="Apple-style-span" style="font-family: Arial, sans-serif;"><br />
</span></div><div class="MsoNormal"><div style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Don't get me wrong, this isn't some process oriented bureaucracy, this is a kanbanagilescrum .NET development shop with all the modern efficiencies as their disposal.</span></div><div style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></div><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 12pt;">You probably thought I forgot where I was going with this. It was close there for a second, but it's still here. The problem with this </span></span><span class="Apple-style-span" style="font-family: Arial, sans-serif;">isn't</span><span class="Apple-style-span" style="font-family: Arial, sans-serif; font-size: medium;"><span class="Apple-style-span" style="font-size: 12pt;"> that the developer made a simple oversight on a search screen and nobody noticed before production. That’s understandable, and we all do similar things routinely. The problem is that there really are developers who intentionally leave all of the sorting to the end-user because they want to save the CPU sort cost for the 400 rows.<o:p></o:p></span></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">These decisions not only <a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/10/18/uncertainty-erodes-confidence-so-add-that-order-by-clause.aspx">erode our users’ confidence</a> in both the development team and the application, they consciously make the tradeoff between CPU time and User time – and choose infinitesimally small bits of CPU time in favor of measurably large amounts of real money paid in the form of user time.<o:p></o:p></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;"><br />
</span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><span style="color: black; font-family: Arial, sans-serif; font-size: 12pt;">Stop wasting my users’ time!<o:p></o:p></span></div><div class="MsoNormal" style="font-family: Arial; font-size: 13px;"><br />
</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-52725394516046826242010-09-10T22:18:00.000-05:002010-09-10T22:18:21.875-05:00Alter EgoSo the other day I'm doing some light reading from Paul Randal about <a href="http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Why-would-CHECKDB-run-out-of-space.aspx">DBCC CHECKDB</a> when I learn that, beginning with SQL 2005, CHECKDB actually performs its checks on an internal database snapshot and not the "real" database. That was a totally unexpected bit of knowledge, so it really caught my eye. It's great to have somebody that loves to share these things with the world. Thanks, Paul!<br />
<br />
A little more reading revealed that the internal database snapshot is implemented as an alternate stream of the live database file. And also that I have no control over where it's created, so it can cause a nearly full drive to fill up during a CHECKDB (the original reason for his original article).<br />
<br />
I had never heard of an alternate stream before, but I did find a couple <a href="http://www.ntfs.com/ntfs-multiple.htm">pretty good</a> <a href="http://www.flexhex.com/docs/articles/alternate-streams.phtml">explanations</a>, that I won't cover here, other than to mention that it's a feature of NTFS and allows multiple data streams to be stored within a single file (the second link has downloadable source-code for utilities that manipulate alternate data streams, if you really want to get down in the weeds).<br />
<br />
I wanted to see one of these alternate streams in action myself, and discovered that you can view a file's alternate streams by using the /R switch of the DIR command. Here is DBCC CHECKDB in action, complete with a peek at the alternate steam in the file system.<br />
<br />
I'm using the following command to provide directory listings for the AdventureWorks database:<br />
<br />
<br />
<div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;">declare</span><span style="font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;"> @cmd <span style="color: blue;">sysname<o:p></o:p></span></span></div><div class="MsoNormal" style="mso-layout-grid-align: none; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;">set</span><span style="font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;"> @cmd <span style="color: grey;">=</span> <span style="color: red;">'dir /R "C:\<blah_blah>\AdventureWorks_Data*"'<o:p></o:p></blah_blah></span></span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;">exec</span><span style="font-family: "Courier New"; font-size: 12.0pt; line-height: 115%;"> <span style="color: blue;">master</span><span style="color: grey;">..</span><span style="color: maroon;">xp_cmdshell</span><span style="color: blue;"> </span>@cmd</span></div><br />
<br />
This shows the AdventureWorks_Data.mdf file:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/__bT4E1xRvVw/TIrzKMA6yDI/AAAAAAAAAkk/HEK873clwLs/s1600/FileOnly.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/__bT4E1xRvVw/TIrzKMA6yDI/AAAAAAAAAkk/HEK873clwLs/s320/FileOnly.png" /></a></div><br />
Now run <span style="color: blue; font-family: "Courier New"; font-size: 14.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-no-proof: yes;">dbcc</span><span style="font-family: "Courier New"; font-size: 14.0pt; mso-ansi-language: EN-US; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-no-proof: yes;"> checkdb<span style="color: grey;">(</span><span style="color: red;">'AdventureWorks'</span><span style="color: grey;">)</span></span> and, while that is still running, kick off the DIR command from a separate SSMS window. This shows the AdventureWorks_Data.mdf file and the alternate stream that is used for the internal database snapshot:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/__bT4E1xRvVw/TIry638PyEI/AAAAAAAAAkc/_CmjOSIoHI0/s1600/AlternateFilestream.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/__bT4E1xRvVw/TIry638PyEI/AAAAAAAAAkc/_CmjOSIoHI0/s320/AlternateFilestream.png" /></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Notice that the DIR command only reported a single file in the summary. The alternate data stream is completely invisible to most DOS style commands, and you'll never see one in Windows Explorer.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">I didn't really expect to do so much digging after a little reading about DBCC CHECKDB, but it turns out there was a lot to learn, and I couldn't resist diving into the mysterious internal database snapshot and its alternate data stream.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div>Enjoy!KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-75595548915177854202010-09-07T23:27:00.003-05:002010-09-07T23:41:18.688-05:00Feeling the LoveDBAs @ Midnight just kicked off its <a href="http://www.midnightdba.com/Jen/2010/09/re-introducting-dbas-midnight/">second season</a>. If, like me, you missed the live season opener, you'll have to settle for the <a href="http://www.midnightdba.com/Jen/2010/09/season-2-sing-a-long/">preview</a> until the recording is available.<div><br /></div><div>In the meantime, they are forming a zombie gang and soliciting public displays of affection. I don't know if I'd make a good zombie gangster but I sure do enjoy the show, so here's my PDA:</div><div><br /></div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/__bT4E1xRvVw/TIcTEbU5v5I/AAAAAAAAAkU/LaMgUeHi2gI/s1600/KenJ+and+MidnightDBA.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 398px; height: 346px;" src="http://4.bp.blogspot.com/__bT4E1xRvVw/TIcTEbU5v5I/AAAAAAAAAkU/LaMgUeHi2gI/s400/KenJ+and+MidnightDBA.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5514397235514294162" /></a><br /><div><br /></div><div>Have a great second season Sean and Jen!</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-9397845728705521392010-09-06T10:00:00.000-05:002010-09-06T10:00:04.601-05:00That's Virtually Twice as Fast<div>We do much of our software testing and validation on virtual machines. We happen to be using VMWare ESX as the host and are testing with Windows XP SP3 as the guest.<div><br /></div><div>We were getting pretty poor launch performance from our application as compared to a physical machine. I'll measure launch time in Task Manager Grid Squares (TMGS). Think of them like story points for performance monitoring. They just represent some arbitrary amount of time (what it is... 9 seconds per square?).</div><div><br /></div><div>We have a main application, a supporting GIS application and a 3rd application for moving things around the network, and it was taking as many as 10 Task Manager grid squares for the three applications to launch. Here is what the launch sequence looks like in Task Manager:</div></div><div><br /></div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://2.bp.blogspot.com/__bT4E1xRvVw/TIB1TdGGr6I/AAAAAAAAAkE/6StNnB1Xhoc/s1600/single+virtual+processor+and+1GB+RAM.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 355px; height: 400px;" src="http://2.bp.blogspot.com/__bT4E1xRvVw/TIB1TdGGr6I/AAAAAAAAAkE/6StNnB1Xhoc/s400/single+virtual+processor+and+1GB+RAM.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5512534920989683618" /></a><div>Here is the key for the image:</div><div></div><blockquote><div>1. Main application launch</div><div>2. GIS application launch -- almost 1 minute of 100% CPU -- OUCH!</div><div>3. Network search</div><div>4. Opening the first search result</div></blockquote><div></div><div><div>To try and help out with the launch speed, we added a virtual processor and a virtual GB of RAM to the virtual machines. This reduced our launch time from 10 Task Manager grid squares to just over 6.</div></div><div><br /></div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/__bT4E1xRvVw/TIB1HYatfVI/AAAAAAAAAj8/xvtFLd4ThxA/s1600/dual+virtual+processors+and+2GB+RAM.png"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 249px;" src="http://1.bp.blogspot.com/__bT4E1xRvVw/TIB1HYatfVI/AAAAAAAAAj8/xvtFLd4ThxA/s400/dual+virtual+processors+and+2GB+RAM.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5512534713575505234" /></a><div style="text-align: left;"><span class="Apple-style-span" style="color:#0000EE;"><u><span class="Apple-style-span" style="color:#000000;"><br /></span></u></span></div><div>You can see that opening a search result wasn't really helped out because it is rather network intensive. Main application launch is a mix of network and processor, and didn't fare much better with two processors than it did before the upgrade.</div><div><br /></div><div>The real benefit we saw was the large gain in both the launch of the GIS application and the processing of search results, both of which are processor intensive. We see the times here cut by more than half.</div><div><br /></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-88210917575752050152010-09-03T10:00:00.000-05:002010-09-03T10:00:01.113-05:00Chew Once, Swallow Twice<div>I've been working on a legacy system that's been sitting on the shelf for about a year. Most of the system is in VB.Net, so I've been writing a fair amount of "VB#" as habit dictates that I end each line with a semi-colon -- it works in T-SQL <i>and</i> C#.</div><div><br /></div><div>A workmate did find an import/export routine written in C#, and it had a great catch statement I thought I'd share. First it catches any error and swallows it, a la ON ERROR RESUME NEXT. But, just for good measure, it also checks the error message so it can execute... a comment!</div><div><br /></div><div><span class="Apple-style-span" style=" border-collapse: collapse; font-family:arial, sans-serif;font-size:13px;"><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style=" ;font-family:Consolas;font-size:10pt;"></span></p><blockquote><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style=" ;font-family:Consolas;font-size:10pt;"> <span style=" ;color:blue;">catch</span> (<span style="color: rgb(43, 145, 175); ">Exception</span> ex)<br />{<br /> <span style=" ;color:blue;">if</span> (ex.Message.Contains(<span style="color: rgb(163, 21, 21); ">"<wbr>Violation of PRIMARY KEY"</span>))<br /> {<br /> <span style=" ;color:green;">//ok</span><br /> }</span></p><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style=" ;font-family:Consolas;font-size:10pt;"> }</span></p></blockquote><p class="MsoNormal" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style=" ;font-family:Consolas;font-size:10pt;"></span></p></span></div><div><br /></div><div>I guess any data integrity error worth suppressing is worth over-suppressing. I've probably done the same type of thing a dozen times, so it's nice to have a little fun with somebody else's forgotten bit of error control.</div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-72188948459001130502010-09-01T22:41:00.000-05:002010-09-01T22:53:24.396-05:00What's in a name?<div>The software team I've been working with had a “string or binary data would be truncated” error where we were trying to copy a 100 character “buyer name” column from one table into a 50 character column in another table. We also had a "seller name" column with the potential for the same error and fixed that one up, too.</div><p style="margin-bottom: 0in; ">I took a quick peek at the data to see how the name lengths were distributed in each table. Both names had an average length in the mid- to upper-teens. Virtually none of the names were longer than 50 characters (probably why the bug went unnoticed for years). </p><p style="margin-bottom: 0in; ">Here are a couple visuals of the distribution:</p><p style="margin-bottom: 0in; "><br /></p><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://4.bp.blogspot.com/__bT4E1xRvVw/TH8dNKqWgbI/AAAAAAAAAjs/HqpKYfYOosk/s1600/seller+name.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 162px;" src="http://4.bp.blogspot.com/__bT4E1xRvVw/TH8dNKqWgbI/AAAAAAAAAjs/HqpKYfYOosk/s400/seller+name.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5512156580962271666" /></a>Since this was a reasonably large sample (more than 100,000 names in each column), it probably makes a good guideline for full name column sizing.<div><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://1.bp.blogspot.com/__bT4E1xRvVw/TH8dMt4xNvI/AAAAAAAAAjk/ftja6GsFIeU/s1600/buyer+name.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 174px;" src="http://1.bp.blogspot.com/__bT4E1xRvVw/TH8dMt4xNvI/AAAAAAAAAjk/ftja6GsFIeU/s400/buyer+name.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5512156573238114034" /></a><p style="margin-bottom: 0in">As is usual with software defects, the standard “we inherited it” rule applies in this case. Which brings up the quick unrelated observation that I rarely meet developers who claim to write bugs, just folks who inherit them. I'll have to write more on that, later.</p><p style="margin-bottom: 0in">At any rate, it seems that bug writers like myself are so prolific that it doesn't take very many of us to keep all the real developers busy.</p><p></p></div>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-87906401907140545442010-02-16T22:12:00.001-06:002010-02-16T22:27:33.498-06:00Are you a reader or a writer?<span xmlns=""><p><span class="Apple-style-span" style="font-family:Arial;"><span class="Apple-style-span" style="font-size:-webkit-xxx-large;"></span></span></p><span class="Apple-style-span" style="font-family:Arial;"><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">I stumbled across a query that gives the percentage reads versus writes and thought it might be interesting to see how some of my servers stack up.</span></p><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">I gave the query a couple tweaks so it returns percentage directly, as well as total reads and writes. I needed the totals so that I could calculate read and write percentages for a group of servers.</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span class="Apple-style-span" style="font-family:'Courier New';font-size:100%;color:#0000FF;"><span class="Apple-style-span" style="font-size:13px;"></span></span></p><span class="Apple-style-span" style="font-family:'Courier New';font-size:100%;color:#0000FF;"><p class="MsoNormalCxSpFirst" style="margin-bottom:0in;margin-bottom:.0001pt; mso-add-space:auto;line-height:50%;mso-layout-grid-align:none;text-autospace: none"></p><p class="MsoNormalCxSpFirst" style="margin-bottom:0in;margin-bottom:.0001pt; mso-add-space:auto;line-height:normal;mso-layout-grid-align:none;text-autospace: none"><span class="Apple-style-span" style="font-family:monospace;"><span class="Apple-style-span" style="font-size: -webkit-xxx-large;"></span></span></p><span class="Apple-style-span" style="font-family:monospace;"><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:blue">select</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:blue"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_seeks</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_scans</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_lookups</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:blue">as</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">total_reads</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">,</span></span><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_updates</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:blue">as</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">total_writes</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">,</span></span><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:fuchsia">sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_seeks</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_scans</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_lookups</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">) *</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">100.0</span></span><span style="font-size:8.0pt;font-family:"Courier New"; color:black"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:gray">/</span></span><span style="font-size:8.0pt; font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:fuchsia">Sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_updates</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">) +</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:fuchsia">sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">(</span></span><span class="apple-style-span"><span style="font-size: 8.0pt;font-family:"Courier New";color:black">user_seeks</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">user_scans</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">user_lookups</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">5</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">,</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">2</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">))</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue">as</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:blue"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">read_percent</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">,</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:fuchsia">Sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_updates</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">) *</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">100</span></span><span style="font-size:8.0pt;font-family:"Courier New"; color:black"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:gray">/</span></span><span style="font-size:8.0pt; font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:fuchsia">cast</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:fuchsia">Sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">user_updates</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">) +</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:fuchsia">sum</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">(</span></span><span class="apple-style-span"><span style="font-size: 8.0pt;font-family:"Courier New";color:black">user_seeks</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">user_scans</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:black"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">+</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">user_lookups</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">)</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">)</span></span><span style="font-size:8.0pt;font-family:"Courier New";color:gray"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:blue">as decimal</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">(</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">5</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:gray">,</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New";color:black">2</span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:gray">))</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:gray"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:blue">as</span></span><span class="apple-converted-space"><span style="font-size:8.0pt;font-family:"Courier New";color:blue"> </span></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family:"Courier New"; color:black">write_percent</span></span><span style="font-size:8.0pt; font-family:"Courier New";color:black"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:blue">from</span></span><span style="font-size:8.0pt; font-family:"Courier New";color:blue"><br /></span><span class="apple-style-span"><span style="font-size:8.0pt;font-family: "Courier New";color:black">sys.dm_db_index_usage_stats</span><o:p></o:p></span></p></span><p></p><p></p></span><p></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><br /></span></p><p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">I recycled the original query from </span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><a href="http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/324/Is-8020-a-90rsquos-Estimate.aspx" target="_blank"><span style="font-size:9.0pt;mso-bidi- Arial","sans-serif";font-family:";font-size:11.0pt;color:#114170;">http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/324/Is-8020-a-90rsquos-Estimate.aspx</span></a> <o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">After running this as a multi-server query against a group of 12 instances, I put the query results into a spreadsheet so I could see how individual servers stacked up against the group.<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">There was quite a bit of variation, but all the instances had more reads than writes.<span style="mso-spacerun:yes"> </span>The most read-skewed instance in the group did nearly 40 reads for each write, while the least read-skewed server only did 4 reads for every 3 writes.</span><span style="font-family: "Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p> </o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;">As a group, these instances were reading over writing at a 5 to 1 ratio: the 80/20 rule we hear so much about is alive and well on these instances.<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="679" style="width:509.25pt;mso-cellspacing:0in;mso-yfti-tbllook:1184;mso-padding-alt: 0in 0in 0in 0in"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td width="143" valign="bottom" style="width:107.25pt;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Instance</span></b><span style="font-family: "Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Reads</span></b><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Read Pct</span></b><span style="font-family: "Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Writes</span></b><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Write Pct</span></b><span style="font-family: "Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#254061; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:white;">Read/Write Ratio</span></b><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:1"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance01</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">5,299,601</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">73%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">1,961,846</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">27%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">2.7:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:2"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance02</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">35,440,282</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">91%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">3,570,652</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">9%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">9.93:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:3"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance03</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">9,083,137</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">79%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">2,475,755</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">21%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">3.67:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:4"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance04</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">27,181,812</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">64%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">15,568,632</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">36%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">1.75:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:5"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance05</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">11,438,327</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">72%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">4,384,756</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">28%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">2.61:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:6"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance06</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">391,095,544</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">94%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">27,148,899</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">6%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">14.41:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:7"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance07</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">295,659,557</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">57%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">220,249,874</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">43%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">1.34:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:8"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance08</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">488,649,390</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">97%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">13,016,100</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">3%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">37.54:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:9"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance09</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">47,661,242</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">87%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">6,896,585</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">13%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">6.91:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:10"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance10</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">63,179,128</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">80%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">15,418,897</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">20%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">4.1:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:11"> <td width="143" valign="bottom" style="width:107.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance11</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">195,456,589</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">87%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">28,239,952</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">13%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#DBE5F1; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">6.92:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:12"> <td width="143" valign="bottom" style="width:107.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">instance12</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">747,410,692</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">86%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">123,282,598</span><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">14%</span><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">6.06:1</span><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:13;mso-yfti-lastrow:yes"> <td width="143" valign="bottom" style="width:107.25pt;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in; margin-bottom:.0001pt;line-height:normal"><b><span style=" font-family:"Arial","sans-serif";mso-fareast-Times New Roman"; font-family:";font-size:12.0pt;color:black;">Group Totals</span></b><span style="font-family: "Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="120" valign="bottom" style="width:1.25in;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><b><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">2,317,555,301</span></b><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="75" valign="bottom" style="width:56.25pt;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><b><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">83%</span></b><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="125" valign="bottom" style="width:93.75pt;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span><b><span style="font-family:"Arial","sans-serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">462,214,546</span></b><span style="font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="81" valign="bottom" style="width:60.75pt;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><b><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">17%</span></b><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> <td width="135" valign="bottom" style="width:101.25pt;background:#BFBFBF; padding:0in 0in 0in 0in"> <p class="MsoNormal" align="right" style="mso-margin-top-alt:auto;margin-bottom: 0in;margin-bottom:.0001pt;text-align:right;line-height:normal"><b><span style="font-family:"Arial","sans-serif";mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;">5.01:1</span></b><span style=" font-family:"Times New Roman","serif";mso-fareast-Times New Roman"font-family:";font-size:12.0pt;"><o:p></o:p></span></p> </td> </tr> </tbody></table> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:0in;margin-bottom: .0001pt;line-height:normal"><span style="font-family:"Times New Roman","serif"; mso-fareast-Times New Roman";font-family:";font-size:12.0pt;color:black;"> </span></p></span><p></p></span><a target="_blank" href="http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/324/Is-8020-a-90rsquos-Estimate.aspx"></a>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0tag:blogger.com,1999:blog-10953016.post-1307547958308168482009-11-13T10:28:00.000-06:002009-11-13T10:29:11.535-06:00Finding Something Somewhere in a Database<p class="MsoNormal">I threw this ugly query together (at the bottom of the message) a few years ago (hence the ancient system table names) and recently stumbled across it in an old email I sent to myself. I figured I would post it here, since it’s a handy way to find a string in a database when you have no idea where to begin looking.</p> <p class="MsoNormal">I wrote it to find a string within a database I was unfamiliar with. You provide a search term to the script -- @searchString -- such as ‘%acre%’ (use the standard LIKE wildcards) in the example below, and it searches all of the char, varchar, nchar, and nvarchar columns in all of the user tables for your term. For my initial needs, all of the tables were in the dbo schema, so the script doesn’t handle table owner/schema. You can tweak it to your needs on that front. </p> <p class="MsoNormal">The result set gives you your initial search term, how many instances where found in which table, and the query used to generate the count for that table so you have a good starting place for further analysis.</p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">DECLARE</span><span style="font-size: 10.0pt;font-family:"Courier New""> @searchString <span style="color:blue">nvarchar</span><span style="color:gray">(</span>100<span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">SET</span><span style="font-size:10.0pt; font-family:"Courier New""> @searchString <span style="color:gray">=</span> <span style="color:red">'%acre%'<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">SET</span><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">NOCOUNT</span> <span style="color:blue">ON<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">CREATE</span><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">TABLE</span> ##search_results<span style="color:blue"> </span><span style="color:gray">(</span>result_table <span style="color:blue">sysname</span><span style="color:gray">,</span> search_term <span style="color:blue">NVARCHAR</span><span style="color:gray">(</span>100<span style="color:gray">),</span> result_count <span style="color:blue">bigint</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">CREATE</span><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">TABLE</span> ##search_queries<span style="color:blue"> </span><span style="color:gray">(</span>result_table <span style="color:blue">sysname</span><span style="color:gray">,</span> query_text <span style="color:blue">NVARCHAR</span><span style="color:gray">(</span><span style="color:fuchsia">MAX</span><span style="color:gray">))<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">DECLARE</span><span style="font-size: 10.0pt;font-family:"Courier New""> @tbl <span style="color:blue">sysname<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">set</span><span style="font-size:10.0pt; font-family:"Courier New""> @tbl <span style="color:gray">=</span><span style="color:blue"> </span><span style="color:gray">(</span><span style="color:blue">select</span> <span style="color:blue">top</span> 1 so<span style="color:gray">.</span>name <span style="color:blue">FROM</span> <span style="color:green">sysobjects</span> <span style="color:blue">as</span> so <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">syscolumns</span> <span style="color:blue">as</span> sc <span style="color:blue">on</span> so<span style="color:gray">.</span>id <span style="color:gray">=</span> sc<span style="color:gray">.</span>id <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">systypes</span> <span style="color:blue">as</span> st <span style="color:blue">on</span> sc<span style="color:gray">.</span>xtype <span style="color:gray">=</span> st<span style="color:gray">.</span>xtype <o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">where</span><span style="font-size:10.0pt; font-family:"Courier New""> so<span style="color:gray">.</span><span style="color:blue">type</span> <span style="color:gray">=</span> <span style="color:red">'u'<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:gray">and</span><span style="font-size:10.0pt; font-family:"Courier New""> st<span style="color:gray">.</span>name <span style="color:gray">in</span><span style="color:blue"> </span><span style="color:gray">(</span><span style="color:red">'char'</span><span style="color:gray">,</span> <span style="color:red">'varchar'</span><span style="color:gray">,</span> <span style="color:red">'nchar'</span><span style="color:gray">,</span> <span style="color:red">'nvarchar'</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">order</span><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">by</span> so<span style="color:gray">.</span>name <span style="color:blue">desc</span> <span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:gray"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">declare</span><span style="font-size: 10.0pt;font-family:"Courier New""> @sql <span style="color:blue">NVARCHAR</span><span style="color:gray">(</span><span style="color:fuchsia">max</span><span style="color:gray">),</span> @where <span style="color:blue">nvarchar</span><span style="color:gray">(</span><span style="color:fuchsia">max</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:gray"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">while</span><span style="font-size:10.0pt; font-family:"Courier New""> @tbl <span style="color:gray">is</span> <span style="color:gray">not</span> <span style="color:gray">null<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">BEGIN<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">set</span> @where <span style="color:gray">=</span> <span style="color:red">''<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">SELECT</span> @where <span style="color:gray">=</span> @where <span style="color:gray">+</span> <span style="color:red">' OR '</span> <span style="color:gray">+</span> <span style="color:fuchsia">quotename</span><span style="color:gray">(</span>sc<span style="color:gray">.</span>name<span style="color:gray">)</span> <span style="color:gray">+</span> <span style="color:red">' LIKE '''</span> <span style="color:gray">+</span> @searchString <span style="color:gray">+</span> <span style="color:red">''''</span> <span style="color:blue">FROM</span> <span style="color:green">sysobjects</span> <span style="color:blue">as</span> so <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">syscolumns</span> <span style="color:blue">as</span> sc <span style="color:blue">on</span> so<span style="color:gray">.</span>id <span style="color:gray">=</span> sc<span style="color:gray">.</span>id <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">systypes</span> <span style="color:blue">as</span> st <span style="color:blue">on</span> sc<span style="color:gray">.</span>xtype <span style="color:gray">=</span> st<span style="color:gray">.</span>xtype <o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">where</span> so<span style="color:gray">.</span><span style="color:blue">type</span> <span style="color:gray">=</span> <span style="color:red">'u'<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:gray">and</span> st<span style="color:gray">.</span>name <span style="color:gray">in</span><span style="color:blue"> </span><span style="color:gray">(</span><span style="color:red">'char'</span><span style="color:gray">,</span> <span style="color:red">'varchar'</span><span style="color:gray">,</span> <span style="color:red">'nchar'</span><span style="color:gray">,</span> <span style="color:red">'nvarchar'</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:gray">and</span> so<span style="color:gray">.</span>name <span style="color:gray">=</span> @tbl<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:green">-- get rid of the initial ' OR ' <o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">SET</span> @where <span style="color:gray">=</span> <span style="color:fuchsia">substring</span><span style="color:gray">(</span>@where<span style="color:gray">,</span> 4<span style="color:gray">,</span> <span style="color:fuchsia">len</span><span style="color:gray">(</span>@where<span style="color:gray">))<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:gray"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">SET</span> @sql <span style="color:gray">=</span> <span style="color:red">'SELECT '</span> <span style="color:gray">+</span> <span style="color:fuchsia">quotename</span><span style="color:gray">(</span>@tbl<span style="color:gray">,</span> <span style="color:red">''''</span><span style="color:gray">)</span> <span style="color:gray">+</span> <span style="color:red">' AS resultsTable, '</span> <span style="color:gray">+</span> <span style="color:fuchsia">quotename</span><span style="color:gray">(</span>@searchString<span style="color:gray">,</span> <span style="color:red">''''</span><span style="color:gray">)</span> <span style="color:gray">+</span> <span style="color:red">' AS searchTerm, count(*) AS resultsCount FROM '</span> <span style="color:gray">+</span> <span style="color:fuchsia">quotename</span><span style="color:gray">(</span>@tbl<span style="color:gray">)</span> <span style="color:gray">+</span> <span style="color:red">' WHERE '<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">INSERT</span> ##search_queries<span style="color:blue"> </span><span style="color:gray">(</span>result_table<span style="color:gray">,</span> query_text<span style="color:gray">)</span> <span style="color:blue">VALUES </span><span style="color:gray">(</span>@tbl<span style="color:gray">,</span> @sql <span style="color:gray">+</span> @where<span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:gray"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">SET</span> @sql <span style="color:gray">=</span> <span style="color:red">'INSERT ##search_results '</span> <span style="color:gray">+</span> @sql <span style="color:gray">+</span> @where<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">EXEC</span> <span style="color:maroon">sp_executesql</span><span style="color:blue"> </span>@statement <span style="color:gray">=</span> @sql<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">set</span> @tbl <span style="color:gray">=</span><span style="color:blue"> </span><span style="color:gray">(</span><span style="color:blue">select</span> <span style="color:blue">top</span> 1 so<span style="color:gray">.</span>name <span style="color:blue">FROM</span> <span style="color:green">sysobjects</span> <span style="color:blue">as</span> so <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">syscolumns</span> <span style="color:blue">as</span> sc <span style="color:blue">on</span> so<span style="color:gray">.</span>id <span style="color:gray">=</span> sc<span style="color:gray">.</span>id <span style="color:gray">inner</span> <span style="color:gray">join</span> <span style="color:green">systypes</span> <span style="color:blue">as</span> st <span style="color:blue">on</span> sc<span style="color:gray">.</span>xtype <span style="color:gray">=</span> st<span style="color:gray">.</span>xtype <o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">where</span> so<span style="color:gray">.</span><span style="color:blue">type</span> <span style="color:gray">=</span> <span style="color:red">'u'<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:gray">and</span> st<span style="color:gray">.</span>name <span style="color:gray">in</span><span style="color:blue"> </span><span style="color:gray">(</span><span style="color:red">'char'</span><span style="color:gray">,</span> <span style="color:red">'varchar'</span><span style="color:gray">,</span> <span style="color:red">'nchar'</span><span style="color:gray">,</span> <span style="color:red">'nvarchar'</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:gray">AND</span> so<span style="color:gray">.</span>name <span style="color:gray"><</span> @tbl<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">order</span> <span style="color:blue">by</span> so<span style="color:gray">.</span>name <span style="color:blue">desc</span><span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">END<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">SELECT</span><span style="font-size:10.0pt; font-family:"Courier New""> sr<span style="color:gray">.</span>search_term<span style="color:gray">,</span> sr<span style="color:gray">.</span>result_count<span style="color:gray">,</span> sr<span style="color:gray">.</span>result_table<span style="color:gray">,</span> sq<span style="color:gray">.</span>query_text <span style="color:blue">FROM</span> ##search_results <span style="color:blue">AS</span> sr <span style="color:gray">JOIN</span> ##search_queries <span style="color:blue">AS</span> sq <span style="color:blue">ON</span> sr<span style="color:gray">.</span>result_table <span style="color:gray">=</span> sq<span style="color:gray">.</span>result_table <span style="color:blue">ORDER</span> <span style="color:blue">BY</span> sr<span style="color:gray">.</span>result_count <span style="color:blue">DESC</span><span style="color:gray">,</span> sr<span style="color:gray">.</span>result_table <span style="color:blue">DESC<o:p></o:p></span></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt; font-family:"Courier New";color:blue">DROP</span><span style="font-size:10.0pt; font-family:"Courier New""> <span style="color:blue">TABLE</span> ##search_results<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New"; color:blue">DROP</span><span style="font-size:10.0pt;font-family:"Courier New""> <span style="color:blue">TABLE</span> ##search_queries</span></p>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com1tag:blogger.com,1999:blog-10953016.post-24028959101560372442009-11-13T10:21:00.000-06:002009-11-13T10:22:22.819-06:00Searching Cached Query Plans<p class="MsoNormal"></p><p class="MsoNormal">A quick way to search cached query plans (for missing indexes, table scans, etc.)</p> <p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;mso-layout-grid-align: none;text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New"; color:green;mso-no-proof:yes">--exec dbo.dba_SearchCachedPlans '%MissingIndexes%' <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;mso-layout-grid-align: none;text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New"; color:green;mso-no-proof:yes">--2. exec dbo.dba_SearchCachedPlans '%ColumnsWithNoStatistics%' <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;mso-layout-grid-align: none;text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New"; color:green;mso-no-proof:yes">--3. exec dbo.dba_SearchCachedPlans '%TableScan%' <o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;mso-layout-grid-align: none;text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New"; color:green;mso-no-proof:yes">--4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'<o:p></o:p></span></p> <p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;mso-layout-grid-align: none;text-autospace:none"><span style="font-size:10.0pt;font-family:"Courier New"; color:green;mso-no-proof:yes"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">DECLARE</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> @StringToSearchFor <span style="color:blue">VARCHAR</span><span style="color:gray">(</span>100<span style="color:gray">)<o:p></o:p></span></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">SET</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> @StringToSearchFor <span style="color:gray">=</span> <span style="color:red">'%<missingindexes>%'</span> <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";mso-no-proof: yes"><o:p> </o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">SET</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:blue">TRANSACTION</span> <span style="color:blue">ISOLATION</span> <span style="color:blue">LEVEL</span> <span style="color:blue">READ</span> <span style="color:blue">UNCOMMITTED</span> <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">SELECT</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:blue">TOP</span> 20 <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";mso-no-proof: yes">st<span style="color:gray">.</span><span style="color:blue">text</span> <span style="color:blue">AS</span> [SQL] <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">,</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> cp<span style="color:gray">.</span>cacheobjtype <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">,</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> cp<span style="color:gray">.</span>objtype <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">,</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:fuchsia">DB_NAME</span><span style="color:gray">(</span>st<span style="color:gray">.</span><span style="color:blue">dbid</span><span style="color:gray">)</span><span style="color:blue">AS</span> [DatabaseName] <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">,</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> cp<span style="color:gray">.</span>usecounts <span style="color:blue">AS</span> [Plan usage] <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">,</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> qp<span style="color:gray">.</span>query_plan <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">FROM</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:green">sys</span><span style="color:gray">.</span><span style="color:green">dm_exec_cached_plans</span> cp <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">CROSS</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:gray">APPLY</span> <span style="color:green">sys</span><span style="color:gray">.</span><span style="color:green">dm_exec_sql_text</span><span style="color:gray">(</span>cp<span style="color:gray">.</span>plan_handle<span style="color:gray">)</span> st <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:gray; mso-no-proof:yes">CROSS</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:gray">APPLY</span> <span style="color:green">sys</span><span style="color:gray">.</span><span style="color:green">dm_exec_query_plan</span><span style="color:gray">(</span>cp<span style="color:gray">.</span>plan_handle<span style="color:gray">)</span> qp <o:p></o:p></span></p> <p class="MsoNormal" style="margin-top:0in;margin-right:0in;margin-bottom:0in; margin-left:.5in;margin-bottom:.0001pt;mso-layout-grid-align:none;text-autospace: none"><span style="font-size:10.0pt;font-family:"Courier New";color:blue; mso-no-proof:yes">WHERE</span><span style="font-size:10.0pt;font-family:"Courier New"; mso-no-proof:yes"> <span style="color:fuchsia">CAST</span><span style="color:gray">(</span>qp<span style="color:gray">.</span>query_plan <span style="color:blue">AS</span> <span style="color:blue">NVARCHAR</span><span style="color:gray">(</span><span style="color:fuchsia">MAX</span><span style="color:gray">))LIKE</span> @StringToSearchFor <o:p></o:p></span></p> <p class="MsoNormal" style="margin-left:.5in"><span style="font-size:10.0pt; font-family:"Courier New";color:blue;mso-no-proof:yes">ORDER</span><span style="font-size:10.0pt;font-family:"Courier New";mso-no-proof:yes"> <span style="color:blue">BY</span> cp<span style="color:gray">.</span>usecounts <span style="color:blue">DESC<o:p></o:p></span></span></p> <span style="font-size:11.0pt;mso-bidi-font-size:10.0pt;font-family:"Calibri","sans-serif"; mso-ascii-theme-font:minor-latin;mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast;mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi; mso-ansi-language:EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA">Found on <a href="http://www.sqlservercentral.com/articles/Performance/66729/">http://www.sqlservercentral.com/articles/Performance/66729/</a></span><p></p>KenJhttp://www.blogger.com/profile/04010787505948843689noreply@blogger.com0