Not attending PASS Summit? Watch LIVE streaming events FOR FREE!

  By Michelle Ufford | October 29, 2010 - 12:10 pm | , , , ,
2 Comments

If you’ve not yet heard, the annual PASS Summit is less than 2 weeks away. This is the largest SQL Server and Business Intelligence conference _in the world_, sponsored by Microsoft and Dell. The return on investment of attending this conference is pretty huge, and I highly recommend you attend if you can swing it. I am once more fortunate to be attending and presenting at the Summit. Here’s where you can find me speaking throughout the week: Tuesday at 3PM Lightning Talk – Page Compression This year, PASS has decided to try something new. A daily Lightning Talk session will be held where speakers present for 5 quick minutes on interesting SQL topics. I’ll be presenting on Tuesday with 6 amazingly talented speakers. My topic is page compression — what is it, how to do it, and (most importantly, of course) how it affects performance. Wednesday at 11:30am in the ballroom Women-In-Technology (WIT) Luncheon I’ll be speaking on this year’s WIT luncheon panel, which is sponsored by GoDaddy.com. Contrary to common misconception, the luncheon is NOT just for women. In fact, men are encouraged to attend! If memory serves, last year’s luncheon had about 300 attendees, with a good … Continue reading →

Metadata for Table Valued Parameters

  By Michelle Ufford | October 28, 2010 - 8:10 am | , , ,
1 Comment

Table-valued parameters (TVP) are a great feature that was new in SQL Server 2008 that allow you to insert a dataset into a table. Previously, the most common way of doing this was by passing and parsing XML. As I’ve previously posted, TVP’s perform an astounding 94% faster than singleton inserts and 75% faster than XML inserts. But for some reason, TVP’s still aren’t widely used and understood. In this post, I’ll walk you through how to use these and how to query the metadata for TVP’s. I’ve previously posted about what TVP’s are and how to use them. But in honor of Halloween this week, I’ve updated my demo script: /* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATE Not Null , customer VARCHAR(20) Not Null   CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) );   CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product VARCHAR(20) Not Null   CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id)   CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) );     /* Create our new table types */ CREATE TYPE dbo.orderTable AS TABLE ( orderDate … Continue reading →

Rename Database Objects En Masse

  By Michelle Ufford | September 14, 2010 - 8:55 am | , ,
2 Comments

Ever need to rename all objects in a database? Yeah, okay… it doesn’t happen very often, but when it does, it can be time consuming. This recently came up as something I needed to do. When you consider all the tables involved and you add in defaults, indexes, and foreign keys, well… you can imagine how the number of objects adds up quickly. After doing a few tedious renames, it occurred to me that I could write a script to generate the rename scripts for me. Work smarter, not harder, right? For anyone interested, here’s the script. Select name , [object_id] , Case When [type] = ‘U’ Then ‘Execute sp_rename N”’ + name + ”’, N”old_’ + name + ”” When [type] IN (’D’, ‘PK’, ‘F’) Then ‘Execute sp_rename N”’ + name + ”’, N”old_’ + name + ”’, N”OBJECT”’ End As ‘renameScript’ , Case When parent_object_id > 0 Then 0 Else 1 End As ‘sortOrder’ From sys.objects Where [type] In (’D’, ‘PK’, ‘U’, ‘F’) Union ALL Select i.name , o.[object_id] , ‘Execute sp_rename N”’ + o.name + ‘.’ + i.name + ”’, N”old_’ + i.name + ”’, N”INDEX”’ As ‘renameScript’ , 0 As ‘sortOrder’ From sys.indexes As i JOIN … Continue reading →

See you in Seattle!

  By Michelle Ufford | July 16, 2010 - 7:34 am | , , ,
6 Comments

My Summit abstract was accepted! I’m still a little surprised, but I’m also excited (okay, and a little nervous) to once more be presenting at the PASS Summit. If you’ll be at Summit this year — and I really hope you are, as it’s well worth the time and cost — then please make sure to say “hi” if you see me wandering around. Aside from the *excellent* content, my favorite thing about Summit is getting to meet so many great people. 🙂 In other news, I’ve once more switched roles within GoDaddy. For the half dozen folks who’ve been following my blog from the beginning, you may remember that I originally started out on the traffic team working with tuning and VLDB’s, then took an opportunity to switch to the BI team to learn more about OLAP. Recently, a new team has been formed under the BI branch that’s tasked with developing a massive hybrid data warehouse (by hybrid, I mean half OLTP and half OLAP). “How massive is it?” Well, it’s SO massive, we’re expecting to be store petabytes of data when everything is said and done. I’m happy to say I’ll be on this new team. So … Continue reading →

SQL Saturday #50 – Call for Speakers

  By Michelle Ufford | June 21, 2010 - 2:04 pm | , , ,
3 Comments

The Call for Speakers is now open for SQL Saturday #50, the East Iowa SQL Saturday event! This is our second time hosting a SQL Saturday, and we’re hoping to build upon the success of last year’s event. We’re looking for a wide variety of topics on SQL Server and related technologies (i.e. PowerShell, R2, LINQ, etc.). We also have had several requests for intro-level topics, such as beginning disaster recovery and basic performance tuning. If you’re even remotely thinking about speaking, please submit an abstract! Last year we had about 100 folks attend from surrounding areas. This year, we’re shooting for 125 attendees, which would max out our facility’s capacity. Not sure how far away Iowa City is? It may be closer than you think. Allow me to rehash my travel times from last year’s plea for speakers: Chicago – 3.5 hours Omaha – 3.5 hours Milwaukee – 4 hours Kansas City – 4.5 hours Minneapolis – 5 hours St. Louis – 5 hours Indianapolis – 6 hours The event will be held on September 18th at the University of Iowa in Iowa City. You can find more information, including an abstract submission form, on our event website at … Continue reading →

Check VLF Counts

  By Michelle Ufford | June 8, 2010 - 9:03 am | , , ,
25 Comments

Today I stumbled across a database with 87,302 VLF’s. Yes, that’s right… 87 THOUSAND. Most of our databases have a few dozen VLF’s, but this was an old database that had grown to 1.5 TB and had the default autogrowth settings left in tact. How did we discover this? During a routine reboot of the server, this database took 30 minutes to recover, but there were no error messages or status messages in the log. Now, this blog post is not about VLF’s or why you should keep the number of VLF’s to a small, manageable number — although I hear under 50 is a good rule of thumb. No, the purpose of this blog post is to share a little script I wrote to check the number of VLF’s each database uses: Create Table #stage( FileID int , FileSize bigint , StartOffset bigint , FSeqNo bigint , [Status] bigint , Parity bigint , CreateLSN numeric(38) );   Create Table #results( Database_Name sysname , VLF_count int );   Exec sp_msforeachdb N’Use ?; Insert Into #stage Exec sp_executeSQL N”DBCC LogInfo(?)”;   Insert Into #results Select DB_Name(), Count(*) From #stage;   Truncate Table #stage;’   Select * From #results Order By VLF_count … Continue reading →

Index Interrogation for SQL Server 2008

  By Michelle Ufford | June 4, 2010 - 12:55 pm | , , ,
3 Comments

I had previously posted an index interrogation script for SQL Server 2005. I’ve updated that script for 2008; namely, it includes filtered index definitions. For anyone interested: Declare @objectID int = Object_ID(’Sales.SalesOrderHeader’);   With indexCTE(partition_scheme_name , partition_function_name , data_space_id) As ( Select sps.name , spf.name , sps.data_space_id From sys.partition_schemes As sps Join sys.partition_functions As spf On sps.function_id = spf.function_id )   Select st.name As ‘table_name’ , IsNull(ix.name, ”) As ‘index_name’ , ix.object_id , ix.index_id , Cast( Case When ix.index_id = 1 Then ‘clustered’ When ix.index_id =0 Then ‘heap’ Else ‘nonclustered’ End + Case When ix.ignore_dup_key <> 0 Then ‘, ignore duplicate keys’ Else ” End + Case When ix.is_unique <> 0 Then ‘, unique’ Else ” End + Case When ix.is_primary_key <> 0 Then ‘, primary key’ Else ” End As varchar(210) ) As ‘index_description’ , IsNull(Replace( Replace( Replace( ( Select c.name As ‘columnName’ From sys.index_columns As sic Join sys.columns As c On c.column_id = sic.column_id And c.object_id = sic.object_id Where sic.object_id = ix.object_id And sic.index_id = ix.index_id And is_included_column = 0 Order By sic.index_column_id For XML Raw) , ‘"/><row columnName="’, ‘, ‘) , ‘<row columnName="’, ”) , ‘"/>’, ”), ”) As ‘indexed_columns’ , IsNull(Replace( Replace( Replace( ( Select c.name … Continue reading →

Summit 2010 Abstract Submission

  By Michelle Ufford | May 28, 2010 - 12:02 pm | , ,
1 Comment

Jeremiah Peschka just tweeted about the looming deadline to submit an abstract for Summit 2010. I’ve been trying to think of a good topic to present on, and this finally got my butt in gear to submit one! For those interested, here’s what I submitted: Heaps of Trouble, Clusters of Glory – A Look At Index Internals Indexes are a crucial component of SQL Server, especially in performance tuning, yet many DBA’s don’t fully understand how indexes work. In this in-depth session, Michelle will examine the anatomy of indexes, from how they’re stored to how they get fragmented. And to make our inner geeks happy, she’ll look at page data to show you what’s happening behind the scenes. Topics covered will include index structure, fragmentation and defragmentation, index partitioning, and index filtering. If you’ve ever wondered just *what* goes on in an index, don’t miss this session! Special thanks to Chris for help with the session title. 🙂

DELETE 5_Useless_Things FROM [SQL Server]

  By Michelle Ufford | May 14, 2010 - 7:34 am | ,
18 Comments

It’s been a while since I’ve been caught up in a round of chainblogging, the blogosphere’s version of a Facebook meme. This time, Denis Gobo tagged me in a post started by Paul Randal. Paul asked us to list the “top-5 things in SQL Server we all wish would just be removed from the product once and for all.” I reviewed other posts, and the good and bad news is that they already listed several of the same things I would have. The good news is I’m apparently not alone; the bad news is that means I need to come up with something original! So while these wouldn’t necessarily be the *first* 5 on my list, they’d still be on the list nevertheless: Default Autogrowth Options Okay, so I lied. I’m not completely original. Yes, I know Paul Randal also commented on this one. While I said I would try to come up with only original ones, this one just has to be repeated. I’ve actually this option overlooked in production environments, resulting in thousands of VLF’s. It’s just a terrible default, and it needs to be changed. Edit Top 200 Rows This “feature” is just asking for trouble. Any … Continue reading →

Index Defrag Script, v4.0

  By Michelle Ufford | April 22, 2010 - 11:07 am | , ,
103 Comments

UPDATE: This script has been updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/. In my blog post, “Index Defrag Script Updates – Beta Testers Needed“, I stated “I’ll hopefully have the new version online in just a few days.” That was dated January 26th. I had every intention of following through with it, too, but something came up: My daughter, Chloe Lynn, was born on February 10th. She’s a happy, healthy baby girl who consumes all of my free time and already has both her parents wrapped around her adorable little finger. So while I apologize for the delay in posting the latest version, I hope you can understand and forgive me. 🙂 Alrighty, back to SQL stuff! This version of the script has been significantly overhauled from previous versions. Here’s a full synopsis of the changes and enhancements: – There’s now a time limit option so you have more control over how long your defrags run. This time limit is checked *before* a defrag is begun, so it’s still possible to have a defrag occur after the time limit is exceeded (i.e. a large index). – I’ve added a static table for managing the index defrag scans. This way, … Continue reading →