SQL 202

This is the second DBA course. As I add articles, they’ll appear here.

• SQL 202-Locking and Blocking Posted on: 20110124
    At a recent Charlotte SQL Server Users Group meeting, one member asked what’s the difference between locking and blocking.  I figured I’d cover that today. Locking When you work in your database, you’re not alone.  There are other users connected hitting the same database as you, the same tables as you.  You have to [...]
• SQL 202-Interpreting your Execution Plan Posted on: 20110119
    OK, I’ve explained how you can see the execution plan for your query (either the actual or the estimated).  So now you can see your plan…Now what?  What do you want to look for?  Here’s the plan for the query I was running when I started this post.  I was looking at the IO [...]
• SQL 202-Indexes with WHERE clauses Posted on: 20110117
    So I have this table in my new database that is used as the base table for more than 50% of the queries that are run on my server.  It’s our product table.  Along with all the basic information about products there’s a column that hold the status of that product, it’s either active [...]
• SQL 202-Covered Indexes Posted on: 20110112
    By this point you should feel pretty comfortable with what an INDEX is and what it does for your queries.  Basically it provides the server to find your data rows more quickly by creating a look-up to find key values.  When teaching indexes I usually open the book closest to me, and show them [...]
• SQL 202-Theoretical Exection Orders Posted on: 20110110
    When you send a query to the server, there’s an order to the steps the server will take to retrieve the data you’re looking for.  It’s important to have an understanding on what order those steps are, so you know where to start making changes in order to minimize the time it takes to [...]
• SQL 202-Performance Tuning Posted on: 20110105
    Honestly, choosing which level to put this content under was one of the harder decisions I had to make when putting this content together.  Before you get into this stuff, You need to understand queries pretty well.  You should also have the fundamentals on what an index is, and how to manage them (add, [...]
• Modifying Data in Partitioned Views Posted on: 20100825
    Yesterday I covered the basics of partitioned views.  I also mentioned there were some gotchas when it comes to modifying the data in those partitioned views.  I’d like to go into more detail about that today.  The first big gotcha on updating data in a partitioned view is making sure you can update the [...]
• Partitioned Views Posted on: 20100824
    Last week I covered partitioning.  I explained how you could set up filegroups in your database, and then split data from a single table or index across those filegroups.  That way you can reduce blocking in your objects by physically separating the data into parts.  I even covered how you could use partitioning to [...]
• Partitioning: MERGE, SPLIT, and SWITCH Posted on: 20100820
    OK, you know how to set up partitioning for a table and an index, and now you think you’ve solved your deadlocking, and your I/O issues forever, right? Hardly. Once you’ve been in SQL long enough you learn that no answer is forever.  Inevitably you’ll have to revisit your old queries and see if [...]
• Partitioned Tables and Indexes Posted on: 20100819
    Ok, at this point you should know how to set up a PARTITION FUNCTION and a PARTITION SCHEME.  Now, we’re going to move on and apply this SCHEME (and in turn the FUNCTION) to a table and an Index.  I’m going to include a little recap, just in case you’ve missed anything so far.  [...]
• Partitioning Posted on: 20100818
    Partitioning was a feature added with Microsoft SQL 2005 to allow users to split up large tables across multiple storage locations.  Partitioning can be applied to tables, indexes and indexed views.  By partitioning the data across multiple locations you can speed up query times, reduce contention between queries, and improve overall performance under certain [...]
• Partition Schemes Posted on: 20100813
    In my last article I started covering partitioning.  I’m going to pick that up and continue with how to create your database to use multiple filegroups.  Without multiple filegroups, you aren’t going to see how partitioning can really improve your database’s performance.  Let’s set up the database that’s going to house our table of [...]
• INDEX ON partition_scheme_name, filegroup_name, and default Posted on: 20090601
    Consider the CREATE INDEX statement below: CREATE INDEX ix_tableName_columnName ON tableName (columnName) ON { partitionSchemeName (columnName) | fileGroupName | default } What’s it all about? Let’s take these one at a time, and dig in a little bit. partitionSchemeName (columnName) Before you can use this option, you have to have your partition scheme defined. [...]
• SET IDENTITY_INSERT Posted on: 20090420
    Let’s say you have the following table on two servers, one in development and one in production. CREATE TABLE person ( personID INT IDENTITY(1,1) , firstName VARCHAR(50) , lastName VARCHAR(50) ) You’re about to implement a new query, but you need to test it out in development before you move it to production.  The [...]
• Disabling Constraints Posted on: 20090413
    Constraints are great for maintaining the integrity of the data in your database. I’m not a big fan of running your database without them. It just opens up the door for too many problems. But there are a few times where you’re going to need to be able to disable them. Let’s say you’re [...]
• SQL 202 - FILLFACTOR and Indexes Posted on: 20090406
    FILLFACTOR specifies the percentage for how full the Database Engine should make the leaf level of each index page during index creation or rebuild. FILLFACTOR must be an integer value from 1 to 100. The default is 0. If FILLFACTOR is 100 or 0 (MS SQL treats these the same), the Database Engine creates [...]
• NULL Values, Blank Values, and Table Design Posted on: 20070601
    Have you dealt with NULL yet? A field is referred to as NULL if it has not received a value. To specify the NULL-ability of a column using T-SQL, just add NULL after the datatype for the column. To specify that values are required, add NOT NULL. If you don’t specify NULL or NOT [...]
• Comparing Query Performance Posted on: 20060115
    A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution [...]
• A Better sp_who2 Posted on: 20051230
    If you’ve ever needed to work with sp_who2 to figure out what’s going on, then you know it would be nice to be able to sort the results, right? Well, if you use the following code, then you’ll get exactly that functionality.  Enjoy!   CREATE TABLE #who2 ( spid INT , [status] SYSNAME , [...]
• Incomplete Tables Posted on: 20051212
    One of the things I’ve noticed while performance tuning is many of the tables in my current environment are incomplete.  They all have columns defined, and they all have data, but they’re still missing something. Many are missing a primary key.  You don’t always have to have a primary key, but when you’re joining [...]
• Quickly Disconnect Users From a Database Posted on: 20050520
    Disconnect All Users of a Database There comes a time in every DBA’s life when he or she has many users connected to a database that needs to be detached, placed in single user mode, or simply refreshed.  The problem is they keep reconnecting more quickly than you can KILL them off.  This is [...]