Category Archives: dba

A Seat at Two Masters’ Table

Paul Randal (blog | twitterblogged about winning a free seat at a SQLskills Master Immersion Event on Internals and Performance in Dallas.

Since this is Paul and Kimberly judging, I’ll be writing it explicitly for them as “It Depends.”

So, if their judging style is based on humor along with Paul / Kimberly / Brent / SQL memes, then here is that entry.

Come on, it’s Paul and Kimberly, one of the greatest sheep herders ever along with the greatest sheep herder herder. Wouldn’t you want to take the opportunity to learn how sheep can be trained to process that much data that fast. There might even be a green fisted Lady Gaga impersonator punching the bacon harder.

After the class, I’d love to go around helping others get the best performance out of their livestock. So many have problems with corrupt sheep or dead sheep and there’s just not enough of the snippy Scotsman to go around. I’d like to also use it as a starting point to becoming a Certified Master in Sheep Query Lawn Server.

Otherwise, if they are judging on technical merit:

I want to learn everything I can about the tool I chose to base my profession on. Paul & Kimberly are recognized as some of the most knowledgeable persons about the internals of that tool. The pair are also very good at teaching and spreading the wisdom and experience they have gained. If I can gleam a portion of that, then I can enhance my performance as a SQL Server professional. This would also be a large first step on becoming an MCM for SQL Server.

One thing I really like doing is helping people. I like solving peoples’ troubles who cannot do so themselves. It’s something I’ve done as long as I can remember. I want to take the knowledge gained and use it to help people. I know that companies are usually the ones that require the technical help. Those companies are made up of people trying to do what they do best, but maybe can’t learn this material at the depth we can. The people suffer if the company does.

Another way to help is to teach others. Professionals like to learn. Teaching a professional or an eager student is a wonderful experience. Knowing that you taught them something new, clarified a portion of thought, or brought a new perspective is very rewarding. This training would help me be a deeper tome from which others can learn.

So Paul, Kim… I’d like a seat at your table when you come to Dallas.

    What Does Confio’s IgniteFree Install on Monitored Instances

    I was trialling Confio’s IgniteFree software on a development box and wanted to see what exactly gets created when you register an instance for monitoring. Confio states that their installs are “Agentless”, which is true.

    I asked Thomas LaRock (blog | twitter) if IgniteFree creates any objects on the server. Answer:

    short answer: #ignite does not require creating objects on the monitored instance. –@SQLRockstar (emphasis mine)

    What I did discover though, objects do get created on the monitored server.

    So what gets created?

    USE [master]
    GO
    /****** Object:  User [DOMAIN\user]    Script Date: 01/21/2011 13:15:11 ******/
    CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user] WITH DEFAULT_SCHEMA=[null]
    GO
    /****** Object:  Schema [DOMAIN\user]    Script Date: 01/21/2011 13:15:10 ******/
    CREATE SCHEMA [DOMAIN\user] AUTHORIZATION [DOMAIN\user]
    GO
    /****** Object:  Table [DOMAIN\user].[ignite_temp]    Script Date: 01/21/2011 13:15:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [DOMAIN\user].[ignite_temp](
    	[index_val] [tinyint] NULL,
    	[name] [varchar](50) NULL,
    	[internal_value] [int] NULL,
    	[char_value] [varchar](255) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    GRANT SELECT ON fn_get_sql to [DOMAIN\user]
    GO
    GRANT VIEW SERVER STATE to [BUILTIN\Administrators]
    GO

    Getting VLF stats for multiple databases

    After watching Paul Randal’s (blog | twitter) MCM video on log files, I wanted to find a quick way to get VLF (Virtual Log File) statistics across multiple databases and servers. I went two routes, PowerShell and T-SQL. Our internal monitoring uses T-SQL already, so I wanted to be able to easily work with the existing framework. I wanted to create a PowerShell version, as some others might find it useful and I just like working with PowerShell.

    T-SQL Version
    I wound up making my table structure the same as David Levy in this blog post. Michelle Ufford has a very similar script at her blog post.

    PowerShell Version
    Thanks to Aaron Nelson (blog | twitter) for giving me an idea to tweak this.

    More on VLFs:


    Active / Active Cluster Server Memory Allocation

    We have several active / active clusters with multiple instances per node in our environment. We want to ensure the instances are spread as evenly as possible across the nodes. I wrote a quick PowerShell script to go through a text file and get the physical node where the instance is running, how much physical memory the node has and how much memory the instance is configured to use.

    Just create a text file “servers.txt” with the list of instances on a cluster and run the following.

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
     
    $servers = get-content "servers.txt"
    foreach($server in $servers)
    {
    $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $server
    [String]::Format("{0} uses {1}MB RAM. Resides on {2}.", $server, $sqlServer.Configuration.MaxServerMemory.ConfigValue, $sqlServer.ComputerNamePhysicalNetBIOS)
     
    $objCS = Get-WmiObject Win32_ComputerSystem -ComputerName $server.Substring(0, $server.IndexOf("\"))
    [String]::Format("{0}\{1}: {2}MB", $objCS.Domain, $objCS.Name, [Math]::Round($objCS.TotalPhysicalMemory / 1mb))
    }
    The formatting leaves much to be desired, but it’s a quick check and that was what I was interested in. Excel can do all the formatting and calculating later.