roy ashbrook

yeah... it rhymes. so what? =P

not really anything to add, i just thought it was a really cool link. i've always wanted to learn lisp.

link!

well, not even really news. here's an article that goes over the different new isolation levels in sql 2005. if you don't understand these concretely, the figures presented in this document give a very good explanation of how things work. if you are having a hard time, just print them out and look at them side by side, that'll make it easier to get your head around.

there are a host of other good tidbits if you aren't familiar with the changes that took place from 2000 to 2005 so if you need to review or newly learn that stuff, check out the rest of that article.

yet another incredible article from chad boyd's blog

this article is about "determining what objects/structures/files/etc. are consuming the largest amount of space at a given time (or over time) within the Sql Server buffer pool" in sql 2005.

i'm going to post something similar to the little one here. I had a little trouble with it running out of space in int for bytes, so I just switched to KB since that's plenty precise for me. I also switched the formatting to something more consistent with what I typically use. code below, enjoy!

set transaction isolation level read uncommitted
select
  case grouping(dbName)
    when 1 then '--- TOTAL ---'
    else dbName
  end dbName
  , case grouping(fileId)
    when 1 then '--- TOTAL ---'
    else fileId
  end fileId
  , case grouping(pageType)
    when 1 then '--- TOTAL ---'
    else pageType
  end pageType
  , count(*) countPages
  , sum(row_count) sumRowCount
  , avg(row_count) avgRowCount
  , sum(freeSpaceKBytes) sumFreeSpaceKBytes
  , avg(freeSpaceKBytes) avgFreeSpaceKBytes
from
  (
    select
      case database_id
        when 32767 then 'resourceDb'
        else cast(db_name(database_id) as varchar(25))
      end dbName
      , cast(file_id as varchar(10)) fileId
      , cast(page_type as varchar(25)) pageType
      , row_count row_count
      , free_space_in_bytes / 1024 freeSpaceKBytes
    from
      sys.dm_os_buffer_descriptors bufferDescriptor
  ) tmp
group by
  dbName
  , fileId
  , pageType
  with rollup
order by
    case grouping(dbName)
      when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
      else dbName
    end
  , case grouping(fileId)
      when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
      else fileId
    end
  , case grouping(pageType)
      when 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
      else pageType
    end

here is a great article on the topic along with some sql code to show how. i'm gonna put a copy of the code here just in case that blog entry ever goes away. enjoy!

if object_id('dbo.fn_indexColumnList') > 0
drop function dbo.fn_indexColumnList
go

create function dbo.fn_indexColumnList(@objectId int, @indexId int)
returns nvarchar(max)
as
/*
Returns a text-based list of column names, in key order, for the object/index combination passed
*/
begin
declare @colList nvarchar(max);
set @colList = N'';

-- First, get just the key columns...
select @colList = @colList + case when len(@colList) > 0 then ',' else '' end + c.name
from sys.index_columns ic with(nolock)
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where ic.object_id = @objectId
and ic.index_id = @indexId
and ic.key_ordinal > 0
and ic.is_included_column = 0
order by ic.key_ordinal;

-- Now append any included columns...
if exists(select * from sys.index_columns where object_id = @objectId and index_id = @indexId and is_included_column > 0) begin
set @colList = @colList + ' (';

select @colList = @colList + c.name + ','
from sys.index_columns ic with(nolock)
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where ic.object_id = @objectId
and ic.index_id = @indexId
and ic.is_included_column > 0;

set @colList = @colList + '$$^^$$';
set @colList = replace(@colList,',$$^^$$',')');
end

return @colList;
end
go

use master
go

if ((object_id('sp_indexUsageInfo') is not null) and (objectproperty(object_id('sp_indexUsageInfo'), 'IsProcedure') = 1))
drop proc [dbo].sp_indexUsageInfo
go

create proc [dbo].sp_indexUsageInfo
@tableName nvarchar(255) = null, -- Name of a specific table/view/object to retrieve index usage information for - if null/default/0, no specific table filter is used
@rowcount int = null, -- Value to limit the result set to (top x) - if not passed, all data is returned
@opts int = 0 -- Bit flags that indicate what to return for the procedure call:
-- 1 bit - if set, order of results will be descending instead of ascending (by default, we order showing least used first up to most used,
-- if this bit is set, we do the opposite instead...
-- 2 bit - if set, whatever sort order is used will be applied to system values instead of user values
as

/*

NOTE: Use of this procedure requires the existence of the following procedures/functions as well:
1. dbo.fn_indexColumnList()

-- Show all structures for the current db, in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, default
-- Only top 25
exec dbo.sp_indexUsageInfo default, 25, default

-- Show all structures for the current db, in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 1

-- Show all structures for the current db, in order of least system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 2

-- Show all structures for the current db, in order of most system-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo default, default, 3

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of least user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, default

-- Show all structures for the current db, for a single tabled called 'tblBatches', in order of most user-based uses and then largest rowCount
exec dbo.sp_indexUsageInfo 'tblBatches', default, 1
*/

set nocount on;
set transaction isolation level read uncommitted;

declare @sql nvarchar(max),
@databaseId int,
@order nvarchar(1000);

-- Format incoming data
select @opts = case when @opts > 0 then @opts else 0 end,
@sql = N'',
@databaseId = db_id(),
@rowcount = case when @rowcount > 0 then @rowcount else 0 end;

select @order = case
when @opts & 3 = 3 then '(u.system_seeks + u.system_scans + u.system_lookups) desc, sizeData.rowCnt desc'
when @opts & 3 = 2 then '(u.system_seeks + u.system_scans + u.system_lookups), sizeData.rowCnt desc'
when @opts & 3 = 1 then '(u.user_seeks + u.user_scans + u.user_lookups) desc, sizeData.rowCnt desc'
when @opts & 3 = 0 then '(u.user_seeks + u.user_scans + u.user_lookups), sizeData.rowCnt desc'
end;

select @sql = @sql + N'
select ' + case when @rowcount > 0 then ' top (@rowcount) ' else '' end +
' object_name(i.object_id) as objectName, isnull(i.name,''HEAP'') as indexName, i.type_desc as indexType,
case when i.type_desc = ''HEAP'' then ''HEAP'' else dbo.fn_indexColumnList(i.object_id, i.index_id) end as columnList,
sizeData.rowCnt as rowCnt, sizeData.totalSpaceMB as totalSpaceMB, sizeData.usedSpaceMB as usedSpaceMB,
u.user_seeks as userSeeks, u.user_scans as userScans, u.user_lookups as userLookups, u.user_updates as userUpdates,
u.system_seeks as sysSeeks, u.system_scans as sysScans, u.system_lookups as sysLookups, u.system_updates as sysUpdates,
datediff(minute,u.last_user_seek,getdate()) as minutesSinceLastUserSeek, datediff(minute,u.last_user_scan,getdate()) as minutesSinceLastUserScan,
datediff(minute,u.last_user_lookup,getdate()) as minutesSinceLastUserLookup, datediff(minute,u.last_user_update,getdate()) as minutesSinceLastUserUpdate,
datediff(minute,u.last_system_seek,getdate()) as minutesSinceLastSystemSeek, datediff(minute,u.last_system_scan,getdate()) as minutesSinceLastSystemScan,
datediff(minute,u.last_system_lookup,getdate()) as minutesSinceLastSystemLookup, datediff(minute,u.last_system_update,getdate()) as minutesSinceLastSystemUpdate
from sys.indexes i
join (
select i.object_id as objectId, i.index_id as indexId,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(select sum(p2.rows) from sys.partitions p2 with(nolock) where p2.object_id = i.object_id and p2.index_id = i.index_id) as rowCnt
from sys.indexes i
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.allocation_units a
on p.partition_id = a.container_id ' +
case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
'group by i.object_id, i.index_id, i.name
) sizeData
on i.object_id = sizeData.objectId
and i.index_id = sizeData.indexId
left join sys.dm_db_index_usage_stats u
on u.object_id = i.object_id
and u.index_id = i.index_id
and u.database_id = @databaseId ' +
case when len(@tableName) > 0 then ' where i.object_id = object_id(@tableName) ' else '' end +
'order by ' + @order;

exec sp_executesql @sql, N'@tableName nvarchar(255), @databaseId int, @rowcount int', @tableName, @databaseId, @rowcount;
go

ok, this is way lame, but it's just a simple poc =P this is a winform app that will launch svn log and all of the files you drop onto it or sendto it and put them all in one window. i also put a little bit of basic logic to remove blank lines and the seperators from between the log entries. the only purpose of this is if you have to regularly get these comments back for about 10 or 20 files, using multiple threads will be way faster. hopefully this will help someone else who needs a basic example of a multi-threaded command line app call =P of course, you can substitude svn log for any command line you wish, or convert it to a command line and feed it a list file. pretty much, whatever. note that this is for a winform app with a form named form1 and a richtextbox named richtextbox1. very unfancy =P

here's the code:

using System;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Windows.Forms;

namespace ForkWannaBe
{
public partial class Form1 : Form
{
private const string c = "svn log ";

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
richTextBox1.AppendText("WORKING...\n\n");
string[] args = Environment.GetCommandLineArgs();
// note the 1, 0 is the exe path
for (int i = 1; i != args.Length; ++i)
ThreadPool.QueueUserWorkItem(new WaitCallback(go), args);
}

private void go(object o)
{
string f = (string)o;
string t = Path.GetTempFileName();
string tt = t + ".cmd";
StringBuilder fc = new StringBuilder();
using(StreamWriter sw = new StreamWriter(tt))
{
sw.AutoFlush = true;
sw.Write("@" + c + "\"" + f + "\"" + " >> " + "\"" + t + "\"");
sw.Close();
}
ProcessStartInfo si = new ProcessStartInfo(tt);
si.UseShellExecute = false;
si.RedirectStandardOutput = true;
si.CreateNoWindow = true;

using (Process p = new Process())
{
p.StartInfo = si;
p.Start();
while (!p.HasExited)
Thread.Sleep(500);

using ( StreamReader sr = new StreamReader(t))
{
sr.ReadLine(); // skip first line
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (!line.Length.Equals(0) & !line.StartsWith("----------"))
fc.AppendLine(line);
}
}
}
richTextBox1.AppendText(
string.Format("begin - {0}\n{1}end - {2}\n"
, f, fc, f));
File.Delete(t);
File.Delete(tt);
}
}
}

if you have a "burning" need, as some do, to view the change log for a bunch of files in svn, here's a quick way in windows to do it. just save the following as showsvnlog.cmd or whatever cmd or bat you want in your sendto folder and highlight whatever files you want and send them to it. you can also just drag and drop files onto it. this script utilizes some basic looping, shift, and alternate data streams. nothing revolutionary, but it does the job. here's the code:

::showsvnlog.cmd
::royashbrook - execute svn log for files and show the output
@echo off
set t="%~f0:temp.txt" 
if exist %t% del %t%
:loop
if "%1"=="" goto end
@echo Processing %1
@echo. >> %t%
@echo **** BEGIN CHANGE LOG FOR %1 **** >> %t%
svn log %1 >> %t%
@echo **** END CHANGE LOG FOR %1 **** >> %t%
@echo. >> %t%
shift
goto loop
:end
start notepad %t%
set t=

i figured i would make a quick post of some rdp type info i know. realize that all of these require you have the appropriate permissions.

have you ever tried to login to a machine via rdp only to get the message that there are too many sessions? well here's how to kill one of those sessions. first of all, you need to see what sessions there are. to do that, we can utilize the command line tool qwinsta. type qwinsta /? to see the syntax. here's a sample:

C:\>qwinsta /server:server1
 SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
 console                                     0  Conn    wdcon
 rdp-tcp                                 65536  Listen  rdpwd
 rdp-tcp#10        user1234                  2  Active  rdpwd

as you can see, user1234 has an active connection on id2. now, typically, someone has an inactive session or something like that. and that's who you'd want to kick. but let's pretend we know that user1234 is just doing something silly like surfing the web on server1 and kick him off. to do *that* we'll utilize rwinsta to reset the connection. at this point you can see the complex naming system emerge. rwinsta for resetting connections, and qwinsta for querying connections. as per usual, type rwinsta /? to see syntax. here's an example:

C:\>rwinsta 2 /server:server1

if you requery, you will see that the session is gone. the other person will get a message saying the session was ended, that it may have been ended by an administrator etc. now you can connect via rdp.


BUT! .... what happens if you are trying to connect via rdp and it isn't enabled. well, you'll probably get an access denied message or some other error when you try and perform a qwinsta, but if rdp isn't turned on, you would already know it's not working because you wouldn't even get a logon screen when you tried to connect via rdp. you'd get an error message instead.

here are some simple commands to check and set rdp on a server:

check:
reg query "\\server1\HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections

set:
reg add "\\server1\HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v fDenyTSConnections /t REG_DWORD 0

the check will return something like this:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server
    fDenyTSConnections  REG_DWORD       0x1

0x1 means the DENY is true. so you can't connect. you want to set it to 0 as in the set example above to DISABLE the DENY. why they couldn't just make it an enable setting and then we could just set it to true or false. yay double negatives in the windows registry. =P

after you set this, you'll have to reboot the machine, which you can simply use the shutdown tool for.

shutdown -r -f -m \\server1

and just in case you say "OH CRAP, I DIDN'T MEAN TO SHUT THAT DOWN" right after you run the command above, here's the abort command too.

shutdown -a -m \\server1


that includes our fun lesson for the day. hopefully that helps someone. i know that qwinsta and rwinsta have been two of the most often used tools in my administrative life/half-lives =P


reposted from my other blog.

i previously blogged about getting random ids from a sql table for testing. i was thinking about it and calling the entire table for a scan and using newid to sort seemed like it would be slower than selecting a random number from the possible range of ids using rand. now, note that this only works if you have an integer based value and if you know the values will be there.  i suppose you could put a safety loop that wouldn't let it out until it found some random value, but ... well whatever. personally, i don't mind missing numbers showing up here because generally i'm only using random numbers for testing anyway. this method is really a different animal from the orderby newid() since this query has the intention of selecting a single record randomly, whereas orderby newid() queries often are returning all the records just sorted in a random order. as always, ymmv and test things yourself first. here's some code:

--start here so we can drop the db
use master
go

--drop the db if it's there
if (db_id('rb') is not null)
  drop database rb
go

--create our ring buffer db
create database rb
go

--switch to it
use rb
go

--create fake user table
create table u(id int identity(1,1), n varchar(4))
go

--populate it
while ((select coalesce(max(id),0) from u) < 1000)
insert u(n) values('name')
go

--old way
select top 1 id from u order by newid()

--plan consistently shows this as ~20% of the above cost
select cast((select max(id) from u) * rand() + 1 as int)

i found this post recently. no idea what i was looking for when i found it, but there it is. basically, the post is about having an application that will show a random user in the top right of the screen. i'll let you read the rest, but i was thinking of what i would do if i wanted to do something like this. i think the approach of adding all of these functions is overkill. based on the information in the article it seems that the goal is to lower the expense of the query. i personally feel like this shouldn't be that expensive. i suppose if you were to select * and just order by random, that would possibly be slower, but why not just select the id you need first, then get the record you want? that seems to go much faster for me.

let's say we *did* want to create a ring buffer in tsql though. a better way i think would be refresh the ring at a set interval, and have the ring move things to the 'end' of the ring after they were selected. you could actually build a method for this, but i'm just going to use triggers. i'm not a big fan of triggers personally, so don't take this as an advocation of using them a lot, it just makes this example very easy. in this case, we're essentially reproducing a certain type of object and some certain behaviour in tsql. here's some sample code:


--start here so we can drop the db
use master
go

--drop the db if it's there
if (db_id('rb') is not null)
  drop database rb
go

--create our ring buffer db
create database rb
go

--switch to it
use rb
go

--create fake user table
create table u(id int identity(1,1), n varchar(4))
go

--populate it
while ((select coalesce(max(id),0) from u) < 1000)
insert u(n) values('name')
go

--create our ring table
create table r(id int identity(1,1), uid int, constraint pk_r primary key clustered (id asc))
go

--populate it randomly, you would truncate it too
-- if you automated this step daily
truncate table r
insert r(uid) select id from u order by newid()
go

--use this select to cycle through the ringbuffer
declare @id int, @uid int
select top 1 @id = id, @uid = uid from r order by newid()
delete from r where id = @id
insert r(uid) select @uid
select @uid
go

maybe that will help someone. =)

these are the sites i frequent for color information. in no particular order:

• http://www.colorblender.com/
• http://www.colorsontheweb.com/
• http://kuler.adobe.com/
• http://www.colourlovers.com/
• http://colormatch5k.com/
• http://www.colr.org/
• http://beta.dailycolorscheme.com/
• http://www.colorschemer.com/online.html
• http://design.geckotribe.com/colorwheel/
• http://www.colorcharts.org/ccorg/
• http://wellstyled.com/tools/colorscheme2/index-en.html

ha! i got ya! you thought this was a sql post, but it's not =P jeff atwood of codinghorror.com fame has a great article posted about his favorite top 10 lists. the top 6 ones to be exact.

here is the brief version with a link to each of them.

1. Jerry Weinberg: The 10 Commandments of Egoless Programming
2. Dare Obasanjo: Top 10 Signs Your Software Project is Doomed
   
3. Omar Shahine: Top 10 Tips for Working at Microsoft (or Anywhere Else)
4. Michael McDonough: The Top 10 Things They Never Taught Me in Design School
5. Andres Taylor: Top 10 Things Ten Years of Professional Software Development Has Taught Me
6. Steve Yegge: 10 Great Books

my personal top 10 items, in no particular order, out of the top 50 of these (i won't include the books) are:

• learn from mistakes. they will happen anyway.
• consult before you rewrite.
• communication is the hardest part.
• rely on proven technology.
• process is no substitute for thinking.
• Get out of your office.
• keep it simple.
• add value all the time.
• learn to say no.
• all things are not equal.
• show respect to everyone.

the OCD among you will see that there are 11. i couldn't let the 11th one there drop off, showing respect for your juniors and seniors i believe is a critical aspect to success. this applies to all area of life.

enjoy!

this is a very basic load tester. it basicaly lets you from the command line specify what command to run, how many times, against what connection string and whether you want to see any results. obviously, you can tweak it to print the results differently as you see fit, currently it just spits out field numbers and a value.

here's the code:

using System;
using System.Threading;
using System.Data.SqlClient;
using System.IO;

namespace TestSQL
{
    class Program
    {
        private static string s;
        private static string cs;
        private static int m;
        private static int a;
        private static int n;
        private static bool b;
        static void Main(string[] args)
        {
            // appname sqlfile threads connectionstring showresults
            // TestSql.exe file.sql 100 "server=x;database=x;user=x" true
            s = new StreamReader(args[0]).ReadToEnd();
            m = Int32.Parse(args[1]);
            cs = args[2];
            b = bool.Parse(args[3]);

            for (int i=0; i < m; i++)
            ThreadPool.QueueUserWorkItem(new WaitCallback(DoSQL), i);

            ThreadPool.GetMaxThreads(out m, out n);
            ThreadPool.GetAvailableThreads(out a, out n);

            while (a != m){
                Thread.Sleep(1000);
                ThreadPool.GetMaxThreads(out m, out n);
                ThreadPool.GetAvailableThreads(out a, out n);
            }
        }
        static void DoSQL(object t)
        {
            Console.WriteLine("start thread#" + t);
            using (SqlConnection c = new SqlConnection(cs)){
                c.Open();
                using (SqlCommand cmd = new SqlCommand(s, c))
                    using (SqlDataReader r = cmd.ExecuteReader()){
                        if(b)
                            while(r.Read())
                                for(int i = 0; i < r.FieldCount;i++)
                                    Console.WriteLine("field #" + i + " = " + r);
                        Console.WriteLine("end thread#" + t);
                    }
            }
        }
    }
}

sample command line syntax:

TestSQL.exe c:\test\t.sql 100 "server=localhost;database=zipcode;integrated security=sspi;Pooling=False;connect timeout=0;" false

i used the same tsql in the t.sql file that i used in this post.