Voice of the DBA

Tag: syndicated

  • Five Lines of Code

    An interesting story on how a database speeds up by changing some application code. Worth the read for sure.

    I caught this from Andy Warren’s blog, and it shows why we need developers to think for themselves and ask relevant questions, not just meet the spec. If someone had asked if Accuweather needed micro-granularity for location earlier, they might never have had issues.

  • Setting a Unique Index on a Bit Field

    Can you set a unique index on a bit field? Well, you can, but you’d end up with a very short table of two (or three) rows. I defined this table:

    CREATE TABLE [dbo].[BitTest](
    [MyBit] [bit] NULL,
    [MyName] [varchar](50) NULL
) ON [PRIMARY]

GO

USE [db1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_BitTest] ON [dbo].[BitTest] 
(
    [MyBit] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

    and then added some data

    INSERT BitTest SELECT 1, 'Test'
INSERT BitTest SELECT 0, 'Test2'
INSERT BitTest SELECT NULL, 'Test 3'

SELECT * FROM BitTest

    This table has these rows:

    bittest2

    Adding another row:

    INSERT BitTest SELECT 1, 'Test 4'

    gets you an error:

    bittest3

    However what about a compound index? What if I make the table larger and add more fields. Here’s a larger table:

    CREATE TABLE UniqueBit
( BureauID INT
, CompanyID INT
, DivisionID int
, DefaultType BIT
, ProductName VARCHAR(100)
)
GO
INSERT Uniquebit SELECT 1, 1, 1, 1, 'Product 1'
INSERT Uniquebit SELECT 2, 1, 1, 1, 'Product 2'
INSERT Uniquebit SELECT 3, 1, 1, 1, 'Product 3'
INSERT Uniquebit SELECT 4, 1, 1, 1, 'Product 4'
INSERT Uniquebit SELECT 1, 2, 2, 1, 'Product 5'
INSERT Uniquebit SELECT 1, 2, 3, 1, 'Product 6'
INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

    If I now add a unique index:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueBit] ON [dbo].[UniqueBit] 
(
    [BureauID] ASC,
    [CompanyID] ASC,
    [DefaultType] ASC,
    [DivisionID] ASC
) ON [PRIMARY]
GO

    It works fine. I can add another unique row like this:

    INSERT Uniquebit SELECT 4, 2, 1, 0, 'Product 7'

    without an error. Adding in a non-unique row:

    INSERT Uniquebit SELECT 4, 1, 1, 0, 'Product 7'

    gives me an error:

    bittest4

    There’s nothing special about a bit column for a unique index. There are restrictions for bit fields in some ways that relate to indexing, but uniqueness is not one of them.

    This was inspired by this post (before the complete details from the OP): http://www.sqlservercentral.com/Forums/Topic1126794-149-1.aspx#bm1126850

  • Data Compression in SQL Server

    Do you know much about data compression? It seems that quite a few people I meet don’t really understand how it works, or what the benefits are. I heard about compression first hand from Sunil Agarwal a few years back at TechEd. I visited one of his sessions and then he was gracious enough to spend about 15-20 minutes with me afterwards answering more questions about additional details. At the time, I was just another schmuck in the audience, but Sunil was very pleasant and patient with me.

    I was lucky that day,but Sunil has a video where he explains some of the ways in which compression works for Microsoft customers.

    http://channel9.msdn.com/posts/SQL-Server-Data-Compression/player?w=512&h=288

    The summary is that data compression works in a few ways. One is, obviously, to save space. The second, however, is that fewer I/Os’s are required to move data onor off a disk, which can result in a performance increase. The SQL Server team tried to strike a balance in how they implemented compression to allow for both of these benefits. The tradeoff is that CPU is needed to compress or decompress data, so while more compression is possible, it can negatively impact performance.

    Compression is a cool feature, and consists of a few parts. You can read more about each of them if you are interested:

    The DBA has complete control over what is compressed, but it is also some work to analyze the space savings and then make a determination about whether or not it is worth you compressing your table. If the space savings is too small, you might end up negating any storage gains with additional CPU costs.

    How Do I Decide?

    There is a stored procedure (sp_estimate_data_compression_savings) that can be used on tables or indexes to help you determine what the savings are. Numerous scripts are available, like this one from Paul Neilsen – Whole Database – Data Compression Procs.

    There’s also one easy decision. Only Enterprise and Data Center editions of SQL Server support this feature, so if you aren’t running those, you don’t have to worry about this.

    Alternatives

    There are a couple other alternatives to implementing data compression.  If you want to get compression savings in other editions of SQL Server, my company, Red Gate Software, makes a product called SQL Storage Compress, which implements compression at the file level.

    This is based on Hyperbac technology and is completely transparent to SQL Server. We are working on some extensive case studies now and this is part of the SQL Server I/O Reliability Program, but this can help performance on your system by reducing I/O.

    If space is an issue in test, customer service, and development environments, you can use Virtual Restore to mount a backup file as a database that you can read, or write to. The writes occur in a sparse file, so your backup file is untouched. However you get a quicker “restore” that is transparent to SQL Server.

    As I mentioned, I work for Red Gate software, so take these as mentions of products my company builds and sells. I’ve heard great things about them, and use them in a few places for work at SQLServerCentral. I’d recommend you try them and see if they are a fit in your environment and have a good price/value ratio for your company.

  • Evaluating Sessions You Attend

    I have seen more than a few posts about feedback that speakers get from sessions in the last couple months. Last week Buck Woody made an attempt to build a better form that could be used for feedback, which I like, but I think it’s a little too long for most events. If you attend 3-6 sessions in a day, filling out forums like this could be a chore.

    Attendees Should Give Feedback

    I know your time is valuable, but please give some feedback. I agree with Buck that the numeric scores are useless, but I know organizers wants something. If you liked the session, give it a 10, if it was OK, a 5, and if it didn’t work for some reason, drop a 1 in there and leave it at that. Let’s just make it a simpler system because no one knows what a 6 is vs a 7.

    But Be Fair.

    The way you perceive a session is not the way everyone else does. If you think the speaker was technically wrong, tell them. If they didn’t cover what was listed in the abstract, tell them. If they wandered around the topic without making sense, tell them.

    But don’t mark up a form with bad scores because you didn’t like their music, or their shirt, or don’t agree with the approach or thought something was too (or not enough) technical. More often than not I find people are so subjective in this area, and the inappropriately send information to other attendees, or impact the speaker’s ability to present in the future.

    If you have specific complaints, speakers would appreciate hearing them. You don’t have to rate everything, but give one specific piece of criticism that you’d want changed. One thing Buck does well is give you a chance to impact a presentation, and you ought to drop one written comment (not a score) on each evaluation you turn in.

    If you don’t have something bad to say, say something.

    I know that many evaluations are used for raffles, so please feel free to fill them out quickly, but I would appreciate some feedback. I do like seeing all 4s or 5s on my scores, but they often don’t help me improve the presentation. Give me one thought about what I could do better.

    For Speakers

    If you’re a speaker, you have some idea of what goes into building a presentation, writing a description, developing flow in your presentation, and pacing. Therefore, you ought to be able to evaluate a few things for other speakers.

    You ought to be able to tell them if they hit their mark in terms of what they said they’d deliver. I think speakers often have a better perspective on evaluating this. Not that non-speaking attendees’ opinions don’t count, but they sometimes aren’t very well thought out

    If you know the speaker, drop them an email, or take a (private) moment to give them an assessment of what you thought. I routinely let others know when I think they could improve something, or have approached a session in a way that turned me (or others) off.

    If you don’t know the speaker, introduce yourself, and say “I have some constructive feedback on your session. You don’t have to do this, but I thought …”  and then explain your point of view. It’s not rude, or assuming, and believe me it’s appreciated.

    Just be aware that it’s an opinion and don’t get upset if the speaker doesn’t make the change you mentioned. You’re still one of many, and while it’s nice to hear your opinion, it’s not a marching order.