Voice of the DBA

Category: Blog

  • Finding the Attribute

    I was playing with some Extended Events recently. If you haven’t tried, I’d encourage you to do so. However, working with XML is not my favorite. I know I can get the GUI in SSMS 16.x to show me events, but I sometimes want to query.

    Here was my quick adventure in XML and XQUERY. I should know this stuff better, but I think I’m working with XML so rarely that I’m constantly re-learning things.

    I had a document like this:

    DECLARE @x XML = CONVERT (XML, 
'<event name="login" package="sqlserver" timestamp="2016-09-28T01:48:31.743Z">
  <data name="is_cached">
    <value>false</value>
  </data>
  <data name="is_recovered">
    <value>false</value>
  </data>
  <data name="is_dac">
    <value>false</value>
  </data>
  <data name="database_id">
    <value>1</value>
  </data>
  <data name="database_name">
    <value>master</value>
  </data>
  <action name="username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>60</value>
  </action>’

    There was more, but this is fine. I had a query someone else sent me that looked like this:

    SELECT 
[message] = @x.value(
                     '(event/data[@name="database_name"]/value)[1]',
                     'nvarchar(250)'
                     )

    That’s fairly simple, but what I really wanted was to get an attribute at the top. In the “event” node, I wanted the “name” attribute. I can go from the query above to that, right? I could have dug into XQUERY, but I’ve found it logical in the past, so I thought I could actually figure this out.

    I know that the path was just event, and I needed to get the attribute from that. I tried this:

    SELECT 
[message] = @x.value(
                     '(event[@name="name"]/value)[1]',
                     'nvarchar(250)'
                     )

    That didn’t work. So I modified things to

    SELECT 
[message] = @x.value(
                     '(event/name/value)[1]',
                     'nvarchar(250)'
                     )

    No go.

    Hmmmm. What do I need to do? I decided to Google a little and saw a note that the attribute is accessed with the @ symbol. OK, so I need to provide that as the path.

    SELECT 
[message] = @x.value(
                     '(event/@name/value)[1]',
                     'nvarchar(250)'
                     )

    Still no good, but then I removed the value.

    SELECT      @x.value(
                '(event/@name)[1]', 
                'nvarchar(250)'
               )

    That was it.

    XPATH and XQUERY make sense once you get the rules, but they’re still annoying to work with. I’ll be trying to work with the GUI in SSMS as much as possible with XE.

  • Quick Tips–Using ii in #SQLPrompt

    SQL Prompt has lots of great features that can help you write SQL quicker. However, you’ve got to train yourself to use a few of these and not just start to type with your old habits. This quick tip looks at one of those areas when inserting data into tables.

    There are times I’m building some test data to try a new piece of code. In the past, I’d start typing INSERT. In SQL Prompt, this gives me a selection after beginning the ins, which is how far I get as a quick typist.

    2016-08-22 10_34_02-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

    Effort: 4 keystrokes.

    I’ve tried to write cleaner T-SQL, so I’d add in the INTO, which for me is “i” plus 2 down arrows.

    2016-08-22 10_34_11-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

    Now: 9 keystrokes

    Next, I’d begin with the table, in this case, my Employee table. For me, this means hitting “e” and then the down arrow.

    2016-08-22 10_35_26-ObjectDefinitionBox

    Now: 11 keystrokes

    Hitting Tab at this point would get me the insert statement, with the list of columns and default values. Having the quotes already there, and the data types, can be really handy.

    Total: 12 keystrokes

    That’s a pretty quick way to get the insert statement. However, there’s a better way. If I type “ii”, I get this:

    2016-08-22 10_41_51-ObjectDefinitionBox

    That’s one of the built in snippets for SQL Prompt that builds an insert statement. Now I can add my table.

    2016-08-22 10_42_02-SQLQuery7.sql - (local)_SQL2014.Sandbox (PLATO_Steve (57))_ - Microsoft SQL Serv

    That means in 6 keystrokes I’ve built the same insert statement, and it’s much quicker. As I’ve gotten used to using ii, I can get test data for an algorithm built very quickly.

    Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of cleanly building SQL Code. You can also read a similar piece I wrote on the Redgate blog.

    Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

    I’ve also written a similar piece on the Redgate Blog,  You can see a complete list of tips at Redgate.

    You can also watch this tip in action:

  • More Azure Database Creation in Powershell

    I wrote about Azure SQL Database for T-SQL Tuesday #82, but I had a few mistakes in my process, at least from a cost standpoint. Since I’ll probably use this a bit more as I flesh out some CI/CD, I decided to document a bit more about the database creation process.

    The Old Code

    One of the problems I noticed right away was that I created an S2 level database in my post, which was too large. That wasn’t needed, and it might eat up my database credits. My original code was:

    New-AzureRMSqlDatabase –ResourceGroupName “Default-SQL-WestEurope” –ServerName “dkranchapps” –DatabaseName “TSQLTuesday” –Edition Standard –RequestedServiceObjectiveName “S1”

    In here, there are a few things that are obvious to me, and some that might not be. I won’t delve into detail on how to choose some of these, but I’ll define what they mean. There is a doc page for the cmdlet, which I used to check what I knew.

    ResourceGroupName

    This is the way you organize Azure resources. In my case, I have one resource group, which I named “Default-SQL-WestEurope”. This was the first group I made, intending this to be for SQL servers, and it’s in the West Europe region. Hence the name. 

    ServerName

    This is self explanatory, but you create servers to host resources in Azure. The one I created a long time ago for experiments was “dkranchapps”. I re-used it here, though I could have created another one.

    DatabaseName

    I’m not defining this. If you don’t know, stop reading.

    Edition

    This is the basic edition for your database. There are multiple options, but I used “Standard”, which was in the sample documentation code. However, a Standard incurs a charge from $0.0202/hr to $0.2016/hr. That doesn’t sound like much, but there are 720 hours in a 30 day month. That’s $14.44 to $145.15 a month. Again, not much, but this is per database. This could cause me issues with my $150/month credit.

    ServiceObjectiveName

    This is the scale within the Edition. I’m not sure why this the Edition is needed if we specify this here, but we can use a name or a value (GUID). In my case, I neglected to check the S2 syntax when I made the database. What I’d want for most things is a Basic or maybe an S level if this is short lived.

    Other Options

    There are other options. I can specify the subscription in a parameter, do a “WhatIf” to see what this would do, Force the command without user conformation, set a maximum size, and more. 

    While you might not need these options, I’d encourage you to look through the list and make sure that it’s not your intention to omit something that might be important in your situation.

    Creating a Basic Database

    One of the things I want to try is scripting the creation of a database in my CI process. That’s not quite simple, and it’s a task for another day, but here’s a basic call that I can work on adding to my builds.

    New-AzureRMSqlDatabase –ResourceGroupName “Default-SQL-WestEurope” –ServerName “dkranchapps” –DatabaseName “” –Edition Basic –RequestedServiceObjectiveName “Basic”

    Once I run this, I can check the portal, and sure enough, I see my database at the correct size.

    2016-09-21 10_52_49-SQL databases - Microsoft Azure

    That’s the first step to automating some builds. Now I need to worry about security and API keys, but for now, I can build a database.

    And, of course, remove it with Remove-AzureRMSqlDatabase.

  • Restaurants At Summit

    At the PASS Summit this year, there won’t be a Thursday night party. I think that’s fine, as it never seemed like a highly attended event, and the costs are high.

    That means that Thursday night is open, and I know lots of you will be looking for something to do. Or you’ll know someone that’s looking for something to do. In either of those cases, Andy Warren and I are looking to try and organize a few things. One of these will be around dinner plans for attendees.

    That’s where I’m looking for help from you. I need a list of restaurants in the downtown Seattle area.

    We’re looking for recommendations from those of you that have eaten dinner in Seattle. Give us a name/address, cost ($ or $$ or $$$) and how busy it was. We’re looking for those places that are walking distance or short Uber/Lyft/cab from the Convention Center area.

    Leave a comment with your recommendation and watch out for our announcement soon on the meetup plans.