Drowning In Technical Debt

'net' command (net user, net localgroup, etc)

royashbrook — Tue, 29 Jul 2008 17:25:00 GMT

it's depressing to me how many people that i run into today that have an excrutiatingly limited knowledge about the command line in windows, and yet they are considered experts on the platform. that aside, i figured i would share a couple of very simple uses.

something people ask a lot is what domain groups they are in. you can run 'net user %username% /domain' from the command line to see what domain groups you are in. it does cut off the names if they are too long, so the ad tools are better or using vbscript to query, but this is quick and easy for general use. there are other uses for this command, but this is the one i use the most often. you can replace %username% with anyone in the domain's ids to view this info. there are some limitations, but it's useful.

another thing that comes up in minor administration tasks is the ability to add a bunch of people at once. you can of course use this with the gui, but if you are already at the command line, you could do something like this on the machine:

net localgroup somegroup /add

net localgroup somegroup user1 user2 user3 user4 user5 /add

the above commands will add somegroup as a localgroup and then add users 1 through 5 to that localgroup. you can add local users or domain users (via domain\user) or domain groups.

if it was a sql box, you could then osql locally and run some commands to add them as some role.

CREATE LOGIN [%whatever your machien name is%\%whatever your local group name is%] FROM WINDOWS WITH DEFAULT_DATABASE=[tempdb]
EXEC master..sp_addsrvrolemember @loginame = N'%whatever your machien name is%\%whatever your local group name is%', @rolename = N'sysadmin' (or whatever role you want to give them)

there you go. how to add a local group, a bunch of users, and even set them up in sql as some role in like 2 minutes from the command line without waiting for stupid computer manager or sql mgmt studio to open. =)

The MOST acronym for strategy planning

David Strommer — Sun, 20 Jul 2008 15:36:00 GMT

I came across an interesting acronym called MOST – Mission, Objectives, Strategies, and Tactics. Original source - http://www.strategy.nildram.co.uk/most.html

MOST helps to clarify where the business intends to go (Mission), they key goals which will help to achieve this (Objectives), analyses what options there are for proceeding forward (Strategies) and how these strategies are going to be put into action (Tactics).

The key is for this whole process to hang together from top to bottom and in reverse. From the top, clarifying the mission drives the objectives, which creates strategic options, which forces tactical actions. From the bottom, every action at tactical level should help to make the strategies work, all strategies should help to achieve the objectives, and all the objectives should take the business towards the mission.

I like easy to remember heuristic acronyms. My three favorite acronyms are KISS, YAGNI and BYOB.

partial trust, permcalc, and caspol, OH MY!

royashbrook — Fri, 18 Jul 2008 15:54:00 GMT

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

System.Security.SecurityExceptions will always be … well some kind of security problem ;)

Running .net exes over the network requires different .net perms. You can see the perms an assembly requires using permcalc from the .net sdk. It used to be called… I think permview.

You can run caspol –rs[g/p] %path to assembly% to see some info on the permissions. G will show you the groups it belongs to in the gac, P will list the permissions.

From: xxxxxxxxxxxxxxx
Sent: Thursday, July 17, 2008 5:40 PM
To: Ashbrook, Roy
Subject: question

Whats up roy… any idea why I’m getting this error xxxxxxxxxxxx

Microsoft Windows XP [Version 5.1.2600]

C:\WINNT>\\server\dotnetapp.exe

Unhandled Exception: System.Security.SecurityException: That assembly does not a

llow partially trusted callers.

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly a

sm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, Secur

ityAction action, Object demand, IPermission permThatFailed)

at ....

Crazy Architects

David Strommer — Sat, 12 Jul 2008 04:33:00 GMT

Pest control is a constant battle especially if you live in Florida. A few months ago, while tending the lawn I noticed several ants. I promptly called the pest control service. I learned later that the ants are known as crazy ants (Paratrechina longicornis) and common in Florida. The name "crazy ant" arises from its characteristics of erratic, rapid movement and not following trails as often as other ants while foraging for food.

Similar to the characteristics of crazy ants, the term crazy architect describes an antipattern of enterprise architects. Instead of foraging for food, crazy architects forage for information in an erratic, rapid and seemingly irrational way. The crazy architect antipattern is typical of an architecture team that does not prescribe to an architectural framework and follow architecture processes.

Are you a crazy architect? If so, you are not alone. Let’s face it; you have to be a bit crazy to be an architect.

A comprehensive analysis of BPM Suites

David Strommer — Sun, 29 Jun 2008 06:47:32 GMT

After months of tweaking and review, our coverage of IBM's BPM technology offering is now live. It joins our coverage of Appian, BEA (we're keeping an eye on this, of course, and will update it as soon as is practical), Lombardi, Software AG and TIBCO.
We've been working on this assessment since the autumn of 2007: the delay is mostly due to the breadth of IBM's portfolio (the assessment report runs to 33 pages, whereas most of the others come in around 20 pages) - combined with the fact that, just as we were about to finalise the report, IBM changed its portfolio positioning, introducing the BPM Suite. Anyhow the effort has been worth it - we think the result is pretty comprehensive and definitely worth reading if you're in the process of selecting a BPM technology vendor.
The IBM BPM assessment report is available as part of our Guest Pass library, here; the detailed comparative scoring information, which you can personalise in line with your preferences and constraints, lives in the online vendor comparison tool that's part of our BPM continuous advisory service. Although this service isn't free, you can get a 7-day free trial, so you can use the tool now to see how IBM stacks up in the context of your own environment and preferences - just fill in this form.
Next up is Pegasystems - the assessment process is already underway.

http://www.mwdadvisors.com/blog/

This work is licensed under a Creative Commons license.

INETA Champs Program

sweisfeld — Sun, 22 Jun 2008 18:24:00 GMT

An XBox or MSDN Subscription to the 10 Most Active Contributors in the User Group Community each quarter.

How? By doing what you are doing already, you stand to win:

a) Valuable Prizes: An MSDN Subscription. And, if you already have one, you can choose an XBox instead.

b) The Fame and Prestige of having an award to hang on your wall that shows that INETA recognizes your contributions to the User Group Community.

c) Official Recognition on the INETA Website for one year.

All of your peers will be able to see that you stand out above the crowd. If the opportunity presents itself for you to show your dedication to the User Group Community in a public way, there is no better way than to show off your name highlighted on the website of a highly respected organization like INETA.

d) A Badge for your website showing that you are a Community Champion.

When folks visit your website, blog or any other place where you publicly post your work, they will see that you are a Community Champion.

Oooohhhh. Recognition by INETA? Valuable Prizes? An award to hang on my wall? A Badge? How can I participate?

Well, I am glad that you asked.

INETA has long been known for it's support of User Groups and this year, there are a number of great new programs supporting the User Group Community. The Community Champs program is one of them. INETA wants to recognize individuals who are demonstrating their involvement in the User Group community. The program is aimed at rewarding those that are the most active with the prizes and award mentioned above. It is INETA's way of recognizing the ones that really bring the community together. So, in short, if you are the kind of person who helps to run user group meetings, codecamps, or helps out in any number of other ways, you should let INETA know the kind of activities that you are involved in. If you are very active, you may be recognized by INETA in a very public and spectacular way for the activities that you currently do to help the user group community.

INETA and Community-Credit are making it happen.

Community Credit has been helping to recognize fellow developers for the past number of years for their accomplishments and INETA has been the mainstay of User Groups for many years, so it is no surprise that the two would be working together to make this great program possible. Best of all, the contributions that you record will also count toward Community Credit prizes, so you may even have a chance to be rewarded with a Geeky, Community Credit prize as an added bonus.

How do I submit my contributions?

Visit the INETA website and go to the Champions section, sign in and let INETA know what you are doing by recording your contributions. The current quarterly period counts for contributions during period of June 30th, 2007 to June 30th, 2008. The final submissions can be made until July 14th. Keep in mind that the end of this current quarter is coming up pretty soon, so if you have been very active over the last year, be sure to enter them soon so that you don't miss this great opportunity.

What are the benefits of participating?

If you are an individual who is always contributing to the User Group Community, you do it because you like it. You don't do it because you expect to be rewarded. At the same time, if you just happen to be rewarded and recognized then that makes it that much better. Imagine playing on an XBox that you received as a thanks for all of your hard work. It makes the games just a little bit more fun. Using your MSDN subscription that you "earned" makes the tools just a little bit better and seeing the award hanging on your wall is a reminder to you and your colleagues just how committed you are.

Can anybody participate?

Unfortunately, the current period (being our first) is for participants in North America only.

File Type and IFILTER Reference For SharePoint Office Server 2007

dbottjer — Thu, 19 Jun 2008 02:11:14 GMT

I see customers expressing more interest in is SharePoint Search. Many clients are shocked to learn SharePoint has a powerful and very configurable search engine built in. Search can crawl anything from network shares to public web sites. Out of the box SharePoint supports the crawling and indexing of many file types which of course include Microsoft Office Documents. However, SharePoint can be made aware of additional file types such as PDF's and DWG's. Furthermore, IFilters can be installed which allow search to index the content contained within files.

For more information concerning supported file types and IFilters read this Technet Article. The article lists all default file types and commonly added file types. There are also links to configuration instructions. This is a good article to keep book marked as a reference.

Technorati Tags: SharePoint Office Server 2007,MOSS 2007,SharePoint Search,MOSS Search,SharePoint File Types,SharePoint IFilters

Disposing of SharePoint Objects

dbottjer — Wed, 18 Jun 2008 04:09:00 GMT

The .NET Framework allows developers to write managed code. The term managed refers to how memory is handled by a computer system. For example, C/C++ code is commonly known as unmanaged because developers must allocate and de-allocate memory. In contrast, the .NET Framework provide a mechanism known as the Garbage Collector (GC) to de-allocate or collect object no longer in use.

I have found it particularly helpful to consider SharePoint a very large ASP.NET Application. If you can visualize SharePoint as an ASP.NET application it becomes familiar and relatable. If you begin to deconstruct SharePoint you will notice that it is comprised of Active Server Pages (ASPX), Master Pages, Style Sheets (CSS), User Controls (ASCX), Server Controls, Web Services (ASMX), Handlers, etc. All these artifacts are common to ASP.NET developers.

SharePoint provides a rich API for developers to program against and extend this large "ASP.NET Application." Two object commonly used in SharePoint development are SPSite and SPWeb. Despite the names of these objects it must be understood that an SPSite object represents a site collection. While an SPWeb object represent a site beneath a site collection. Both of these object are managed wrappers of unmanaged code. Furthermore, both of these object implement IDisposible. So now for the million dollar question... When should you as a developer explicitly dispose of these object? Well, the answer is, it depends.

   1: String title;   2:     3: using(SPSite siteCollection = new SPSite("http://mossdev"))   4: {   5:   using(SPWeb site = siteCollection.OpenWeb())   6:    {   7:        title = site.Title;   8:    }   9: }  

In the example above we created a new SPSite and SPWeb object. We explicitly created a new SPSite object while we called the OpenWeb() method of SPSite to return a new SPWeb Object. Since in effect we created new instances of both object we should dispose of both objects. Note that, wrapping the objects within a using statement will automatically dispose of that object when it is no longer needed. A good example of when not to dispose of an SPSite or SPWeb object is when a reference to an existing object is returned from a method call. For example, the method call SPControl.GetContextSite() will return a reference to an SPSite object we did not create therefore we should not dispose of this particular SPSite object. Disposing of this particular instance will crash current page and possibly the site.

Disposing of SharePoint objects such as SPSite and SPWeb will free both managed and unmanaged resources as soon as possible which will increase system performance and scalability.

Technorati Tags: SharePoint Object Model,SharePoint Object,SPSite,SPWeb,Disposing of SharePoint Objects

count lines with linq

royashbrook — Wed, 11 Jun 2008 14:07:00 GMT

so i needed to count the lines of a bunch of different filetypes in a folder. i decided to try using linq for this. here's what i came up with. basically a single linq statement =P

you can tune the regex to taste. i just said (i think, i'm not a regex wizard) something that isn't a newline (.+) and the line terminators i'm using (\r\n).

using System;
using System.Collections;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace LineCounter
{
    class LineCounter
    {
        static void Main(string[] args)
        {
            // dir to search
            string d = @"c:\pathtocode";

            // regex match for newlines
            string lf = @".+\r\n";

            // get the file list with directory
            string[] f = Directory.GetFiles(d, "*.*", SearchOption.AllDirectories);

            // filter to only get these files
            string filter = ".cs,.resx,.sql,.htm,.html,.xml,.xsl,.xslt";

            // count total lines
            int lines = (
                    from name in f
                    where filter.Contains(Path.GetExtension(name.ToLower()))
                    select new
                    {
                        length = Regex.Matches(
                            new StreamReader(name).ReadToEnd(), lf).Count
                    }
                ).Sum(a => a.length);

            // write out the total lines
            Console.WriteLine(lines);

            Console.ReadLine();
        }
    }
}

directory list with a stack instead of recursion

royashbrook — Tue, 10 Jun 2008 18:41:00 GMT

Stack q = new Stack();
q.Push(argument);
while (q.Count > 0)
{
    string d = q.Pop().ToString();
    Console.WriteLine(d);
    foreach (string sd in Directory.GetDirectories(d))
        q.Push(sd);
}

Generics , Extension Methods, and XML Serialization

sweisfeld — Thu, 05 Jun 2008 15:42:00 GMT

At the Jax SQL Saturday I was asked to turn a .NET object into an xml file and then reverse the process.

Step 1: Serialization with extension methods
C# 3.0 comes with this cool feature called extension methods. This is syntactical sugar for static methods that allows for you to “add” methods to other objects. To write one of these write a static class with a static method that takes the type you want to extend as the first parameter. The only difference from what you would do normally is to add the keyword “this” before the first parameter. The beauty is that it feels very natural for developers to just type the variable name and press dot and they get your new method in the statement completion in VS. (BTW you can still call the method the way you would in C# 2.0) So with the method below we now have added a Serialize method to Object thus it is inherited everywhere in the entire object model!

public static void Serialize(this object o, string filename)
{
    Type t = o.GetType();
    if (t.IsSerializable)
    {
        XmlSerializer serializer = new XmlSerializer(t);
        using (TextWriter tw = new StreamWriter(filename))
        {
            serializer.Serialize(tw, o);
            tw.Close();
        }
    }
    else
    {
        throw new InvalidOperationException(string.Format("Objects of type {0} are not serializable.", t.Name));
    }
}

Step 2: De-serialization with generics
I like generics for the process of de-serializing, this allows my method to return a strongly typed copy of the object that was serialized to the file.

public static T DeSerializeObject<T>(string filename)
{
    T t;
    XmlSerializer serializer = new XmlSerializer(typeof(T));
    using (TextReader tr = new StreamReader(filename))
    {
        t = (T)serializer.Deserialize(tr);
        tr.Close();
    }
    return t;
}

Use the method like this:
Serializer.DeSerializeObject<Customer>("customer.xml")

Customer c = new Customer { FirstName = "Shawn", LastName = "Weisfeld" };
c.Serialize("customer.xml");

C# 3.0 (.NET 3.5) Language Features & Delegates

sweisfeld — Thu, 05 Jun 2008 11:52:00 GMT

Two more questions from the floor at TechEd.
Perhaps the most frequent question at TechEd is “What are the new features of C# 3.0?” Here is the short list:
• Implicitly typed local variables
• Extension methods
• Lambda expressions
• Object and collection initializers
• Anonymous types
• Implicitly typed arrays
• Query expressions
• Expression trees
Check out Anders presentation from last years TechEd here: http://www.microsoft.com/emea/msdn/spotlight/sessionh.aspx?videoid=319
And all the details in this word document:
http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8134b8/CSharp_3.0_Specification.doc or this URL http://msdn.microsoft.com/en-us/library/bb383815.aspx

Also got a question about what a Delegate was. Here is the definition from the documentation. A delegate is a type that references a method. Once a delegate is assigned a method, it behaves exactly like that method. The delegate method can be invoked like any other method, with parameters and a return value. (http://msdn.microsoft.com/en-us/library/ms173171.aspx)

REST Services and Metadata EndPoints in WCF

David Strommer — Thu, 05 Jun 2008 04:26:02 GMT

http://www.danrigsby.com/blog/index.php/2008/05/29/rest-services-and-metadata-endpoints-in-wcf/

Dan Rigsby posted an approach to exposing REST services metadata using mexHttpBinding.

This work is licensed under a Creative Commons license.

Silverlight Deep Zoom (aka SeaDragon)

sweisfeld — Wed, 04 Jun 2008 12:03:00 GMT

In my second in the series of TechEd Questions I have a good one. Unfortunately it had nothing to do with C#, but that doesn’t diminish its cool factor.

The attendee, lets call her Suzie, volunteered to help organization working with an artist that recently passed away catalogue his work on the internet. She wanted a good way to post high resolution images on the web allowing the public to enjoy this persons artistic contribution. The first thing that jumped to mind with the Hard Rock Demo from Mix (http://memorabilia.hardrock.com) you have got to check this out! You can see the scratches on the guitars. Scott Hanselman has a great blog post with a how to if you want to create your own deep zoom images (http://www.hanselman.com/blog/DeepZoomSeadragonSilverlight2MultiScaleImagesAtMix.aspx). Remember to note that this technology is only as good as the original image so you will need good HighRes images to start with.

Building a solution with many projects is SLOWWWWWWW

sweisfeld — Wed, 04 Jun 2008 11:47:00 GMT

This week I am attending TechEd in Orlando FL and I was honored to be requested to work the C# booth by the MVP and C# teams. I got many great questions from attendees and I thought I would post some of the more interesting ones. So here goes.

An attendee, let’s call him Bob, came up and said that his builds were very slow. After some chatting Bob told me that he has a huge number of projects in his solution all “joined” together using project references. This is a convent feature of Visual Studio that provides for “cascading” builds. By that I mean if you change some code in a business library when VS does the build it will build that library first, copy the dll into any projects that reference it, then build those projects. This is very convent but as Bob noted when he changes one line of code in a method it causes the entire project to rebuild. This is not a big deal if the project is small and the builds are quick but if the project is large this could be a very painful experience. So we tossed around some things I would classify as workarounds like for example writing some post build steps that would move dll’s around and the like. But in my opinion the best solution is a properly architected solution. To that end I pointed Bob to an article put together by the P&P group at Microsoft that provides guidance for the architecture of large application with many projects and how to break them up into manageable chunks.

Structuring Projects and Solutions
http://www.codeplex.com/VSTSGuidance/Wiki/View.aspx?title=Chapter%203%20-%20Structuring%20Projects%20and%20Solutions%20in%20Source%20Control

SQL Saturday Jax May 3 2008

sweisfeld — Fri, 02 May 2008 15:50:00 GMT

Tomorrow will be the long awaited third installment of SQL Saturday in Jacksonville FL. I want to thank Brian, Andy and the team for all their hard work putting the event together. If you attended my talk or just want to see some cool stuff about using the CLR in SQL Server check out my presentation (http://cid-e6edc1213d79e105.skydrive.live.com/self.aspx/Public/2008_05_03_SQL_CLR.zip).

Need to delete lots of data, do it in small chunks

sweisfeld — Fri, 25 Apr 2008 18:55:00 GMT

By adding a top clause to your delete statement you can delete a chunk of records at a time. By combining it with a while you can put it in a loop and wipe the entire table.

WHILE EXISTS (SELECT * FROM Foo)

BEGIN

DELETE TOP(100) FROM Foo

END

Had someone send me an email telling me that it might be better to use a TRUNCATE TABLE command then this method, and in most cases this is preferable. Conversly not always do us "evil" developers get TRUNCATE permissions from our DBA's. You can read more about TRUNCATE here (http://msdn2.microsoft.com/en-us/library/aa260621.aspx).

Chage the colors on a button

sweisfeld — Fri, 25 Apr 2008 15:51:00 GMT

Got a question from an attendee at the Orlando Launch Event, he wanted to know how to change the colors of a button from code (i.e. the click event). Both of these items are properties on the button and can be changed easily with the following code.

button1.ForeColor = System.Drawing.Color.Blue;
button1.BackColor = System.Drawing.Color.Yellow;

Forcing Windows Auth on WCF services

sweisfeld — Wed, 23 Apr 2008 13:26:00 GMT

At the last ONETUG meeting a question came up how to force WCF to use Windows Auth (i.e. Kerberos). By default WCF is in negotiate mode. While this is good for many cases where you cannot ensure that Kerberos will be available if you are in an intranet environment where you know it will be you can speed up your service by skipping the negotiation phase. Matevz Gacnik has a good sample in his blog (http://www.request-response.com/blog/PermaLink,guid,4b5f46cd-3c15-4213-9570-1a235c4a615e.aspx) using certificates, the only change is to set the clientCredentialType to “Windows”.

It Channel News: MVP Summit Interview

dbottjer — Tue, 22 Apr 2008 03:06:00 GMT

Last week I was in Redmond, WA from April 13th to April 18th attending the Microsoft MVP Global Summit. This was my second year attending the event and it was quite amazing. Some of the highlights included participating in Open Spaces Sessions and seeing both Ray Ozzie and Steve Ballmer speak. Steve is quite energetic to say the least.

A few days prior to the event I received a phone call from SearchITChannel.com's, senior news editor, Barbara Darrow wanting to interview me about being an MVP and the up coming summit. I was flattered by her call and took time to answer her questions. On Monday the 14th she sent me this link http://searchitchannel.techtarget.com/news/article/0,289142,sid96_gci1309724,00.html to her story in which I am quoted. The story includes quotes from several fellow MVP's including Shane Young.

I have really enjoyed attending the MVP summit twice now because it is an opportunity for MVP's to give Microsoft direct product feedback and boy do we. The event is also a great opportunity to network with other MVP's and Microsoft Employees.

Technorati Tags: MVP,MVP SUMMIT,SearchITChannel

Drop Me!

sweisfeld — Sat, 12 Apr 2008 22:03:00 GMT

I was trying to get rid of all the objects (tables, procedures, views) in my database and whipped up this little script and while it is far from perfect it is well worth sharing. . .

SELECT
CASE WHEN type = 'P' THEN 'DROP PROCEDURE ' + name
      WHEN type = 'U' THEN 'DROP TABLE ' + name
   WHEN type = 'V' THEN 'DROP VIEW ' + name
      WHEN type = 'FN' THEN 'DROP FUNCTION ' + name
    ELSE '' END AS dropSQL, *
FROM sys.objects
WHERE type != 'S' AND type != 'IT' AND type != 'SQ'

Free book samples from MS Press

David Strommer — Wed, 26 Mar 2008 20:23:00 GMT

· Linq

· Ajax

· Silverlight

http://csna01.libredigital.com/

how can i get some performance statistics on my stored procedures in sql2005?

royashbrook — Fri, 21 Mar 2008 20:57:00 GMT

select
    case when dbid = 32767
        then 'resource'
        else db_name(dbid)
    end [db_name]
    , object_schema_name(objectid,dbid) [schema_name]
    , object_name(objectid,dbid) [object_name]
    , sum(usecounts) [executions]
    , max(max_execution_time) [last_execution_time]
    , sum(total_logical_reads) [total_logical_reads]
    , sum(total_logical_reads) / sum(usecounts) * 1.0 [avg_logical_reads]
    , sum(total_elapsed_time) / 1000 [total_elapsed_time_ms]
    , ( sum(total_elapsed_time) / sum(usecounts) * 1.0 ) / 1000 [avg_elapsed_time_ms]
from
    sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) qt
    join (
        select
            plan_handle
            , max(last_execution_time) [max_execution_time]
            , sum(total_elapsed_time) [total_elapsed_time]
            , sum(total_logical_reads) [total_logical_reads]
        from
            sys.dm_exec_query_stats
        group by
            plan_handle
        ) r on r.plan_handle = cp.plan_handle
where
    objtype = 'Proc'
    and qt.text LIKE '%create%proc%'
    --and db_name(dbid) = 'some_database'
    --and object_name(objectid,dbid) = 'some_stored_procedure'
group by
    dbid, objectid
order by
    db_name(dbid), object_name(objectid,dbid)

thanks to this article for the inspiration.

SQL Server 2005 Emergency Diagnostic and Performance Queries

royashbrook — Fri, 21 Mar 2008 19:19:00 GMT

Great useful base diag scripts! Snipped from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry

-- SQL Server 2005 Emergency Diagnostic and Performance Queries
-- Glenn Berry 3-17-2008
      

-- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of time?
-- If yes, run HIGH CPU queries below:

-- Step 2 - Check Performance Monitor
-- SQL Server Buffer Manager: Buffer Cache Hit Ratio and Page Life Expectancy
-- SQL Server Memory Manager: Memory Grants Pending and Memory Grants Pending
-- Physical Disk: Avg disk sec/Read and Avg disk sec/Write

-- Step 3 - Check for locking, blocking and missing indexes
-- Run the BLOCKING queries below: 

-- Step 4 - Is the transaction log full?
-- Run the TRANSACTION LOG FULL queries below:

-- Step 5 - Check for IO Problems
-- Run the IO ISSUES queries below



    -- HIGH CPU *******
    -- Get the most CPU intensive queries
    SET NOCOUNT ON;

    DECLARE @SpID smallint
    DECLARE spID_Cursor CURSOR
    FAST_FORWARD FOR
    
    SELECT TOP 25 spid
    FROM master..sysprocesses
    WHERE status = 'runnable'
    AND spid > 50   -- Eliminate system SPIDs
    AND spid <> @@SPID
    ORDER BY CPU DESC

    OPEN spID_Cursor

    FETCH NEXT FROM spID_Cursor
    INTO @spID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Spid #:' + STR(@spID)
            EXEC ('DBCC INPUTBUFFER (' + @spID + ')')

               FETCH NEXT FROM spID_Cursor
            INTO @spID
        END

    -- Close and deallocate the cursor
    CLOSE spID_Cursor
    DEALLOCATE spID_Cursor


      -- HIGH CPU *******
      -- Isolate top waits for server instance
      WITH Waits AS
      (
        SELECT
            wait_type,
            wait_time_ms / 1000. AS wait_time_s,
            100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
            ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
        FROM sys.dm_os_wait_stats
        WHERE wait_type NOT LIKE '%SLEEP%'
      )
      SELECT
        W1.wait_type, 
        CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
        CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
        CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
      FROM Waits AS W1
      INNER JOIN Waits AS W2
      ON W2.rn <= W1.rn
      GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
      HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
      ORDER BY W1.rn;

      -- HIGH CPU *******
      -- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
      SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
           '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
      FROM sys.dm_os_wait_stats;

      -- HIGH CPU *******
      -- Check SQL Server Schedulers to see if they are waiting on CPU (runnable tasks above 10 is very bad)
      SELECT scheduler_id, current_tasks_count, runnable_tasks_count
      FROM sys.dm_os_schedulers
      WHERE scheduler_id < 255

      -- HIGH CPU *******
      --  Who is running what at this instant 
      SELECT st.text AS [Command text], login_time, [host_name], 
      [program_name], sys.dm_exec_requests.session_id, client_net_address,
      sys.dm_exec_requests.status, command, db_name(database_id) AS DatabaseName
      FROM sys.dm_exec_requests 
      INNER JOIN sys.dm_exec_connections 
      ON sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
      INNER JOIN sys.dm_exec_sessions 
      ON sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE sys.dm_exec_requests.session_id >= 51
      AND sys.dm_exec_requests.session_id <> @@spid
      ORDER BY sys.dm_exec_requests.status

      -- HIGH CPU *******
      -- Get a snapshot of current activity
      SELECT LTRIM (st.text) AS 'Command Text',[host_name], der.session_id AS 'SPID',
      der.status, db_name(database_id) AS DatabaseName, ISNULL(der.wait_type, 'None')AS 'Wait Type', der.logical_reads 
      FROM sys.dm_exec_requests AS der
      INNER JOIN sys.dm_exec_connections AS dexc
      ON der.session_id = dexc.session_id
      INNER JOIN sys.dm_exec_sessions AS dexs
      ON dexs.session_id = der.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE der.session_id >= 51
      AND der.session_id <> @@spid
      ORDER BY der.status

      -- BLOCKING ************
      -- Detect blocking
      SELECT blocked_query.session_id AS blocked_session_id,
      blocking_query.session_id AS blocking_session_id,
      sql_text.text AS blocked_text, sql_btext.text AS blocking_text, waits.wait_type AS blocking_resource
      FROM sys.dm_exec_requests AS blocked_query
      INNER JOIN sys.dm_exec_requests AS blocking_query 
      ON blocked_query.blocking_session_id = blocking_query.session_id
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
      ) sql_btext
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
      ) sql_text
      INNER JOIN sys.dm_os_waiting_tasks AS waits 
      ON waits.session_id = blocking_query.session_id

      -- BLOCKING ************
      -- Index Contention
      SELECT dbid=database_id, objectname=object_name(s.object_id),
      indexname=i.name, i.index_id, row_lock_count, row_lock_wait_count,
      [block %]= CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)),
      row_lock_wait_in_ms,
      [avg row lock waits in ms]= CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2))
      FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) AS s
      INNER JOIN sys.indexes AS i
      ON i.object_id = s.object_id
      WHERE objectproperty(s.object_id,'IsUserTable') = 1
      AND i.index_id = s.index_id
      ORDER BY row_lock_wait_count DESC

      -- TRANSACTION LOG FULL *****
      -- Find the log reuse description for the transaction log
      SELECT name, database_id, log_reuse_wait_desc 
      FROM sys.databases

      -- TRANSACTION LOG FULL *****
      -- Individual File Size query
      SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
      size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
      FROM sys.database_files;

      -- IO ISSUES *****************
      -- Analyze DB IO, ranked by IO Stall %
      WITH DBIO AS
      (
        SELECT
            DB_NAME(IVFS.database_id) AS db,
            CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
            SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
            SUM(IVFS.io_stall) AS io_stall
        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
            JOIN sys.master_files AS MF
              ON IVFS.database_id = MF.database_id
              AND IVFS.file_id = MF.file_id
        GROUP BY DB_NAME(IVFS.database_id), MF.type
      )
      SELECT db, file_type, 
        CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
        CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
        CAST(100. * io_stall / SUM(io_stall) OVER()
               AS DECIMAL(10, 2)) AS io_stall_pct,
        ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
      FROM DBIO
      ORDER BY io_stall DESC;

      -- HIGH CPU ************
      -- Get Top 100 executed SP's ordered by execution count
      SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
      qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.total_worker_time AS 'TotalWorkerTime',
      qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
      qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.execution_count DESC

      -- HIGH CPU *************
      -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)
      SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.execution_count AS 'Execution Count', 
      ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
      ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
      qs.max_logical_reads, qs.max_logical_writes, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.total_worker_time DESC

how can i find when a stored procedure was last executed in sql 2005?

royashbrook — Fri, 21 Mar 2008 18:22:00 GMT

select top 10
    t.text
    , s.last_execution_time
    , *
from
    sys.dm_exec_query_stats s
    cross apply sys.dm_exec_sql_text(s.sql_handle) t
where
    t.objectid is not null
    and text like '%procname%'
order by
    s.last_execution_time desc

obviously replace procname with the procname you want to find. you might get multiple results, and you can probably clean it up some, but it works fine for what i generally need to get the info for. while i was researching this, i also came accross this pretty cool query:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) AS 'Calls/Minute',
qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()), 1) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) DESC

quoted from: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!248.entry