Voice of the DBA

Tag: administration

  • The Gambler

    I'll be speaking here. Come shake my hand if you can come and register with the code "Jones"
    I’ll be speaking here. Come shake my hand if you can come and register with the code “Jones”

    In April of this year, the SQL Intersection conference is coming to Las Vegas. I’m speaking, along with Grant Fritchey and many others. It’s a fun event, in a city with a huge variety of things to do in the evenings after a full day of SQL Server sessions. At night I tend to look for networking chances to met new people and catch up with friends at night, though there have been a few times a comedy show has enticed me away from my hotel. I like Las Vegas, though I’m not a gambler. Despite the fact that most people think of visitors looking for their chance to sit at a table with dice or cards, there are many of us that go for other activities.

    I was at in a session recently and heard a speaker recommend that the audience run DBCC checks regularly. That’s good advice, and it’s what I recommend in my sessions as well. A person in the audience raised their hand and politely disagreed, saying that they almost never run DBCC CHECKDB. This person found it to be a waste of resources since they’d never encountered corruption in their career, and hadn’t known anyone in over a decade that had experienced on a SQL Server system. This person asked the speaker how many times the speaker had seen corruption (five was the answer) and then said across thousands of days of backups, it just wasn’t worth the resources to run DBCC CHECDB.

    If you feel that way, then you’re a gambler. You are accepting a higher level of risk than I do, and higher than I recommend. Consistency checks are designed to help us catch corruption. Since we never know when it will occur, we want to detect is ASAP so that we avoid, or at least minimize, data loss. If you run those checks and never experience corruption, those checks are insurance payments you’ve made and never needed to file a claim. However if you don’t run those checks, and experience corruption, you’ve placed a bet you’ve lost. Whether or not that cleans you out depends on the data loss your organization experiences and their tolerance for that loss. I’d seriously consider this a career limiting, or employment terminating, event, especially if the best practice recommendation from Microsoft and many experts is to run DBCC checks.

    I don’t recommend skipping your DBCC checks, but if that’s how you feel, think about coming to SQL Intersection (register with the code “Jones” to support me). You might enjoy that gaming tables at night, and I know the other speakers and myself would welcome the chance to change your mind about skipping DBCC checks during the day.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Resetting DMVs

    I'd prefer more control on when I reset DMV data.
    I’d prefer more control on when I reset DMV data.

    Recently I was working on an indexing presentation and looking for missing, duplicate, and unused indexes. As I set up demos to show the effects of indexing, I was constantly adding and deleting objects, and even resetting the DMVs to show statistics by starting the instance. The documentation for some DMVs, like sys.dm_db_index_usage_stats include a note that the counters are initialized to zero when the instance is restarted. If a database is taken offline, or detached, all rows referencing the database are removed.

    That got me thinking. Why is this data removed? I’m sure some of the data is stored in memory and automatically reset, but is this the best way to handle this data? Wouldn’t it be better to persist this data and allow the DBA to reset values when they were ready? I know we can store this data in a table periodically now, but I think keeping this inside the system views would make sense as an option in SQL Server. When we shut down a database, persist this data inside the database. That would be closer to my vision of self-describing databases, which contain all their own metadata.

    I know there might be performance impacts to persisting this information, but I can also see benefits, especially when you might have system crashes or problems. Being able to recover the information from DMVs, even incomplete information, brings us closer to a robust, flight-recorder kind of system that monitors itself and allows administrators to review information that might help them discover the causes of any issues.

    I don’t think that every piece of data needs to be guaranteed to be written to the DMVs, or that the system should slow down to do so, but give the DBAs some control here. If we have the need for more extensive logging, or we want to retain whatever information we can, allow it to persist in the DMVs, or at least make snapshots of DMV data easy to take and store. It’s another form of logging that I’d appreciate having available from the platform.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • T-SQL Tuesday #40– File and Filegroups

    tsqltuesdayIt’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server.

    If you’re like to participate, write a post and drop a comment (or pingback) on Jen’s blog. Watch the #tsql2sday hashtag on twitter for next month’s invitation.

    Filestream and Filegroups

    I have a couple talks that deal with Filestream related topics, so I decided on a quick introductory lesson on how this works.

    Filestream was built into the AdventureWorks 2008 sample database. Requiring administrators to turn on Filestream caused some confusion and complaints, despite the fact that it’s easy to do.

    What does Filestream have to do with filegroups? In a database that is enabled for Filestream data, you need to add a filegroup specifically for the Filestream data. This is actually a folder on your file system, which you can access through T-SQL, or through the Win32 API. If you are using SQL Server 2012 or later, you can also access this data with a Filetable, which is built on Filestream.

    Let’s create a database, and add a filegroup for Filestream. We start with the “New Database” dialog in SSMS.

    fs_a

    With the normal defaults, we see a data file (FS_Test) and a log file (FS_test_log). For Filestream data, we need a new place to store it. Let’s add a file:

     

    fs_b

    Once I add the file, I mark it as storing Filestream data. The other options are rows (data files) or log files. However this presents a problem. When I scroll right, I see that there is no filegroup for Filestream data. I can’t put this in an existing filegroup.

    fs_c

    Let’s add one of those. Here’s the default filegroup dialog.

    fs_d

    I can click add, and put in a filestream filegroup. The name doesn’t matter, it’s just for administrative purposes. Once I do that, I can go back to the files dialog, and if I select the dropdown, my new filegroup appears.

    fs_e

    Now I need a location. Outside of SQL Server, I created a folder in my data directory. This can be anywhere, but I did it in the default location. It’s called FilestreamDataTest.

    fs_g

    I then select this in the files dialog, using the ellipsis to the right of the Path column.

    fs_f

    I see my folder in the file picker and choose it.

    fs_h

    Once I’ve selected it, I don’t click OK. I click “Script” and get the script below:

    CREATE DATABASE [FS_test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FS_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [fs_test_fsdata] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'fs_test_fsdata', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FilestreamDataTest\fs_test_fsdata' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'FS_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

    I can run this, and once I do, if I go into the folder that contains my Filestream file, I see this:

    fs_i

    As I create tables that hold Filestream data, including FileTable data, I’ll see entries in here for each column (or Filetable) that holds this data. There is a folder that holds logging information for this data, which I do not manage.

    Hopefully this is a quick, short piece that helps you understand Filegroups and Filestream.

  • Dropping Indexes

    While working on some demos recently, I needed to drop an index for a test. I executed this generic statement for an index I’d just created.

       1: DROP INDEX ix_IndexName

    Needless to say I was surprised when I got this error:

    Msg 159, Level 15, State 1, Line 1

    Must specify the table name and index name for the DROP INDEX statement.

     

    I haven’t done much index maintenance in the last few years, but since I had specified the name, and I expected names to be unique, I was surprised. That’s not the case, however, since indexes aren’t seen as objects.

    I created the index in AdventureWorks with this code:

       1: -- paste in create index statement

       2: CREATE NONCLUSTERED INDEX ix_IndexName

       3: ON Sales.SalesOrderHeader ( [TerritoryID],[ShipMethodID], [SubTotal], [Freight] )

       4: INCLUDE ([SalesOrderNumber], [CustomerID]);

    As a test, I then added this index:

       1: CREATE NONCLUSTERED INDEX ix_IndexName

       2: ON Production.Product ( [Name],[ListPrice]);

    Same name, different table.

    A quick check in sys.objects surprised me.

       1: select *

       2:  from sys.objects

       3:  where name = 'ix_Indexname'

    This returned no results. Hmmm, let’s investigate further. I next decided to check sys.indexes.

       1: select *

       2:  from sys.indexes where name = 'ix_Indexname'

    This returned two results:

    indexes

    Two entries, with two object_ids. I wondered what those objects were, so I ran more code:

       1: select *

       2:   from sys.objects

       3:   where object_id in (1010102639, 1717581157)

    I received the two tables back as the objects.

    indexes2

    This surprised me, though I’m sure I’ve read the details in a book at some point, or even seen the documentation in sys.indexes. The entry for name says it is unique only within the space of the object, which would be the parent table.

    I had assumed that indexes were objects, but they aren’t. They are an attribute of an object, and as such, I needed this code to remove my index:

       1: -- cleanup

       2: DROP INDEX ix_IndexName

       3:  ON Sales.SalesOrderHeader

       4: ;

       5: GO

    Update: As noted in a few comments, you can also drop the index as:

       2: DROP INDEX Sales.SalesOrderHeader.ix_IndexName

    And, of course, I needed to drop my test object.

       1: Drop INDEX ix_IndexName

       2: ON Production.Product

       3: ;