|
|
 This month’s T-SQL Tuesday, “T-SQL Tuesday #010: What are your thoughts on Database Indexes?” is hosted by Michael J Swart (Blog/Twitter) aka the Database Whisperer. The topic is: Indexes.
Riddle me this:
Which method is faster?
- Partitioning an entire table, that is currently not partitioned, based on rebuilding a clustered index
Or
- Moving data from a temporary table to an empty table that is partitioned based on the clustered index
We are currently testing both situations as we can’t logically figuring the fastest way based on theory. There have been lots of debates and lots of theories on which is going to hold up.
Why?
You may ask why would one care about the faster option, well, here’s the situation we are currently in:
One of our logging tables in our production environment has an ID column that is an int. This ID column is currently at about the 1,900,000,000 range – that alone makes me say YIKES! An int “only” goes up to 2,147,483,647, so we are approaching this mark fast. The solution that we are going with is to convert that column to a bigint data type. A bigint’s max value goes up to 9,223,372,036,854,775,807 – now that ought to do it! 
Our Options:
Here’s our current options with the basic steps to change the data type:
Option 1
- Drop all the indexes on the table
- Drop the partition schema
- Drop the partition function
- Alter table to the new data type
- Create the partition function
- Create the partition schema
- Recreate the indexes on new functions which will partition the table
- Test
Option 2
- Copy data to new table
- Drop existing table
- Drop the partition schema
- Drop the partition function
- Create the partition function
- Create the partition schema
- Create table and indexes
- Copy data back to original table
- Test
We’ve started a pool on what’s going to be the fastest. It’s all coming down to the indexing – leave a comment and let me know your educated guess and why. I’ll definitely be blogging more about this one!
This month’s T-SQL Tuesday, “T-SQL Tuesday #009: Beach Time” is hosted by Jason Brimhall (Blog/Twitter) aka the sqlrnnr. The topic is: What do you do as a DB professional to earn a little “Beach Time?” What do you do prior to “Beach Time” to ensure that the beach time will not involve work?
Most of my vacations involved traveling out of cell range, so my vacations are usually guaranteed to not be interrupted by work. My college buddies and I go on an annual trip to Canada’s Quetico Provincial Park, my parents have a cabin on the Gunflint Trail in northern MN. Being out of cell range is a bit more stressful than it sounds, but that’s why I’ve worked out a system to ensure things run smoothly when I’m gone. My system is all based on taking care of things before I leave the office because – I believe it was Ben Franklin who said, “an ounce of prevention is worth a pound of cure.”
First, I always let the people who I’m working with know I’m going on vacation weeks before. I tell them in meetings and email my boss once plans are firmed up and dates are set. One thing at my current job which I couldn’t stand at first, but now I find useful is that we send out meeting requests to each other saying when we are out of the office. The big keys to sending these out is to disable the reminder, set the time as free time and do not request responses. This way it’s on other’s calendar as a reminder, but that’s about it.
Next, and this is the hard one for me, at times, is I tell people no. No, I can’t get this done before I leave. No, I can’t make your meeting, I’m finishing up Project A. etc. I also decline all meetings and block out my calendar (set to busy) for the days I’m going to be gone.
Finally, I’m a big list maker. I usually list out what I’ve got to do everyday, when I’m going on vacation I’ll add a column to my list and put a secondary name by it. I go over everything I can with the secondary person, so they know what’s going on and if there is an issue while I’m away, they can be contacted. Because I’m a list maker, I’ve also got a checklist of “Things to do for Vacation”. Most of the items are for issues with specific environmental tasks around here, but the big one is to turn on my out of office assistant. I did forget once and had a few of upset users know the best way to get a hold of me is via email and I wasn’t responding.
These things don’t really take too much time to do. If you do these simple things, there will be not only less hassle when you get back in the office – making your vacation stick a bit longer, but also it will give you a chance to fully relax when your on vacation. It’s just an ounce of prevention!
This month’s T-SQL Tuesday, “T-SQL Tuesday #008: Gettin’ Schooled” is hosted by Robert Davis (Blog/Twitter) aka the SQL Soldier. The topic is: How do you learn? How do you teach? What are you learning or teaching?
I usually try to teach others the same way I learn – a bit of theory and lots of hands on examples. Mainly though I always try to “Keep It Simple” or “Keep It Simple, Stupid” – aka K.I.S.S. I’ve always loved the idea that a genius can take a complicated situation and make it simple. How many times do we take a simple situation and make it complicated? That’s kind of the opposite if you ask me.
The Setup
When I’m teaching someone I always remember the words of a football coach I had, Dennis Raarup. ”Keep It Simple – Stupid!” When he’d ask what coverage the defense was in, a hot shot young kid would say something complicated like “Smash – Man Free with a Monster”. Coach Raarup would yell back, “No – Are they chasing the receivers or checking off? If they are chasing them, it’s man coverage and we need to keep moving. If they are checking off, it’s a zone and our receivers need to settle in the holes.” That was his views on the passing game – it was that simple. He could take a complicated situation and break it down to running or settling – the rest was easy.
Back to SQL, when I’m teaching someone somthing, I always try to keep it as simple as I can. I always take the situation to a test server, create a database named Test, create a table named Table1 with column 1, column2, etc. I then create the other objects to mimic the situation’s trouble spot. It’s amazing what you can recreate quickly and easily on your local machine and how obvious the problem becomes when it’s table1 outer joined to table2 using column1 from both tables instead of the Incident table outer joined to Contact on SecondaryContactLastName and ContactLastNameID. Not only can you simplify complicated situations, but you can also identify and investigate the actual problem instead of getting wrapped up in the details of the real situation. Instead of “it doesn’t work”, you can explain that the process is missing a step or the code needs to include a where clause, etc.
Another benefit to working on a test server in a separate database is as I like to say “Russian roulette is not the same without a gun“. That means that it’s a much safer environment and if you mess up – which is a great way to learn – you won’t stop 30 application developers from meeting their deadline. I always try to build up the scripts and save them so if something goes awry, you can drop your test database and easily recreate the situation. Here is an example of the start of one:
/*
--------------------------------------------------------------------------------
-- Test database create scripts --
-- --
-- Note this DROPS the database and recreates it --
-- --
-- Created by Andy Lohn of www.sqlfeatherandquill.com --
--------------------------------------------------------------------------------
*/
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test')
DROP DATABASE [Test]
GO
CREATE DATABASE [Test]
GO
Use [Test]
Go
Create table Table1 (column1 int identity(1,1),
column2 datetime)
Go
Insert Table1 (column2) Select GETDATE()
The Payoff
I recently used this technique with a soon-to-be junior DBA here. He was running some tests trying to get test data ready. I showed him how to randomly create data and populate a column (this situation inspired this random data post). I didn’t give him too many specifics, but showed him the idea. About seven emails later that day from him – he was quite excited, he had figured out he could set up all his test data in multiple tables like this and what normally took him a day of manual data entry, now took him less than a day to do initially and now will take him less than an hour for any test coming up. Situations like this is why I started this blog – to help people do things I know are possible and I know will help. This is also why I need to teach and have an impact on people – like Coach Raarup had on me.
 Coach Raarup
The SQLChicken, Jorge Segarra, (blog/twitter) is hosting this months T-SQL Tuesday. It’s called “T-SQL Tuesday #007: Summertime in the SQL”. The question is “What’s your favorite hot new feature in the R2 (I’ll be nice and include 2008 in general) release?”
Well, the answer to the question is simple – the merge statement…and IntelliSense…and the resource governor…and compression both for data and backups. Okay, there are lots of good changes in SQL 2008 and I’ve seen lots of changes in my day.
In my old guy voice I say, “Young DBA’s these days, don’t know what it’s all about. Things are easy, point and click, drag and drop, right click, create script, etc….”
Back in my day…when we want to move data, we would have to create update statements, insert statements (usually based on a outer join where the joining column was null) and delete statements. There wasn’t any such thing as a merge statement. And we used to have to type these things out by hand, or copy and paste them from a result set from sysobjects – there was no IntelliSense. The way I used do it was to create temp tables, stage the data usually by creating a script in Excel and copying it for all the rows in the table. We had to write out Insert #TempTable1 Values (<A1>, <A2>…) then on the next line it would be Insert #TempTable1 Values (<B1>, <B2>…). There was no Insert #TempTable1 Values (<A1>, <A2>…), (<B1>, <B2>…). I usually had my standard of #TempTable1 would be updates, #TempTable2 would be inserts and #TempTable3 would be deletes. There wasn’t any table variables either, so we always had to drop the temp tables – or TempDB might fill up during maintenance.
Back in those days, there was no auto file grow either. There better be enough room in the database files for everything to fully complete or things were going to fill up and be left in an incomplete state.
There was no resource governor either, we scheduled index rebuilds, data imports, data cleanup tasks for off hours – when the systems weren’t expected to be online.
Back in the early days, there were no DDL triggers either, there was no SOX, so there wasn’t as much of a need to have DDL triggers. If someone needed a change in production, the green play button on the procedure you were looking at would change it. There wasn’t a “Generate Change Button” for tables and columns either, we did it the same way we did it in the development environment – by hand via the GUI.
Even creating databases, we had to create the devices first, then the database and we had to back them up to backup devices (that actually made restores simpler as the file name was always the same). There was no compression built into SQL. For large backups, we used 3rd party tools and then we’d include the tool in the backup directory and put it to tape. If we ever had to restore the backups, we would have the tool on the tape as well – who knows how many licensing agreements we broke by doing that.
When trouble shooting issues, we used to have keep hitting the refresh button in activity monitor to see if anything had changed – there was no auto refresh. There were no filters in it either, so we had to look through all the connections to the server.
If we found a performance issue, we didn’t have an execution plan to go off of, much less SQL didn’t tell us what indexes were missing! I always used the “Set Statistics IO” option and looked at the logical reads.
Things have changed a lot since SQL 6.5, but the bottom line is that a table is still a table, a column is still a column and an index is still an index (although those are changing)
I’m writing this in response to Adam Machanic’s – T-SQL Tuesday - #001: Date/Time Tricks.
Here’s the a very useful script I still use all the time. I created it when I was working on a reporting project – it simply displays all the different date formats in SQL server.
--If you are looking to display dates, this script is a must!
---it prints out all the date formats and you can choose
SET NOCOUNT ON
Declare @TList table (
[TheDate] varchar(50) NOT NULL,
[Style] [int] NOT NULL)
--This will take care of 0 - 14
declare @counter int
set @counter = 0
while @counter < 15
begin
Insert @TList Select convert(varchar(50), Getdate(), @counter), @counter
set @counter = @counter + 1
end
--This will take care of 100 - 114
declare @counter2 int
set @counter2 = 100
while @counter2 < 115
begin
Insert @TList Select convert(varchar(50), Getdate(), @counter2), @counter2
set @counter2 = @counter2 + 1
end
Select * from @TList
|
|
