Search results matching tags 'speaking' and 'SQL Server'

Speaking at University of Virginia

Andrew Kelly — Mon, 05 Nov 2012 17:37:00 GMT

The University of Virginia is having a fall conference that is free and open to the public on Tuesday November 13th. My good friend Andy Leonard and I will be speaking on various SQL Server related topics so if you are in the area and available that day you are most welcome to stop by. You can find more information and register for the conference here.

Andrew J. Kelly

See you in Columbus Saturday?

drsql — Mon, 06 Jun 2011 04:32:45 GMT

Assuming all goes as planned, I will be in Columbus, OH this Friday night and Saturday for SQL Saturday 75. I really love SQL Saturday events the best of all of the events because they are very intimate in nature. As a fairly antisocial person, I sometimes get overwhelmed by the size of other events, even the SQL Rally was just barely in my comfort range. Here the number of people and size of rooms just feels like home, like you are shooting the breeze with a group of friends.

My session will be at 9:00 AM (full schedule), so don’t be late!

Characteristics of a Great Relational Database
When queried, most database professionals would mention normalized as one of the most important characteristics that tell the difference between a good and bad database design. I won't disagree in the least, but there is so much more to be considered. Even if you did a great job of normalization, poor naming, poorly implemented keys, too many or too few indexes, and so on can derail your design. In this session I will present seven primary characteristics of a design that differentiates between an ugly design that will have your colleagues nitpicking you to death and one that will have them singing your praises. Characteristics such as comprehendible, documented, secure, well performing, and more (including normalized, naturally) will be discussed.

It is the second time I will do this presentation, and the first time where I can see the faces of the recipients, so it will be nice to gauge how people like it. It is a lot of fun actually, though no matter what I am talking about I want to talk more about normalization, which I believe is the key to improving the databases of the future. So many of the session that are given at these things are geared towards systems that are already screwed up and limping along, and I really want to evangelize the merits of doing it right. That is where this presentation fits in, the time period between design and performance tuning, where you determine the future work that is done with the system. Is it well performing, understandable, easy to maintain and use? Or does it take a crew of ten thousand DBAs doing nothing but putting their fingers in the leaks to keep the thing running?

And don’t forget all of those BI sessions too… The better you do with the relational database, the easier the dimensional designer/implementers have it too.

Anyhow, I hope to see you all there (well, not all of you, just the SQL nerds who are reading this. The history buffs who are still wondering why we are going to be inside the founder of our continent this weekend, well, you I feel sorry for you.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

Quest DMV Breakfast Code – Disk IO

drsql — Wed, 04 Nov 2009 17:52:43 GMT

For any of you who attended the Quest breakfast at PASS (and anyone else really) I wanted to go ahead and post the code and give a little wrap up/explanation. (I will post the stuff on perf counters later. They are cool, but this is really what I wanted to make sure people saw.)

I had a plan for my presentation, but time was less than I expected, and the good stuff kinda fell off. So here is the stuff around the Disk IO DMV section that I really messed up and didn’t show enough. I read a few of the comments that my presentation wasn’t that useful to you, and that is clearly on me. If the index ones are useful, these are too, and can be used to help spread load around your disks.

So, here is the slide I presented:

By knowing the amount of data written to a database file, you can see how busy the file is, the filegroup is, the database, the disk drive, etc, by aggregating the data in the following query in various way. The query is pretty simple and will return all files in all databases (there are parms on the function to change that, but check BOL for more info):

--since reboot
SELECT db_name(mf.database_id) as databaseName,
            mf.physical_name, divfs.num_of_reads,
            divfs.num_of_bytes_read, divfs.io_stall_read_ms,
            divfs.num_of_writes, divfs.num_of_bytes_written,
            divfs.io_stall_write_ms,
            divfs.io_stall,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

The “stall” columns give you how long any processes have waited on your disk drives to serve up data for this file. This information plus the information in _bytes_written and _bytes_read columns can give you how much data was moved, and how long your CPU had to wait for the data before it could get working on it. Very powerful stuff, and can really give you a look at opportunities for performance tuning.

That query gives you data from the last reboot of the server, but usually you want to see up to date values. So you have to get a baseline of values, then compare them at a later point. You can build tools that do this, and you can use the performance data warehouse in 2008, and I will demonstrate the former. Using a temp table and a WAITFOR DELAY command, I build a quick tool to see waits and stats over 20 seconds. Note that I use a #temp table to allow you to run the last query over and over to see the comparison to the baseline until you want to drop the #temp table.

--over the following delay
declare @delay varchar(10) = '00:00:20'

SELECT db_name(mf.database_id) as databaseName,
       mf.physical_name, divfs.num_of_reads,
       divfs.num_of_bytes_read, divfs.io_stall_read_ms,
       divfs.num_of_writes, divfs.num_of_bytes_written,
       divfs.io_stall_write_ms,
       divfs.io_stall,size_on_disk_bytes,
       getdate() as baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

WAITFOR DELAY @delay

;WITH currentLine as (
    SELECT db_name(mf.database_id) as databaseName,
            mf.physical_name,num_of_reads, num_of_bytes_read,
            io_stall_read_ms, num_of_writes,
            num_of_bytes_written, io_stall_write_ms,
            io_stall,size_on_disk_bytes,
                getdate() as currentlineDate
     FROM sys.dm_io_virtual_file_stats(null,null) as divfs
                JOIN sys.master_files as mf
                        ON mf.database_id = divfs.database_id
                            and mf.file_id = divfs.file_id)

SELECT DATEDIFF(s,#baseline.baselineDate,currentlineDate),
        currentLine.databaseName,
      left(currentLine.physical_name,1) as drive,
      currentLine.physical_name
     ,currentLine.io_stall - #baseline.io_stall as io_stall_ms
     ,currentLine.io_stall_read_ms -
               #baseline.io_stall_read_ms as io_stall_read_ms
     ,currentLine.io_stall_write_ms -
              #baseline.io_stall_write_ms as io_stall_write_ms
     ,currentLine.num_of_reads - #baseline.num_of_reads as
                                               num_of_reads

    ,currentLine.num_of_writes - #baseline.num_of_writes
                                            as num_of_writes
   ,currentLine.num_of_bytes_written -
        #baseline.num_of_bytes_written as num_of_bytes_written
FROM currentLine
      join #baseline
          on #baseLine.databaseName = currentLine.databaseName
             and #baseLine.physical_name =
                              currentLine.physical_name
go
--drop table #baseline

The most interesting part of this query is often the stall columns. When I see really bad IO, the number of seconds waited can end up being way more than the actual number of seconds. Disk IO is more or less serial (particularly on my mini HP with Win7 Starter Edition), so if you have many queries running concurrently, every one of them needs data from the same IO source, and if one query used the disk for 1 second, the other processes might wait for 1 second each. When wait stats are really low, that means the disk were ready, willing, and able to serve all processes.

On your test server, you can use a query like:

--create database DMV
--go
use DMV
go
create table testDMV
(
testDMVid int identity,
bigun char(8000)
)
go
set nocount on
insert into testDMV(bigun)
select REPLICATE('a',8000)
go 5000

--drop table testDmv

select *
from testDMV

To create some data. Note that data will be written to the disk in various counts. I set all of the rows to take a full data page, and each of the rows is an individual transaction. But it won’t be a 1-1 match with the number of rows written or read. SQL Server flushes data to the disk in the manner it wants to, and some manner of caching will change the number over time. Run the select * from testDMV query over and over and you will not see any change in the disk IO, because it is in cache now.

I hope this is better. Sometimes as a presenter I am a good writer, and frankly the lack of bacon really threw me off this morning. Feel free to email me at louis@drsql.org or tweet me at drsql and I will be happy to share more. All of this will be in our RedGate book that will be coming out before the end of the millennium, at the very minimum.

NJSQL Saturday Event (10/24)

Mike C — Tue, 06 Oct 2009 19:37:00 GMT

The NJSQL user group is hosting a one-day Saturday SQL Server event at the Microsoft Iselin offices. On October 24th Jacob Sebastian, Robert Pearl and I will be presenting on the following SQL Server topics:

* T-SQL Defensive coding and exception handling best practices
* Database forensics in SQL Server ("Who did it and ran?")
* SQL Server 2008 spatial data ("Where in the World?")

This one-day event is free, but registration is required. More details, including registration link at: http://njsql.org/Default.aspx

Presenting in Louisville; Meeting in Cincinnati

Mike C — Mon, 01 Jun 2009 23:15:00 GMT

I'll be presenting "New SQL Server 2008 Features" in downtown Louisville, KY on June 22, in conjunction with the Louisville SQL Server Users Group thanks to group leader Malathi Mahadevan. We'll be giving away some SQL 2008 books and other goodies. I'll post further details (exact time and location) shortly.

Update: The address is for the Louisville meeting is:

Louisville Free Public Library
301 York Street
Louisville, KY 40202

We'll be meeting from 12 noon to 1 PM.

June 23rd I'll be stopping by the Cincinnati .NET User Group (for the UG meeting and a Geek Dinner -- hopefully at Skyline Chili! ;-) ) thanks to Mike Wood, director of the Cincy .NET Users Group. We'll be having an informal discussion of SQL Server and .NET technology and features after the main meeting, having some good food, and giving away a few more books! Visit http://cinnug.org for more info.

Headed..South..To Atlanta for SQL Saturday

drsql — Fri, 24 Apr 2009 02:55:00 GMT

Heading down to SQL Saturday (www.sqlsaturday.com) in Atlanta to give a 9:15 AM session on Database Design, with a greater than normal dose of normal thrown in. I hope there are more than 7 people to show up, because I would hate if everyone got some swag (I usually like tossing out stuff when people make cool and interesting statements, and NOT when they prove me wrong…you know who (all) you are :). Apparently they were at their capacity, so maybe not. If you need to know where it is, check here: http://www.sqlsaturday.com/eventhome.aspx?eventid=17, and come on out. The more smart alecky know-it-alls I get who want to prove me wrong the better…well, as long as you sit there quietly and learn something…

(Hmm… I hasten to send you that link (www.sqlsaturday.com), because frankly you will have to see that someone has a bad where clause on a query somewhere. I would offer up prizes for the best guess, but every time I do contests no one ever gets it or even tries.)