Site: http://www.sqlskills.com/BLOGS/KIMBERLY/ Link: http://www.sqlskills.com/BLOGS/KIMBERLY/syndication.axd
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 5/21/2010 7:59:00 PM
It's official! We're doing another 5-day SQL Immersion Event (our ever-popular 5-day Deep Dive into Internals, Performance and Maintenance) this year in the US! We decided to have a class near Seattle because Washington is *fabulous* in August! The class will cover our best content in these areas: On-disk structures: how the data is stored Index internals: how the data is organized Logging and recovery: how the data is protected Choosing the RIGHT Data Ty ...
[ read more ]
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 5/18/2010 8:33:00 PM
OK, I first blogged about re-writing sp_helpindex here as sp_helpindex2 (April 2008). Shortly thereafter (Aug 2008), a reader found a bug and when I went digging, so did I (a couple of others). I did a few tweaks and the version that's stuck for the past couple of years is in this blog post: Updates (fixes) to sp_helpindex2. However, as much as I've used that - I've always been frustrated by its output. And, so, over the months+, I wrote sp_helpindex3 (this solely added the index ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 5/16/2010 9:17:00 PM
As a follow-on discussion to my SQL Server Magazine Q&A titled “Determining the position of search arguments in a join” where I was responding to a reader question about whether or not search arguments could be moved up into the FROM clause instead of placed in the WHERE clause and whether or not it would improve performance. While I was so focused on creating a scenario to highlight the problems when doing this, I missed a great point about what happened in my specific exa ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/22/2010 5:50:00 PM
I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL generaters/ORM/LINQ, etc.) can start to consume too much plan cache and have problems with "single-use plans" in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an option/feature specifically to help reduce the bloat on the plan cache by only storing a query plan hash the first time a plan is created. Fi ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/21/2010 7:18:00 PM
OK, so, this is where we're at... When a client application makes a request to SQL Server there are a few ways in which they can do it: Submit an adhoc transact-SQL statement This statement can be parameterized and the parameterized statement can be deemed: Safe: These are statements where the optimizer has evaluated the plan and feels that changes to values will not affect the execution plan. And, there are quite a few rules that you must meet for ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/19/2010 11:37:00 AM
Last week we completed another SQL Server Magazine/SQLConnections conference and already, it's time to start planning for the Fall show. Once again, Paul and I will be managing the SQL side of the conference and we'd like to invite you to submit abstracts for sessions at the conference. The Fall 2010 SQL Connections conference will be held in Las Vegas from November 1st through 5th (2010). The conference will focus heavily on SQL Server 2008. Abstracts are still welcome on best ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/15/2010 1:28:00 PM
I started this series with the post titled: Little Bobby Tables, SQL Injection and EXECUTE AS. I then moved to discussing some of the differences with the post titled: EXEC and sp_executesql - how are they different? Today, I want to address a few of the comments as well as continue with a few tips and tricks using these commands. First off - could we have helped the performance of the sp_executesql statement? Yes... If we know that a statement returns a varying amount of data (base ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/5/2010 9:51:00 PM
In my last post: Little Bobby Tables, SQL Injection and EXECUTE AS, I wanted to highlight how to avoid SQL Injection when using EXEC. A few people brought up the point that I could have avoided SQL Injection by using sp_executesql. And, while sp_executesql can be used in some cases, it's not always the most ideal from a performance perspective. So, to really show this, I'm going to start with focusing on the similarities and differences of EXEC and sp_executesql. I'll ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/4/2010 4:42:00 PM
OK, I know many of you have seen this before (an oldie, but a goodie!): (image from xkcd.com, with "copy and share" license described here: License) But, what can you do to prevent this? And, when would this even be possible? This is possible when DSE (dynamic string execution) occurs. There are still some VERY relevant and important reasons to use DSE and some are performance related (ok, this is another post for another day) but suffice it to say - I use DSE b ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/2/2010 8:48:00 AM
In our Boston SQL Training workshop today we're discussing disaster recovery and minimizing data loss. One of the key discussions is around backup/restore so we starting discussing resources and found that there just isn't a great single place where a bunch of resources are listed (ok, maybe there is but we didn't find one) so, while Paul's (blog|twitter) lecturing for a bit - I thought I'd put together a list for you to use! Webcasts to learn more about backups and ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 9/24/2009 9:45:00 AM
Time flies when you're having fun, eh? This week Paul and I are in Dublin, Ireland delivering a deep technical "SQL Immersion Event" on Internals, Performance Tuning and Maintenance with our partner ProData (and having a blast - we have a really fun and very interactive group). And, today's an even more interesting day to be in Dublin as it's the 250th anniversary of the Guinness Brewery...everyone around Dublin will be toasting the most popular drink in Ireland ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 8/10/2009 7:10:00 AM
OK, for years I've been saying that SQL Server doesn't care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. In both the fixed-width portion of the row as well as the variable-width portion of the row, the columns are defined in the order in which they are declared. So, what does matter? It's all in the cost of the variable array' ...
by kimberly.nospam@nospam.sqlskills.com (Admin) via Kimberly L. Tripp on 7/10/2009 3:58:00 PM
Paul and I will be teaching a week-long public Immersion Event in Dublin, September 21-25, in partnership with our good friends at Prodata and Microsoft Ireland. The class will cover: Day 1: SQL Server Internals (On-disk structures, index internals, logging, recovery, transaction log architecture) Day 2: Designing for Performance (data types, table and index partitioning) Day 3: Indexing for Performance (Access patterns, covering, INCLUDE, indexing strategies ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 6/30/2009 8:34:00 AM
I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters (fyi - using QUOTENAME can be a fantasic solution to protectng identifiers as input parameters but it can't protect more complex strings). Having said that, what if I'm looking a ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 6/7/2009 9:29:00 PM
OK, I'll definitely take a beating from all of you for having gone so long between my survey posts and now. I won't even go into the details but between some crazy work schedules, multiple sinus problems and even migraines... well, I've been a bit behind. Let's just say that April/May were rough at best. I'm feeling better and well, now I'm trying to catch up. I had really gotten the blogging bug in March but I completely lost it in April. But, this tipping point series ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 5/7/2009 9:55:00 AM
I've seen (and heard of a few other cases where people received) the following error: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. And, the problem (when I got this error) was the compatibility mode of the database I was in didn't support what was being attempted. In SQL Server 2005, the reports used to generate an "Incorrect syntax near '('." error. For what I was trying to do (execute/access some of ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/12/2009 11:20:00 PM
Recently, the SQL Server 2008 Internals title was released (and only in the past few days have people actually received their copies)! In fact, I still haven't seen the book in person... soon though! Anyway, Kalen, Paul, Conor, Adam and I worked to create a comprehensive resource on SQL Server 2008 internals and to supplement the written content, many of us created demo scripts and examples. I've now gone back and created a sample script based on ALL of the code in the enti ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/10/2009 3:28:00 PM
If you haven't seen Paul's weekly survey, please take a look. We're trying to get a feel for the general state of database structures out there... Here's the link: http://www.sqlskills.com/BLOGS/PAUL/post/Weekly-survey-does-size-really-matter-or-is-it-what-you-do-with-it.aspx. THANKS and have a great weekend! kt ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 4/10/2009 10:52:00 AM
A couple of weeks ago I wrote a blog post titled Whose job is it anyway? It's an interesting debate and something I've been hearing more and more - that SQL Server is a "set it and forget it" technology - a black box where you just don't need to know how it works to do well with it. In fact, I've even had a few folks comment that they think it would be better to "roll their own" database rather than have to learn how to work in a "general purpose&qu ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/27/2009 9:33:00 AM
I started the series here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Spring-cleaning-your-indexes-Part-I.aspx and I want to continue with Part II today by clarifying some great questions/comments that have come up on the series. In Part III, I'll give you a few more ways to get rid of (or consolidate) indexes. And, I think there's still a bit more that Paul and I will investigate further (wrt to operational stats) but, I want to address a few comments and a few interesting thing ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/27/2009 8:47:00 AM
This morning, I woke to a wonderful email and I thought I would share it: Good morning Kimberly, I just wanted to send you an email to let you know how awesome you are! I have applied some of your indexing strategies to our website’s homegrown CMS and we are experiencing a significant performance increase. WOW....amazing tips delivered in an exciting way! Thanks again, Jim OK, I seriously can't complain about the awesome part ;-)... but, what I think is s ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/23/2009 2:14:00 PM
First and foremost, happy spring! I truly hope we're on the path to summer (although who would know it here - we're in Florida for SQL Connections and the weather is a bit chilly and it's been raining off/on today - I hope this is short term (no, I don't want to look at the forecast as I don't want to jinx it :)). But, wherever you are - I hope you're on your way to nicer weather and minimal cold weather (ok, I guess I only have warm wishes for the northern hem... for yo ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/15/2009 8:17:00 PM
OK, I was tagged by Ward Pond here. Ward was tagged by Jimmy May here. Jimmy was tagged by Kevin Kline here. Kevin was tagged by Chris Shaw here. Chris was tagged by Michelle Uford here. And, Michelle was tagged by Mike Walsh here. Ah... at least I know who to blame. Ah, thanks Mike! ;-) So, as for some things I know now that I wish I knew then and/or what I value the most. First, I took a peek at a lot of the other posts. There are some really good tips and tricks in all of them and so i ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/15/2009 8:11:00 PM
We've opened up the call for abstracts for the FALL Connections show and Paul's already blogged all about it. Because most of you read both of our blogs, I won't repeat it here but - if you don't read Paul's blog (ah, really - you don't? Why not?? ;-)) then here's the link: http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Connections-Fall-2009-Call-for-Abstracts.aspx. Thanks and we look forward to your submissions! kt ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/14/2009 2:23:00 PM
This is a tough topic. It's a big topic and more than any other - I think there are a lot of misunderstandings about what the log is for, why it's so critical and ESPECIALLY when/why it gets extrememly large. Simply put, it gets large when it's not managed correctly. OK, there are times when it can become large - even if it is well managed. But, more often than not, when a transaction log is wildly out of control (orders of magnitude larger than the data itself) it indicates a manag ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/13/2009 9:45:00 AM
When you decide to rebuild or reorganzie an index, you have an option to set something called FILLFACTOR. This is probably the MOST IMPORTANT thing to understand about index maintenance and reducing fragmentation (especially in databases that are prone to it). Unfortunately, we need to dive into some internals to really understand why this is so helpful... What is FILLFACTOR? FILLFACTOR defines how full the leaf level pages of the index are filled when rebuilding or reorganizing an index. ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/12/2009 4:01:00 PM
OK, it seems as though there's A LOT of confusion about what steps are required for proper database maintenance. And, it seems as though some recommendations are being given as "quick fixes" without any real recommendation for root cause analysis. I'm not saying that the generalizations are horribly wrong but in many cases they're just too broad and/or unspecific to actually be useful (and, well, in all honesty, some of them are just really bad recommendations because they ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/9/2009 8:28:00 AM
OK, so this is interesting. I've got a few answers to my last survey (Tipping Point Query #1) and well, there's a good mix of answers (and, yes, some are correct! ;)). Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. So, now I want to see if people really know the basis of "the tipping point". Try these two: Tipping Point Query #2 Table1 (t1) has 1 million rows at 100 rows per page. The table ha ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/8/2009 5:29:00 PM
Along the same lines of improving database design and getting better performance on SQL Server (which [IMO] DOES take an experienced SQL Server database developer - but, we'll talk more about "whose job this really is" in many more posts and probably even a RunAs - which Richard and I just setup to record on Thursday (Mar 12)), I started thinking about how I could convince people of why they NEED a database developer. So, I thought I'd ask this VERY important question... Wh ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/5/2009 1:06:00 PM
Expanding on the topic of "are you kidding me"... one of the MOST PREVALENT problems I see today is the dreaded "GUIDs as PKs" problem. However, just to be clear, it's not [as much of a] problem that your PRIMARY KEY is a GUID as much as it is a problem that the PRIMARY KEY is probably your clustering key. They really are two things BUT the default behavior in SQL Server is that a PRIMARY KEY uses a UNIQUE CLUSTERED INDEX to enforce entity integrity. So, I thought I' ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/5/2009 1:30:00 AM
Well, today has definitely been enlightening. I've been around and around with a few folks (mostly Tim Huckaby and Richard Campbell - who are BOTH great friends and people whose opinions I greatly respect!!!) and I've come a conclusion... Paul started down this line of commentary here: http://www.sqlskills.com/BLOGS/PAUL/post/Mandatory-SQL-breathalyzer-test.aspx and this was in reference to something I was saying in our long thread... But, where I'm at is somewhere that I ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/4/2009 3:03:00 PM
**** UPDATE March 17, 2009 **** Be sure to read the comments. This doesn't appear to be as bad as it seemed...still glad I did the rant though; I learned more about what it's actually doing! :) And, it's still good to have good database design - NO MATTER WHAT! **** OK... today's been a bit of a weird day. I've been doing research for some upcoming events and in general, I'm learning a few things. However, as one does with the web - you can g ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 3/1/2009 2:17:00 PM
It's an exciting year for us for DevConnections! SQL Server 2008 has now been out for a few months and an SP is coming up soon. This is the sign that some customers wait for to migrate over to the new release feeling that an SP indicates a higher level of stability. But, this is also a time when some companies are shying away from upgrades because of the immediate and very quantifiable costs. And so Paul and I really struggled with what to focus on when we put together our Connections line- ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/28/2009 5:37:00 PM
Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez - who, unfortunately, does not have a blog or anything...yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index - one without a PRIMARY KEY or UNIQUE key constraint. At first glance this might seem like something relatively insignificant but in terms of reducing indexes and/or consolidating indexes it offers somethi ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/24/2009 1:30:00 PM
Given the general state of the economy...many companies are looking to cut back. Going back over what we've done and "optimizing" things -> budgets, expenses, etc. is the norm right now. And, scaling back is not always a bad thing - unless the wrong things are cut. Unless the wrong things are used to motivate you. Prioritizing and/or really assessing what gives you the biggest gains for your dollars is hard. In fact, one of the things that always seems to be first on the cuttin ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/13/2009 4:54:00 PM
Paul and I started discussing a comment that came up regarding the many issues surrounding logging & recovery. It's one of our favorite topics and in fact was the title to an article that Paul recently wrote for TechNet here: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx. And, as a sidenote, depending on how much you already know about the transaction log - you might want to review that article first! The comment that came up was related to a common misunderstandin ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/13/2009 10:54:00 AM
Paul and I started discussing a comment that came up regarding the many issues surrounding logging & recovery. It's one of our favorite topics and in fact was the title to an article that Paul recently wrote for TechNet here:http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx. The comment that came up was related to a common misunderstanding on what is and what is not required to make a backup transactionally consistent when restored. And, in my opinion, some of the conf ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/5/2009 12:24:00 AM
I know where I go but where the heck does the time go (it seems to move much faster than I)? First off, I hope everyone had a truly lovely holiday season. I know they've long since past for everyone but maybe this reminder will bring back a nice memory from the holidays as it does for me. Paul and I escaped the world of SQL and focused on finding frogfish. Unfortunately, it just wasn't the right time of year to find them though. Oh well - we did see some very coolstuff including a batfi ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 11/23/2008 5:34:00 PM
Well, this has been a great "conference season" but I'm also glad that it's over for a bit. In the past 4 weeks, Paul and I have been to Vienna (check out Paul's post here), Barcelona (for TechED EMEA ITPro), Las Vegas (for SQL Connections) and finally to Seattle (for PASS). We live in Redmond - about 15 miles from Seattle - so, this was a nice and rare "conference at home" but I have to admit that a conference "at home" is even a bit harder than one on ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 11/14/2008 6:06:00 AM
This is something that both Paul and I want to research more but, there have been a lot of questions about this recently. So, I thought I'd pull together a quick post of some important resources if you're interested in virtualization. 1) You should know what the support implications are - Bob Ward (Microsoft PSS Principal Escalation Engineer extraordinaire) - has blogged about the support implications here: SQL Server Support in a Hardware Virtualization Environment 2) You should ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 11/13/2008 6:42:00 AM
Way back in June 2005, I blogged about '8 Steps to better transaction log throughput'. I did this blog post after seeing (again and again) overly fragmented transaction logs... Transaction logs can become *VERY* fragmented when they are not preallocated and instead they grow excessively through unmanaged (and probably the default settings for) auto-growth. While having WAY too many VLFs because of auto-growth is still the most common form of problem within transaction logs, anot ...
by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 11/9/2008 9:24:00 AM
There's a new feature of SQL Server 2008 that during beta was called a Configuration Server... In RTM it was renamed to a Central Management Server. It's not overly obvious and without knowing how cool it can be - you might skip right by it. And, if you're using SQL Server 2008 to manage 2000, 2005 and 2008 servers - this actually works for all of those (and can be quite cool). In the "Registered Servers" pane, expand the "Database Engine" option to see this f ...
by kimberly.nospam@nospam.sqlskills.com (admin) via Kimberly L. Tripp on 8/24/2008 1:23:29 PM
I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and for SQL2008 it also adds the filter predicate. So, there were two versions of sp_helpindex2 depending on which verison you're using. A lot of folks like the changes to this sp but, alas, it had a bug (or two :) and in fact, I found a few others when I went back over this as well. So, thanks to Jo ...
by kimberly.nospam@nospam.sqlskills.com (admin) via Kimberly L. Tripp on 8/24/2008 1:12:29 PM
YES!!! OK, well, I guess I should be more specific because as in most things in SQL Server, the real answer is "it depends". And for these two options, it depends mostly on your SQL Server version. Since SQL Server 7.0, the way that auto update works, has changed (much so for the better!!). So, if you're in SQL Server 2005 or SQL Server 2008, I would say most definitely - leave these ON (or if you turned them off - turn them back on!!!)! If you still have problems with a specific index causing y ...
The content of the postings is owned by the respective author. SQL Feeds is not responsible for the contents of the postings. This site is automatically generated and cannot be reviewed for abusive content. If you find abusive content on SQL Feeds, please contact us. Designated trademarks and brands are the property of their respective owners. All rights reserved.
