Roy Ashbrook

let go or be dragged
posts - 198, comments - 29, trackbacks - 8

March 2007 Entries

learn lisp for free from mit

yeah... it rhymes. so what? =Pnot really anything to add, i just thought it was a really cool link. i've always wanted to learn!

posted @ Friday, March 30, 2007 9:13 PM | Feedback (0) |

old news, but still

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...

posted @ Friday, March 30, 2007 8:25 PM | Feedback (0) | Filed Under [ DBA ]

more cool usage information for sql2005

yet another incredible article from chad boyd's blogthis 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 uncommittedselect  case grouping(dbName)    when 1 then '--- TOTAL ---'    else...

posted @ Friday, March 30, 2007 8:07 AM | Feedback (0) | Filed Under [ DBA ]

identify most used indexes

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_indexColumnListgocreate 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 + from sys.index_columns ic with(nolock) join sys.columns c on...

posted @ Friday, March 30, 2007 8:00 AM | Feedback (0) | Filed Under [ DBA ]

stupid svn log tricks

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...

posted @ Friday, March 30, 2007 7:35 AM | Feedback (0) | Filed Under [ DEV ]

view svn change log, quick and dirty

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 offset...

posted @ Friday, March 30, 2007 5:22 AM | Feedback (0) | Filed Under [ DEV ]

qwinsta, rwinsta, and reg, OH MY!!

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 ...

posted @ Thursday, March 29, 2007 2:35 PM | Feedback (0) | Filed Under [ SA ]

faster random ids

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...

posted @ Wednesday, March 28, 2007 7:26 AM | Feedback (0) | Filed Under [ DBA ]

ring buffer in tsql

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...

posted @ Wednesday, March 28, 2007 5:45 AM | Feedback (0) | Filed Under [ DBA ]

top 11 colour tools on the web

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

posted @ Wednesday, March 28, 2007 5:03 AM | Feedback (0) |

select top 6 from (select top 10 from lists)

ha! i got ya! you thought this was a sql post, but it's not =P jeff atwood of fame has a great article posted about his favorite top 10 lists. the top 6 ones to be is the brief version with a link to each of them. Jerry Weinberg: The 10 Commandments of Egoless ProgrammingDare Obasanjo: Top 10 Signs Your Software Project is Doomed Omar Shahine: Top 10 Tips for Working at Microsoft (or Anywhere Else) Michael McDonough: The Top 10 Things They Never Taught Me in Design School Andres Taylor: Top 10 Things Ten Years of Professional Software Development Has Taught...

posted @ Tuesday, March 27, 2007 7:03 PM | Feedback (0) | Filed Under [ ARCH ]

basic sql load testing with a multithreaded c# console app

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;     ...

posted @ Tuesday, March 20, 2007 8:58 AM | Feedback (0) | Filed Under [ DEV ]

query snmp from vbscript

if anyone finds a way to do more, let me know. i have not found a way to query a specific oid via vbscript without using a com object specifically for this or an external command. this method uses the wmi snmp provider. valid values for the class variable below, as far as i know,  are currently: SNMP_RFC1213_MIB_snmp SNMP_RFC1213_MIB_ip SNMP_RFC1213_MIB_ipRouteTable SNMP_RFC1213_MIB_ipAddrTable SNMP_RFC1213_MIB_ipNetToMediaTable SNMP_RFC1213_MIB_interfaces SNMP_RFC1213_MIB_ifTable SNMP_RFC1213_MIB_icmp SNMP_RFC1213_MIB_tcp SNMP_RFC1213_MIB_tcpConnTable SNMP_RFC1213_MIB_egp SNMP_RFC1213_MIB_egpNeighTable SNMP_RFC1213_MIB_udp SNMP_RFC1213_MIB_udpTable SNMP_RFC1213_MIB_system SNMP_RFC1213_MIB_atTable using the wmi cim studio tools available from microsoft for free download, you can find these if you browse to root/snmp/smir and look in snmpmacro/snmpobjecttype set objLocator = CreateObject("WbemScripting.SWbemLocator") set objServices = objLocator.connectServer("", "root/snmp/localhost") set objNamedValueSet = CreateObject("WbemScripting.SWbemNamedValueSet") objNamedValueSet.Add "AgentAddress", "localhost" objNamedValueSet.Add "AgentReadCommunityName", "public" objNamedValueSet.Add "AgentWriteCommunityName", "private" class = "SNMP_RFC1213_MIB_system" set objset =...

posted @ Tuesday, March 20, 2007 6:32 AM | Feedback (0) | Filed Under [ SA ]


this script has always had a fond place in my heart. here it is in it's original unadulterated glory!!! ' fork.vbs ' Roy Ashbrook - NT Sys Admin Group ' syntax: '  - fork.vbs infile outfile command batchsize ' purpose: '  - circumvent ability to only run one instance of a given shell command at a time. ' details: '  - reads in a list of machines from the infile '  - executes "command" however many times it needs to be run. '  - groups the executions into sizes of "batchsize" '  - at the end of every "batch" it reads the results in, then writes them to outfile ' notes: '  - only works on files with a single...

posted @ Tuesday, March 20, 2007 4:33 AM | Feedback (0) | Filed Under [ SA ]

testing with a random id in sql

this isn't exactly a new tip, but if you don't know, you can select a random id from a table using the following syntax:select top 1  ffrom  torder by  newid()so... if you need to perform random performance tests, you can use the above to retrieve a random id and then test against it. here's an example:declare @v varchar(20)select top 1  @v = citynamefrom  zipcodeorder by  newid()select  *from  zipcodewhere  cityname = @v  very simple, yes. but effective for quick testing. =)

posted @ Monday, March 19, 2007 9:16 PM | Feedback (1) | Filed Under [ DBA ]

optional sql parameters

if you have optional parameters in a stored procedure, this is a better way to do the comparison. by optional, i mean they will either be set to null, or will be null because they are not passed in or are otherwise not set. of course, realize indexing can affect performance as well as many other factors. you should always test. generally, this will be faster time wise. occasionally, i have found coalesce to incur more cpu time, but that is normally repaid by a faster turn around time as it seems to eliminate more rows quickly in the plan.this...

posted @ Saturday, March 17, 2007 5:02 PM | Feedback (1) | Filed Under [ DBA ]

object_id vs exists performance comparison

i previously blogged about using the object_id function in a drop statement instead of the usual way. i wondered how it did performance wise. it may be prettier, but is it faster? slower? the short answer is it appears to be faster to use the function than the default drops. for reference, we are talking about comparing this:if EXISTS (SELECT * FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[MSreplication_options]')  AND type in (N'U'))  drop proc dbo.msreplication_optionswith this:if (object_id('dbo.msreplication_options') is not null)  drop proc dbo.msreplication_optionsas you can see, the auto-generated drop script makes the same call can see some detailed results if you execute this:print '1'set...

posted @ Friday, March 16, 2007 8:21 PM | Feedback (0) | Filed Under [ DBA ]

tsql replace vs ltrim/rtrim performance

so after looking at a bunch of ltrim/rtrim statements that were just getting rid of the spaces, i wondered if i had values padded with spaces, would it be faster just to use the replace function in sql. the short answer is no. here's some test code. you should be able to mix and match or create comletely isolated sql statements if you like. the results were all the same when i tested, replace was always slower. increase the count if you want for a larget diff. declare @t table(id int identity(1,1), v varchar(20))while ((select coalesce(max(id),0) from @t) < 1000)  insert...

posted @ Wednesday, March 14, 2007 4:38 PM | Feedback (0) | Filed Under [ DBA ]

nopatchforstupid #4 - of trims and empty values

stupidselect f1 from t where ltrim(rtrim(f2)) != ''not stupidselect f1 from t where ltrim(f2) != ''why?you could use ltrim or rtrim by itself. why are you trimming both sides when you are searching for an empty. if it trims to empty, then either one will result in the same. the only time you would need both is if you actually want to trim a string on both sides, here you just want to exclude strings with just spaces.

posted @ Wednesday, March 14, 2007 4:06 PM | Feedback (0) | Filed Under [ DBA ]

nopatchforstupid #3

today's item of stupidity is the dynamic orderby clause. i see this particular abomination all over the place when i'm refactoring procs, reports, etc. firstly, why is this bad? firstly, most people include a null check and then build a dyanmic sql statement to execute. dynamic sql should always be avoided if possible. secondly, using this methodology leaves you vulnerable to injection attacks. finally for me, sorting can generally be done on the front end. normally the user wants to sort and resort etc, why would i want to sort it on the back end when they will just resort...

posted @ Wednesday, March 14, 2007 2:25 PM | Feedback (0) | Filed Under [ DBA ]

nopatchforstupid #2

stupid@@identitynot stupidscope_identity()

posted @ Wednesday, March 14, 2007 1:48 PM | Feedback (0) | Filed Under [ DBA ]

bulk insert trc files

here's how i bulk import trc files for analysis. in my example, i'm only selecting three fields, you can select more or less or all if you want depending on your needs. if you already have a table, obviously some of this is moot.SELECT textdata, duration, cpu into logdata FROM ::fn_trace_gettable('C:\log1.trc',1) iinsert logdataSELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log2.trc',1) iunion all SELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log3.trc',1) iunion all SELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log4.trc',1) iunion all SELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log5.trc',1) iunion all SELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log6.trc',1) iunion all SELECT textdata, duration, cpu FROM ::fn_trace_gettable('C:\log7.trc',1) iunion all...

posted @ Monday, March 12, 2007 7:49 PM | Feedback (0) | Filed Under [ DBA ]

nopatchforstupid #1

stupid 123456789101112131415161718192021222324252627282930313233343536 public bool CleanUpTable(decimal id) { bool bRet = true; try { ...

posted @ Friday, March 9, 2007 2:12 PM | Feedback (0) | Filed Under [ DEV ]

ping check - aka isup

@echo off set a=1 ping -n 1 %1 | find /c "bytes=32" > nul if %errorlevel% == 1 set a=0 echo %1 %a% set a= (via don't feed the penguins) *edit* this is a very simple shell script that i used to use for ping checks. i found that it even outperformed using the wmi ping and several other mechanisms for pinging machines that i tried. i'm sure i could rant on at length about all of the times and ways that i had used it, but i won't. note that you could just look for 1 as an error and just use error level, i wanted to...

posted @ Friday, March 9, 2007 2:05 PM | Feedback (0) | Filed Under [ SA ]

update statistics undocumented

these are undocumented for a reason as they will affect performance and you will break your sql production server if you use them! they are *really* helpful for simulations for query plan analysis without having large amounts of data though.original article hererock on with this cool code sample from above link:use tempdbgocreate table t1(i int, j int)gocreate table t2(h int, k int)goset statistics profile ongoselect distinct(i) from t1goselect * from t1, t2 where i = k order by j + kgoupdate statistics t1 with rowcount = 10000, pagecount = 10000update statistics t2 with rowcount = 100000, pagecount = 100000goselect distinct(i)...

posted @ Friday, March 9, 2007 2:03 PM | Feedback (0) | Filed Under [ DBA ]

How to find a stored procedure that contains certain text?


posted @ Friday, March 9, 2007 2:01 PM | Feedback (0) | Filed Under [ DBA ]

zipcode sql2005 endpoint sample/example

i went to look for a super simple way to setup a test of the endpoint feature in sql 2005. i couldn't find one simple enough, so i made this one up. here's some sql code. just run all of this. there are comments in here for what's going on. --copy the database file from-- save the txt file in it--to c:\projects if you want to --match my setup--create the databasecreate database zipcodesgo--use ituse zipcodesgo--create a tablecreate table zip_codes( zip varchar (255) , latitude varchar (50) , longitude varchar (50) , city varchar (50) , state varchar (50) , county varchar (50)...

posted @ Friday, March 9, 2007 1:59 PM | Feedback (0) | Filed Under [ DBA ]

SQL Query Processing

There are some distinct steps that take place when you submit some tsql to a mssql server. Here's a short overview of them.1. Parse Query - Check Syntax, Store Query2. Standardization - Prepare query for optimization3. Optimize4. ExecutionLet's look at #3 briefly. MSSql does Cost Based optimization which means it will utilize data it knows about the tables involved (statistics) to determine a cost for each possible execution path. It will then pick the lowest cost. MSSql has three steps to it's query optimization.1. Query Analysis - Identify optimizable clauses (x=1, name='fred')2. Index Selection - Choose indexes that have a lower cost than a...

posted @ Friday, March 9, 2007 1:58 PM | Feedback (0) | Filed Under [ DBA ]

drop object sql

IF (OBJECT_ID('objectname') IS NOT NULL) drop proc/table/etc objectnameGO

posted @ Friday, March 9, 2007 1:56 PM | Feedback (3) | Filed Under [ DBA ]

passing csv to sql is bad mm'kay

passing csv to a stored proc is bad m'kay. it leaves you open to sql injection attacks and in general isn't the best practice anyway. i'm going to avoid entire code samples just because it's too much typing, but let's take a sample proc and assume you are passing in a varchar(1000) (@csv) comma delimited string. you could do something like this: declare sql varchar(2000) set sql = 'select * from table where id in (' + @csv + ')' sp_executesql @sql obviously this will work fine if you pass in 1,2,3,4 into your list. but what if some bad person sends you 1,2,3,4,null);select...

posted @ Friday, March 9, 2007 1:48 PM | Feedback (0) | Filed Under [ DBA ]


if you live in a hole in the ground and haven't heard of asterisk, please, PLEASE go check it out. why? cuz it's cool and it's free that's why. well, as free as anything is. here's a great article on how to get this setup, i'm thinking seriously about setting this up at home.

posted @ Thursday, March 8, 2007 8:12 PM | Feedback (0) | Filed Under [ SA ]

multi-tenant architecture

Dave sent me the following link to examine. Take a moment to peruse it and see what you think. We currently use this in some apps and I’ve seen this strategy in place in the past as well. In general, I personally haven't seen a place where it's *really* needed. The mixture of all of the data together could normally be solved by the much simpler solution of copying the data structure for each party. If you have common tables, some simple tell could be done or a shared database could be setup. I think it *seems* simpler because you can program common items for each...

posted @ Thursday, March 8, 2007 4:44 PM | Feedback (2) | Filed Under [ ARCH ]

running wss 2 and wss 3 side by side

it's pretty easy. i think that there are probably several methods you could use to do this, but this how i do it. if you have 2 or 3 installed, remove them. make sure you have .net 2.0 and 3.0 installed. make sure you have your asp.nets marked as allowed in iis. install 3.0, take all defaults. in v3 central admin site, delete the default site, delete site and db install 2.0, take all defaults test 2.0 in v3 central admin site, create new, change db and use a unique url or port test 3.0 and rejoice the end.

posted @ Thursday, March 8, 2007 4:29 PM | Feedback (0) | Filed Under [ SA ]

Powered by: