Friday, August 6, 2010

Using Excel to double click to RDP to a server

I regret that it's been a while since my last post.  I started a new job, and it's kept me busy! In my new role I manage many SQL Servers.  For years I've used mstsc.exe from a shell to connect to an RDP session.  Since the customer lacks a CMDB I created a crude one in Excel.  I wanted some functionality to save time when I'm working down the list of servers.  So I looked into how I could make Excel a launch point for my RDP sessions.
Example Spreadsheet
Here's how it works.  Cell A has the hostname, Cell B has a Wingding with a colon character (which looks like a computer in Windows).  I bound some simple code to the Worksheet.BeforeDoubleClick Event.  It checks if the selected cell is in column B and then uses the range to find the value of the corresponding row's A column.  If the A column value is not an empty string it calls a function called launchRDP.  Note: I used column B instead of column A so that I could retain the default double click functionality in column A.  You could modify the code to work with column A but you might not like the end result.  

The code for the event driven macro is below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
    currentCell = Replace(Target.Address, "$", "")
    hostName = Range("A" & Right(currentCell, Len(currentCell) - 1)).Value
    
    If (Left(currentCell, 1) = "B") Then
        If (hostName <> "") Then
            Call launchRDP(hostName)
        End If
    End If
    
End Sub


This code for launchRDP is below (put this in a new module):

Sub launchRDP(serverName)

    If (serverName <> "") Then
        RDPWindow = Shell("C:\windows\system32\mstsc.exe /w:950 /h:900 /v:" & serverName, 1)
    End If
 
End Sub

Thursday, July 1, 2010

Motorola T-605


Introduction
This is a little off-topic since it doesn't have to do with computers.  Last year I purchased a Motorola T-605 Bluetooth hands free system for my car.  In my younger days I experimented quite a bit with automotive electronics so I felt I had both the experience and the interest to install it myself.

Effort * Quality = Results
With any skill, lack of use makes the neurons that drive the skill reluctant to participate.  However, if you can find the right leader, the rest of them soon fall in line.  Initially, I too was reluctant to commit to this effort.  I was hesitant to put in the time to run dedicated wiring for power and ground.  I thought that it might be alright to simply tap into the power and ground sources for my head unit.  This method worked about as good as the effort put into it: not very much.  The problem was that the wiring used by the vehicle's manufacturer just barely covers the amount of power required without introducing anomalies.  In fact, if the T-605 drew enough power, it could exceed the wire's ability to maintain it's temperature while delivering the electrons downstream.  So, this minimal amount of effort mixed with low-quality wiring led to the introduction of noise in the signal delivered to my head unit.

Size Matters
Wire selection is critical.  There are many types of wires available, stranded, solid core, shielded, insulated, etc... In this case, the wire needed to supply 3 amps at 12 v reliably over a distance of six feet.  The distance is important because the further you need electricity to travel, the more resistance you will experience with a given wire.  Wire selection can become complicated easily, but for my purposes I simply needed to select the proper gauge of insulated wire. The 24 gauge wires in my vehicle were insufficient to supply the kind of power the T-605 required.  Since six feet of wire isn't very expensive, I chose to over-engineer the solution by using 16 gauge wire dedicated to the T-605.

Effort makes a difference

Now that I was committed to completing the project and doing it correctly, I broke out my soldering tools.  I made sure that all wire connections were soldered and insulated properly.  Where I wanted to join the 16 gauge wire to the 18 gauge wire that the T-605 came with, I used a "Western Union" splice and then sealed it with solder and heat shrink wrap.  I like this splice because it's designed to become tighter if you should ever accidentally pull on it.


I used a couple of soldered quick-disconnect connectors to make it easy to service the T-605 should I ever have to remove it.  I used a pair near the battery and another pair near the T-605.


Western Union Splice


Conclusion
The point of this article is to illustrate the relationship and importance between effort and quality.  If you work to maximize both, the project will take MUCH longer but you will be much less likely to re-visit the effort in the future.  I know this from my experience managing projects, but in my personal life, time is so limited that sometimes I neglect to remind myself of my professional experience.  In this case, I didn't come to this conclusion on my own.  I had a discussion with one of Motorola's excellent support engineers.  He was patient and led me to the conclusion that I had simply done a poor job without being condescending.  I really appreciated his help.  More than a year later, the T-605 remains in my vehicle and works great.  I've enjoyed streaming blogs, audio books and Pandora over my factory head unit.  My kids love listening to children's audio books as much as I do.  It's been a life-extender for my vehicle.  I recommend trying a project like this on your own if you should ever feel the inclination, just remember to consider Quality and Effort!

Tuesday, March 9, 2010

WMI Resources

Why I use WMI
The company I work for has strict application performance SLAs. I am responsible for "near real-time" performance monitoring of the systems that my company uses to process business. The recession hasn't been kind to us and our budget is tight so often I find myself using alternatives to traditional Enterprise products for monitoring our environments. With that in mind, WMI is an extremely powerful tool for monitoring Microsoft Windows environments.  WMI provides a simple interface to Windows Performance Monitor objects (aka: Perfmon).


Limitations
To be fair, I must confess that WMI has some limitations.  We mitigate the maximum query per second limits by spreading the requests across many servers in our management and monitoring infrastructure.  These servers need not be powerful, but you will need more than one OS.  This is a good case for virtualization of WMI query servers.  We take advantage of our VMware vSphere infrastructure to accomplish this.   


How we use WMI
One of the tools we use in our environment is Paessler PRTG Network Monitor. PRTG is heavily dependent on WMI for it's out of the box monitoring. We also use it's custom capabilities including PowerShell and VBScripting but until they can support multiple channels from one script, it's fairly limited.  In the future I'll expand on scripting and the how much control you can gain through WMI.

For now, I've collected some of my favorite resources for WMI and I intend to maintain them on this page for my own reference and hopefully for your benefit as well.

Microsoft References:
FAQ - Brief synopsis of WMI & useful scripts
Classes - Classes defined by WMI
Providers - Preinstalled providers for WMI managed objects
COM API- COM interfaces to WMI
Scripting API - Components of the Scripting API for WMI
WQL - WMI query language

Log Files - WMI & providers logging & troubleshooting
Security- Security objects and methods to manipulate security or privileges.
Command-Line Tools -Syntax used by mofcomp, winmgmt, wmic, smi2smir, and wmiadap.
Infrastructure Objects and Values - WMI return types
Basic WMI Testing - Windows Performance Team discussion on troubleshooting WMI



Paessler References
WMI Code Error 80041010 - Invalid Class'
Article: Don't Use Windows Vista And Windows 2008 for Network Monitoring via WMI!
WMI sometimes gives object not found errors because the WMI schema on the Windows OS being monitored is out of date. If you encounter this condition you must run: wmiadap /f on the device in question. Paessler has an extensive help resource for WMI issues.
http://www.paessler.com/support/kb/prtg7/wmi_not_working


Tools & Downloads:

Free WMI Tools from Microsoft:
Other free WMI tools:

Wednesday, March 3, 2010

Powershell Glass for Windows Vista & Windows 7

If you want to dress up your Powershell window all you need is Powershell Glass by weloytty. I can think of no fancier way to use Powershell.

Koobface Removal

A friend asked me to do a quick write-up of the Koobface Removal Instructions for sharing with our mutual friends on Facebook. Here's what I did to remove it from another person's computer. Alternatively, you could follow Symantec's removal instructions.
  1. Download the Malicious Software Removal Tool: http://www.microsoft.com/security/malwareremove/default.aspx
  2. Reboot with F8 into safemode, be sure watch the bottom of the screen for a prompt hit ESC to skip loading drivers. I always like to shutdown the computer before booting just to be sure that the RAM has been purged. It's a habit I developed back when computers with 4 GB hard drives were all the rage.
  3. Run the Malicious Software Removal Tool with a full scan (will take a couple of hours)
  4. Hopefully this will identify and remove it, it does give you a prompt in the end explaining what it did.

Tuesday, March 2, 2010

PRTG, MSMQ and PowerShell



Recently I discovered the limitations of WMI when it comes to monitoring MSMQ running in a cluster. I researched the solutions and didn't like the mess I found. One of my MSMQ savvy colleagues wrote me a VBScript utility to connect to queues count the number of messages. His solution was great because it guaranteed that we would have accurate message count data back from the queues and simultaneously tested the queue's up-time. Unfortunately, we discovered that this isn't perfect. The problem is that in order to get the message count we had to create a MSMQ.MSMQManagement COM object (YUCK!).

 Since we're performance minded folks, we monitored cscript.exe while it ran our script. We were shocked to find that although it ran for less than 2 seconds, it used ~160 MB of RAM and 99% of the CPU. This was totally unacceptable. We have over a hundred queues to monitor, and it would crater our monitoring environment. So then we moved on to plan B. I blew the dust off of a Powershell book someone let me borrow that I had been aggressively ignoring.

At first I found Powershell confusing, it was familiar in many ways, but unfamiliar in just as many. Fortunately, the book and with the encouragement of my book's owner I was able to throw down a script to mimic the work of the VBscript but using .NET's System.Messaging's Message Queue class. The trick was making the script reliable so that if it failed, our monitoring tool (PRTG) could send the operator a friendly message. It turns out that it was much easier than I expected. Without further ado, I give you the PRTG MSMQ Monitoring Powershell
Script:



Thursday, February 11, 2010

February 11 is BOSD day!

Windows patch cripples XP with blue screen, users claim Microsoft users are reporting on the company's support forum that Tuesday's security updates are crippling Windows XP-based PCs. For recovery, try: Note: you have an encrypted drive, you'll need to follow your software vendor's decryption instructions before you follow these. PointSec's instructions are available below these recovery instructions. 1. Boot from your Windows XP CD or DVD and start the recovery console (see this Microsoft article for help with this step) Once you are in the Repair Screen type: 2. CHDIR $NtUninstallKB978262 $\spuninst 3. BATCH spuninst.txt 4. systemroot 5. Repeat steps 2 - 4 for each of the following updates: * KB978262 * KB971468 * KB978037 * KB975713 * KB978251 * KB978706 * KB977165 * KB975560 * KB977914 6. When complete, type this command: exit Thanks to maxyimus and FindbyFollowMe on the Microsoft Forums for their research and suggestions: http://social.answers.microsoft.com/Forums/en-US/vistawu/thread/73cea559-ebbd-4274-96bc-e292b69f2fd1/#e9b28c45-635c-4adf-8d24-817bf39c207 PointSec Recovery Procedure Pointsec has two methods to recover data from an encrypted system, bootable recovery disk and connecting the encrypted hard disk to another machine (Slave Drive). Only Computer Support Coordinators (CSC) and OAAIS Enterprise Information Security (EIS) can perform recovery tasks. In order to perform the following tasks, you must have Pointsec installed on your machine or access to a machine that has Pointsec installed. Pointsec Management Console requires Microsoft .net 2.0 later. http://www.microsoft.com/downloads/details.aspx?FamilyID=0856eacb-4362-4b0d- 8edd-aab15c5e04f5&displaylang=en Bootable Recovery Disk A common scenario in which recovery is required is when something fails in a computer that is protected by Pointsec PC, and you are unable to start windows properly. To Remedy this problem, the administrator creates a bootable media on another computer, using the recovery file of the failed computer. The administrator, or whoever is performing the recovery, then uses the bootable media to recover the faulty computer. The bootable media performs the following tasks:
  • Enables the administrator to recover data on the faulty computer.
  • Decrypts the faulty computer’s encrypted volumes.
  • Removes the pre-boot authentication from the faulty computer.
  • Gives direct access to Windows on the faulty computer once decryption has completed successfully.
  • Pointsec PC stores the recovery file in two locations, locally in the directory C:\Documents and Settings\All Users\Application Data\Pointsec\Pointsec for PC and on the Pointsec file server.
Note: Recovery can only be performed if you have the appropriate Pointsec administrator access on the machine that has Pointsec installed. Creating a Recovery Disk
  1. Insert a USB memory stick or floppy disk into your computer. oNote: Pointsec will erase the contents of the disk.
  2. Open Pointsec Management Console - Start Menu-> All Programs-> Check Point -> Pointsec PC-> Management Console
  3. Login to the Pointsec Management Console with your username and password and select Remote from the left column. Click on Create recovery media.
  4. The recovery wizard will open, and follow the on screen prompts to select the machine’s recovery file.
  5. After the recovery file is chosen Pointsec will prompt for administrator authentication, enter in both administrator accounts in to the screen prompts.
  6. Once authentication is complete select which device the recovery disk will be created on.
Sourced from the PointSec Administration Guide.

Monday, January 25, 2010

How to run IE in safe mode

Every few months I find myself hopelessly annoyed at IE because it freezes, crashes or is otherwise useless. It's worth spending time disabling the old add-ons that are so shamefully inefficient that they frequently crash my browser. Unfortunately, you can't get to them unless you can actually start your browser. Enter safe mode. Microsoft published information about IE's safemode in KB article 936213 (Vista & Win 7 only) which also talks about how to purge and/or reset your IE settings. If all else fails, Microsoft published FixIt 50195 that you can download and will effectively reset all of your settings. Whis will cover XP as well as Vista and Windows 7. Reference: http://support.microsoft.com/kb/923737

Tuesday, December 22, 2009

Doctor rating websites seem to miss the point

I'm really dissapointed with websites rating doctors. Everyone who the doctor has ever agravated speaks up but no one talks about good experiences. Also, nobody seems to rate what really matters. Besides general better business ratings, I'd like to see more information like this for doctor's offices. Am I off base? Did I miss the best ratings website with my web search?
  1. Was the staff courteous and friendly?
  2. Was the process for registering as a new patient quick and straight forward?
  3. Did the disclosure and consent forms make you want an attorney present?
  4. Was the waiting room clean and comfortable?
  5. If you were on-time, did you have a long wait?
  6. If your were late, were you reminded that you would have to wait until there was a break in the schedule so that those who were on time were not penalized?
  7. After you're called to the examination room, did you have to wait long for the Doctor?
  8. Was there a clear set of credentials (diploma, specialities, etc...) prominently displayed?
  9. Did the doctor attend a University in the United States?
  10. Was the doctor courteous and generous with information?
  11. Do you wish the doctor spent more time talking to or examining you?
  12. If you received a prescription, did you get information about the drug, it's side effects and how to take it from your doctor?
  13. Was the checkout process quick and courteous?
  14. Did they get your medical coding correct so that the insurance company doesn't send you EOB statements that you'll have to follow up on?
  15. If you later made a telephone call, were you called back (within an hour, same day, next day, later, never)
  16. Who returned your calls? (the receptionist, a nurse, a physician's assistant, a doctor, your doctor)
  17. Would you recommend this doctor to a friend or family member?

Wednesday, November 25, 2009

Office 2007 Copy + Paste behavior & Defaults

I genuinely dislike the default paste functionality in Office products (although it seems that in Office 2010 they have finally addressed the issue.) I'm sure if you're at this page it's because you also have the same distaste. Fret not a moment longer. In Office 2007 after you paste text there is a small icon that looks like the paste toolbar button. Some of you might have even rolled your eyes at Microsoft for having such a thing. However, the key to changing the default copy+paste behavior is there! Simply click the icon and choose "Set Default Paste..." Now you'll be taken to a screen where you can set the default copy+paste behavior!!! Enjoy.

Tuesday, November 10, 2009

SNMP Walk Tools

SNMP-Probe 2.0

A tree orientated, optimized, graphical representation of a SNMP Walk output.

AdRem SNMP Manager 1.0.1.30

Enterprise-class console for full-fledged SNMP control of remote network devices.

Unbrowse SNMP 1.6

Essential SNMP tool for MIB Walking, Trap monitoring, and SNMPv3 admin.

Freeware Multi/Dual Monitor Tools

I've compiled a list of helpful tools I use in my multi/dual monitor configurations. These are all freeware. You may download them from my computer. I excluded one that is commercial (Ultramon) because it's not free although it is outstanding as well.

Explanations are below:

Sizer – allows right-click on taskbar of any window to resize to configurable sizes.

WinSplit Revolution – you have to use this one to really understand it, but it's my favorite. It breaks the screen into quadrants and allows you to use CTRL+ALT and the number keys to arrange the windows on the screen. For example, to make a window use half of the screen, you use CTRL+ALT+6. If you want it to use ¼ of the screen you use CTRL+ALT+9.

DisplayFusion – a few little features in the free version, the pro version has additional features.

  • Drag maximized windows between screens
  • Middle click on a window to move it to another screen
  • Wallpaper – allows you to run different wallpaper on different screens

Wednesday, October 21, 2009

Windows Server Memory Pressure Drops connections

We were having problems with dropped connections on some of our busiest SQL Servers. We couldn't figure out what was the cause until we discovered this:

Tuesday, October 6, 2009

T-SQL Don't forget to defrag the hard drive!

I'm not sure how many folks actually de-fragment the drives their databases run on. If you grow or shrink your database files on demand or on schedule you should really consider checking your drives for fragmentation.
Tip: Fighting OS-Level Fragmentation by Brian Moran
Reading the SQL Server log files using T-SQL Written By: Greg Robidoux http://www.mssqltips.com/tip.asp?tip=1476

T-SQL Locking, Blocking and Waiting

Microsoft's reference on Lock Events: http://msdn.microsoft.com/en-us/library/ms177493.aspx Microsoft SQL Server 2005 Waits and Queues troubleshooting guide (the document is at the bottom): http://technet.microsoft.com/en-us/library/cc966413.aspx INF: Understanding and resolving SQL Server blocking problems (KB224453) SQL Dev's exhaustive list of wait types: http://www.sqldev.net/misc/waittypes.htm Wait Types:
Wait Type NameNumeric Wait Type Description
MISCELLANEOUS0x00Collection bucket for all unknown wait types, should be zero. In any case it does not represent any meaningful data.
LCK_M_SCH_S0x01Schema stability lock
LCK_M_SCH_M0x02Schema modification lock
LCK_M_S0x03Share lock
LCK_M_U0x04 Update lock
LCK_M_X0x05Exclusive lock
LCK_M_IS0x06Intent-Share lock
LCK_M_IU0x07Intent-Update lock
LCK_M_IX0x08Intent-Exclusive lock
LCK_M_SIU0x09Shared intent to update lock
LCK_M_SIX0x0AShare-Intent-Exclusive lock
LCK_M_UIX0x0BUpdate-Intent-Exclusive lock
LCK_M_BU0x0CBulk Update lock
LCK_M_RS_S0x0DRange-share-share lock
LCK_M_RS_U0x0ERange-share-Update lock
LCK_M_RIn_NL0x0FRange-Insert-NULL lock
LCK_M_RIn_S0x10Range-Insert-Shared lock
LCK_M_RIn_U0x11Range-Insert-Update lock
LCK_M_RIn_X0x12Range-Insert-Exclusive lock
LCK_M_RX_S0x13Range-exclusive-Shared lock
LCK_M_RX_U0x14Range-exclusive-update lock
LCK_M_RX_X0x15Range-exclusive-exclusive lock
GROUP0x20All types with 0x20 are now used for I/O COMPLETION
SLEEP0x20This waittype indicates that the SPID is waiting for a specified time and is a common state for the background threads that process the lazywrites, the checkpoints, or the server-side profiler trace events.
IO_COMPLETION0x21This waittype indicates that the SPID is waiting for the I/O requests to complete. When you notice this waittype for an SPID in the sysprocesses system table, you must identify the disk bottlenecks by using the performance monitor counters, profiler trace, the fn_virtualfilestats system table-valued function, and the SHOWPLAN option to analyze the query plans that correspond to the SPID. You can reduce this waittype by adding additional I/O bandwidth or balancing I/O across other drives. You can also reduce I/O by using indexing, look for bad query plans, and look for memory pressure.
ASYNC_IO_COMPLETION0x22This waittype indicates that the SPID is waiting for the asynchronous I/O requests to complete. Like the IO_COMPLETION waittype, this waittype also indicates an I/O bottleneck. You may see this waittype for the SPIDs during the long-running I/O-bound operations, such as BACKUP, CREATE DATABASE, ALTER DATABASE, or the database autogrow. This waittype may also indicate disk bottlenecks
GROUP0x40All type with 0x40 do not below to a specific group
RESOURCE_SEMAPHORE0x40This waittype indicates that the SPID is waiting on a resource. Here, the SPIDs generally wait to acquire the memory for the sorting or the hashing operation during the query execution. This waittype may also indicate that memory pressure exists in the visible part of the buffer pool.

When an SPID is waiting and the RESOURCE_SEMAPHORE waittype is logged in the sysprocesses system table for the SPID, this may also indicate that there are many SPIDs that are waiting for query optimizations. You cannot differentiate whether the SPID is waiting for a query optimization or is waiting for a Memory object just by seeing the waittype. Therefore, you must run the DBCC Memorystatus and review the results of the performance monitor trace.

Note You can monitor the SPIDs that are waiting in the query optimization queue by using the DBCC MEMORYSTATUS Transact-SQL statement.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 271624 Using DBCC MEMORYSTATUS to monitor SQL Server memory usage

DTC0x41This waittype indicates that the SPID is waiting on the Microsoft Distributed Transaction Coordinator (MS DTC) service.
OLEDB0x42This waittype indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This waittype may also indicate that the SPID is waiting for remote procedure calls or linked server queries to return the required data. The SPID may also be waiting for BULK INSERT commands or full-search queries to return the required data.
FAILPOINT0x43
RESOURCE_QUEUE0x44This is an ordinary “idle” state for background threads in SQL Server.
ASYNC_DISKPOOL_LOCK0x45You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file.
UMS_THREAD0x46This waittype indicates that a batch has been received from a client application but that there are no worker threads that are available to service the request. If you consistently see 0x0046 waittypes for multiple SPIDs, there is a significant bottleneck elsewhere in the system that is using all the available worker threads. Note that the waittime column is always 0 for the UMSTHREAD waittype, and the lastwaittype column may erroneously show the name of a different waittype instead of UMSTHREAD."
PIPELINE_INDEX_STAT0x47// All types with values between PWAIT_PIPELINE_BASE and // PWAIT_LAST_PIPELINE_BASE are now used for PIPLINE IDs. Each of these wait types // directly correlate to an enum type PipelineId, which is used by class // PipelineRequest. If a new PiplineId is added, then a new wait type must also // be added here. //
PIPELINE_LOG0x48
PIPELINE_VLM0x49
GROUP0x80All types with 0x80 bit set relate to DBTABLE type of waits
WRITELOG0x81This waittype indicates that the SPID is waiting for a transaction log I/O request to complete. This waittype may also indicate a possible disk bottleneck (Waiting on a writelog )
LOGBUFFER0x82Waiting on a free buffer
GROUP0x100All types with 0x100 bit set is waiting for a pss
PSS_CHILD0x101These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
GROUP0x200All types with 0x200 bit set are using upwait ()
EXCHANGE0x200These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
XCB
DBTABLE0x202This waittype indicates that a thread is waiting to perform a checkpoint and another thread is already checkpointing the database.
EC0x203This waittype indicates that the SPID is waiting for access to execution context.
TEMPOBJ0x204This waittype indicates that the SPID is waiting to drop a temporary object that is still being used.
XACTLOCKINFO0x205This waittype indicates that the SPID is waiting to perform maintenance on its lock list.
LOGMGR0x206This waittype is used when the SPID tries to shut down a database and waits for the pending transaction log I/O requests to complete.
CMEMTHREAD0x207This waittype indicates that the SPID is waiting for access to a thread-safe memory object. The serialization makes sure that while the users are allocating or freeing the memory from the memory object, any other SPIDs that are trying to perform the same task have to wait, and the CMEMTHREAD waittype is set when the SPIDs are waiting. You may notice this waittype in many scenarios. However, this waittype is most frequently logged when the ad hoc query plans are being quickly inserted into a procedure cache from many different connections to the instance of SQL Server. You can address this bottleneck by limiting the data that must be inserted or removed from the procedure cache, such as explicitly parameterizing the queries so that the queries can be reused or using stored procedures where appropriate.
CXPACKET0x208These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
PAGESUPP0x209This waittype tracks the wait time that is incurred because of the required serialization in distributing rows to multiple callers in a parallel scan.
SHUTDOWN0x20AThis waittype indicates that a SHUTDOWN command has been issued by the SPID, and the SPID is waiting for active queries to complete.
WAITFOR0x20BThis waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement.
CURSOR0x20CThis waittype indicates that the SPID is participating in the thread synchronization while it uses asynchronous cursors. The sp_configure ‘cursorthreshold’ configuration setting may determine when a cursor is created asynchronously.
EXECSYNC0x20DGeneral sync during execution
GROUP0x400All types with 0x400 bit set are latch types
LATCH_NL0x400NULL latch
LATCH_KP0x401Keep latch
LATCH_SH0x402Shared latch
LATCH_UP0x403Update latch
LATCH_EX0x404Exclusive latch
LATCH_DT0x405Destroy latch
PAGELATCH_NL0x410NULL buffer page latch
PAGELATCH_KP0x411Keep buffer page latch
PAGELATCH_SH0x412Shared buffer page latch
PAGELATCH_UP0x413Update buffer page latch
PAGELATCH_EX0x414Exclusive buffer page latch
PAGELATCH_DT0x415Destroy buffer page latch
PAGEIOLATCH_NL0x420NULL buffer page I/O latch
PAGEIOLATCH_KP0x421Keep buffer page I/O latch
PAGEIOLATCH_SH0x422Shared buffer page I/O latch
PAGEIOLATCH_UP0x423Update buffer page I/O latch
PAGEIOLATCH_EX0x424Exclusive buffer page I/O latch
PAGEIOLATCH_DT0x425Destroy buffer page I/O latch
TRAN_MARK_NL0x430NULL transaction latch
TRAN_MARK_KP0x431Keep transaction latch
TRAN_MARK_SH0x432Shared transaction latch
TRAN_MARK_UP0x433Update transaction latch
TRAN_MARK_EX0x434Exclusive transaction latch
TRAN_MARK_DT0x435Destroy transaction latch
NETWORKIO0x800This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.

Searching all columns in a database for data

It's too bad that there's not a set-based way to search the content of all of the columns of a database. Here's an approach to the problem: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm. My concern with the approach is that it will likely create full page scans across the data but I haven't thought of a better approach. Perhaps when I find some free time I'll take a stab at it.

Really great looking dashboards

There's a tool called Microcharts that I really like made by Bonavista systems. It lets you create really great looking dashboards with minimal effort. Anyone interested in aggregating lots of data into a KPI dashboard should look into it. They make great use of Edward Tufte's sparklines. Below are some examples. They also have a plugin for Excel that makes creating microcharts as simple as a formula. Check out this article: http://www.experiglot.com/2007/01/10/bonavista-microcharts-a-very-cool-excel-charts-add-in/

Microsoft on Disk Tuning & Performance

http://technet.microsoft.com/en-us/library/cc938959.aspx

Monday, October 5, 2009

The dreaded TokenAndPerfmUserStore cache issue

I recently encountered this issue in the wild. It's worth implementing a tool to record the size of your TokenAndPermUserStore before you run into problems of your own.

Problem

There is a bug (KB:927396) with SQL Server 2005 related to TokenAndPermUserStore cache. The bug appears to exist in all versions and under all service packs for SQL 2005. Additionally the bug has different manifestations which may require different hotfixes. The users in DBA forums do not believe that the hotfixes fully address issue. There is at least one hotfix which requires contacting Microsoft support to obtain. At this time I cannot confirm that we need that hotfix.

Systems exhibiting the issue do not always show the same symptoms. However, the commonality I found in documented experiences is that all of these servers have large amounts of RAM, have heavy transaction volumes and at some point in time become unable to accept new connections or run queries.

The TokenandPermUserStore cache is used by SQL Server to store security related information. The items stored in this cache include: LoginTocken, SecContext Token, TokenAccessResult, TokenPerm and UserToken. On RECHOUVSQL01 the majority of the tokens are of class 65535.

Microsoft Customer Service and Support (CSS) offer an article with technical details on the issue.

Symptoms

Queries that typically run faster take a longer time to finish running.

CPU usage of SQL Server process is relatively higher. CPU usage could come down after remaining high for a period of time.

Connections from your applications keep increasing (specifically in connection pool environments)

You encounter connection or query timeouts

When you experience decreased performance when you run an ad hoc query, you view the query from the sys.dm_exec_requests or sys.dm_os_waiting_tasks dynamic management view. However, the query does not appear to be waiting for any resource.

The size of the TokenAndPermUserStore cache store grows at a steady rate.

The size of the TokenAndPermUserStore cache store is in the order of several hundred megabytes (MB).

In some cases, execution of the DBCC FREEPROCCACHE command provides temporary relief.

References and Documentation

Microsoft KB Explaining Symptoms (927396)

Query for freeing TokenAndPermUserStore when it reaches 100 MB

Query Performance issues associated with a large sized security cache (very detailed)

Related Hotfixes:

Service Pack 3

KBA: 959823 - How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3

Service Pack 2

Cumulative Updates: In order to get all of these fixes, you can install the Cumulative update package 3 for SQL Server 2005 Service Pack 2. This will take you to build 9.00.3186.00. You might also install a later Cumulative Update package and that will include all of these fixes as well.

SQL Server 2005 SP2 build [9.00.3042.00]

SQL Server 2005 post SP2 hotfix build [9.00.3153.00]

SQL Server 2005 post SP2 hotfix build [9.00.3171.00]

SQL Server 2005 post SP2 hotfix build [09.00.3179.00]

  • Fix to prevent Memory consumption increase by the TokenandPermUserStore even if the number of entries does not increase
  • KBA: 939871: Not yet published to support.microsoft.com

Simulating High Latency and Low Bandwidth

John Paul Cook has a nice article about using Shunra VE Desktop to force Windows to delay packets for testing databases. I think the product could be useful in many more scenarios. http://sqlblog.com/blogs/john_paul_cook/archive/2008/04/13/simulating-high-latency-and-low-bandwidth-for-database-connectivity-testing.aspx

Reviews on Network & Application Performance Monitoring Tools

It's not very detailed but EnterpriseITPlanet.com has aggregated a list of various tools under one roof. More than anything it helps to market network monitoring tools. I find it's hard to find a listing of all of them under one roof. http://products.enterpriseitplanet.com/networking/performance/recent1.html

SQL Profiler can be a dangerous tool

Left unchecked SQL Profiler can be a dangerous tool. There are some conditions where a trace that has worked safely in the past can wreak havoc on a production server.

FIX: CPU utilization is high when you run a trace that contains a text filter in SQL Server 2005 (http://support.microsoft.com/kb/953496) Note: sometimes I find that Microsoft fixes don't always completely resolve an issue. This can simply happen because the cause of the issue is not well understood. I like to consider them even if the fix is in place. Microsoft guidance on using Profiler: http://support.microsoft.com/kb/224587/ Microsoft SQL Performance Troubleshooting Guidance: http://support.microsoft.com/kb/298475 Patrick LeBlanc's article on using WildCard Filter http://www.sqlservercentral.com/blogs/sqldownsouth/archive/2009/08/21/sql-profiler-wild-card-filter-on-textdata.aspx SQL Server Central Forums discussion on trace creating CPU and Thread waits: http://www.sqlservercentral.com/Forums/Topic765171-360-1.aspx

Friday, August 21, 2009

Wednesday, August 19, 2009

T-SQL Using GO for Running a Query Multiple Times

Starting in SQL 2005 you can use GO to run a query multiple times. For example, this query will insert the date into the DateCreated column 10 times: CREATE TABLE dbo.TestTable(ID INT IDENTITY (1,1), DateCreated datetime) INSERT INTO dbo.TestTable (DateCreated) VALUES (GetDate()) GO 10 Reference: http://www.mssqltips.com/tip.asp?tip=1216

Learn about how cars work

FamilyCar.com has a nice Cars 101 for those who are intimidated by cars. I thought it would be good to promote the articles: http://www.familycar.com/classroom/Autoshop101.htm http://www.familycar.com/classroom/Autoshop101.htm

Wednesday, June 24, 2009

Quirky Keyboard Mappings on Macs running Windows in BootCamp

By now most people know that you can run Windows on an Intel-based Mac. I use this feature often and at some point found myself lost with regard to keyboard mappings. Fortunately, there's a way to learn them. http://support.apple.com/kb/HT2587

Tuesday, June 23, 2009

Apple Boot Key Combos: Bypass startup drive and boot from external (or CD).... CMD-OPT-SHIFT-DELETE Boot from CD (Most late model Apples) ................. C Force the internal hard drive to be the boot drive .... D Boot from a specific SCSI ID #.(#=SCSI ID number)...... CMD-OPT-SHIFT-DELETE- #Zap PRAM .............................................. CMD-OPT-P-R Boot into open Firmware ............................... CMD-OPT-O-F Clear NV RAM. Similar to reset-all in open Firmware ... CMD-OPT-N-V Disable Extensions .................................... SHIFT Rebuild Desktop ....................................... CMD-OPT Close finder windows.(hold just before finder starts).. OPT Boot with Virtual Memory off........................... CMD Trigger extension manager at boot-up................... SPACE Force Quadra av machines to use TV as a monitor........ CMD-OPT-T-V Boot from ROM (Mac Classic only)....................... CMD-OPT-X-O Force PowerBooks to reset the screen................... R Force an AV monitor to be recognized as one............ CMD-OPT-A-V Eject Boot Floppy...................................... Hold Down Mouse Button Select volume to start from............................ OPT Start in Firewire target drive mode.................... T Startup in OSX if OS9 and OXS in boot partition........ X or CMD-X Attempt to boot from network server ................... N (Hold until Mac Logo appears) Hold down until the 2nd chime, will boot into 9?....... CMD-OPT OSX: Watch the status of the system load............... CMD-V OSX: Enter single-user mode (shell-level mode)......... CMD-S After startup: Bring up dialogue for shutdown/sleep/restart........... POWER Eject a Floppy Disk.................................... CMD-SHIFT-1 or(2) or (0) Force current app to quit.............................. CMD-OPT-ESC Unconditionally reboot................................. CTRL-CMD-POWER Fast Shutdown.......................................... CTRL-CMD-OPT-POWER Goto the debugger (if MacsBug is installed)............ CMD-POWER Put late model PowerBooks & Desktops to sleep.......... CMD-OPT-POWER Controlling the Post-Startup Environment Most Macintosh users know about holding the Shift key down to prevent extensions from loading, but there are numerous startup modifiers that affect the state of the system after the boot process finishes. * Shift causes the Mac to boot without extensions, which is useful for troubleshooting extension conflicts. If you hold down Shift after all the extensions have loaded but before the Finder launches, it also prevents any startup items from launching. * Spacebar launches Apple's Extensions Manager early in the startup process so you can enable or disable extensions before they load. Casady & Greene's Conflict Catcher, if you're using it instead of Extensions Manager, also launches if it sees you holding down the spacebar, or, optionally, if Caps Lock is activated. Conflict Catcher also adds the capability to configure additional startup keys as ways of specifying that a particular startup set should be used. Choose Edit Sets from the Sets menu, select a set in the resulting dialog and click Modify. In the sub-dialog that appears, you can specify a startup key and check the checkbox to make it effective. * Option, if held down as the Finder launches, closes any previously open Finder windows. On stock older Macs, holding down Option does nothing at startup by default, although some extensions may deactivate if Option is held down when they attempt to load; see below for Option's effect on new Macs and Macs with Zip drives. * Control can cause the Location Manager to prompt you to select a location. Although Control is the default, you can redefine it in the Location Manager's Preferences dialog, and since Control held down at startup also activates Apple's MacsBug debugger (see below), you may wish to pick a different key combination. * Command turns virtual memory off until the next restart. * Shift-Option disables extensions other than Connectix's RAM Doubler (and MacsBug - see below). To disable RAM Doubler but no other extensions, hold down the tilde (~) key at startup. Choosing Startup Disks Many of the startup modifiers affect the disk used to boot the Mac. A number of these are specific to certain models of the Macintosh. * The mouse button causes the Mac to eject floppy disks and most other forms of removable media, though not CD-ROMs. * The C key forces the Mac to start up from a bootable CD-ROM, if one is present, which is useful if something goes wrong with your startup hard disk. This key doesn't work with some older Macs or clones that didn't use Apple CD-ROM drives; they require Command- Shift-Option-Delete instead (see below). * Option activates the new Startup Manager on the iBook, Power Mac G4 (AGP Graphics), PowerBook (FireWire), and slot-loading iMacs. The Startup Manager displays a rather cryptic set of icons indicating available startup volumes, including any NetBoot volumes that are available. On some Macs with Iomega Zip drives, holding down Option at startup when there is a Zip startup disk inserted will cause the Mac to boot from the Zip disk. * Command-Shift-Option-Delete bypasses the disk selected in the Startup Disk control panel in favor of an external device or from CD-ROM (on older Macs). This is also useful if your main hard disk is having problems and you need to start up from another device. (On some PowerBooks, however, this key combination merely ignores the internal drive, which isn't as useful.) * The D key forces the PowerBook (Bronze Keyboard and FireWire) to boot from the internal hard disk. * The T key forces the PowerBook (FireWire) (and reportedly the Power Mac G4 (AGP Graphics), though I was unable to verify that on my machine) to start up in FireWire Target Disk Mode, which is essentially the modern equivalent of SCSI Disk Mode and enables a PowerBook (FireWire) to act as a FireWire-accessible hard disk for another Macintosh.

Wednesday, April 1, 2009

Wikipedia on Network Monitoring

Wikipedia has a nice comparison chart of various network monitoring systems. Too bad it doesn't output as a table so that you can import it into Excel and filter the columns. http://en.wikipedia.org/wiki/Comparison_of_network_monitoring_systems

Style for Programmers and DBAs

I'm always interested in the best way to approach a problem.  More often than not, I just don't have the time to sort it all out so I try to keep my mind fresh by reading through other people's solutions.  In this case I'm interested in programming style. 

In the "real" world grammar is a an agreed upon style for the language that helps us understand other people's writing.  In application development style helps us better understand what other people's code does (or was it was supposed to do).

Programming style encompasses naming conventions, structure, naming standards and more. Adhering to style helps others better understand your work. Below are various sources of information I have found on the subject.  I hope this set of references is useful to you as well.



Capitalization Rules:

Database Naming Conventions:
Microsoft .NET Technologies

C# Coding Style:
Metadata Conventions
  • ISO/IEC 11179-1: Information technology — Metadata registries (MDR) — Part 1:Framework (2nd Edition, 2004-09-15)
  • ISO/IEC 11179-2: Information technology — Metadata registries (MDR) —Part 2: Classification (2nd Edition, 2005-11-15)
  • ISO/IEC 11179-3: Corrections to ISO/IEC 11179-3
  • ISO/IEC 11179-4: Information technology – Metadata registries (MDR) – Part 4: Formulation of data definitions (2nd Edition, 2004-07-15)
  • ISO/IEC 11179-5: Information technology – Metadata registries (MDR) – Part 5: Naming and identification principles (2nd Edition, 2003-02-15)
  • ISO/IEC 11179-6: Information technology – Metadata registries (MDR) – Part 6: Registration (2nd Edition, 2005-01-15)
Joel's Spolsky's Blog:
Other useful stuff


Books you can buy to help:








    What is Autodidactech?

    Autodidacticism is a ridiculously long word for self-study. I have been an avid autodidact for many years. In fact, I probably spend more time in self-study than any hobby I have taken up. We're all autodidacts from time to time, but the purpose of this blog is to aggregate the various places one can go to learn about topics that interest me. I do this for myself, but I make it public to the world because at least one other person might find what I record useful.
    A common misconception is that autodidacts are lonely people. On the contrary, I find that many autodidacts prefer to engage in dialogue with others because it enhances the process.
    Autodidactech is simply a collection of useful references for myself and others who, like me, are interested in learning new things.