Tag: SQL202

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 share…

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 statistics…

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 or…

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 the…

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 optimize…

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, alter,…

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 data…

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 speed…

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.  All…